red/.planning/phases/18-template-schema-and-crud-api/18-01-PLAN.md

---
phase: 18-template-schema-and-crud-api
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - teressa-copeland-homes/src/lib/db/schema.ts
  - teressa-copeland-homes/drizzle/0012_*.sql
autonomous: true
requirements: [TMPL-01]

must_haves:
  truths:
    - "document_templates table exists in the Drizzle schema with all 7 columns"
    - "Migration file 0012_*.sql exists and contains CREATE TABLE document_templates"
    - "TypeScript compiles with no errors after schema change"
  artifacts:
    - path: "teressa-copeland-homes/src/lib/db/schema.ts"
      provides: "documentTemplates table definition and relations"
      contains: "documentTemplates"
    - path: "teressa-copeland-homes/drizzle/0012_*.sql"
      provides: "SQL migration creating document_templates table"
      contains: "document_templates"
  key_links:
    - from: "documentTemplates.formTemplateId"
      to: "formTemplates.id"
      via: "FK reference"
      pattern: "references.*formTemplates\\.id"
---

<objective>
Add the `document_templates` Drizzle table to schema.ts and generate the SQL migration.

Purpose: Every subsequent template feature (editor, API, apply-to-document) depends on this table existing. This plan establishes the schema foundation.
Output: Updated schema.ts with `documentTemplates` table + `drizzle/0012_*.sql` migration file.
</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/18-template-schema-and-crud-api/18-CONTEXT.md

<interfaces>
<!-- Existing types and tables the executor needs -->

From teressa-copeland-homes/src/lib/db/schema.ts:
```typescript
export interface SignatureFieldData {
  id: string;
  page: number;
  x: number;
  y: number;
  width: number;
  height: number;
  type?: SignatureFieldType;
  signerEmail?: string;
  hint?: string;
}

export const formTemplates = pgTable("form_templates", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull(),
  filename: text("filename").notNull().unique(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
```

Existing migration files: 0000 through 0011. Next migration is 0012.
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Add documentTemplates table to schema.ts</name>
  <read_first>
    - teressa-copeland-homes/src/lib/db/schema.ts
  </read_first>
  <files>teressa-copeland-homes/src/lib/db/schema.ts</files>
  <action>
Add the `documentTemplates` table definition to schema.ts, placed immediately after the `formTemplates` table definition (before the `documents` table). Per D-01, D-02, D-03, D-04, D-05, D-06:

```typescript
export const documentTemplates = pgTable("document_templates", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull(),
  formTemplateId: text("form_template_id").notNull().references(() => formTemplates.id),
  signatureFields: jsonb("signature_fields").$type<SignatureFieldData[]>(),
  archivedAt: timestamp("archived_at"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
```

Key points:
- `signatureFields` is nullable (per D-04) — template starts empty, editor fills it in Phase 19
- `formTemplateId` FK has NO `onDelete` option (per D-06) — no cascade
- `archivedAt` is nullable — NULL means active (per D-07)
- `updatedAt` must be set explicitly in PATCH queries, not via DB trigger (per D-05)

Also add a relation from documentTemplates to formTemplates:

```typescript
export const documentTemplatesRelations = relations(documentTemplates, ({ one }) => ({
  formTemplate: one(formTemplates, { fields: [documentTemplates.formTemplateId], references: [formTemplates.id] }),
}));
```

Do NOT add `onDelete: 'cascade'` to the formTemplateId FK. Per D-06, if a form is removed, templates referencing it should remain.
  </action>
  <verify>
    <automated>cd /Users/ccopeland/temp/red/teressa-copeland-homes && npx tsc --noEmit 2>&1 | head -20</automated>
  </verify>
  <acceptance_criteria>
    - `documentTemplates` export exists in schema.ts
    - Table has exactly 7 columns: id, name, formTemplateId, signatureFields, archivedAt, createdAt, updatedAt
    - `formTemplateId` references `formTemplates.id` with no onDelete
    - `signatureFields` typed as `SignatureFieldData[]` via `$type`
    - `archivedAt` is nullable timestamp (no `.notNull()`)
    - `npx tsc --noEmit` passes with zero errors
  </acceptance_criteria>
  <done>documentTemplates table and relations defined in schema.ts, TypeScript compiles cleanly</done>
</task>

<task type="auto">
  <name>Task 2: Generate Drizzle migration</name>
  <read_first>
    - teressa-copeland-homes/src/lib/db/schema.ts
    - teressa-copeland-homes/drizzle.config.ts
  </read_first>
  <files>teressa-copeland-homes/drizzle/0012_*.sql</files>
  <action>
Per D-13, run the Drizzle migration generator from within the teressa-copeland-homes directory:

```bash
cd teressa-copeland-homes && npx drizzle-kit generate
```

This will produce `drizzle/0012_*.sql` (Drizzle auto-names it). The migration should contain a `CREATE TABLE "document_templates"` statement with all 7 columns and the FK constraint to `form_templates`.

After generation, read the migration file to verify it contains the expected CREATE TABLE statement and FK constraint. The migration should NOT contain any DROP or ALTER statements on existing tables.

Commit both schema.ts and the migration file.
  </action>
  <verify>
    <automated>ls /Users/ccopeland/temp/red/teressa-copeland-homes/drizzle/0012_*.sql && grep -l "document_templates" /Users/ccopeland/temp/red/teressa-copeland-homes/drizzle/0012_*.sql</automated>
  </verify>
  <acceptance_criteria>
    - Migration file `drizzle/0012_*.sql` exists
    - File contains `CREATE TABLE` for `document_templates`
    - File contains FK constraint referencing `form_templates(id)`
    - File does NOT alter or drop any existing tables
    - No other 0012 migration files exist (exactly one)
  </acceptance_criteria>
  <done>Migration 0012 generated with CREATE TABLE document_templates, FK to form_templates, all 7 columns</done>
</task>

</tasks>

<verification>
1. `npx tsc --noEmit` passes — schema.ts compiles
2. `ls drizzle/0012_*.sql` — exactly one migration file
3. `grep "document_templates" drizzle/0012_*.sql` — CREATE TABLE present
4. `grep "form_template_id" drizzle/0012_*.sql` — FK column present
</verification>

<success_criteria>
- documentTemplates table defined in schema.ts with all 7 columns per D-02
- Drizzle relation to formTemplates exists
- Migration 0012_*.sql generated and contains CREATE TABLE
- TypeScript compiles with zero errors
</success_criteria>

<output>
After completion, create `.planning/phases/18-template-schema-and-crud-api/18-01-SUMMARY.md`
</output>