116 lines
5.0 KiB
Markdown
116 lines
5.0 KiB
Markdown
|
---
|
||
|
|
phase: 04-pdf-ingest
|
||
|
|
plan: 01
|
||
|
|
subsystem: database
|
||
|
|
tags: [drizzle, postgres, schema, migrations, seed, form-templates]
|
||
|
|
|
||
|
|
# Dependency graph
|
||
|
|
requires:
|
||
|
|
- phase: 03-agent-portal-shell
|
||
|
|
provides: documents table and portal data layer this plan extends
|
||
|
|
provides:
|
||
|
|
- form_templates table with id (text PK), name, filename (unique), createdAt, updatedAt
|
||
|
|
- documents table extended with formTemplateId (nullable FK) and filePath (nullable text)
|
||
|
|
- Drizzle migration 0002 applied to local PostgreSQL
|
||
|
|
- seeds/forms/ directory tracked via .gitkeep
|
||
|
|
- npm run seed:forms CLI: reads seeds/forms/*.pdf, upserts into form_templates via onConflictDoUpdate
|
||
|
|
affects: [04-02, 04-03, 04-04]
|
||
|
|
|
||
|
|
# Tech tracking
|
||
|
|
tech-stack:
|
||
|
|
added: []
|
||
|
|
patterns:
|
||
|
|
- DOTENV_CONFIG_PATH=.env.local prefix for tsx seed scripts (mirrors Phase 3 pattern)
|
||
|
|
- onConflictDoUpdate on filename for idempotent form template upserts
|
||
|
|
- formTemplates table placed above documents table in schema.ts to satisfy FK forward-reference
|
||
|
|
|
||
|
|
key-files:
|
||
|
|
created:
|
||
|
|
- teressa-copeland-homes/scripts/seed-forms.ts
|
||
|
|
- teressa-copeland-homes/seeds/forms/.gitkeep
|
||
|
|
- teressa-copeland-homes/drizzle/0002_wealthy_zzzax.sql
|
||
|
|
- teressa-copeland-homes/drizzle/meta/0002_snapshot.json
|
||
|
|
modified:
|
||
|
|
- teressa-copeland-homes/src/lib/db/schema.ts
|
||
|
|
- teressa-copeland-homes/package.json
|
||
|
|
- teressa-copeland-homes/drizzle/meta/_journal.json
|
||
|
|
|
||
|
|
key-decisions:
|
||
|
|
- "formTemplates table uses text PK (crypto.randomUUID()) — consistent with all other tables in schema.ts"
|
||
|
|
- "seed:forms uses DOTENV_CONFIG_PATH=.env.local prefix — dotenv/config reads .env by default but project uses .env.local"
|
||
|
|
- "formTemplateId and filePath are nullable — custom uploads have no template, legacy rows have no file path"
|
||
|
|
- "onConflictDoUpdate on filename column — enables idempotent monthly re-run when PDFs change names"
|
||
|
|
|
||
|
|
patterns-established:
|
||
|
|
- "Seed scripts: DOTENV_CONFIG_PATH=.env.local npx tsx scripts/<script>.ts pattern for all future seed commands"
|
||
|
|
- "Schema ordering: tables with FK references must come after the referenced table in schema.ts"
|
||
|
|
|
||
|
|
requirements-completed: [DOC-01, DOC-02]
|
||
|
|
|
||
|
|
# Metrics
|
||
|
|
duration: 8min
|
||
|
|
completed: 2026-03-19
|
||
|
|
---
|
||
|
|
|
||
|
|
# Phase 4 Plan 01: PDF Ingest Data Layer Summary
|
||
|
|
|
||
|
|
**Drizzle form_templates table + documents schema extension + idempotent npm run seed:forms CLI for monthly SkySlope PDF sync**
|
||
|
|
|
||
|
|
## Performance
|
||
|
|
|
||
|
|
- **Duration:** ~8 min
|
||
|
|
- **Started:** 2026-03-19T21:30:00Z
|
||
|
|
- **Completed:** 2026-03-19T21:38:00Z
|
||
|
|
- **Tasks:** 2
|
||
|
|
- **Files modified:** 6
|
||
|
|
|
||
|
|
## Accomplishments
|
||
|
|
- Added form_templates table to PostgreSQL via Drizzle migration (id text PK, name, filename unique, createdAt, updatedAt)
|
||
|
|
- Extended documents table with formTemplateId (nullable FK to form_templates) and filePath (nullable text)
|
||
|
|
- Created seed-forms.ts script that reads seeds/forms/, derives human-readable names from filenames, upserts via onConflictDoUpdate
|
||
|
|
- Wired npm run seed:forms with DOTENV_CONFIG_PATH=.env.local prefix for correct env loading
|
||
|
|
- Tracked seeds/forms/ directory in git via .gitkeep placeholder
|
||
|
|
|
||
|
|
## Task Commits
|
||
|
|
|
||
|
|
Each task was committed atomically:
|
||
|
|
|
||
|
|
1. **Task 1: Add formTemplates table and extend documents table in schema.ts** - `bbbbdbe` (feat)
|
||
|
|
2. **Task 2: Create seed script and wire npm run seed:forms** - `f82364d` (feat)
|
||
|
|
|
||
|
|
**Plan metadata:** (docs commit below)
|
||
|
|
|
||
|
|
## Files Created/Modified
|
||
|
|
- `teressa-copeland-homes/src/lib/db/schema.ts` - Added formTemplates table; added formTemplateId + filePath to documents
|
||
|
|
- `teressa-copeland-homes/drizzle/0002_wealthy_zzzax.sql` - Migration: CREATE TABLE form_templates + ALTER TABLE documents
|
||
|
|
- `teressa-copeland-homes/drizzle/meta/0002_snapshot.json` - Drizzle schema snapshot
|
||
|
|
- `teressa-copeland-homes/drizzle/meta/_journal.json` - Updated migration journal
|
||
|
|
- `teressa-copeland-homes/scripts/seed-forms.ts` - CLI seed script for form_templates from seeds/forms/*.pdf
|
||
|
|
- `teressa-copeland-homes/package.json` - Added seed:forms npm script
|
||
|
|
- `teressa-copeland-homes/seeds/forms/.gitkeep` - Tracks seed directory in git
|
||
|
|
|
||
|
|
## Decisions Made
|
||
|
|
- text PK (not integer) for formTemplates.id — consistent with crypto.randomUUID() pattern throughout schema.ts
|
||
|
|
- formTemplateId and filePath are nullable — custom uploads have no template; legacy documents rows need no file path
|
||
|
|
- onConflictDoUpdate on filename — filename is the natural unique key for idempotent monthly sync re-runs
|
||
|
|
- DOTENV_CONFIG_PATH=.env.local prefix on seed:forms — mirrors Phase 3 seed script decision; dotenv/config reads .env by default
|
||
|
|
|
||
|
|
## Deviations from Plan
|
||
|
|
|
||
|
|
None - plan executed exactly as written.
|
||
|
|
|
||
|
|
## Issues Encountered
|
||
|
|
None.
|
||
|
|
|
||
|
|
## User Setup Required
|
||
|
|
None - no external service configuration required.
|
||
|
|
|
||
|
|
## Next Phase Readiness
|
||
|
|
- form_templates table and documents extensions ready for Phase 4 Plans 02-04
|
||
|
|
- seeds/forms/ is empty; Teressa populates it by downloading PDFs from the SkySlope portal and running npm run seed:forms
|
||
|
|
- Monthly sync workflow: add/replace PDFs in seeds/forms/, re-run npm run seed:forms (idempotent via onConflictDoUpdate)
|
||
|
|
|
||
|
|
---
|
||
|
|
*Phase: 04-pdf-ingest*
|
||
|
|
*Completed: 2026-03-19*
|