Database Schema¶
PostgreSQL 16 (CloudNativePG). Migrations are embedded SQL files under api/internal/migrations/ and run automatically on API startup via golang-migrate.
Tables¶
organizations¶
Top-level grouping. Each org contains projects.
| Column | Type | Notes |
|---|---|---|
| id | BIGSERIAL PK | |
| name | TEXT NOT NULL | |
| description | TEXT NOT NULL | Default '' |
| gitea_org | TEXT NULL | Gitea organisation slug (e.g. Skatzi). NULL = no sync. |
| created_at | TIMESTAMPTZ | |
| updated_at | TIMESTAMPTZ |
projects¶
Belongs to one organization. Contains tasks and task groups.
| Column | Type | Notes |
|---|---|---|
| id | BIGSERIAL PK | |
| organization_id | BIGINT FK → organizations.id | CASCADE delete |
| name | TEXT NOT NULL | |
| description | TEXT NOT NULL | Default '' |
| gitea_repo | TEXT NULL | Gitea repo name (e.g. platform). NULL = no sync. Gitea sync is active when both gitea_org (on the org) and gitea_repo are set. |
| created_at | TIMESTAMPTZ | |
| updated_at | TIMESTAMPTZ |
task_groups¶
Kanban columns within a project (Backlog, In Progress, Done, etc.).
| Column | Type | Notes |
|---|---|---|
| id | BIGSERIAL PK | |
| project_id | BIGINT FK → projects.id | CASCADE delete |
| name | TEXT NOT NULL | |
| position | INT NOT NULL | Display order |
| created_at | TIMESTAMPTZ |
tasks¶
Unit of work. Belongs to a project, optionally placed in a task group.
| Column | Type | Notes |
|---|---|---|
| id | BIGSERIAL PK | |
| project_id | BIGINT FK → projects.id | CASCADE delete |
| group_id | BIGINT FK → task_groups.id NULL | SET NULL on delete |
| milestone_id | BIGINT FK → milestones.id NULL | SET NULL on delete |
| epic_id | BIGINT FK → epics.id NULL | SET NULL on delete |
| title | TEXT NOT NULL | |
| description | TEXT NOT NULL | Markdown. Default '' |
| done | BOOLEAN NOT NULL | Default false |
| priority | SMALLINT NOT NULL | 0=none 1=low 2=medium 3=high. Default 0 |
| assignee | TEXT NULL | Username or agent ID |
| assignee_type | SMALLINT NULL | 1=user 2=agent |
| gitea_issue_number | BIGINT NULL | Issue number in the linked Gitea repo. NULL = not synced. |
| created_at | TIMESTAMPTZ | |
| updated_at | TIMESTAMPTZ |
milestones¶
Organization-level time-boxed milestones. Tasks and epics can be assigned to a milestone.
| Column | Type | Notes |
|---|---|---|
| id | BIGSERIAL PK | |
| organization_id | BIGINT FK → organizations.id | CASCADE delete |
| title | TEXT NOT NULL | |
| description | TEXT NOT NULL | Markdown. Default '' |
| due_date | TIMESTAMPTZ NULL | |
| created_at | TIMESTAMPTZ | |
| updated_at | TIMESTAMPTZ |
epics¶
Organization-level grouping that spans across projects.
| Column | Type | Notes |
|---|---|---|
| id | BIGSERIAL PK | |
| organization_id | BIGINT FK → organizations.id | CASCADE delete |
| milestone_id | BIGINT FK → milestones.id NULL | SET NULL on delete |
| title | TEXT NOT NULL | |
| description | TEXT NOT NULL | Markdown. Default '' |
| status | SMALLINT NOT NULL | Default 0 |
| created_at | TIMESTAMPTZ | |
| updated_at | TIMESTAMPTZ |
api_keys¶
Persistent API keys for agent and service access (alternative to Keycloak JWT).
| Column | Type | Notes |
|---|---|---|
| id | BIGSERIAL PK | |
| name | TEXT NOT NULL | Human label |
| key_hash | TEXT NOT NULL | bcrypt hash of the key |
| key_prefix | TEXT NOT NULL | First 8 chars for display |
| created_at | TIMESTAMPTZ | |
| last_used_at | TIMESTAMPTZ NULL |
Migration history¶
| # | Migration | Notes |
|---|---|---|
| 001 | create_tasks | Initial tasks table |
| 002 | add_projects | projects table + project_id on tasks |
| 003 | add_task_groups | task_groups table + group_id on tasks |
| 004 | add_task_priority | priority column on tasks |
| 005 | add_task_assignee | assignee, assignee_type columns on tasks |
| 006 | add_organizations | organizations table + organization_id on projects |
| 007 | fix_org_cascade | Correct cascade behaviour on org delete |
| 008 | add_milestones | milestones table + milestone_id on tasks |
| 009 | add_api_keys | api_keys table |
| 010 | add_epics | epics table + epic_id on tasks |
| 011 | add_gitea_fields | gitea_org on organizations, gitea_repo on projects, gitea_issue_number on tasks |