78 lines
3.3 KiB
Markdown
78 lines
3.3 KiB
Markdown
|
---
|
||
|
|
phase: 18-template-schema-and-crud-api
|
||
|
|
plan: "01"
|
||
|
|
subsystem: database-schema
|
||
|
|
tags: [drizzle, schema, migration, postgresql]
|
||
|
|
dependency_graph:
|
||
|
|
requires: [formTemplates table (form_templates), SignatureFieldData interface]
|
||
|
|
provides: [documentTemplates table (document_templates), documentTemplatesRelations]
|
||
|
|
affects: [Phase 19 template editor, Phase 20 apply-template endpoint]
|
||
|
|
tech_stack:
|
||
|
|
added: []
|
||
|
|
patterns: [drizzle pgTable, jsonb $type, nullable timestamp soft-delete, FK no-cascade]
|
||
|
|
key_files:
|
||
|
|
created:
|
||
|
|
- teressa-copeland-homes/drizzle/0012_ancient_blue_shield.sql
|
||
|
|
modified:
|
||
|
|
- teressa-copeland-homes/src/lib/db/schema.ts
|
||
|
|
decisions:
|
||
|
|
- "signatureFields nullable JSONB: template starts empty; Phase 19 fills it via PATCH"
|
||
|
|
- "formTemplateId FK has no onDelete cascade: archived templates remain even if form is removed"
|
||
|
|
- "archivedAt nullable timestamp: NULL = active; soft-delete only (no hard deletes)"
|
||
|
|
- "updatedAt set explicitly in UPDATE queries, not via DB trigger (existing pattern)"
|
||
|
|
metrics:
|
||
|
|
duration: "4 min"
|
||
|
|
completed_date: "2026-04-06"
|
||
|
|
tasks_completed: 2
|
||
|
|
files_changed: 2
|
||
|
|
---
|
||
|
|
|
||
|
|
# Phase 18 Plan 01: Template Schema and Migration Summary
|
||
|
|
|
||
|
|
**One-liner:** Drizzle `document_templates` table with 7 columns, nullable JSONB fields, soft-delete via `archivedAt`, FK to `form_templates` with no cascade, and auto-generated SQL migration 0012.
|
||
|
|
|
||
|
|
## Tasks Completed
|
||
|
|
|
||
|
|
| Task | Name | Commit | Files |
|
||
|
|
|------|------|--------|-------|
|
||
|
|
| 1 | Add documentTemplates table to schema.ts | 9e677f9 | teressa-copeland-homes/src/lib/db/schema.ts |
|
||
|
|
| 2 | Generate Drizzle migration | c33c4ec | teressa-copeland-homes/drizzle/0012_ancient_blue_shield.sql |
|
||
|
|
|
||
|
|
## What Was Built
|
||
|
|
|
||
|
|
Added the `documentTemplates` Drizzle table definition to `schema.ts` immediately after `formTemplates`. The table has exactly 7 columns per D-02:
|
||
|
|
|
||
|
|
- `id` — TEXT PRIMARY KEY via `crypto.randomUUID()`
|
||
|
|
- `name` — TEXT NOT NULL
|
||
|
|
- `formTemplateId` — TEXT NOT NULL, FK to `formTemplates.id` with no `onDelete` (D-06)
|
||
|
|
- `signatureFields` — JSONB nullable, typed as `SignatureFieldData[]` (D-04)
|
||
|
|
- `archivedAt` — TIMESTAMP nullable, soft-delete sentinel (D-07)
|
||
|
|
- `createdAt` — TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
- `updatedAt` — TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
|
||
|
|
Also added `documentTemplatesRelations` joining to `formTemplates` via `formTemplateId`.
|
||
|
|
|
||
|
|
Generated migration `0012_ancient_blue_shield.sql` via `drizzle-kit generate`. The migration contains only a `CREATE TABLE` statement and an `ALTER TABLE ... ADD CONSTRAINT` for the FK — no alterations to existing tables.
|
||
|
|
|
||
|
|
## Verification
|
||
|
|
|
||
|
|
- `npx tsc --noEmit` — zero errors
|
||
|
|
- `ls drizzle/0012_*.sql` — exactly one migration file (0012_ancient_blue_shield.sql)
|
||
|
|
- `grep "document_templates" drizzle/0012_ancient_blue_shield.sql` — CREATE TABLE present
|
||
|
|
- `grep "form_template_id" drizzle/0012_ancient_blue_shield.sql` — FK column present
|
||
|
|
|
||
|
|
## Deviations from Plan
|
||
|
|
|
||
|
|
None — plan executed exactly as written.
|
||
|
|
|
||
|
|
## Known Stubs
|
||
|
|
|
||
|
|
None — this plan is schema-only. No UI or API routes were implemented. Phase 18-02 handles the CRUD API routes.
|
||
|
|
|
||
|
|
## Self-Check: PASSED
|
||
|
|
|
||
|
|
- `teressa-copeland-homes/src/lib/db/schema.ts` — FOUND, contains documentTemplates export
|
||
|
|
- `teressa-copeland-homes/drizzle/0012_ancient_blue_shield.sql` — FOUND, contains CREATE TABLE document_templates
|
||
|
|
- Commit 9e677f9 — FOUND
|
||
|
|
- Commit c33c4ec — FOUND
|