Data model

The schema is small, opinionated, and conservative. Every domain entity lives in Postgres 16, keyed by UUIDv7, scoped to a workspace, and timestamped in UTC. Soft delete applies where recoverability matters; hard delete applies where the row is ephemeral; the audit log is never deleted.

This page is the operator-facing summary. The exhaustive column-level reference lives in spec/01_data_model.md. ADR-0005 covers the soft-delete policy; ADR-0007 covers the workspace decision.

Foundational conventions

These apply to every table. Understand them once; they hold everywhere.

Identifiers

• Primary keys are UUIDv7, generated in the application layer via the uuid6 package. UUIDv7 is time-ordered, so inserts cluster at the B-tree leaf edge instead of fragmenting the index. This matters most on the append-only tables (audit_event, tool_invocation_log, webhook_delivery) where insert volume dominates.
• Tickets also have a display ID of the form {PROJECT_KEY}-{counter} — e.g. PROJ-123. The display ID is what the UI shows, what wikilinks reference, and what bots cite when delegating. It is materialized on insert via a per-project counter (project.next_ticket_number) updated under SELECT ... FOR UPDATE inside the same transaction.
• Why UUIDv7 over a bigserial? Opaque sequential IDs leak volume and make cross-environment merges painful. UUIDv7 gives sortability without the leak.

Timestamps

Every table has created_at and updated_at, typed TIMESTAMP WITH TIME ZONE (timestamptz), defaulting to now() at the DB level. Application code uses timezone-aware UTC datetime objects throughout; naive datetimes are an error.

Workspace scoping

Every domain entity carries workspace_id as a NOT NULL foreign key from day one, even though v1 has exactly one workspace. The rationale is asymmetric cost: one column today versus a coordinated ALTER TABLE and backfill across every table later. The repository base class applies WHERE workspace_id = :ws automatically; service code does not pass workspace through every call.

The single workspace is enforced at the application layer (a startup check confirms exactly one row). The DB allows more so that flipping MULTI_WORKSPACE_ENABLED is a config change, not a migration. See ADR-0007.

Soft delete policy

The schema uses four deletion buckets, applied per entity. The full list and rationale live in ADR-0005.

Bucket	Policy	Applies to
Soft delete	deleted_at column; NULL = live; default queries filter it.	ticket, wiki_page, project, comment
Hard delete	The row is gone on delete.	sticky_note, label, workflow_state, ticket_relation, ticket_watcher, webhook
Sweeper-driven	A background job purges expired rows; not user-triggered.	session, idempotency_record, tool_invocation_log
Never deleted by app	Append-only; no DELETE statement exists in the codebase.	audit_event, wiki_revision, webhook_delivery, bot_token

Note

bot_token lives in the "never deleted" bucket even though the table has no deleted_at column. Rotation flips is_active=false and stamps rotated_at; revocation stamps revoked_at. The row stays for forensics — every token ever issued must remain identifiable.

Comment deletion has a special twist. Soft-deleting a comment is allowed, but the deletion audit_event payload must include the full body and edit_history at time of deletion. The audit trail survives even if the row is later hard-purged. This is the only place where audit payload is load-bearing beyond "what happened" into "what was the content."

Enums

All enum-like columns are stored as text with a CHECK constraint, not as native Postgres ENUM types. Native enums are painful to alter via Alembic; text + CHECK is straightforward and Pydantic enforces the same domain in the application layer.

Entity relationships

erDiagram
    workspace ||--o{ project : contains
    workspace ||--o{ user : contains
    workspace ||--o{ webhook : contains
    workspace ||--o{ wiki_page : contains
    workspace ||--o{ sticky_note : contains
    workspace ||--o{ audit_event : contains

    user ||--o| bot_token : "active (if bot)"
    user ||--o{ session : "has (if human)"
    user ||--o{ tool_invocation_log : "invoked (if bot)"

    project ||--o{ ticket : contains
    project ||--o{ workflow_state : defines
    project ||--o{ label : defines
    project ||--o{ wiki_page : "optional scope"
    project ||--o{ sticky_note : "optional scope"

    ticket ||--o{ ticket : "parent_of"
    ticket }o--|| workflow_state : "is in"
    ticket }o--o| user : "assigned to"
    ticket }o--|| user : "created by"
    ticket ||--o{ comment : has
    ticket ||--o{ ticket_label : tags
    ticket ||--o{ ticket_watcher : watched_by
    ticket ||--o{ ticket_relation : "source of"
    label ||--o{ ticket_label : applied

    wiki_page ||--o{ wiki_revision : "has"
    wiki_page ||--o{ wiki_link : "outgoing"

    webhook ||--o{ webhook_delivery : delivers
    audit_event ||--o{ webhook_delivery : "triggered by"

    user ||--o{ audit_event : actor
    tool_invocation_log }o--o| audit_event : "pointed to by (FK nullable post-purge)"

The diagram omits the workspace_id FK on every entity to keep the picture readable. Every box hangs off workspace in reality.

Identity entities

The identity layer models humans and bots as rows in a single user table discriminated by type. This avoids dual-FK proliferation across every actor-referencing column in the schema; see ADR-0003.

user

A unified actor record. The type discriminator drives both authentication and authorization.

Field	Type	Notes
id	uuid	UUIDv7.
workspace_id	uuid	FK.
type	text	human or bot.
username	text	Unique per workspace. For bots, a stable handle (e.g. bot-claude-1).
display_name	text	Free-form.
email	text	Humans only; NULL for bots.
password_hash	text	Humans only; bcrypt cost ≥ 12. NULL for bots.
is_active	boolean	Disables login/API access without deletion.
bot_mode	text	Bots only; read or write.
bot_project_allowlist	uuid[]	Bots only; NULL for humans. Empty array = access to ALL projects (global-access). Non-empty = restricted to listed ids.
can_edit_global_wiki	boolean	Bots only in practice; humans bypass the check at the service layer.
rate_limit_per_minute	integer	Reserved for future per-actor REST + MCP throttling. v1 stores the value but does not enforce it; only login is rate-limited.

Invariants (enforced both in Pydantic and via DB CHECK):

• type = 'human' ⇒ password_hash NOT NULL, bot_mode NULL, bot_project_allowlist NULL.
• type = 'bot' ⇒ password_hash NULL, bot_mode NOT NULL, email NULL.

At the Python layer, User is a Pydantic discriminated union — service code switches on user.type and gets the narrow type for free. The SQLAlchemy ORM stays as a single User class.

There is no soft delete on user — use is_active=false. The row is referenced by audit events forever, so hard delete is forbidden.

bot_token

Hashed credentials for a bot. One active token per bot at a time; rotations create a new row and deactivate the old one.

Field	Type	Notes
id	uuid
workspace_id	uuid	FK to workspace.id; every row is workspace-scoped.
bot_user_id	uuid	FK to user.id (must reference a bot).
token_hash	text	argon2id hash of the plaintext token.
token_prefix	text	First 8 chars of the plaintext, for lookup hints (not auth).
is_active	boolean	Exactly one active row per bot, enforced by partial unique index.
last_used_at	timestamptz	Throttled write (≤ once per 60s).
rotated_at	timestamptz	Set when superseded by a newer token.
revoked_at	timestamptz	Explicit revoke; separate from rotation.
created_at	timestamptz	Issuance time.

argon2id parameters: time_cost=3, memory_cost=64*1024, parallelism=4. argon2id was chosen over bcrypt because bot tokens are verified far more often than human passwords and benefit from memory-hardness.

See Auth and identity for the token format, verification flow, and rotation semantics.

session

Server-side store for human session cookies. The tb_session cookie carries a random token; the SHA-256 hash of that token maps to a row here.

Field	Type	Notes
id	uuid
user_id	uuid	FK; must reference a human.
token_hash	text	SHA-256 of the cookie value.
created_at	timestamptz
last_seen_at	timestamptz	Throttled write — updated only if older than 60 seconds.
expires_at	timestamptz	created_at + SESSION_ABSOLUTE_TIMEOUT_DAYS (default 30).
revoked_at	timestamptz	Set on logout or admin revoke; NULL = live.
ip	text	First-seen IP at login; forensic only.
user_agent	text	First-seen UA at login; forensic only.

Why hash the token? The cookie value is a bearer credential. Storing it plaintext means a leaked DB snapshot leaks every active session. The cookie has 256 bits of entropy, so SHA-256 (no salt) is sufficient.

The retention sweeper hard-deletes rows where expires_at < now(). Idle expiry is computed live as now() - last_seen_at > SESSION_IDLE_TIMEOUT_DAYS.

Project and ticket entities

project

A container of tickets with its own workflow, labels, and per-project ticket counter.

Field	Type	Notes
id	uuid
key	text	UPPERCASE, ^[A-Z][A-Z0-9]{1,9}$ (e.g. PROJ, TKBD).
name	text	Display name.
description	text	Markdown.
owner_user_id	uuid	Must be a human.
next_ticket_number	bigint	Per-project counter; mutated under row-level lock.
deleted_at	timestamptz	Soft delete.

The per-project counter is updated with SELECT next_ticket_number FROM project WHERE id = :id FOR UPDATE inside the same transaction that inserts the ticket. This avoids Postgres sequences (which are not transactional and require dynamic DDL per project). Contention is negligible at this scale — ticket creation is low-throughput.

workflow_state

A named state a ticket can occupy within a project.

Field	Type	Notes
id	uuid
project_id	uuid
name	text	e.g. "To Do", "In Progress", "Blocked", "Done".
category	text	backlog, active, or done. Drives board grouping and reports.
position	integer	Sort order within project. Renumbered on reorder.
is_default	boolean	Exactly one default per project (partial unique index).

Ordering uses an integer position with a full-list renumber on reorder. The state list per project is bounded at ~10 rows; LexoRank or float-gap positioning is unnecessary at that scale.

ticket

The unit of work. Polymorphic on type to model the epic/story/subtask hierarchy in a single table.

Field	Type	Notes
id	uuid
project_id	uuid
ticket_number	bigint	Per-project counter.
display_id	text	Materialized {key}-{number}, e.g. PROJ-123.
type	text	epic, story, or subtask.
title	text
body	text	Markdown.
state_id	uuid	Must belong to the same project.
priority	text	low, medium, high, urgent.
parent_ticket_id	uuid	NULL for epics; story may have epic parent; subtask must have story parent.
assignee_user_id	uuid	Nullable.
reporter_user_id	uuid	The creator; always set.
due_date	date	Date only.
closed_at	timestamptz	Set when state's category is done; cleared if it moves back.
extension	jsonb	Reserved extensibility slot; default '{}'; gated by ADR.
search_tsv	tsvector	Maintained by trigger; GIN-indexed for FTS.
deleted_at	timestamptz	Soft delete.

Hierarchy invariants (enforced in the service layer):

• type = 'epic' ⇒ parent_ticket_id IS NULL.
• type = 'story' ⇒ parent is NULL or an epic.
• type = 'subtask' ⇒ parent is a story (NOT NULL).

The extension JSONB column exists so we don't pay a migration cost the first time we genuinely need an extra field. It is off by default — no read or write to it in v1. Any usage requires a new ADR and a typed accessor; unconstrained JSONB undermines schema-as-documentation.

ticket_relation

Typed edges between tickets. Models "blocks," "relates to," "duplicates."

Field	Type	Notes
source_ticket_id	uuid
target_ticket_id	uuid
relation_type	text	blocks, relates_to, or duplicates.
created_by_user_id	uuid

blocks and duplicates are directional and asymmetric. relates_to is conceptually symmetric but stored as a single directed row to keep the model simple; the UI renders both directions identically. The reverse view of blocks ("blocked by") is computed by querying target_ticket_id = :id AND relation_type = 'blocks'.

comment

Markdown discussion on a ticket. Editable by the author with retained edit history.

Field	Type	Notes
ticket_id	uuid
author_user_id	uuid
body	text	Current markdown body.
edit_history	jsonb	Array of {at, body} prior versions; service appends on edit.
edited_at	timestamptz	NULL if never edited.
deleted_at	timestamptz	Soft delete; audit event carries the full body and edit history (see ADR-0005).

label, ticket_label, ticket_watcher

• label: per-project tag with a display color (CSS hex, validated in Pydantic).
• ticket_label: many-to-many join; composite PK on (ticket_id, label_id).
• ticket_watcher: subscription rows auto-populated by service rules (assignee, commenter, reporter) and explicitly editable. subscribed_via records how the row was created (manual, auto_assignee, ...).

All three are hard-delete: removing the row is the desired state.

Knowledge entities

wiki_page

Markdown document, identified by slug. Either global (project_id IS NULL) or project-scoped.

Field	Type	Notes
id	uuid
project_id	uuid	NULL = global page.
slug	text	Lowercase, must match ^[a-z0-9][a-z0-9-]*$; unique within (workspace, project).
title	text
body	text	Current markdown source.
rendered_html_cache	text	Cache of server-rendered HTML; NULL after save (see below).
current_revision_id	uuid	Convenience pointer to latest wiki_revision.
created_by_user_id	uuid
last_edited_by_user_id	uuid
search_tsv	tsvector	setweight('A', title) || setweight('B', body).
deleted_at	timestamptz	Soft delete.

Wiki rendering cache

The rendered_html_cache column holds server-rendered HTML for the page body. It is invalidated on save (set NULL) and lazily filled on next read. This avoids re-parsing markdown on every page view without introducing a separate cache layer. The cache is never queried; it is fetched alongside the row by ID, so no index is needed.

The full invalidation contract lives in spec/05_wiki_and_notes.md.

wiki_revision

Full snapshot on every save. Markdown is small, so storage is cheap.

Field	Type	Notes
wiki_page_id	uuid
title	text	Snapshot at save time.
body	text	Snapshot at save time.
editor_user_id	uuid
comment	text	Optional edit message.

Revisions are never deleted, even when the parent page is soft-deleted.

wiki_link

Edges in the wikilink graph. Populated by parsing [[wikilinks]] from page body on save. Drives backlinks and the "broken links" report.

Field	Type	Notes
id	uuid
workspace_id	uuid	FK to workspace.id; matches the source page.
source_page_id	uuid
target_slug	text	The raw slug as written.
target_page_id	uuid	NULL means the link is unresolved (broken).
created_at	timestamptz

On save the service deletes the source page's existing wiki_link rows and re-parses, inserting new rows. When a new page is created, a hook re-resolves rows where target_slug = new_page.slug AND target_page_id IS NULL so backlinks wire up retroactively.

sticky_note

Markdown scratchpad. Either personal (visible only to creator) or project-scoped (visible to everyone with project access).

Field	Type	Notes
project_id	uuid	NULL = personal-scope.
owner_user_id	uuid	Creator; also the visibility owner for personal notes.
body	text	Markdown.
color	text	Optional CSS hex.
converted_to_ticket_id	uuid	Set when the user invokes "convert to ticket." Set-once.

Sticky notes are hard-deleted: no FKs point in, no archival value, and "I'm done with this note" means "get it out of my view."

Observability entities

audit_event

The system-wide append-only log. Backs both the audit log UI and the activity feed. See ADR-0006 for the one-table rationale.

Field	Type	Notes
id	uuid
category	text	activity (user-visible feed) or audit_only (admin/security only).
event_type	text	Dotted code: ticket.created, auth.login.failed, bot.token.rotated, ...
actor_user_id	uuid	NULL only for system-originated events.
actor_kind	text	Denormalized snapshot: human, bot, or system. Survives user deletion if ever allowed.
target_kind	text	Entity kind acted upon (ticket, wiki_page, ...).
target_id	uuid	ID of the affected row.
project_id	uuid	Denormalized for fast per-project feed queries; NULL for workspace-level events.
ticket_id	uuid	Denormalized for fast per-ticket feed queries.
payload	jsonb	Structured event-specific data.
request_id	uuid	Correlates events emitted from the same HTTP/MCP request.
tool_invocation_log_id	uuid	Set only for bot.tool_invoked rows; FK can go NULL post-retention.

The table is append-only by convention and by application code. The DB role used by the app can also have UPDATE and DELETE revoked on this table as belt-and-braces.

The activity feed UI does SELECT ... WHERE category = 'activity'. The audit log UI sees everything. Webhook fan-out reads the same stream filtered by event_type. See Observability for the emission rules and event taxonomy.

tool_invocation_log

Per-call detail of every MCP tool invocation. Separated from audit_event because the heavier payload (redacted args, result status, duration) deserves its own retention story. See ADR-0008.

Field	Type	Notes
bot_user_id	uuid	Must reference a bot.
request_id	uuid	Same value as the corresponding audit_event.request_id.
tool_name	text	Snake-case identifier (e.g. create_ticket).
arguments_json	jsonb	Redacted args; 4 KiB cap with "<truncated>" marker.
result_status	text	ok or error.<envelope_code>.
error_message	text	Operator-readable summary on failure; NULL on success.
duration_ms	integer	Wall-clock duration of the tool dispatch.

Redaction rules: any JSON field whose key matches password, token, secret, api_key, private_key (case-insensitive) is replaced with "<redacted>" before write.

Retention: rows are purged after TOOL_INVOCATION_LOG_RETENTION_DAYS (default 90). The pointer audit_event row outlives the detail row — when the log row is purged, the audit row's tool_invocation_log_id FK is set NULL. The fact-of-invocation persists; the click-through goes dead.

webhook

Workspace-level subscription. One row per active subscription.

Field	Type	Notes
id	uuid
workspace_id	uuid	FK to workspace.id.
url	text	Target URL. Pydantic schema requires https://.
secret_hash	text	sha256 hash of the plaintext HMAC secret. Plaintext shown once at create/rotate.
event_types	jsonb	Allowlist (e.g. ["ticket.created", "comment.created"]) or ["*"] for all.
is_active	boolean	Toggle without deleting; inactive subscriptions skip dispatch.
created_by	uuid	FK to the human user.id who created it.
created_at	timestamptz
updated_at	timestamptz
last_success_at	timestamptz	Last delivery that returned 2xx; NULL if never succeeded.
last_failure_at	timestamptz	Last delivery that didn't return 2xx; NULL if never failed.

The HMAC secret never lives in the database in plaintext. The server stores its sha256 hash and uses that hash as the HMAC key when signing payloads. Integrators must keep the plaintext they received at create/rotate time — there is no recovery path.

webhook_delivery

Append-only log of attempted deliveries. One row per attempt; retries get fresh rows. The audit_event_id FK links each delivery back to the event that triggered it.

Field	Type	Notes
id	uuid
webhook_id	uuid	FK to webhook.id.
audit_event_id	uuid	FK to audit_event.id; the originating event.
attempt	integer	1, 2, 3 — matches the position in _BACKOFF_SECONDS = (1.0, 5.0, 30.0).
status	text	pending, success, failed, dead_letter.
response_status	integer	HTTP status returned by the receiver; NULL on network failure.
response_body	text	First ~4 KiB of the response body, truncated.
error_message	text	Operator-readable failure summary; NULL on success.
duration_ms	integer	Wall-clock duration of the request.
created_at	timestamptz	Time the delivery attempt began.

See Observability for the signature scheme, retry policy, and failure modes.

idempotency_record

Backs the Idempotency-Key header on POST endpoints. Stores the original response so a client retry with the same key replays the exact response; a retry with a mismatched body returns 409.

Field	Type	Notes
actor_user_id	uuid	Idempotency scope is per-actor.
key	text	The client-supplied header value (≤ 128 chars).
request_fingerprint	text	sha256(method + path + canonicalized_json(body)).
status_code	smallint	Original HTTP status.
response_body	text	Serialized JSON response (stored as text for TOAST and log-friendliness).
response_headers	jsonb	Subset to replay (notably Location on creates).
expires_at	timestamptz	created_at + IDEMPOTENCY_KEY_TTL_HOURS (default 24h).

Rows are purged by the sweeper once expires_at < now().

Indexes that matter

The schema includes ~30 indexes. A handful are load-bearing for hot read paths:

Index	Hot path
UNIQUE (project_id, ticket_number) on ticket	Display ID generation; no collisions.
UNIQUE (workspace_id, display_id) WHERE deleted_at IS NULL on ticket	PROJ-123 lookups (wikilinks, bot references).
INDEX (project_id, state_id, deleted_at) on ticket	Board column queries.
GIN (search_tsv) on ticket and wiki_page	The /search endpoint.
INDEX (ticket_id, created_at DESC) WHERE ticket_id IS NOT NULL on audit_event	Per-ticket activity feed.
INDEX (project_id, category, created_at DESC) on audit_event	Per-project activity feed.
INDEX (workspace_id, created_at DESC) on audit_event	Recent activity / audit log.
UNIQUE (bot_user_id) WHERE is_active = true on bot_token	"Exactly one active token per bot" invariant.
UNIQUE (token_hash) on session	O(1) session lookup per request.
INDEX (next_retry_at) WHERE next_retry_at IS NOT NULL on webhook_delivery	Retry worker scan.

The full index list lives in spec/01_data_model.md next to each entity.

Full-text search

The /search endpoint queries across two tsvector columns and returns mixed results ranked by ts_rank_cd.

Table	Weighted contents
ticket	A: title, display_id; B: body; C: concatenated recent comments.
wiki_page	A: title; B: body.

A single Alembic migration installs the triggers that keep search_tsv current on insert/update. Comments updating their parent ticket's search_tsv is the most invasive trigger; v1 ships with a "rebuild on comment insert/delete" approach. If it becomes a hot spot, a background reindex job replaces it.

Result scoping per actor:

• Humans: the entire workspace.
• Bots: tickets in their project allowlist + all global wiki pages.

See Auth and identity for the authorization filter.

Summary of cross-cutting decisions

Decision	Choice	Why
Primary keys	UUIDv7 everywhere	Time-ordered inserts; no B-tree fragmentation on hot tables.
Ticket display IDs	PROJECT_KEY-N materialized at insert	Wikilink-friendly; greppable; independent of the UUID.
Timestamps	timestamptz everywhere, UTC in application code	Aligns with Postgres recommendation and SQLAlchemy 2.0 typed columns.
Workspace scoping	workspace_id on every entity from day one	Cheap insurance vs. a coordinated retrofit later.
Audit + activity	One audit_event table with category discriminator	Identical emission point; presentation is the filter.
Tool invocation detail	Separate tool_invocation_log table; pointer row in audit_event	Different retention; richer payload; keeps audit table small and uniform.
Soft vs. hard delete	Mixed: soft for referential-heavy, hard for ephemeral	Pay the soft-delete cost where the recoverability benefit lives.
Comment deletion	Soft delete + full body in audit payload	Audit trail survives any later hard purge.
Bot token hashing	argon2id (t=3, m=64MiB, p=4)	Memory-hard; OWASP-recommended; no bcrypt 72-byte limit.
Session token storage	SHA-256 of cookie value	The cookie has 256 bits of entropy; password-style hashing is unnecessary.
Per-project ticket counter	project.next_ticket_number + SELECT ... FOR UPDATE	Transactional, simple, no per-project DDL.
Workflow state ordering	Integer position with full-list renumber on reorder	Bounded list size (~10/project); the simplest approach is also the best.
Enums	text with CHECK constraints	Alembic-friendly; Postgres native enums are painful to alter.
JSONB extensibility	ticket.extension exists; gated by ADR	Keeps schema as documentation; the escape hatch is one line of code, not a migration.

Any change to these requires an ADR. The full spec is in spec/01_data_model.md.