simone
d1b1047368
Catalog Database-View UX & Legacy Consolidation (OFFER-07/08/09/10/13) - 11-01: tags table + polymorphic junction + additive legacy migration - 11-02: getAllServices tag join + inline-edit/tag/quick-add server actions - 11-03: EditableCell + TagMultiSelect components - 11-04: database-view ServiceTable + client-side search Verified by gsd-plan-checker (VERIFICATION PASSED). Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
463 lines
26 KiB
Markdown
463 lines
26 KiB
Markdown
---
|
|
phase: 11-catalog-database-view-ux-legacy-consolidation
|
|
plan: 01
|
|
type: execute
|
|
wave: 1
|
|
depends_on: []
|
|
files_modified:
|
|
- src/db/schema.ts
|
|
- src/db/migrations/0002_add_tags_table.sql
|
|
- src/db/migrations/meta/_journal.json
|
|
- scripts/push-11-tags-migration.ts
|
|
- scripts/migrate-tags.ts
|
|
- scripts/validate-tags-migration.ts
|
|
autonomous: true
|
|
requirements: [OFFER-13]
|
|
|
|
must_haves:
|
|
truths:
|
|
- "La tabella `tags` esiste nel DB locale con junction polimorfica (entity_type/entity_id)"
|
|
- "Le righe storiche di service_catalog e offer_services sono confluite in services (migrated_from popolato), verificabile via conteggio righe"
|
|
- "Le righe migrate da offer_services hanno il tag 'Offerta' assegnato in tags"
|
|
- "Nessuna riga di clients/projects/payments/phases/service_catalog/offer_services/offer_micro_services è stata droppata o troncata"
|
|
artifacts:
|
|
- path: "src/db/schema.ts"
|
|
provides: "tags pgTable (id, entity_type, entity_id, name, created_at) + Tag/NewTag types"
|
|
contains: "export const tags = pgTable(\"tags\""
|
|
- path: "src/db/migrations/0002_add_tags_table.sql"
|
|
provides: "Drizzle-generated CREATE TABLE tags migration"
|
|
contains: "CREATE TABLE"
|
|
- path: "scripts/push-11-tags-migration.ts"
|
|
provides: "Idempotent local migration runner for tags table"
|
|
contains: "CREATE TABLE IF NOT EXISTS"
|
|
- path: "scripts/migrate-tags.ts"
|
|
provides: "Assigns 'Offerta' tag to services rows where migrated_from='offer_services'"
|
|
contains: "Offerta"
|
|
- path: "scripts/validate-tags-migration.ts"
|
|
provides: "Row-count + orphan validation for tags + services consolidation (OFFER-13)"
|
|
contains: "ALL CHECKS PASSED"
|
|
key_links:
|
|
- from: "scripts/migrate-tags.ts"
|
|
to: "src/db/schema.ts (tags, services)"
|
|
via: "drizzle insert/select on tags + services.migrated_from"
|
|
pattern: "migrated_from.*offer_services"
|
|
- from: "scripts/push-11-tags-migration.ts"
|
|
to: "src/db/migrations/0002_add_tags_table.sql"
|
|
via: "reads and executes the generated SQL idempotently"
|
|
pattern: "0002_add_tags_table"
|
|
---
|
|
|
|
<objective>
|
|
Add the polymorphic `tags` table to the schema (foundation for OFFER-08, reused by Phase 14 for CRM-09), generate and apply its migration to the local dev database, and complete the additive legacy consolidation (OFFER-13) by running the existing Phase 7 migration/validation scripts plus a new tag-assignment script that marks every service migrated from `offer_services` with the "Offerta" tag (D-02).
|
|
|
|
Purpose: This is the schema/data foundation Plans 02-04 build on. Without the `tags` table existing locally, the query layer (Plan 02) and UI (Plans 03-04) cannot be typechecked or tested against a live DB. Without the consolidation scripts running, OFFER-13 ("dati storici confluiti senza perdita") is not satisfied.
|
|
|
|
Output:
|
|
- `tags` table + Drizzle types in `src/db/schema.ts`
|
|
- Generated migration `src/db/migrations/0002_add_tags_table.sql`
|
|
- `scripts/push-11-tags-migration.ts` (idempotent, additive-only)
|
|
- `scripts/migrate-tags.ts` + `scripts/validate-tags-migration.ts`
|
|
- Local dev DB updated: `tags` table exists, `services` rows from `service_catalog`/`offer_services` are present with `migrated_from` set, and `migrated_from='offer_services'` rows carry the "Offerta" tag
|
|
</objective>
|
|
|
|
<execution_context>
|
|
@$HOME/.claude/get-shit-done/workflows/execute-plan.md
|
|
@$HOME/.claude/get-shit-done/templates/summary.md
|
|
</execution_context>
|
|
|
|
<context>
|
|
@.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
|
|
</context>
|
|
|
|
<interfaces>
|
|
<!-- Polymorphic junction precedent — comments table in src/db/schema.ts (lines 100-111) -->
|
|
```typescript
|
|
export const comments = pgTable("comments", {
|
|
id: text("id")
|
|
.primaryKey()
|
|
.$defaultFn(() => nanoid()),
|
|
entity_type: text("entity_type").notNull(), // task | deliverable
|
|
entity_id: text("entity_id").notNull(),
|
|
author: text("author").notNull(), // client | admin
|
|
body: text("body").notNull(),
|
|
created_at: timestamp("created_at", { withTimezone: true })
|
|
.notNull()
|
|
.defaultNow(),
|
|
});
|
|
```
|
|
|
|
<!-- services table audit columns (src/db/schema.ts lines 183-195) — migrated_from/migrated_id already exist -->
|
|
```typescript
|
|
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"), // "service_catalog" | "offer_services" | null
|
|
migrated_id: text("migrated_id"),
|
|
created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
|
|
});
|
|
```
|
|
|
|
<!-- Existing Phase 7 migration scripts — scripts/migrate-services.ts (idempotent, already implements OFFER-13 row-copy for service_catalog + offer_services -> services) -->
|
|
<!-- scripts/validate-services-migration.ts already checks row counts + orphan refs for this consolidation -->
|
|
<!-- These scripts are RUN (not rewritten) by Task 2 of this plan, since they were written in Phase 7 but the
|
|
consolidation was deferred to Phase 11 (per ROADMAP Phase 7 status note). -->
|
|
</interfaces>
|
|
|
|
<tasks>
|
|
|
|
<task type="auto">
|
|
<name>Task 1: Add `tags` table to schema, generate migration, push to local DB [BLOCKING]</name>
|
|
<files>
|
|
src/db/schema.ts
|
|
src/db/migrations/0002_add_tags_table.sql (generated by drizzle-kit)
|
|
src/db/migrations/meta/_journal.json (updated by drizzle-kit)
|
|
scripts/push-11-tags-migration.ts
|
|
</files>
|
|
<read_first>
|
|
src/db/schema.ts (existing comments table at lines 100-111 for the polymorphic pattern; services table at lines 183-195 for audit-column conventions; end of file for type-export conventions at lines 567-618)
|
|
scripts/push-services-migration.ts (idempotent CREATE TABLE IF NOT EXISTS pattern to replicate)
|
|
.planning/phases/11-catalog-database-view-ux-legacy-consolidation/11-CONTEXT.md (D-05, D-06, D-07, D-08)
|
|
</read_first>
|
|
<action>
|
|
1. In `src/db/schema.ts`, add a new `tags` table immediately after the `comments` table definition (after line 111), following the exact polymorphic pattern of `comments`. Do NOT use a composite primaryKey for `(entity_type, entity_id, id)` — `id` is already the primary key (text, nanoid default). Instead add a **unique index** on `(entity_type, entity_id, name)` to prevent duplicate tag names per entity, using Drizzle's `uniqueIndex` from `drizzle-orm/pg-core`:
|
|
|
|
```typescript
|
|
// ============ TAGS (polymorphic — services now, leads in Phase 14) ============
|
|
// entity_type scopes the tag pool (D-06): "services" tags and "leads" tags are
|
|
// separate pools even though they share this table. No `color` column — badge
|
|
// color is derived deterministically from `name` via hash (D-07).
|
|
export const tags = pgTable(
|
|
"tags",
|
|
{
|
|
id: text("id")
|
|
.primaryKey()
|
|
.$defaultFn(() => nanoid()),
|
|
entity_type: text("entity_type").notNull(), // "services" | "leads" (Phase 14)
|
|
entity_id: text("entity_id").notNull(),
|
|
name: text("name").notNull(),
|
|
created_at: timestamp("created_at", { withTimezone: true })
|
|
.notNull()
|
|
.defaultNow(),
|
|
},
|
|
(t) => ({
|
|
entityTagUnique: uniqueIndex("tags_entity_name_unique").on(
|
|
t.entity_type,
|
|
t.entity_id,
|
|
t.name
|
|
),
|
|
entityIdx: index("tags_entity_idx").on(t.entity_type, t.entity_id),
|
|
})
|
|
);
|
|
```
|
|
|
|
2. Add `uniqueIndex` and `index` to the import list at the top of `src/db/schema.ts` (extend the existing `drizzle-orm/pg-core` import that currently includes `pgTable, text, integer, numeric, timestamp, boolean, primaryKey`).
|
|
|
|
3. Add a `tagsRelations` export near the other polymorphic relation (`commentsRelations` at line ~459-461), following the same "no direct FK — entity_type/entity_id at query time" comment pattern:
|
|
|
|
```typescript
|
|
export const tagsRelations = relations(tags, (_) => ({
|
|
// Polymorphic: no direct FK relation — entity_type + entity_id used at query time
|
|
}));
|
|
```
|
|
|
|
4. Add `Tag`/`NewTag` TypeScript types at the end of the file alongside the other type exports (after `export type Comment = ...` / `export type NewComment = ...` around line 579-580):
|
|
|
|
```typescript
|
|
export type Tag = typeof tags.$inferSelect;
|
|
export type NewTag = typeof tags.$inferInsert;
|
|
```
|
|
|
|
5. Run `npx drizzle-kit generate` from the project root. This produces `src/db/migrations/0002_add_tags_table.sql` (or the next sequential number — check `src/db/migrations/meta/_journal.json` first; existing entries go up to `0001_add_services_table`, but files 0003/0004/0005 already exist on disk without journal entries — drizzle-kit will pick the next number based on the highest existing migration file, likely `0006_*`. Whatever number drizzle-kit generates, use that exact filename for the push script in step 6). Confirm the generated SQL contains `CREATE TABLE "tags"` with columns `id`, `entity_type`, `entity_id`, `name`, `created_at` and a unique index on `(entity_type, entity_id, name)`.
|
|
|
|
6. Create `scripts/push-11-tags-migration.ts` following the exact idempotent pattern of `scripts/push-services-migration.ts` (postgres client, `process.env.DATABASE_URL`, `CREATE TABLE IF NOT EXISTS`, catches "already exists" and exits 0):
|
|
|
|
```typescript
|
|
import postgres from "postgres";
|
|
|
|
async function push() {
|
|
const databaseUrl = process.env.DATABASE_URL;
|
|
if (!databaseUrl) {
|
|
console.error("DATABASE_URL environment variable is required");
|
|
process.exit(1);
|
|
}
|
|
|
|
const client = postgres(databaseUrl);
|
|
|
|
try {
|
|
console.log("Pushing tags table migration...");
|
|
|
|
await client`
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id text PRIMARY KEY,
|
|
entity_type text NOT NULL,
|
|
entity_id text NOT NULL,
|
|
name text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
)
|
|
`;
|
|
|
|
await client`
|
|
CREATE UNIQUE INDEX IF NOT EXISTS tags_entity_name_unique
|
|
ON tags (entity_type, entity_id, name)
|
|
`;
|
|
|
|
await client`
|
|
CREATE INDEX IF NOT EXISTS tags_entity_idx
|
|
ON tags (entity_type, entity_id)
|
|
`;
|
|
|
|
console.log("✓ tags table created successfully");
|
|
process.exit(0);
|
|
} catch (err: unknown) {
|
|
if (err instanceof Error) {
|
|
if (err.message.includes("already exists")) {
|
|
console.log("✓ tags table already exists (skipped)");
|
|
process.exit(0);
|
|
}
|
|
console.error("Error pushing migration:", err.message);
|
|
} else {
|
|
console.error("Unknown error:", err);
|
|
}
|
|
process.exit(1);
|
|
}
|
|
}
|
|
|
|
push();
|
|
```
|
|
|
|
7. Run `npx tsx scripts/push-11-tags-migration.ts` against the local `DATABASE_URL` (dev DB). This is the [BLOCKING] step — Plan 02's query layer and Plan 04's UI require `tags` to exist locally for typecheck/build to reflect reality.
|
|
|
|
8. **Production migration note (do NOT execute):** Per CLAUDE.md Data Safety and project memory (Gitea→Coolify, prod Postgres only via SSH+docker exec), this migration (`CREATE TABLE tags` + 2 indexes, purely additive) MUST be applied to production manually via SSH+docker exec BEFORE the schema-dependent code (Plans 02-04) is deployed. Add a comment block at the top of `scripts/push-11-tags-migration.ts` documenting this:
|
|
|
|
```typescript
|
|
// PRODUCTION DEPLOY NOTE: This migration is additive-only (CREATE TABLE IF NOT EXISTS +
|
|
// 2 indexes, no drops/truncates). Per CLAUDE.md Data Safety, apply to production via
|
|
// SSH+docker exec BEFORE pushing Phase 11 schema-dependent code (Plans 02-04).
|
|
// Run: npx tsx scripts/push-11-tags-migration.ts (with prod DATABASE_URL)
|
|
```
|
|
</action>
|
|
<verify>
|
|
<automated>npx tsc --noEmit 2>&1 | grep -i "schema.ts" ; echo "exit: $?"</automated>
|
|
</verify>
|
|
<acceptance_criteria>
|
|
- `grep -c "export const tags = pgTable" src/db/schema.ts` returns `1`
|
|
- `grep -c "export type Tag = typeof tags" src/db/schema.ts` returns `1`
|
|
- `grep -c "export type NewTag = typeof tags" src/db/schema.ts` returns `1`
|
|
- A file matching `src/db/migrations/*_*.sql` newly created by this task contains `CREATE TABLE "tags"` (verify via `grep -l "CREATE TABLE \"tags\"" src/db/migrations/*.sql`)
|
|
- `src/db/migrations/meta/_journal.json` contains a new entry for the tags migration (check via `grep -c "tags" src/db/migrations/meta/_journal.json` returns >= 1)
|
|
- `grep -c "CREATE TABLE IF NOT EXISTS tags" scripts/push-11-tags-migration.ts` returns `1`
|
|
- Running `npx tsx scripts/push-11-tags-migration.ts` exits 0 and prints either "✓ tags table created successfully" or "✓ tags table already exists (skipped)"
|
|
- After running the push script, querying `information_schema.tables` for `table_name = 'tags'` returns one row (verify via a one-off `psql` or `postgres` client query against `DATABASE_URL`)
|
|
- `npx tsc --noEmit` does not report new errors originating from `src/db/schema.ts`
|
|
</acceptance_criteria>
|
|
<done>
|
|
`tags` table + `Tag`/`NewTag` types exist in schema.ts, migration SQL generated, push script created and successfully run against local dev DB — `tags` table physically exists with the unique index on (entity_type, entity_id, name).
|
|
</done>
|
|
</task>
|
|
|
|
<task type="auto">
|
|
<name>Task 2: Run legacy consolidation (OFFER-13) + assign "Offerta" tag to migrated offer_services rows [BLOCKING]</name>
|
|
<files>
|
|
scripts/migrate-tags.ts
|
|
scripts/validate-tags-migration.ts
|
|
</files>
|
|
<read_first>
|
|
scripts/migrate-services.ts (existing Phase 7 script — already implements the service_catalog + offer_services -> services row-copy with migrated_from/migrated_id; idempotent, skips already-migrated rows)
|
|
scripts/validate-services-migration.ts (existing Phase 7 validation — row counts + orphan checks for services consolidation)
|
|
src/db/schema.ts (tags table added in Task 1; services.migrated_from column)
|
|
.planning/phases/11-catalog-database-view-ux-legacy-consolidation/11-CONTEXT.md (D-01, D-02, D-04)
|
|
</read_first>
|
|
<action>
|
|
Per D-01, the additive consolidation of `service_catalog`/`offer_services` into `services` is the row-copy logic already written in Phase 7's `scripts/migrate-services.ts` (with `migrated_from`/`migrated_id` audit columns) — that script was written but its execution was deferred to Phase 11 (see ROADMAP.md Phase 7 status: "consolidamento finale rinviato a v2.1 Phase 11"). This task RUNS that existing script (it is idempotent — already-migrated rows are skipped via the `migrated_from`+`migrated_id` existence check), runs its validator, then adds the new "Offerta" tag-assignment script for D-02.
|
|
|
|
1. Run `npx tsx scripts/migrate-services.ts` against the local dev DB. This copies any not-yet-migrated rows from `service_catalog` and `offer_services` into `services` with `migrated_from`/`migrated_id` set (idempotent — already-migrated rows produce "skipped" output, not duplicates). Capture the console output (inserted/skipped counts) for the SUMMARY.
|
|
|
|
2. Run `npx tsx scripts/validate-services-migration.ts` against the local dev DB. Confirm it prints `ALL CHECKS PASSED` (PASS on: service_catalog fully migrated, offer_services fully migrated, no orphaned migrated_id references, quote_items.service_id -> service_catalog FK intact, offer_micro_services.service_id -> offer_services FK intact). If any check fails, STOP and report — do not proceed to step 3, since OFFER-13 depends on this passing first.
|
|
|
|
3. Create `scripts/migrate-tags.ts` implementing D-02 (assign "Offerta" tag to every `services` row where `migrated_from = 'offer_services'`), following the idempotent pattern of `scripts/migrate-services.ts`:
|
|
|
|
```typescript
|
|
import { db } from "@/db";
|
|
import { services, tags } from "@/db/schema";
|
|
import { eq, and } from "drizzle-orm";
|
|
|
|
async function migrate() {
|
|
console.log("Starting tags migration: assigning 'Offerta' tag to services migrated from offer_services...\n");
|
|
|
|
const offerServices = await db
|
|
.select({ id: services.id })
|
|
.from(services)
|
|
.where(eq(services.migrated_from, "offer_services"));
|
|
|
|
let taggedCount = 0;
|
|
let skippedCount = 0;
|
|
|
|
for (const service of offerServices) {
|
|
const existing = await db
|
|
.select()
|
|
.from(tags)
|
|
.where(
|
|
and(
|
|
eq(tags.entity_type, "services"),
|
|
eq(tags.entity_id, service.id),
|
|
eq(tags.name, "Offerta")
|
|
)
|
|
)
|
|
.limit(1);
|
|
|
|
if (existing.length > 0) {
|
|
skippedCount++;
|
|
continue;
|
|
}
|
|
|
|
await db.insert(tags).values({
|
|
entity_type: "services",
|
|
entity_id: service.id,
|
|
name: "Offerta",
|
|
});
|
|
taggedCount++;
|
|
}
|
|
|
|
console.log(`Assigned 'Offerta' tag: ${taggedCount} services, ${skippedCount} already tagged`);
|
|
console.log("\nMigration complete. Run scripts/validate-tags-migration.ts next.");
|
|
process.exit(0);
|
|
}
|
|
|
|
migrate().catch((err) => {
|
|
console.error("Migration failed:", err);
|
|
process.exit(1);
|
|
});
|
|
```
|
|
|
|
4. Create `scripts/validate-tags-migration.ts` implementing the row-count + orphan checks for OFFER-13's tag dimension, following the pattern of `scripts/validate-services-migration.ts`:
|
|
|
|
```typescript
|
|
import { db } from "@/db";
|
|
import { services, tags } from "@/db/schema";
|
|
import { eq, and, sql } from "drizzle-orm";
|
|
|
|
async function validate() {
|
|
let failures = 0;
|
|
|
|
const [offerServicesCount] = await db
|
|
.select({ n: sql<number>`count(*)::int` })
|
|
.from(services)
|
|
.where(eq(services.migrated_from, "offer_services"));
|
|
|
|
const [offerTagCount] = await db
|
|
.select({ n: sql<number>`count(*)::int` })
|
|
.from(tags)
|
|
.where(and(eq(tags.entity_type, "services"), eq(tags.name, "Offerta")));
|
|
|
|
console.log(`offer_services-derived services: ${offerServicesCount.n} | Offerta tags: ${offerTagCount.n}`);
|
|
|
|
if (offerServicesCount.n !== offerTagCount.n) {
|
|
console.log("FAIL: Offerta tag count does not match offer_services-derived services count");
|
|
failures++;
|
|
} else {
|
|
console.log("PASS: all offer_services-derived services have the Offerta tag");
|
|
}
|
|
|
|
const orphanedTags = await db.execute(sql`
|
|
SELECT COUNT(*)::int AS n FROM tags t
|
|
WHERE t.entity_type = 'services'
|
|
AND NOT EXISTS (SELECT 1 FROM services s WHERE s.id = t.entity_id)
|
|
`);
|
|
const orphanedTagsCount = (orphanedTags as unknown as Array<{ n: number }>)[0]?.n ?? 0;
|
|
if (orphanedTagsCount > 0) {
|
|
console.log(`FAIL: ${orphanedTagsCount} tags reference non-existent services`);
|
|
failures++;
|
|
} else {
|
|
console.log("PASS: no orphaned tag references");
|
|
}
|
|
|
|
const tagCounts = await db.execute(sql`
|
|
SELECT entity_type, COUNT(*)::int AS n FROM tags GROUP BY entity_type
|
|
`);
|
|
const counts = tagCounts as unknown as Array<{ entity_type: string; n: number }>;
|
|
for (const row of counts) {
|
|
console.log(`INFO: ${row.n} tags for entity_type=${row.entity_type}`);
|
|
}
|
|
|
|
console.log(`\n${failures === 0 ? "ALL CHECKS PASSED" : `${failures} CHECK(S) FAILED`}`);
|
|
process.exit(failures === 0 ? 0 : 1);
|
|
}
|
|
|
|
validate().catch((err) => {
|
|
console.error("Validation failed:", err);
|
|
process.exit(1);
|
|
});
|
|
```
|
|
|
|
5. Run `npx tsx scripts/migrate-tags.ts` then `npx tsx scripts/validate-tags-migration.ts` against the local dev DB. Confirm `ALL CHECKS PASSED`.
|
|
|
|
6. **Deferred low-risk note (per CONTEXT.md "Claude's Discretion"):** `src/lib/admin-queries.ts` (lines ~335/343 and ~531/539) still does `leftJoin(service_catalog, eq(quote_items.service_id, service_catalog.id))` for the `quote_items` label, but `quote_items.service_id` is typed in `schema.ts` (line ~213-214) as `references(() => services.id, ...)` — i.e. it points at `services.id`, not `service_catalog.id`. For any `quote_items` row created after Phase 8 (where `service_id` references a `services.id`), this JOIN will not match and `label` will fall back to `COALESCE(..., quote_items.custom_label)`, which may be `null` for non-custom items. This is a PRE-EXISTING issue, not introduced by Phase 11, and per CONTEXT.md is explicitly NOT blocking for Phase 11's success criteria. Record this in the plan's SUMMARY.md as a "Known issue — deferred" note: "JOIN `quote_items.service_id` -> `service_catalog.id` in admin-queries.ts (lines ~335/343, ~531/539) is stale post-Phase-8 (FK now points to services.id). Low risk, low frequency (admin-only label display in client/project workspace quote_items list). Recommended fix: change `leftJoin(service_catalog, ...)` to `leftJoin(services, eq(quote_items.service_id, services.id))` and `service_catalog.name` to `services.name` in both COALESCE expressions — trivial 4-line change, candidate for Phase 12 cleanup or a standalone hotfix." Do NOT make this code change in Phase 11 — out of scope per CONTEXT.md.
|
|
</action>
|
|
<verify>
|
|
<automated>npx tsx scripts/validate-services-migration.ts 2>&1 | tail -1 | grep -c "ALL CHECKS PASSED" && npx tsx scripts/validate-tags-migration.ts 2>&1 | tail -1 | grep -c "ALL CHECKS PASSED"</automated>
|
|
</verify>
|
|
<acceptance_criteria>
|
|
- `npx tsx scripts/migrate-services.ts` exits 0
|
|
- `npx tsx scripts/validate-services-migration.ts` exits 0 and output contains `ALL CHECKS PASSED`
|
|
- `grep -c "Offerta" scripts/migrate-tags.ts` returns >= 1
|
|
- `grep -c "migrated_from, \"offer_services\"" scripts/migrate-tags.ts` returns >= 1 (or equivalent `eq(services.migrated_from, "offer_services")`)
|
|
- `npx tsx scripts/migrate-tags.ts` exits 0
|
|
- `npx tsx scripts/validate-tags-migration.ts` exits 0 and output contains `ALL CHECKS PASSED`
|
|
- A direct query of `tags` where `entity_type='services' AND name='Offerta'` returns a row count equal to the count of `services` where `migrated_from='offer_services'`
|
|
- No rows were deleted from `service_catalog`, `offer_services`, `offer_micro_services`, `clients`, `projects`, `payments`, or `phases` (spot-check row counts before/after are identical for these tables)
|
|
</acceptance_criteria>
|
|
<done>
|
|
`service_catalog` and `offer_services` rows are fully represented in `services` (migrated_from/migrated_id populated, validated via row-count script), and every `services` row with `migrated_from='offer_services'` has the "Offerta" tag in the new `tags` table. Both validation scripts report `ALL CHECKS PASSED`. The stale `quote_items` <-> `service_catalog` JOIN is documented as a deferred, non-blocking known issue.
|
|
</done>
|
|
</task>
|
|
|
|
</tasks>
|
|
|
|
<threat_model>
|
|
## Trust Boundaries
|
|
|
|
| Boundary | Description |
|
|
|----------|--------------|
|
|
| One-off scripts -> Postgres | `scripts/*.ts` run with full `DATABASE_URL` credentials, executed manually by the developer (not exposed via HTTP) |
|
|
| Drizzle schema -> migration SQL | `drizzle-kit generate` produces SQL applied to a live database; incorrect schema changes could alter production data shape |
|
|
|
|
## STRIDE Threat Register
|
|
|
|
| Threat ID | Category | Component | Disposition | Mitigation Plan |
|
|
|-----------|----------|-----------|-------------|-----------------|
|
|
| T-11-01 | Tampering | `scripts/push-11-tags-migration.ts` | mitigate | Use `CREATE TABLE IF NOT EXISTS` + `CREATE INDEX IF NOT EXISTS` only — no `DROP`/`ALTER ... DROP COLUMN`/`TRUNCATE`. Idempotent: safe to re-run, catches "already exists" and exits 0. |
|
|
| T-11-02 | Tampering | `scripts/migrate-tags.ts` / `migrate-services.ts` | mitigate | Both scripts only INSERT new rows; existence checks via `migrated_from`/`migrated_id` (services) and `(entity_type, entity_id, name)` (tags) prevent duplicate inserts on re-run. No UPDATE/DELETE on `service_catalog`, `offer_services`, `offer_micro_services`. |
|
|
| T-11-03 | Repudiation | Migration audit trail | accept | `migrated_from`/`migrated_id` on `services` provide traceability for rollback; no separate audit log needed for an additive-only operation. |
|
|
| T-11-04 | Information Disclosure | `tags` table polymorphic `entity_id` | mitigate | `entity_type` is constrained at the application layer to a known enum (`"services"`, future `"leads"`) — enforced in Plan 02's server actions (not at DB level in this plan), preventing cross-entity tag pollution. Flagged here for Plan 02 to implement the validation. |
|
|
| T-11-05 | Denial of Service | Migration scripts run against prod | accept | Scripts are run manually via SSH+docker exec per project convention, not triggered by any HTTP-reachable endpoint. Out of scope for automated threat mitigation. |
|
|
|
|
All HIGH-severity items (T-11-01, T-11-02) are mitigated via idempotent additive-only SQL. No threats in this plan are left unmitigated/unaccepted.
|
|
</threat_model>
|
|
|
|
<verification>
|
|
1. `npx tsc --noEmit` passes (no new type errors from schema.ts changes)
|
|
2. `tags` table exists in local dev DB with columns `id, entity_type, entity_id, name, created_at` and unique index `tags_entity_name_unique`
|
|
3. `scripts/validate-services-migration.ts` and `scripts/validate-tags-migration.ts` both exit 0 with `ALL CHECKS PASSED`
|
|
4. Row counts for `clients`, `projects`, `payments`, `phases`, `service_catalog`, `offer_services`, `offer_micro_services` are unchanged before/after running all scripts in this plan
|
|
</verification>
|
|
|
|
<success_criteria>
|
|
- `tags` table + types committed to `src/db/schema.ts`, migration generated and applied locally
|
|
- OFFER-13 satisfied: `service_catalog` + `offer_services` rows fully present in `services` with `migrated_from`/`migrated_id`, validated via row-count script (zero data loss)
|
|
- D-02 satisfied: every `services` row with `migrated_from='offer_services'` carries the "Offerta" tag
|
|
- Production migration documented as a manual pre-deploy step (not executed by this plan)
|
|
- Stale `quote_items` <-> `service_catalog` JOIN documented as a non-blocking known issue in SUMMARY.md
|
|
</success_criteria>
|
|
|
|
<output>
|
|
After completion, create `.planning/phases/11-catalog-database-view-ux-legacy-consolidation/11-01-SUMMARY.md`
|
|
</output>
|