139 lines
5.9 KiB
Markdown
139 lines
5.9 KiB
Markdown
# 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*
|