Database

Overview

The platform uses Cloudflare D1 (edge SQLite) for all structured data. Schema is defined in db/schema.sql, seed data in db/seed.sql, and incremental changes in db/migrations/.

db/migrations/ is forward-only — wrangler auto-applies every .sql file there in alphabetical order. Manual rollbacks live in db/rollbacks/ and are never auto-applied; see Database Migrations → Rollbacks.

Tables

Core

Table	Purpose
`users`	Authenticated users (auto-created on first login). Auth-only: id, email, name, picture, job_title, pronunciation, access_level, template IDs
`tool_permissions`	Per-tool, per-access-level permission grants
`org_settings`	Organisation-level key-value settings (timezone, holiday_state)
`user_preferences`	Per-user preference overrides (timezone, holiday_state)
`squads`	Team groupings derived from `people.squad` column during Productive sync
`sidebar_favorites`	Per-user pinned sidebar items
`daily_quotes`	Rotating quotes shown on dashboard
`history`	Audit trail of changes across all resource types
`notes`	Freeform notes attached to any resource (company, contact, deal, project, etc)

People & HR

Table	Purpose
`people`	Source of truth for all employee/HR data. Synced from Productive, enriched via Xero. Includes `pronunciation` (phonetic name guide, propagated from the linked user). `manager_id` drives data scoping hierarchy
`person_linkedin_activities`	LinkedIn activity snapshots for people
`person_change_alerts`	Alerts when LinkedIn/external data indicates a change

Templates

Table	Purpose
`onboarding_templates`	Named onboarding templates assigned to users
`scorecard_templates`	Named scorecard templates assigned to users
`scorecard_template_items`	Checklist items belonging to a scorecard template
`review_templates`	Named KPI templates for performance reviews
`review_template_items`	KPI items per review template (area, label, is_mandatory, sort_order)
`template_profiles`	Named role bundles that group one onboarding + one scorecard + one review template

Onboarding

Table	Purpose
`onboarding_phases`	5 phases with name, description, sort order
`onboarding_items`	Checklist items within phases (linked to a template; `tpl-onb-common` = shown to all users)
`onboarding_progress`	Per-user completion state for each item. `person_id` links to `people`

Objectives

Table	Purpose
`objectives`	Business objectives with category (financial/customer/operational/people)
`priorities`	Current priorities linked to an objective, with is_active flag
`kpi_definitions`	KPI definitions linked to a priority, scoped by access_level

Scorecards

Table	Purpose
`scorecards`	Scorecard header (squad, week, type, period_key, notes, scorecard_template_id). `person_id` links to `people`
`scorecard_scores`	Individual score items (status, note)
`scorecard_kpis`	KPI entries (text, target, actual, status, kpi_definition_id)
`scorecard_metrics`	Financial metrics (billable, utilisation, revenue, profit)

Performance

Table	Purpose
`review_cycles`	Review periods (quarterly/annual) with draft/open/closed lifecycle
`reviews`	One per person per cycle; status workflow, KPI pass/fail, self-assessment, salary decision
`review_kpi_ratings`	Snapshot of template items with 3-tier ratings (needs_urgent_work/achieving/exceeding)
`review_goals`	SMART goals (goal, success measures, support needed, completion status)
`feedback_requests`	360 feedback assignments with pending/submitted/declined status
`feedback_responses`	Structured 5-area feedback with ratings and comments
`review_questions`	Central question bank for reviews and feedback
`report_snapshots`	Cached performance reporting data snapshots

Announcements

Table	Purpose
`announcements`	Title, content, category (process/team/client/company), attachment URL/name, author
`announcement_reads`	Read receipts per user per announcement
`announcement_distributions`	Distribution tracking: channel (slack/google_calendar/email), status, external_id, error

Calendar & Events

Table	Purpose
`calendar_settings`	Per-user calendar display and sync settings
`events`	Team events with voting, categories, and scheduling
`event_votes`	Per-user votes on event proposals

Connections

Table	Purpose
`connections`	Org-level OAuth/API tokens for external services (Xero, Slack, Productive). Tokens are AES-GCM encrypted
`user_connections`	Per-user OAuth tokens for personal integrations (Google, GitHub, etc)

Leave

Table	Purpose
`leave_types`	Cached from Xero PayItems (leave type ID, name, is_paid)
`leave_balances`	Per-person leave balances cached from Xero (1hr TTL)
`leave_requests`	Leave requests with status, dates, approval chain, external integration IDs

Holidays

Table	Purpose
`public_holidays`	Cached Australian public holidays from Nager.Date API (24hr TTL)

Payroll

Table	Purpose
`pay_summaries`	Cached pay template info from Xero (annual salary, earnings YTD, next pay date). PK is `person_id`
`payslips`	Cached payslip summaries from Xero

Documents

Table	Purpose
`documents`	Employee documents (contracts, policies, certificates) with type, file URL, expiry tracking

Companies & Contacts (CRM)

Table	Purpose
`companies`	Company records with industry, domain, logo, pronunciation, Productive/Stripe IDs, parent company hierarchy
`contacts`	Individual contacts linked to a company with role, email, phone, pronunciation, LinkedIn, is_primary flag
`contact_communications`	Communication history with contacts (emails, calls, meetings)
`contact_relationship_profiles`	AI-enriched relationship context per contact
`contact_life_events`	Key dates and life events for relationship nurturing
`contact_partners`	Contact-to-contact partner/relationship links

Deals

Table	Purpose
`deals`	Sales pipeline deals with stage, value, probability, company, close date
`deal_pipeline_templates`	Named pipeline configurations
`deal_pipeline_stages`	Ordered stages within a pipeline template
`deal_participants`	Team members assigned to a deal
`deal_contact_links`	Contacts associated with a deal
`deal_research_briefs`	AI-generated research context for deals
`deal_project_links`	Links between won deals and resulting projects

Contracts

Table	Purpose
`contracts`	Service contracts with terms, value, renewal dates, signing status
`contract_templates`	Reusable contract templates
`contract_signers`	Signers on a contract with status tracking

Proposals

Table	Purpose
`proposals`	Client proposals with status, approval chain, pricing
`proposal_templates`	Reusable proposal templates
`proposal_template_pages`	Page content within proposal templates
`proposal_pages`	Page content within individual proposals
`proposal_pricing_sections`	Pricing sections with subtotals
`proposal_pricing_items`	Individual line items within pricing sections
`proposal_approvals`	Internal approval workflow entries
`proposal_team_members`	Team members presented on a proposal
`proposal_signers`	External signers with signing status
`proposal_views`	Analytics: when a proposal was viewed by the client
`proposal_slide_views`	Analytics: per-slide view tracking

Recruitment (ATS)

Table	Purpose
`recruitment_jobs`	Job postings with title, department, status, salary range, remote policy
`recruitment_candidates`	Candidate profiles with contact info, pronunciation, source, LinkedIn, resume
`recruitment_applications`	Applications linking candidates to jobs with stage tracking
`recruitment_application_stage_history`	Audit trail of stage transitions
`recruitment_pipeline_templates`	Named pipeline configurations for hiring
`recruitment_pipeline_stages`	Ordered stages within a pipeline template
`recruitment_hiring_team`	Team members assigned to a job
`recruitment_job_requirements`	Skills/requirements for a job posting
`recruitment_consents`	Candidate GDPR/privacy consent records
`recruitment_interviews`	Scheduled interviews with type, date, location, feedback
`recruitment_interview_participants`	Interviewers assigned to an interview
`recruitment_evaluations`	Post-interview evaluations by team members
`recruitment_evaluation_scores`	Scores per criterion within an evaluation
`recruitment_scorecard_templates`	Evaluation criteria templates for jobs
`recruitment_scorecard_criteria`	Individual criteria within a scorecard template
`recruitment_email_templates`	Email templates for candidate communication
`recruitment_stage_email_rules`	Auto-send email rules triggered by stage transitions
`recruitment_communications`	Sent emails and messages to candidates

Projects & Tasks

Table	Purpose
`projects`	Client projects with company, owner, delivery manager, squad, SLA policy, type, auto-ticket config
`project_members`	Team members assigned to a project with role
`project_goals`	OKR-style goals within a project
`project_decisions`	Key decisions recorded on a project with context and outcome
`project_ai_summaries`	AI-generated project summaries cached per project
`project_reports`	Periodic project status reports with metrics and narrative
`project_report_settings`	Per-project report configuration (frequency, recipients, sections)
`project_setup_templates`	Reusable project setup templates with phases and tasks
`project_setup_template_phases`	Phase definitions within a setup template
`project_setup_template_tasks`	Task definitions within a setup template phase
`tasks`	Task/issue tracking scoped to any resource. Includes stage, priority, estimate, approval fields
`task_resources`	Resource allocations within a task (compute, budget, team)
`task_sla`	SLA tracking per task — response/resolution deadlines, breach flags
`task_todos`	Ordered checklist items within a task
`task_watchers`	Users watching a task for notifications
`sprints`	Time-boxed iterations within a project

Roadmaps

Table	Purpose
`roadmaps`	Named roadmaps with description and date range
`roadmap_items`	Individual items on a roadmap with status, dates, owner

Budgets & Invoices

Table	Purpose
`budgets`	Project budgets with type (fixed/time_and_materials), total, billing details
`budget_line_items`	Line items within a budget (service, quantity, rate, amount)
`invoices`	Invoices with status, amounts, payment terms, Xero sync tracking
`invoice_line_items`	Line items within an invoice
`invoice_payments`	Payment records against invoices
`invoice_mapping_rules`	Rules for mapping time entries to invoice line items

Time Tracking

Table	Purpose
`time_entries`	Manual time entries with project, task, duration, billing status
`activity_signals`	Auto-detected activity signals from connected tools (Memtime-style)
`auto_time_drafts`	AI-generated draft time entries from activity signals
`auto_tracking_preferences`	Per-user auto-tracking configuration

Resourcing

Table	Purpose
`allocations`	Resource bookings linking a user to a project, deal, or recruitment role for a date range
`user_weekly_schedules`	Per-user weekly availability schedules
`user_resourcing_profiles`	Skills, rates, and capacity data for resourcing
`resourcing_suggestions`	AI-suggested resource matches for project/role needs

SLA

Table	Purpose
`sla_policies`	Named SLA policy definitions with response/resolution targets
`sla_policy_rules`	Priority-based rules within an SLA policy

Emails

Table	Purpose
`emails`	Synced emails with subject, body, thread tracking, entity matching
`email_contacts`	Email address ↔ contact resolution
`email_sync_state`	Per-user email sync cursor/token state
`inbound_emails`	Emails received via the inbound webhook (auto-ticket creation)

Pages (Wiki)

Table	Purpose
`pages`	Rich-text wiki pages with title, content, icon, cover, parent hierarchy
`page_favourites`	Per-user bookmarked pages
`page_discussions`	Discussion threads on a page
`page_comments`	Comments within a page discussion
`page_versions`	Version history snapshots of page content

Meetings

Table	Purpose
`meetings`	Synced calendar meetings with attendees, transcript, entity matching
`meeting_attendees`	Attendees per meeting
`meeting_transcripts`	Meeting transcript content
`meeting_action_items`	Action items extracted from meetings

Client Intelligence

Table	Purpose
`client_profiles`	Client tier, health score, assigned DM, dashboard settings. FK to `companies`
`client_onboarding_templates`	Templates for client onboarding journeys
`client_onboarding_stages`	Stages within a client onboarding template
`client_onboarding_milestones`	Milestones within a client onboarding stage
`client_milestone_progress`	Per-client progress on onboarding milestones
`client_health_scores`	Historical health score records per client
`client_tier_history`	Tier scoring history for trend tracking
`client_platform_connections`	OAuth/API credentials for connected platforms (Shopify, Meta Ads, etc)
`client_metric_snapshots`	Time-series key-value metrics (ad_spend, revenue, sessions, etc)
`client_campaign_snapshots`	Denormalised campaign-level metrics for ad platforms
`client_dashboard_insights`	AI-generated weekly analysis with health score and recommendations
`client_oauth_states`	OAuth flow state for client platform connections
`client_goals`	Metric targets with baseline, current value, and tracking
`client_qbrs`	Quarterly Business Reviews with AI-generated content and portal visibility
`client_service_cells`	Service offering cells within a client relationship
`client_success_profiles`	Financial year, trading periods, channel mix, growth priorities
`client_wins`	Notable outcomes linked to projects, goals, or meetings

Portal

Table	Purpose
`portal_users`	External client users with access to the client portal
`portal_invitations`	Pending invitations to the client portal

Notifications

Table	Purpose
`notifications`	In-app notifications with type, title, body, action URL, read/dismissed state
`notification_preferences`	Per-user notification channel preferences
`notification_types`	Registered notification type definitions
`notification_delivery_log`	Delivery tracking per notification per channel
`push_subscriptions`	Web push subscription endpoints per user

Squads

Table	Purpose
`squad_sessions`	4P squad kickoff/retro sessions
`squad_actions`	Action items from squad sessions
`squad_leads`	Squad leadership assignments
`squad_notification_config`	Per-squad notification routing config

Action Centre

Table	Purpose
`action_items`	Centralised action items across all sources (meetings, reviews, squads)
`action_centre_preferences`	Per-user action centre display preferences

Custom Fields & Config

Table	Purpose
`custom_field_definitions`	User-defined field schemas per entity type
`custom_field_values`	Field values per entity per custom field definition
`config_lists`	Named configuration lists (dropdown options, categories)
`config_list_items`	Items within a configuration list
`service_types`	Service type definitions for projects and budgets

Agents (Dev/AI)

Table	Purpose
`agent_project_config`	Per-project agent configuration (enabled features, thresholds)
`agent_task_meta`	Agent metadata per task (session_id, status, last run)
`agent_task_runs`	Individual agent execution runs with input/output/duration

Relationships (CRM Intelligence)

Table	Purpose
`relationship_auto_actions`	AI-suggested relationship nurturing actions
`relationship_messages`	Drafted/sent relationship messages

Miscellaneous

Table	Purpose
`lead_scan_log`	Lead scanning activity log

Dropped Tables

The following tables have been removed during migrations:

Table	Reason
`user_external_ids`	External IDs moved to `people` table columns (`xero_employee_id`, `productive_id`) in Phase 3
`squad_members`	Squad membership now derived from `people.squad` column, dropped in Phase 7
`leave_request_history`	Removed — leave audit trail now tracked via the `history` table

People-Centric Data Model Migration

The platform migrated from user-centric to people-centric data ownership across 7 phases:

Phase 1: Employee/HR fields moved from users to people. The users table is now auth-only.
Phase 2: Added person_id foreign keys to 8 employee data tables. Auth middleware resolves personId on every request.
Phase 3: Eliminated user_external_ids. External ID lookups read directly from people columns.
Phase 4: Added getScopedPersonIds() using people.manager_id hierarchy for data scoping.
Phase 5: Dropped dual-writes. All data queries use person_id. Manager verification uses people.manager_id.
Phase 6: Migrated payroll and leave balance reads to person_id. Replaced squad_members usage with people.manager_id hierarchy.
Phase 7: Recreated all 8 data tables to drop user_id columns. person_id is NOT NULL REFERENCES people(id) ON DELETE CASCADE. Dropped squad_members table.

Running Migrations

All wrangler commands must run from apps/app/ (where wrangler.toml lives):

# Check pending migrations
npx wrangler d1 migrations list nucleus-db --local

# Apply migrations locally
npx wrangler d1 migrations apply nucleus-db --local

# Apply migrations to production
npx wrangler d1 migrations apply nucleus-db --remote

# Initial local setup (schema + seed)
pnpm db:migrate && pnpm db:seed

Rollbacks

db/migrations/ is forward-only. Never drop a *.down.sql (or any other non-forward script) into it — wrangler will auto-apply it as another forward migration. Manual rollbacks live in db/rollbacks/ and are run on demand:

cd apps/app
pnpm wrangler d1 execute nucleus-db --remote \
  --file=db/rollbacks/<migration-id>.down.sql

See Database Migrations → Rollbacks for the full convention.