Skip to content

Data Model

Doable's database schema is defined as numbered SQL migration files in packages/db/migrations/. Migrations are applied in lexicographic order by pnpm db:migrate.

There is no ORM. Queries live in packages/db/src/queries/ using the lightweight postgres tagged-template driver.

Required PostgreSQL extensions

Extension Used for
pgcrypto UUID v4 generation (gen_random_uuid()), HMAC helpers
pgvector Embeddings for semantic context search
pg_trgm Fuzzy text search over project names, marketplace listings

Loaded by docker/init.sql on first DB boot.

Domain map

The schema groups roughly by business domain. Each group is one or more migration files plus a query module.

Domain Migration files Query module
Users & auth (created earliest, updated by later files) queries/users.ts, queries/auth.ts
Workspaces & members 017_teams.sql, 023_platform_roles.sql queries/workspaces.ts
Projects & folders (early), 046_project_views.sql, 047_project_files.sql queries/projects.ts, queries/folders.ts, queries/project-views.ts
Project files (cache) 047_project_files.sql (raw SQL in services/api)
Versioning 024_git_versioning.sql queries/security.ts (incl. snapshots)
Chat & AI sessions (early) queries/chat.ts
AI providers & preferences 011_user_ai_preferences.sql, 038_ai_providers_extended_columns.sql queries/ai-settings*.ts
Analytics 004_analytics.sql, 016_analytics_v2.sql queries/analytics*.ts
Billing & credits 013_billing.sql, 014_credit_system.sql queries/billing.ts, queries/credits.ts
GitHub integration 014_github.sql queries/github.ts
Deployments 018_deployments.sql queries/deployments.ts
Security events 019_security.sql queries/security.ts
Community / stars 020_community.sql queries/community.ts, queries/stars.ts
Live collaboration 021_live_collaboration.sql queries/folders.ts (presence cache)
Custom domains 022_custom_domains.sql queries/custom-domains.ts
Thumbnails 026_thumbnail_logs.sql queries/projects.ts
Share tracking 041_share_tracking.sql queries/share-tracking.ts
Email queue / config 048_email_queue.sql, 049_email_config.sql services/api/src/lib/email/
Mode / tool config 050_mode_tool_config.sql queries/mode-tools.ts
Marketplace & templates 020_community.sql, 040_* queries/marketplace*.ts, queries/templates.ts
Skills (early) queries/skills.ts
Connectors / integrations (early) queries/connectors.ts
Environments / env vars (early) queries/environments*.ts, queries/env-vars.ts
Team chat (early) queries/team-chat.ts
Activity events (early) queries/activity-events.ts
Feature flags 012_platform_admin_feature_flags.sql queries/feature-flags.ts
Context (semantic) (early) queries/context.ts

Files stored on disk (not in the DB) include:

  • PROJECTS_ROOT/<projectId>/ — every file the AI writes for a project.
  • services/api/thumbnails/ — generated PNG previews.
  • ~/.copilot/session-state/<sessionId>/ — Copilot SDK session snapshots.

The DB stores metadata, ownership, and a fast-read cache — never the source of truth for code.

Core tables (cheat sheet)

A few of the most important columns to know:

-- users
id UUID PK,
email TEXT UNIQUE,
display_name TEXT,
password_hash TEXT,            -- bcrypt; null if OAuth-only
oauth_provider TEXT,           -- 'github' | 'google' | null
oauth_subject TEXT,
created_at TIMESTAMPTZ

-- workspaces (multi-tenant boundary)
id UUID PK,
slug TEXT UNIQUE,
name TEXT,
owner_user_id UUID REFERENCES users(id),
plan TEXT,                     -- 'free' | 'pro' | 'business'
ai_settings JSONB,             -- per-workspace AI overrides
created_at TIMESTAMPTZ

-- workspace_members
workspace_id UUID, user_id UUID,
role TEXT,                     -- 'owner' | 'admin' | 'member'
PRIMARY KEY (workspace_id, user_id)

-- projects
id UUID PK,
workspace_id UUID,
folder_id UUID NULL,
slug TEXT,
name TEXT,
template TEXT,
visibility TEXT,               -- 'private' | 'workspace' | 'public'
published_at TIMESTAMPTZ,
custom_domain TEXT NULL,
created_at TIMESTAMPTZ

-- project_files (cache; canonical = filesystem)
id UUID PK,
project_id UUID,
file_path TEXT,
content TEXT,
UNIQUE (project_id, file_path)

-- chats (one per project)
id UUID PK,
project_id UUID,
title TEXT,
mode TEXT,                     -- 'build' | 'plan' | 'chat'
created_at TIMESTAMPTZ

-- chat_messages
id UUID PK,
chat_id UUID,
role TEXT,                     -- 'user' | 'assistant' | 'tool'
content JSONB,                 -- normalized DoCoreEvent payload
created_at TIMESTAMPTZ

Why tables aren't generated

Rather than introduce an ORM (Prisma, Drizzle), Doable keeps:

  • Hand-written SQL migrations — explicit and reviewable.
  • Hand-written query helpers — full control over indexes, JOINs, and locking.
  • Generated TypeScript types in types-core.ts / types-ai.ts for compile-time safety.

If you prefer an ORM in your fork, both Prisma and Drizzle can introspect the schema (pnpm db:migrate first, then prisma introspect / drizzle-kit pull).

See also