Appearance
Data model sketch (Drizzle / D1)
Not final; enough to start. Tables arrive with the milestone that needs them, each as an append-only migration (Database).
| Group | Tables | Status |
|---|---|---|
| Auth (Better Auth) | user, session, account, verification, organization (client business), member, invitation, two_factor | Built (migration foundation) |
| System | setting, template_version, audit_log | Built (migrations foundation, immutability_triggers). notification and email_outbox (the email log, named email_log here) are built too. |
| Business | client_business (org id, username prefix, plan, status, start/end), plan_version (name, price, inclusions, version, active), subscription (Stripe ids, status) | Partly built: client (named client_business here), client_login, client_seat, client_note, client_activity. plan_version (a template_version of kind plan) and subscription are planned. |
| Sales | sales_day_item (salesperson, date, business name, notes), outreach_link (token, salesperson, business, created), link_scan, objection_option, outreach_objection, outreach_outcome | Built, except objection_option and outreach_objection, which are planned. |
| Leads | lead (status, source link, salesperson, form answers JSON, consent at), research_job, research_brief, call_note | Built, except call_note, which is planned. |
| Commercial | quote_template_version, quote (frozen snapshot JSON, status, token), contract_template_version, contract (frozen text, hash, signer name, IP, signed at, token), payment (Stripe or manual), commission_line | Built, with lead_quote as the quote table. Template versions are template_version rows. |
| Calendar | event_type, event (type, start, end, all-day, client id, lead id, video id, generated, anchor event id, overridden), event_assignee, crew_unavailability | Partly built: calendar_event (named event here). event_type, event_assignee and crew_unavailability are planned. |
| Content | shoot (client, date, location, videographer), script (client, shoot, version, body, status, selected), script_change_request (range, reason, status), video (client, script, shoot, stage, videographer, editor), video_version (R2 key, uploaded by, kind: footage/edit), footage_request, review_comment (target video/script + version, timestamp/range or text range, region, role tag, status, author), review_comment_reply, review_outcome (approved/disapproved, by, attributed to) | Built: script, script_change_request, video, video_version, video_comment (named review_comment here), video_activity, footage_request, review_outcome. shoot and review_comment_reply are planned. |
| Client tools | todo_list, todo_item (assignee, due, done), subaccount_request | Built: todo_item, sub_account_request. todo_list is planned. |
| Crew | crew_profile, crew_contract, pay_terms, pay_ledger_line, pay_run, post_metric | Partly built: crew_member, crew_agreement, pay_run, pay_line (the ledger). post_metric is planned. Names differ from this sketch. |
| Clipper | apikey (Better Auth API key plugin, prefix sgc_), clipper_client, clipper_project, clipper_clip, clipper_usage, clipper_event | Built (migration clipper_server). Handoff adds video_version rows and no tables. |
Engineering conventions (added during setup, not in the original spec)
- IDs: text primary keys (ULID or UUID, generated in code).
- Timestamps: integer epoch milliseconds (
timestamp_ms), UTC. Calendar dates that mean "a day" in the agency's timezone are stored asYYYY-MM-DDtext with the timezone in settings. - Money: integer pence, never floats.
- Statuses: text columns validated by the domain package's Zod enums, not DB enums, so adding a status needs no migration.
- JSON columns whose shape may change are stored as
{ schemaVersion, data }(Versioning). - History that must not change (settings, audit, published templates) is protected by triggers.