Skip to content

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).

GroupTablesStatus
Auth (Better Auth)user, session, account, verification, organization (client business), member, invitation, two_factorBuilt (migration foundation)
Systemsetting, template_version, audit_logBuilt (migrations foundation, immutability_triggers). notification and email_outbox (the email log, named email_log here) are built too.
Businessclient_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.
Salessales_day_item (salesperson, date, business name, notes), outreach_link (token, salesperson, business, created), link_scan, objection_option, outreach_objection, outreach_outcomeBuilt, except objection_option and outreach_objection, which are planned.
Leadslead (status, source link, salesperson, form answers JSON, consent at), research_job, research_brief, call_noteBuilt, except call_note, which is planned.
Commercialquote_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_lineBuilt, with lead_quote as the quote table. Template versions are template_version rows.
Calendarevent_type, event (type, start, end, all-day, client id, lead id, video id, generated, anchor event id, overridden), event_assignee, crew_unavailabilityPartly built: calendar_event (named event here). event_type, event_assignee and crew_unavailability are planned.
Contentshoot (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 toolstodo_list, todo_item (assignee, due, done), subaccount_requestBuilt: todo_item, sub_account_request. todo_list is planned.
Crewcrew_profile, crew_contract, pay_terms, pay_ledger_line, pay_run, post_metricPartly built: crew_member, crew_agreement, pay_run, pay_line (the ledger). post_metric is planned. Names differ from this sketch.
Clipperapikey (Better Auth API key plugin, prefix sgc_), clipper_client, clipper_project, clipper_clip, clipper_usage, clipper_eventBuilt (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 as YYYY-MM-DD text 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.

Internal documentation. Not for clients.