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

# Phase 18: Template Schema and CRUD API - Context

**Gathered:** 2026-04-06
**Status:** Ready for planning

<domain>
## Phase Boundary

Schema migration + CRUD API only. No UI, no template editor, no apply-to-document logic. Those are Phases 19 and 20.

Deliverables:
1. Drizzle migration adding `document_templates` table
2. `GET /api/templates` — list active templates (archivedAt IS NULL)
3. `POST /api/templates` — create template (pick a form from the library)
4. `PATCH /api/templates/[id]` — rename template
5. `DELETE /api/templates/[id]` — soft-delete (set archivedAt)

</domain>

<decisions>
## Implementation Decisions

### Schema Design

- **D-01:** New table `document_templates` — separate from `form_templates`. The `form_templates` table is a read-only seeded catalog. `document_templates` is agent-authored.

- **D-02:** Table columns:
  ```
  document_templates:
    id               TEXT PRIMARY KEY (crypto.randomUUID())
    name             TEXT NOT NULL
    formTemplateId   TEXT NOT NULL REFERENCES form_templates(id)
    signatureFields  JSONB NULL  — SignatureFieldData[] with roles in signerEmail slot, hints in hint slot
    archivedAt       TIMESTAMP NULL  — soft-delete; NULL = active
    createdAt        TIMESTAMP NOT NULL DEFAULT NOW()
    updatedAt        TIMESTAMP NOT NULL DEFAULT NOW()
  ```

- **D-03:** NO separate `signerRoles` or `textHints` columns. Both are already embedded per-field in `signatureFields`:
  - Role labels stored in `field.signerEmail` (e.g. "Buyer", "Seller")
  - Text hints stored in `field.hint` (already on `SignatureFieldData`)
  - No duplication, no sync issues.

- **D-04:** `signatureFields` is nullable on creation (template starts empty, editor fills it in Phase 19). Default NULL.

- **D-05:** `updatedAt` must be set explicitly in every UPDATE query (no DB trigger). Follow existing pattern from `clients` and `formTemplates` tables.

- **D-06:** NO `ON DELETE CASCADE` on the `formTemplateId` FK. If a form is removed from the library, templates referencing it should remain (archivedAt pattern handles cleanup).

### Soft-Delete

- **D-07:** Deletion sets `archivedAt = NOW()`. Hard delete is never performed.
- **D-08:** All list queries filter `WHERE archivedAt IS NULL`. Archived templates are invisible to the agent.
- **D-09:** Archived templates are NOT restored. If agent needs the template back, they create a new one.

### API Structure

- **D-10:** Routes at `/api/templates` (top-level, matching `/api/documents` pattern):
  - `GET /api/templates` — returns active templates with: id, name, formTemplateId, formName (joined), fieldCount (derived from signatureFields length), createdAt, updatedAt
  - `POST /api/templates` — body: `{ name: string; formTemplateId: string }` — creates with empty signatureFields
  - `PATCH /api/templates/[id]` — body: `{ name?: string; signatureFields?: SignatureFieldData[] }` — partial update
  - `DELETE /api/templates/[id]` — sets archivedAt = NOW()

- **D-11:** All routes auth-gated (agent session required). Same `auth()` guard as all other portal API routes.

- **D-12:** `fieldCount` on GET list is derived server-side: `(signatureFields ?? []).length`. Not stored in DB.

### Migration

- **D-13:** Use existing Drizzle pattern: edit `schema.ts`, run `drizzle-kit generate` → `drizzle/0012_*.sql`, commit both. Next migration after `0011_common_mystique.sql`.

### Claude's Discretion
- Exact migration filename (Drizzle generates automatically)
- Whether to include formTemplate name JOIN in the list query (recommended — avoids extra fetches in UI)

</decisions>

<canonical_refs>
## Canonical References

**Downstream agents MUST read these before planning or implementing.**

### Schema (existing patterns to follow)
- `teressa-copeland-homes/src/lib/db/schema.ts` — existing tables; `document_templates` goes here; follow `clients` table pattern for updatedAt
- `teressa-copeland-homes/drizzle/` — existing migrations 0000–0011; new migration is 0012
- `teressa-copeland-homes/drizzle.config.ts` — migration config

### Existing API patterns
- `teressa-copeland-homes/src/app/api/documents/route.ts` — POST documents pattern to follow
- `teressa-copeland-homes/src/app/api/documents/[id]/route.ts` — PATCH pattern to follow

### Research
- `.planning/research/ARCHITECTURE.md` — schema decision reasoning, build order
- `.planning/research/PITFALLS.md` — soft-delete rationale, FK constraint notes

</canonical_refs>

<code_context>
## Existing Code Insights

### Reusable Assets
- `auth()` from `@/lib/auth` — all existing API routes use this for session guard; follow same pattern
- `db` from `@/lib/db` — Drizzle client; import and use directly
- `SignatureFieldData` interface — already in schema.ts; `signatureFields` column types to `SignatureFieldData[]`
- `formTemplates` table — JOIN target for getting form name in list query

### Established Patterns
- `crypto.randomUUID()` via `$defaultFn` for IDs — all existing tables use this pattern
- `timestamp("updated_at").defaultNow().notNull()` — existing pattern; updated explicitly in every PATCH
- `jsonb("column").$type<T>()` — existing JSONB pattern (signatureFields, signers, contacts)
- `text("foreign_key_id").notNull().references(() => otherTable.id)` — FK pattern

### Integration Points
- `formTemplates` table (existing) — `document_templates.formTemplateId` FKs to this
- Phase 19 will add the template editor UI that PATCHes `signatureFields`
- Phase 20 will add the apply-template endpoint (POST to create document from template)

</code_context>

<specifics>
## Specific Ideas

- `fieldCount` in GET /api/templates response: `(t.signatureFields?.length ?? 0)` — computed at query time, no extra DB column
- PATCH route handles both rename (`name`) AND field save (Phase 19 calls it with `signatureFields`) — one route, two use cases

</specifics>

<deferred>
## Deferred Ideas

None — discussion stayed within phase scope.

</deferred>

---

*Phase: 18-template-schema-and-crud-api*
*Context gathered: 2026-04-06*