Database

Overview

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

Tables

Core

Table	Purpose
users	All authenticated users (auto-created on first login)
tool_permissions	Per-tool, per-access-level permission grants
squads	Team groupings for data scoping
squad_members	User-to-squad membership with manager flag

Onboarding

Table	Purpose
onboarding_phases	5 phases with name, description, sort order
onboarding_items	Checklist items within phases
onboarding_progress	Per-user completion state for each item

Scorecards

Table	Purpose
scorecards	Scorecard header (user, squad, week, notes)
scorecard_scores	Individual score items (role, status, note)
scorecard_kpis	Monthly KPIs (text, target, actual, status)
scorecard_metrics	Financial metrics (billable, utilisation, revenue, profit)

Running Migrations

# Apply schema locally
pnpm db:migrate

# Apply seed data locally
pnpm db:seed

# Apply to remote (production)
wrangler d1 execute dotcollective-tools-db --remote --file=db/schema.sql
wrangler d1 execute dotcollective-tools-db --remote --file=db/seed.sql