# 02 — Data Model (PostgreSQL)

Complete schema, written as golang-migrate migrations under `internal/db/migrations/`. Conventions from [01-architecture.md](01-architecture.md) apply (identity PKs, timestamptz, soft delete, `ON DELETE RESTRICT` FKs). Extensions: `citext`.

Statuses/priorities are **fixed** product-wide, so native Postgres enums are safe and self-documenting. Estimation is **story points**, Fibonacci subset.

Deviations from the first iteration's SQLite schema are marked *(deviation)* with reasons; the full rationale is in [17-decisions.md](17-decisions.md).

## Enum types

- [ ] `project_status`: `backlog | planned | in_progress | completed | canceled`
- [ ] `epic_status`: `backlog | planned | in_progress | completed | canceled`
- [ ] `issue_status`: `backlog | planned | in_progress | ready_for_review | done | canceled | duplicate`
- [ ] `priority`: `low | medium | high | urgent | frantic` (nullable column = "none")
- [ ] `member_role`: `owner | member`
- [ ] `auth_provider`: `github | gitea`
- [ ] `resource_kind`: `project | issue | epic | comment | wiki_page | user | label | attachment | link | member | webhook | auth`
- [ ] `job_status`: `pending | running | done | failed | dead`
- [ ] `receipt_status`: `pending | processed | failed | ignored`
- [ ] `delivery_status`: `pending | success | failed`
- [ ] `email_mode`: `off | instant | daily_digest`

## Tables

### Identity & auth

- [ ] **users**
  - [ ] `id` bigint identity PK
  - [ ] `username` citext UNIQUE NOT NULL; `name` text NOT NULL; `email` citext UNIQUE NOT NULL
  - [ ] `password_hash` text NULL (bcrypt cost 12; NULL = OAuth-only account)
  - [ ] `avatar_url` text NULL; `is_admin` boolean NOT NULL DEFAULT false
  - [ ] `email_mode` email_mode NOT NULL DEFAULT 'instant'
  - [ ] `created_at`, `updated_at` NULL, `deleted_at` NULL
  - [ ] Seeds: id 1 `system` ("System"), id 2 `deleted-user` ("Deleted User") — both `password_hash` NULL, excluded from login, member pickers, and search; identity sequence restarted at 3
- [ ] **oauth_accounts** *(deviation: replaces the first iteration's single `provider` column on users — allows local+GitHub+Gitea linked to one account, links by verified email at callback)*
  - [ ] `id` PK; `user_id` FK→users NOT NULL; `provider` auth_provider NOT NULL; `provider_user_id` text NOT NULL; `created_at`
  - [ ] UNIQUE(`provider`, `provider_user_id`); UNIQUE(`user_id`, `provider`)
- [ ] **sessions** — exact scs pgxstore schema
  - [ ] `token` text PK; `data` bytea NOT NULL; `expiry` timestamptz NOT NULL; index on `expiry`
- [ ] **api_tokens**
  - [ ] `id` PK; `user_id` FK NOT NULL; `name` text NOT NULL
  - [ ] `token_hash` text UNIQUE NOT NULL (SHA-256 hex of full token `solopm_<40 random base62>`)
  - [ ] `prefix` text NOT NULL (first 12 chars, display only)
  - [ ] `last_used_at` NULL; `expires_at` NULL; `revoked_at` NULL; `created_at`

### Projects

- [ ] **projects**
  - [ ] `id` PK; `creator_id` FK→users NOT NULL; `owner_id` FK→users NOT NULL
  - [ ] `title` text NOT NULL; `description` text NOT NULL DEFAULT '' (markdown)
  - [ ] `status` project_status NOT NULL DEFAULT 'backlog'; `priority` priority NULL
  - [ ] `start_date` date NULL; `target_date` date NULL; `completed_at` timestamptz NULL (set when status→completed)
  - [ ] `git_repository_url` text NULL (used to route incoming webhooks)
  - [ ] `next_issue_number` int NOT NULL DEFAULT 1 *(deviation: explicit counter for safe concurrent `#N` allocation)*
  - [ ] `created_at`, `updated_at`, `deleted_at`
  - [ ] Indexes: (`owner_id`); partial (`id`) WHERE `deleted_at IS NULL`
- [ ] **project_members**
  - [ ] `id` PK; `project_id` FK NOT NULL; `user_id` FK NOT NULL; `role` member_role NOT NULL DEFAULT 'member'
  - [ ] `invited_by` FK→users NULL; `created_at`
  - [ ] UNIQUE(`project_id`, `user_id`); index (`user_id`)
  - [ ] Invariant (service-enforced): exactly one row with role `owner` per project, matching `projects.owner_id`
- [ ] **project_dependencies** — "this project is blocked by that project"
  - [ ] `id` PK; `project_id` FK NOT NULL; `depends_on_project_id` FK→projects NOT NULL; `created_by` FK NOT NULL; `created_at`
  - [ ] UNIQUE(`project_id`, `depends_on_project_id`); CHECK(`project_id <> depends_on_project_id`)
  - [ ] Cycle detection at service layer (DFS over edges) → `409 cycle_detected`
- [ ] **project_favorites**
  - [ ] composite PK(`user_id`, `project_id`); `created_at` (dashboard sorts favorites first)
- [ ] **labels** *(deviation: renamed from `project_labels`)*
  - [ ] `id` PK; `project_id` FK NOT NULL; `name` text NOT NULL; `color` text NOT NULL (hex)
  - [ ] `created_by` FK NOT NULL; `created_at`; `deleted_at`
  - [ ] Partial UNIQUE(`project_id`, `name`) WHERE `deleted_at IS NULL`
  - [ ] Seeded on project creation: enhancement, bug, chore, task, feature, documentation (colors from the design-system palette, [14-design-system.md](14-design-system.md))

### Work items

- [ ] **epics**
  - [ ] `id` PK; `project_id` FK NOT NULL; `creator_id` FK NOT NULL
  - [ ] `title` text NOT NULL; `description` text NOT NULL DEFAULT ''; `status` epic_status NOT NULL DEFAULT 'backlog'
  - [ ] `color` text NOT NULL (hex, from palette)
  - [ ] `created_at`, `updated_at`, `deleted_at`
  - [ ] Partial index (`project_id`) WHERE `deleted_at IS NULL`
- [ ] **issues**
  - [ ] `id` PK; `project_id` FK NOT NULL; `project_issue_id` int NOT NULL — UNIQUE(`project_id`, `project_issue_id`)
  - [ ] `creator_id` FK NOT NULL; `assignee_id` FK→users NULL
  - [ ] `epic_id` FK→epics NULL; `parent_issue_id` FK→issues NULL (sub-issues; one level of UI nesting, arbitrary depth allowed in data)
  - [ ] `title` text NOT NULL; `description` text NOT NULL DEFAULT '' (markdown)
  - [ ] `status` issue_status NOT NULL DEFAULT 'backlog'; `priority` priority NULL
  - [ ] `estimate` smallint NULL CHECK (`estimate` IN (1,2,3,5,8,13)) — story points
  - [ ] `start_date` date NULL; `target_date` date NULL; `completed_at` timestamptz NULL (set automatically when status→done, cleared when leaving done)
  - [ ] `board_rank` text NOT NULL — fractional lexicographic rank within status column (kanban order)
  - [ ] `epic_rank` text NULL — order within its epic
  - [ ] `created_at`, `updated_at`, `deleted_at`
  - [ ] Indexes: (`project_id`, `status`), (`assignee_id`), (`epic_id`), (`parent_issue_id`)
  - [ ] Number allocation inside the insert tx: `UPDATE projects SET next_issue_number = next_issue_number + 1 WHERE id = $1 RETURNING next_issue_number - 1`
- [ ] **issue_blockers** — "issue X is blocked by issue Y" (same project only)
  - [ ] `id` PK; `issue_id` FK NOT NULL; `blocked_by_issue_id` FK→issues NOT NULL; `created_by` FK; `created_at`
  - [ ] UNIQUE(`issue_id`, `blocked_by_issue_id`); CHECK(`issue_id <> blocked_by_issue_id`); service-layer cycle detection → 409
- [ ] **issue_labels** *(deviation: hard-delete pivot, no `deleted_at` — audit log preserves history)*
  - [ ] `id` PK; `issue_id` FK NOT NULL; `label_id` FK NOT NULL; `applied_by` FK NOT NULL; `created_at`
  - [ ] UNIQUE(`issue_id`, `label_id`)
- [ ] **issue_watchers**
  - [ ] composite PK(`issue_id`, `user_id`); `muted` boolean NOT NULL DEFAULT false; `created_at`
  - [ ] `muted=true` = explicitly unsubscribed; auto-watch must never overwrite it

### Collaboration

- [ ] **comments**
  - [ ] `id` PK; `issue_id` FK NOT NULL; `author_id` FK NOT NULL; `body` text NOT NULL (markdown)
  - [ ] `resolved_at` NULL; `resolved_by` FK→users NULL (resolvable threads)
  - [ ] `created_at`, `updated_at`, `deleted_at`
  - [ ] Index (`issue_id`, `created_at`)
- [ ] **links** *(deviation: one polymorphic table replaces project_links/issue_links/comment_links — culling handles integrity, API surface shrinks 3×)*
  - [ ] `id` PK; `resource_type` resource_kind NOT NULL (allowed: project|issue|comment); `resource_id` bigint NOT NULL
  - [ ] `url` text NOT NULL; `title` text NULL; `created_by` FK NOT NULL; `created_at`; `deleted_at`
  - [ ] Index (`resource_type`, `resource_id`)
- [ ] **attachments** *(same polymorphic deviation)*
  - [ ] `id` PK; `resource_type` resource_kind NOT NULL (project|issue|comment); `resource_id` bigint NOT NULL
  - [ ] `uploader_id` FK NOT NULL; `file_path` text NOT NULL — relative to `UPLOAD_DIR`, format `project_{pid}/{uploader_id}_{yyyymmddhhmmss}_{rand16}.{ext}`
  - [ ] `original_filename` text NOT NULL; `mime_type` text NOT NULL; `size_bytes` bigint NOT NULL
  - [ ] `created_at`; `deleted_at`
  - [ ] Index (`resource_type`, `resource_id`)
- [ ] **wiki_pages**
  - [ ] `id` PK; `project_id` FK NOT NULL; `slug` text NOT NULL (kebab-case from title); `title` text NOT NULL; `body` text NOT NULL DEFAULT '' (markdown)
  - [ ] `created_by` FK NOT NULL; `updated_by` FK NOT NULL
  - [ ] `created_at`, `updated_at`, `deleted_at`
  - [ ] Partial UNIQUE(`project_id`, `slug`) WHERE `deleted_at IS NULL`

### Audit, notifications, events, jobs

- [ ] **audit_log** — immutable; never UPDATEd; culled only by retention policy
  - [ ] `id` PK; `created_at` NOT NULL DEFAULT now()
  - [ ] `initiated_by` FK→users NOT NULL (system user id 1 for automation); `via_webhook` boolean NOT NULL DEFAULT false
  - [ ] `project_id` bigint NULL *(deviation: denormalized so project activity feed is one indexed query)*
  - [ ] `resource_type` resource_kind NOT NULL; `resource_id` bigint NOT NULL; `action_type` text NOT NULL (matches event taxonomy, [03-domain-events.md](03-domain-events.md))
  - [ ] `previous_values` jsonb NULL; `new_values` jsonb NULL (changed fields only)
  - [ ] Indexes: (`resource_type`, `resource_id`), (`initiated_by`, `created_at`), (`created_at`), (`project_id`, `created_at` DESC)
  - [ ] Display text is generated app-side from action_type + values (no stored `summary` prose)
- [ ] **notifications**
  - [ ] `id` PK; `user_id` FK NOT NULL (recipient); `actor_id` FK NOT NULL
  - [ ] `type` text NOT NULL: `assigned | mentioned | commented | added_to_project | status_changed`
  - [ ] `project_id` FK NULL; `resource_type` resource_kind NOT NULL; `resource_id` bigint NOT NULL
  - [ ] `payload` jsonb NOT NULL (denormalized title/snippet/refs for rendering without joins)
  - [ ] `read_at` NULL; `created_at`
  - [ ] Index (`user_id`, `read_at`, `created_at` DESC)
- [ ] **events** — transactional outbox ([03-domain-events.md](03-domain-events.md))
  - [ ] `id` bigserial PK (ordering matters); `event_type` text NOT NULL; `actor_id` bigint NOT NULL; `project_id` bigint NULL
  - [ ] `resource_type` resource_kind NOT NULL; `resource_id` bigint NOT NULL
  - [ ] `payload` jsonb NOT NULL (prev/new snapshots, mention lists, etc.); `via_webhook` boolean NOT NULL DEFAULT false
  - [ ] `created_at` NOT NULL DEFAULT now(); `processed_at` NULL; `attempts` int NOT NULL DEFAULT 0
  - [ ] Partial index WHERE `processed_at IS NULL`
- [ ] **jobs** — retryable async work
  - [ ] `id` PK; `kind` text NOT NULL: `send_email | webhook_delivery | search_sync | daily_digest | cull`
  - [ ] `payload` jsonb NOT NULL; `status` job_status NOT NULL DEFAULT 'pending'
  - [ ] `run_at` timestamptz NOT NULL DEFAULT now(); `attempts` int NOT NULL DEFAULT 0; `max_attempts` int NOT NULL DEFAULT 8; `last_error` text NULL
  - [ ] `created_at`, `updated_at`
  - [ ] Index (`status`, `run_at`)

### Webhooks

- [ ] **incoming_webhook_receipts**
  - [ ] `id` PK; `project_id` FK NULL (NULL when repo URL didn't resolve to a project)
  - [ ] `source` auth_provider NOT NULL; `event_type` text NOT NULL; `delivery_id` text NOT NULL — UNIQUE(`source`, `delivery_id`) for redelivery idempotency
  - [ ] `payload` text NOT NULL (raw body, replayable); `signature_valid` boolean NOT NULL
  - [ ] `status` receipt_status NOT NULL DEFAULT 'pending'; `failure_reason` text NULL
  - [ ] `received_at` NOT NULL DEFAULT now(); `processed_at` NULL
- [ ] **outgoing_webhooks**
  - [ ] `id` PK; `project_id` FK NOT NULL; `url` text NOT NULL; `secret` text NOT NULL
  - [ ] `active` boolean NOT NULL DEFAULT true; `event_types` text[] NOT NULL DEFAULT '{}' (empty = all events)
  - [ ] `created_by` FK NOT NULL; `created_at`; `deleted_at`
- [ ] **outgoing_webhook_deliveries**
  - [ ] `id` PK; `webhook_id` FK NOT NULL; `event_id` FK→events NOT NULL — UNIQUE(`webhook_id`, `event_id`) (idempotent fan-out)
  - [ ] `event_type` text NOT NULL; `payload` jsonb NOT NULL
  - [ ] `status` delivery_status NOT NULL DEFAULT 'pending'; `response_status` int NULL; `response_body` text NULL (truncated 4 KB); `attempts` int NOT NULL DEFAULT 0
  - [ ] `delivered_at` NULL; `created_at`
  - [ ] Index (`webhook_id`, `created_at` DESC)

## Migration & seed checklist

- [ ] Migration 0001: extensions (`citext`) + all enum types
- [ ] Migrations grouped per domain in dependency order (users → oauth/sessions/tokens → projects family → epics/issues family → collaboration → audit/notifications/events/jobs → webhooks)
- [ ] Every migration has a working `.down.sql`
- [ ] Seed migration: system user (id 1) + zombie user (id 2), sequence restart at 3
- [ ] Label seeding on project creation implemented in the projects service (not a DB trigger)
- [ ] sqlc config covers all tables; generated code compiles; `make sqlc` is idempotent