---
phase: 11-catalog-database-view-ux-legacy-consolidation
plan: 02
type: execute
wave: 2
depends_on: ["11-01"]
files_modified:
- src/lib/admin-queries.ts
- src/app/admin/catalog/actions.ts
autonomous: true
requirements: [OFFER-07, OFFER-08, OFFER-09]
must_haves:
truths:
- "getAllServices() returns each service together with its assigned tag names"
- "An admin-only server action exists to update any single editable field of a service (name, description, category, unit_price, active)"
- "An admin-only server action exists to add/remove a tag from a service, scoped to entity_type='services', creating the tag row if it doesn't exist"
- "An admin-only server action exists to quick-add a new service with unit_price=0 from just a name"
artifacts:
- path: "src/lib/admin-queries.ts"
provides: "ServiceWithTags type + getAllServices() returning Service & { tags: string[] }"
contains: "export type ServiceWithTags"
- path: "src/app/admin/catalog/actions.ts"
provides: "updateServiceField, addTagToService, removeTagFromService, quickAddService server actions"
exports: ["updateServiceField", "addTagToService", "removeTagFromService", "quickAddService", "createService", "updateService", "toggleServiceActive"]
key_links:
- from: "src/app/admin/catalog/actions.ts"
to: "src/db/schema.ts (tags table)"
via: "drizzle insert/delete with entity_type='services'"
pattern: "entity_type.*services"
- from: "src/lib/admin-queries.ts getAllServices"
to: "src/db/schema.ts (tags table)"
via: "leftJoin on tags where entity_type='services' and entity_id=services.id"
pattern: "leftJoin\\(tags"
---
Extend the catalog query layer and server actions to support the database-view UX: `getAllServices()` now returns each service's assigned tags, and four new server actions (`updateServiceField`, `addTagToService`, `removeTagFromService`, `quickAddService`) provide the inline-edit, tag-assignment, and quick-add primitives that Plans 03-04 wire into the UI.
Purpose: Interface-first foundation — Plan 03 (EditableCell/TagMultiSelect components) and Plan 04 (ServiceTable rewrite) import `ServiceWithTags` and these four actions directly, with no further query-layer changes needed.
Output:
- `src/lib/admin-queries.ts`: `ServiceWithTags` type + rewritten `getAllServices()`
- `src/app/admin/catalog/actions.ts`: 4 new server actions, all behind `requireAdmin()`, all calling `revalidatePath("/admin/catalog")`
@$HOME/.claude/get-shit-done/workflows/execute-plan.md
@$HOME/.claude/get-shit-done/templates/summary.md
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/11-catalog-database-view-ux-legacy-consolidation/11-CONTEXT.md
@.planning/DESIGN-SYSTEM.md
```typescript
export const tags = pgTable("tags", {
id: text("id").primaryKey().$defaultFn(() => nanoid()),
entity_type: text("entity_type").notNull(), // "services" | "leads"
entity_id: text("entity_id").notNull(),
name: text("name").notNull(),
created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});
export type Tag = typeof tags.$inferSelect;
export type NewTag = typeof tags.$inferInsert;
export const services = pgTable("services", {
id: text("id").primaryKey().$defaultFn(() => nanoid()),
name: text("name").notNull(),
description: text("description"),
unit_price: numeric("unit_price", { precision: 10, scale: 2 }).notNull(),
category: text("category"),
active: boolean("active").notNull().default(true),
migrated_from: text("migrated_from"),
migrated_id: text("migrated_id"),
created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});
export type Service = typeof services.$inferSelect;
export type NewService = typeof services.$inferInsert;
```
```typescript
export async function getAllServices(): Promise {
return db
.select()
.from(services)
.orderBy(asc(services.name));
}
```
```typescript
import { db } from "@/db";
import {
clients, projects, payments, phases, tasks, deliverables, comments, documents,
notes, time_entries, quote_items, service_catalog, services, settings,
offer_micros, offer_macros, project_offers, offer_phases, offer_phase_services,
quotes, leads,
} from "@/db/schema";
import { eq, inArray, asc, isNull, sql, and } from "drizzle-orm";
import type {
Client, Project, Phase, Task, Deliverable, Payment, Document, Note, Comment,
ServiceCatalog, Service, OfferMicro, OfferMacro, ProjectOffer, OfferPhase,
OfferPhaseService, Quote, Lead,
} from "@/db/schema";
```
```typescript
"use server";
import { db } from "@/db";
import { services } from "@/db/schema";
import { revalidatePath } from "next/cache";
import { eq } from "drizzle-orm";
import { z } from "zod";
import { getServerSession } from "next-auth";
import { authOptions } from "@/lib/auth";
const serviceSchema = z.object({
name: z.string().min(1, "Nome richiesto"),
description: z.string().optional(),
unit_price: z.coerce.number().min(0.01, "Prezzo deve essere maggiore di 0"),
category: z.string().optional(),
});
async function requireAdmin() {
const session = await getServerSession(authOptions);
if (!session) throw new Error("Non autorizzato");
}
export async function createService(formData: FormData) { ... }
export async function updateService(serviceId: string, formData: FormData) { ... }
export async function toggleServiceActive(serviceId: string, active: boolean) { ... }
```
Task 1: Extend getAllServices() with tags join (ServiceWithTags)
src/lib/admin-queries.ts
src/lib/admin-queries.ts (full imports block lines 1-45; getAllServices at lines 359-364; existing leftJoin pattern for quote_items/service_catalog at lines 332-345 as a syntax reference for leftJoin + grouping)
src/db/schema.ts (tags table from Plan 01; services table)
- Test 1 (manual/typecheck): `getAllServices()` return type is `Promise` where `ServiceWithTags = Service & { tags: string[] }`
- Test 2: A service with 2 tags ("Offerta", "Premium") returns `tags: ["Offerta", "Premium"]` (order by `tags.name asc`)
- Test 3: A service with 0 tags returns `tags: []` (not `[null]` or `[""]` — left join nulls must be filtered out)
- Test 4: Services remain ordered by `services.name asc` regardless of tag count
1. Add `tags` to the import from `@/db/schema` (extend the existing destructured import on lines 2-24 — add `tags` to the list alongside `services`).
2. Add `and` is already imported from `drizzle-orm` (line 25) — no change needed there.
3. Immediately before the existing `getAllServices` function (line 359), add the new type:
```typescript
// ── ServiceWithTags — services + assigned tag names (Phase 11 database-view) ──
export type ServiceWithTags = Service & { tags: string[] };
```
4. Replace the body of `getAllServices` (lines 359-364) with:
```typescript
export async function getAllServices(): Promise {
const rows = await db
.select({
id: services.id,
name: services.name,
description: services.description,
unit_price: services.unit_price,
category: services.category,
active: services.active,
migrated_from: services.migrated_from,
migrated_id: services.migrated_id,
created_at: services.created_at,
tag_name: tags.name,
})
.from(services)
.leftJoin(
tags,
and(eq(tags.entity_type, "services"), eq(tags.entity_id, services.id))
)
.orderBy(asc(services.name), asc(tags.name));
const serviceMap = new Map();
for (const row of rows) {
const { tag_name, ...serviceFields } = row;
if (!serviceMap.has(row.id)) {
serviceMap.set(row.id, { ...serviceFields, tags: [] });
}
if (tag_name) {
serviceMap.get(row.id)!.tags.push(tag_name);
}
}
return Array.from(serviceMap.values());
}
```
5. `getClientFullDetail` and `getProjectFullDetail` both have an `activeServices: Service[]` field populated from `db.select().from(services).where(eq(services.active, true))...` (lines ~251-255 and ~542) — these queries are UNCHANGED in this plan (they don't need tags; they feed the project workspace service-assignment dropdown, out of scope for Phase 11 per CONTEXT.md deferred items). Do NOT modify these.
npx tsc --noEmit 2>&1 | grep -i "admin-queries" ; echo "exit: $?"
- `grep -c "export type ServiceWithTags = Service & { tags: string\[\] }" src/lib/admin-queries.ts` returns `1`
- `grep -c "export async function getAllServices(): Promise" src/lib/admin-queries.ts` returns `1`
- `grep -c "leftJoin(\s*tags" src/lib/admin-queries.ts` (or `leftJoin(\n tags`) returns >= 1 — verify with `grep -A1 "leftJoin(" src/lib/admin-queries.ts | grep -c tags`
- `grep -c "tags," src/lib/admin-queries.ts` >= 1 in the import block (line 2-24 region)
- `npx tsc --noEmit` produces zero errors referencing `src/lib/admin-queries.ts`
- `getClientFullDetail` and `getProjectFullDetail` function bodies are byte-for-byte unchanged except for surrounding context (verify via `git diff src/lib/admin-queries.ts` shows no changes inside those two functions)
`getAllServices()` returns `ServiceWithTags[]` with each service's tags as a string array (empty array when no tags), ordered by service name then tag name. Typecheck passes.
Task 2: Add inline-edit, tag, and quick-add server actions
src/app/admin/catalog/actions.ts
src/app/admin/catalog/actions.ts (full file — requireAdmin pattern lines 18-21, serviceSchema lines 11-16, existing CRUD actions lines 23-67)
src/db/schema.ts (tags table from Plan 01)
.planning/phases/11-catalog-database-view-ux-legacy-consolidation/11-CONTEXT.md (D-06, D-08, D-12)
- Test 1: `updateServiceField(id, "name", "New Name")` updates only the `name` column, calls `revalidatePath("/admin/catalog")`
- Test 2: `updateServiceField(id, "unit_price", "150.5")` validates as a positive number, stores as `"150.50"` (2 decimals)
- Test 3: `updateServiceField(id, "name", "")` throws `"Nome richiesto"` (required field validation)
- Test 4: `updateServiceField(id, "active", false)` updates the boolean `active` column
- Test 5: `addTagToService(serviceId, "Premium")` inserts a row into `tags` with `entity_type="services"`, `entity_id=serviceId`, `name="Premium"` (trimmed); calling it again with the same name does NOT create a duplicate (`onConflictDoNothing`)
- Test 6: `addTagToService(serviceId, " ")` throws (empty/whitespace tag name rejected)
- Test 7: `removeTagFromService(serviceId, "Premium")` deletes the matching row from `tags` scoped to `entity_type="services"` and `entity_id=serviceId`
- Test 8: `quickAddService("Nuovo servizio")` inserts a `services` row with `unit_price="0.00"`, `active=true`, `migrated_from=null`
- Test 9: `quickAddService("")` throws `"Nome richiesto"`
- Test 10: All 4 new actions call `requireAdmin()` first (throw `"Non autorizzato"` when no session)
1. Update imports at the top of `src/app/admin/catalog/actions.ts`:
```typescript
"use server";
import { db } from "@/db";
import { services, tags } from "@/db/schema";
import { revalidatePath } from "next/cache";
import { eq, and } from "drizzle-orm";
import { z } from "zod";
import { getServerSession } from "next-auth";
import { authOptions } from "@/lib/auth";
```
2. Keep the existing `serviceSchema`, `requireAdmin`, `createService`, `updateService`, `toggleServiceActive` UNCHANGED (lines 11-67).
3. Append the following 4 new server actions at the end of the file.
**updateServiceField** — single-field inline edit, used by `EditableCell` (Plan 03/04):
```typescript
const EDITABLE_FIELDS = ["name", "description", "category", "unit_price", "active"] as const;
type EditableField = (typeof EDITABLE_FIELDS)[number];
export async function updateServiceField(
serviceId: string,
fieldName: EditableField,
value: string | boolean
) {
await requireAdmin();
if (!EDITABLE_FIELDS.includes(fieldName)) {
throw new Error(`Campo non editabile: ${fieldName}`);
}
if (fieldName === "name") {
const s = String(value).trim();
if (s.length === 0) throw new Error("Nome richiesto");
await db.update(services).set({ name: s }).where(eq(services.id, serviceId));
} else if (fieldName === "description") {
const s = String(value).trim();
await db.update(services).set({ description: s || null }).where(eq(services.id, serviceId));
} else if (fieldName === "category") {
const s = String(value).trim();
await db.update(services).set({ category: s || null }).where(eq(services.id, serviceId));
} else if (fieldName === "unit_price") {
const num = parseFloat(String(value));
if (isNaN(num) || num < 0) throw new Error("Prezzo invalido");
await db.update(services).set({ unit_price: num.toFixed(2) }).where(eq(services.id, serviceId));
} else if (fieldName === "active") {
const boolValue = typeof value === "boolean" ? value : value === "true";
await db.update(services).set({ active: boolValue }).where(eq(services.id, serviceId));
}
revalidatePath("/admin/catalog");
}
```
Note: `unit_price` allows `0` here (>= 0, not > 0 like `serviceSchema`) — this is intentional per D-12 (quick-add creates services with `unit_price=0`, and the user must be able to leave it at 0 or correct it inline without hitting the `0.01` minimum from the create form).
**addTagToService / removeTagFromService** — tag assignment scoped to `entity_type="services"` (D-06), used by `TagMultiSelect` (Plan 03/04):
```typescript
export async function addTagToService(serviceId: string, tagName: string) {
await requireAdmin();
const trimmed = tagName.trim();
if (trimmed.length === 0) throw new Error("Nome tag richiesto");
await db
.insert(tags)
.values({
entity_type: "services",
entity_id: serviceId,
name: trimmed,
})
.onConflictDoNothing();
revalidatePath("/admin/catalog");
}
export async function removeTagFromService(serviceId: string, tagName: string) {
await requireAdmin();
await db
.delete(tags)
.where(
and(
eq(tags.entity_type, "services"),
eq(tags.entity_id, serviceId),
eq(tags.name, tagName)
)
);
revalidatePath("/admin/catalog");
}
```
Note on `onConflictDoNothing()`: this relies on the unique index `tags_entity_name_unique` on `(entity_type, entity_id, name)` created in Plan 01. Drizzle's `.onConflictDoNothing()` without a `target` argument falls back to "do nothing on any conflict" — verify this compiles; if Drizzle requires an explicit target for this version, use `.onConflictDoNothing({ target: [tags.entity_type, tags.entity_id, tags.name] })`.
**quickAddService** — OFFER-09 quick-add row, used by `ServiceTable` (Plan 04):
```typescript
export async function quickAddService(name: string) {
await requireAdmin();
const trimmed = name.trim();
if (trimmed.length === 0) throw new Error("Nome richiesto");
await db.insert(services).values({
name: trimmed,
unit_price: "0.00",
active: true,
});
revalidatePath("/admin/catalog");
}
```
Per D-12: `unit_price="0.00"` is intentional — the row becomes a normal editable row immediately after creation, and the user corrects the price inline via `updateServiceField`.
npx tsc --noEmit 2>&1 | grep -i "actions.ts" ; echo "exit: $?"
- `grep -c "export async function updateServiceField" src/app/admin/catalog/actions.ts` returns `1`
- `grep -c "export async function addTagToService" src/app/admin/catalog/actions.ts` returns `1`
- `grep -c "export async function removeTagFromService" src/app/admin/catalog/actions.ts` returns `1`
- `grep -c "export async function quickAddService" src/app/admin/catalog/actions.ts` returns `1`
- `grep -c "await requireAdmin()" src/app/admin/catalog/actions.ts` returns `7` (3 existing + 4 new)
- `grep -c "entity_type: \"services\"" src/app/admin/catalog/actions.ts` returns `2` (addTagToService insert + removeTagFromService where)
- `grep -c "revalidatePath(\"/admin/catalog\")" src/app/admin/catalog/actions.ts` returns `7` (3 existing + 4 new)
- `npx tsc --noEmit` produces zero errors referencing `src/app/admin/catalog/actions.ts`
- Existing `createService`, `updateService`, `toggleServiceActive` exports remain present and unchanged (`grep -c "export async function createService\|export async function updateService\|export async function toggleServiceActive" src/app/admin/catalog/actions.ts` returns `3`)
Four new server actions exist, each calling `requireAdmin()` and `revalidatePath("/admin/catalog")`. Tag operations are scoped to `entity_type="services"`. `updateServiceField` validates per-field (required name, non-negative price). `quickAddService` creates a `unit_price="0.00"` row. Typecheck passes.
## Trust Boundaries
| Boundary | Description |
|----------|--------------|
| Admin browser -> Server Actions | All 4 new actions are Next.js Server Actions invoked from `/admin/catalog` client components; session-authenticated |
| Server Actions -> Postgres | Drizzle queries with user-supplied field values (tag names, service field values) |
## STRIDE Threat Register
| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-11-06 | Spoofing/Elevation of Privilege | `updateServiceField`, `addTagToService`, `removeTagFromService`, `quickAddService` | mitigate | Every action calls `await requireAdmin()` as its first statement, throwing `"Non autorizzato"` if no Auth.js session exists — identical to existing `createService`/`updateService`/`toggleServiceActive` pattern. |
| T-11-07 | Tampering | `updateServiceField` field whitelist | mitigate | `EDITABLE_FIELDS` is a closed TypeScript union (`"name" \| "description" \| "category" \| "unit_price" \| "active"`) checked at runtime via `EDITABLE_FIELDS.includes(fieldName)` — prevents arbitrary column writes via a crafted `fieldName` string from a compromised client bundle. |
| T-11-08 | Tampering | `addTagToService`/`removeTagFromService` `entity_type` | mitigate | `entity_type` is hardcoded to the literal `"services"` in both actions — never accepted as a parameter from the client — so a crafted call cannot write/delete tags for `entity_type="leads"` (Phase 14's future pool) via this catalog action set. |
| T-11-09 | Tampering | SQL injection via tag name / field values | accept | All values pass through Drizzle's parameterized query builder (`.values()`, `.set()`, `eq()`) — no raw `sql` string interpolation of user input in this plan's actions. |
| T-11-10 | Denial of Service | Duplicate tag inserts | mitigate | `onConflictDoNothing()` on the `(entity_type, entity_id, name)` unique index (from Plan 01) makes `addTagToService` idempotent — no unbounded duplicate rows from repeated clicks. |
All HIGH-severity items (T-11-06, T-11-07, T-11-08) are mitigated.
1. `npx tsc --noEmit` passes with zero errors in `src/lib/admin-queries.ts` and `src/app/admin/catalog/actions.ts`
2. `getAllServices()` callable from a server component and returns `ServiceWithTags[]`
3. All 4 new actions present, admin-gated, and exported
- `ServiceWithTags` type + `getAllServices()` join with `tags` implemented and typechecked
- `updateServiceField`, `addTagToService`, `removeTagFromService`, `quickAddService` implemented, admin-gated, revalidating `/admin/catalog`
- Tag operations strictly scoped to `entity_type="services"` (D-06 enforced at the action layer)
- Existing catalog actions (`createService`, `updateService`, `toggleServiceActive`) unchanged