Data model

The schema is small and regular. Everything is scoped to a project; every mutable row tracks who changed it and when; and every change is recorded in an append-only audit log. Models live under backend/app/models/.

Primary keys are UUIDs (gen_random_uuid()), tables and columns are snake_case, and timestamps are timezone-aware.

Tasks

models/task.py — the central entity.

Column	Type	Notes
id	UUID	Primary key.
project_id	UUID	FK → projects, ON DELETE CASCADE. Every task belongs to one project.
parent_id	UUID, nullable	Self-FK forming the tree. ON DELETE RESTRICT — you can't delete a task that still has children.
title	varchar(500)	Required.
description	text	Defaults to empty.
completion_criteria	text	Defaults to empty. Free-text "when is this done" criteria; a plain-text twin of description (ADR 0060). A frontend-only soft reminder surfaces it when a task is marked complete.
stage	varchar	Must be one of the project's stages.
status	enum	backlog, not_started, in_progress, blocked, qa, complete (ADR 0052), dropped (ADR 0075). Stored as a checked string (see below). New tasks default to backlog.
priority	enum, nullable	low, medium, high, or NULL = no priority (ADR 0052). Same native_enum=False checked-string storage as status; nullable, no backfill.
is_section	bool	true marks a grouping section rather than a real task (ADR 0048). Default false. See Sections.
color	varchar(7), nullable	Hex colour.
canvas_x, canvas_y	float	Saved canvas position.
canvas_collapsed	bool	Whether the branch is collapsed on the canvas.
start_date	timestamptz, nullable	Optional explicit start. When null, derived from blockers.
due_date	timestamptz, nullable	Optional due date.
created_at / created_by	timestamptz / UUID	Set once at creation.
updated_at / updated_by	timestamptz / UUID	Re-set on every write. updated_at uses clock_timestamp() — see Concurrency.

A CHECK constraint enforces start_date <= due_date when both are set.

The status enum and the native_enum=False pattern

status (and priority) are SQLAlchemy Enums declared with native_enum=False. Rather than a PostgreSQL ENUM type, this stores the value as a varchar guarded by a CHECK constraint listing the allowed values. The same pattern is used for membership roles, the audit action, and the user's dark-mode preference.

There's a sharp edge here worth knowing before you write a migration: Alembic autogenerate silently drops these CHECK constraints. Migrations therefore hand-write the CHECK rather than trusting autogenerate. This is covered in Migrations — read it before generating one.

Sections and inferred status (ADR 0048)

is_section is a plain boolean — a section is a grouping node, not a distinct type, so it needs no CHECK constraint (and so escapes the autogenerate gotcha above). "Convert to/from section" is an ordinary PATCH /tasks/{id} of is_section; the toggle flows through the normal audit diff, optimistic-concurrency check, and SSE event with no special-casing. Converting never touches the stored status — the manual value is preserved on the row and surfaces again if the node is converted back, so the round trip is lossless.

A section's displayed status is inferred from its descendants, and that rollup is computed on the client, not the server. The reasoning (ADR 0048): the value is purely derived, and because a child's status change never touches its parent's row, the frontend must recompute it from children between SSE events regardless — a server-side copy would duplicate the rule and still go stale. So the backend only stores the flag and the preserved status; frontend/src/tasks/sectionStatus.ts is the single source of truth for the rollup. The rule (progress-aware, blocked- and QA-hidden, extended by ADR 0052 and ADR 0075): a blocked and a qa child are mapped to in_progress before aggregating, so neither surfaces on a section. A dropped child is discarded before aggregating rather than mapped — abandoned work leaves the count entirely, so it neither inflates the rollup (as counting it complete would) nor puts a finished state permanently out of reach (as counting it outstanding would). Then: empty → not_started; every child dropped → dropped (stated separately, because discarding leaves an empty list and an empty list already means "no children"); all-complete → complete; all-backlog → backlog; all-backlog/not-started → not_started; otherwise in_progress. It recurses over the subtree (a child section contributes its own rolled-up status) and reads the parent tree only — the dependency graph is ignored. The wire status of a section is therefore the stored value, not the inferred one; any non-frontend API consumer would need to apply the rule itself.

The tree: parents and children

parent_id is a self-referential foreign key. A null parent_id is a root task; otherwise the row points at its parent. The constraints keep the tree honest:

• A task can't be deleted while it has children (ON DELETE RESTRICT).
• Re-parenting that would create a loop (making a task its own ancestor) is refused at the API layer with a 409 — see API surface.

Dependencies

models/dependency.py — a directed edge between two tasks, separate from the parent/child tree.

Column	Notes
blocker_id	FK → tasks. The task that must finish first.
blocked_id	FK → tasks. The task that waits.

So "A blocks B" is a row with blocker_id = A, blocked_id = B. Constraints:

• Unique on (blocker_id, blocked_id) — no duplicate edges.
• No self-edge — blocker_id ≠ blocked_id.

Dependencies form a DAG; the API refuses an edge that would create a cycle. Two tasks in completely different branches of the tree can still depend on each other — the dependency graph is independent of the parent tree.

Assignees

models/task_assignee.py — a join table giving each task any number of assignees (ADR 0034).

Column	Notes
task_id	FK → tasks, ON DELETE CASCADE (composite PK).
user_id	FK → users, ON DELETE RESTRICT (composite PK) — preserves history.
assigned_at / assigned_by	When and by whom, like a membership row.

The wire shape is an assignee_ids: list[UUID] field on the task schemas; a PATCH replaces the whole set, and a service diffs it against the join table.

Comments, @-mentions, and notifications

Task discussion (ADR 0049) and the @-mention notification surface (ADR 0050) replaced the old orphaned tasks.implementation_notes column (dropped, its content backfilled into one seed comment per task).

models/task_comment.py — an authored comment on a task, exposed as the sub-resource /api/v1/projects/{pid}/tasks/{tid}/comments (GET/POST/PATCH/DELETE). Comments are appends + own-edits, so they deliberately skip the optimistic-concurrency machinery, and they are not written to the audit log (no Ctrl+Z, no audit verbs).

Column	Notes
id	UUID primary key.
task_id	FK → tasks, ON DELETE CASCADE.
project_id	FK → projects, ON DELETE CASCADE. Denormalised from the task for authz + event routing.
author_id	FK → users, ON DELETE RESTRICT — preserves authorship.
body	text. May contain @[Display Name](user:UUID) mention tokens.
created_at	clock_timestamp() — distinct within a transaction so chronological (oldest-first) ordering is stable.
updated_at	nullable; NULL until edited. edited on the wire is updated_at is not None.

models/comment_mention.py — the structural set of users a comment @-mentions, parsed from the body on create/edit and validated against project membership (a non-member token is dropped, not an error). Composite-PK (comment_id, user_id) join, ON DELETE CASCADE both sides; mirrors task_assignees.

models/notification.py — a per-user inbox row, created when a comment newly @-mentions a user (never the author). Read via /api/v1/users/me/notifications (list / unread_count / {id}/read / read_all).

Column	Notes
id	UUID primary key.
user_id	Recipient, FK → users, ON DELETE CASCADE. Indexed (user_id, created_at) WHERE read_at IS NULL for the unread count.
type	native_enum=False checked string — comment_mention (v1).
actor_id / project_id / task_id / comment_id	Context FKs, all ON DELETE CASCADE (comment_id nullable for future types).
read_at	nullable timestamptz; NULL = unread.
created_at	timestamptz.

Live delivery (not the audit-backed stream). Comments and notifications ride a separate live-only "ephemeral" SSE event with no seq and no audit_log row — comment.* on the project channel, notification.* on a new per-user channel (/users/me/notifications/stream). There is no catch-up for these; durability is the tables themselves, re-fetched via GET on (re)connect. The broadcaster routes project-targeted ephemeral events by project_id and user-targeted ones by target_user_id. See Real-time sync.

Availability

models/availability_pattern.py and models/availability_override.py (ADR 0062) — where each person expects to be. These are the schema's only workspace-level mutable tables: they hang off users, carry no project_id, and are served from /api/v1/availability rather than a project-scoped route. A person's whereabouts belong to the person, not to a project.

Two layers. availability_patterns is a person's default week — "where is this person normally?", set once and rarely revisited. availability_overrides holds dated exceptions that win over it. A third table, availability_members, decides who is on the board at all.

availability_patterns	Notes
id	UUID primary key.
user_id	FK → users, ON DELETE CASCADE.
weekday	smallint, CHECK 0–6. 0 = Monday, matching Python's date.weekday() — not JavaScript's Sunday-first getDay().
kind	office / home / unavailable, the native_enum=False checked-string pattern.
start_time / end_time	time (no zone), nullable. Both null = all day; CHECKs enforce both-or-neither and start < end.

availability_overrides	Notes
id	UUID primary key.
user_id	FK → users, ON DELETE CASCADE.
on_date	date. One row is one date, never a range — a range carrying both dates and times is ambiguous for partial days.
kind, start_time, end_time	As above, same CHECKs.
tentative	bool. An estimate the person isn't confident about; rendered hatched.
note	text, default ''. "At a client site".
block_id	nullable UUID grouping the days of one multi-day gesture, so a holiday edits and deletes as a unit — the same idiom as audit_log.group_id.

A block_id groups a set of dates, not a span, and two invariants follow that every write path holds (ADR 0064 §7). First, a one-row block never exists: shrinking a block to a single date, splitting a day out of a two-day block, and deleting one day of a two-day block all clear the survivor's block_id, because block_id is null is precisely what distinguishes "this one day" from "this holiday" in the create path and in every block route — a row stranded with a block_id nobody shares would offer block affordances that act on a single day. Second, moving a row keeps its block_id: on_date on the row PATCH changes which date one day of the block falls on without changing the block's size. Growing or shrinking the set is the block PATCH — or PATCH /overrides/{o}/dates, which does the same diff addressed by a row rather than by a block and is therefore also the one write that can give a stand-alone row a block_id (ADR 0069 §4); detaching a day first is the split route.

Times are stored as the person wrote them, and the calendar's rendering is not the record. An all-day office or home row (both times null) is drawn on the time axis at 09:00–17:00, because "in the office all day" answers where rather than when; an all-day unavailable row keeps the all-day gutter. That substitution happens in exactly one place on the client — resolveDay in availability/resolve.ts, which sets an impliedTimes flag alongside the substituted minutes — so every consumer downstream sees an ordinary timed segment and only the gutter and the editor ask the flag. Nothing about it reaches the database or the API.

Who is on the board (ADR 0063)

Availability is opt-in: models/availability_member.py (ADR 0063) is the set of people who may use the board and appear on it, replacing ADR 0062 §1's "every row in users". It is a bare set, not a role table — deliberately unlike project_memberships.

availability_members	Notes
user_id	UUID primary key, FK → users, ON DELETE CASCADE. One row per member; the PK is the set.
added_at	timestamptz, clock_timestamp() server default — an act of admission, hence not created_at.
added_by	FK → users, ON DELETE RESTRICT, NOT NULL. "Who let them in" is answerable without an audit ledger.

No role column, and no updated_at. Every member has an identical capability set — read the whole board, write only your own rows — so the only capability a role could vary is roster management, and that is what the admin flag encodes. A set row has no mutable field either, which sidesteps the onupdate=func.now() transaction-wide-timestamp trap by construction.

users.is_availability_admin is a plain boolean on the users table, default false, seeded once by migration b6f4a2c8d013 from a case-insensitive match against the AVAILABILITY_ADMIN_EMAIL environment variable (documented in backend/.env.example). Being a real boolean, it needs no CHECK constraint — worth saying out loud so the native_enum=False gotcha above isn't applied reflexively to a column that doesn't have it. The migration reads os.environ rather than importing Settings, because a committed migration must not break when a settings field is renamed — and for the same reason there is deliberately no Settings field for it at all: nothing in the application reads the address, so a field would be a knob that looks like it moves the admin and does not. It is a bootstrap parameter — read once to find a row, never compared against the caller at request time. A zero-row match on a fresh database is expected, not an error; backend/scripts/grant_availability_admin.py is the documented recovery.

Two properties follow from putting the privilege on a column rather than in the set:

• The admin is implicitly a member — the flag grants access with no row — so the "last admin removes themselves and strands the feature" failure mode that project_memberships prevents with a 409 rule cannot occur here, because no membership gesture can delete a column.
• Removal keeps the data. Deleting a member row leaves that person's patterns and overrides untouched; the read is filtered by the member set, so they vanish from every client's board immediately and re-adding restores their history exactly. The ON DELETE CASCADE from users is still what cleans everything up when a person is deleted — the genuine "this person is gone" event, which membership removal is not.

Segments, not one row per day. Neither the pattern nor the override table constrains uniqueness on (user, day): both hold any number of non-overlapping segments, so a split day — office in the morning, home in the afternoon — is expressible as a default and as an exception. Non-overlap is a cross-row invariant, so it lives in services/availability.py and is applied by all three write paths rather than in the schema; an all-day segment collides with everything, and two timed segments collide on a half-open [start, end) comparison, so 09:00–12:00 and 12:00–17:00 sit back-to-back without colliding.

Resolution is client-side, like the section rollup (ADR 0048): for any (person, date) take the pattern's segments for that weekday, but if the date carries any override segment, discard the pattern for that date entirely. All-or-nothing, never a merge — otherwise "I'm only in for the morning on Thursday" would leave the afternoon showing through. frontend/src/availability/resolve.ts is the single source of truth.

Times are naive wall clock in WORKSPACE_TIMEZONE (a deployment setting, default Australia/Sydney), not instants. "In the office 09:00–17:00" means 09:00 at the office, whoever is reading it and wherever they are.

No audit, no SSE, no undo. Like comments and notifications, availability writes produce no audit_log row and no events — so the audit-verb contract, Ctrl+Z, and the sequence-cursor catch-up path are all untouched. Freshness is refetch-on-focus.

Projects and scoping

models/project.py. Every route is under /api/v1/projects/{project_id}/…, and every task, dependency, and audit row carries a project_id (ADR 0020). A non-member gets a 404 — the project's very existence is hidden.

Column	Notes
name	Project name.
stages	JSONB array of stage names. A task's stage must be one of these. Default: an engineering-R&D lifecycle (Concept → Prototype → Validation → Low-Volume Batch → Commercialisation).
due_date_warning_days	How many days before a due date to warn. CHECK: 1–365.

Memberships and roles

models/project_membership.py (ADR 0021). A user's access to a project is a row here with a role:

• editor — read everything, edit tasks and dependencies.
• admin — everything an editor can do, plus edit project metadata, manage memberships, and delete the project.

Roles are a two-level hierarchy (admin ≥ editor); an earlier viewer role was retired. The system refuses to demote or remove the last admin, so a project can't be orphaned. Authorization mechanics are in Auth.

Team Roles

models/team_role.py + models/team_role_member.py (ADR 0068). A per-project responsibility breakdown — "who owns firmware" — as distinct from who is assigned a given task.

Do not confuse this with the membership role above. project_memberships.role is authorisation; a team role is descriptive and grants nothing. New code never uses a bare role identifier: the membership enum is MembershipRole, and this feature is TeamRole throughout.

• team_roles — parent_id self-FK gives the hierarchy, capped at three levels (root = depth 1). sort_order is a double precision fractional rank, lower = earlier among siblings, assigned midpoint-of-neighbours on a drag so a move rewrites one row rather than renumbering a sibling set (the ADR 0057 idiom). description is plain text.
• team_role_members — (role_id, user_id) PK. Zero-to-many people per role; a role with none renders as Vacant. One person may hold several. No primary/lead flag — a lead is modelled as its own role.

Three composite foreign keys do the integrity work that would otherwise be handler code, which is why both tables carry a project_id and team_roles has an otherwise-redundant UNIQUE (id, project_id):

FK	Buys
(parent_id, project_id) → team_roles (id, project_id)	A role cannot be parented across projects; deleting a role cascades its whole subtree.
(role_id, project_id) → team_roles (id, project_id)	An allocation cannot point at a role in another project.
(user_id, project_id) → project_memberships (user_id, project_id)	Role holders are always project members — and removing someone from a project vacates every role they held, with no application code involved.

Depth and cycle rules are not expressible as constraints, so they live in services/team_roles.py as pure functions, checked on create and update: 422 for exceeding depth, 409 for a cycle. A move validates depth_of(new_parent) + subtree_height(moved), not the moved node alone.

Team role writes are admin-only, and carry no audit rows, no SSE events and no undo — the comments/notifications/availability precedent. Accountability comes from created_by / updated_by / updated_at on the row instead; freshness comes from refetch-on-focus. There is no if_unchanged_since: writes are rare, admin-only, and per-row idempotent.

Users

models/user.py. A user is keyed by a stable external object ID (their Entra identity in production, or a synthetic local:<name> in development). Email is intentionally not unique — different tenants in a multi-workspace world may collide. Users carry a display name, an optional colour, a dark-mode preference, and is_availability_admin — the one workspace-level privilege bit in the schema (see Availability).

The audit log

models/audit_log.py — append-only, and the backbone of both undo and real-time catch-up.

Column	Notes
id	BigInteger, autoincrement. Doubles as the global sequence cursor for SSE.
project_id	Scopes the entry; indexed with id for fast per-project catch-up.
task_id	nullable, ON DELETE SET NULL — the audit row outlives the task.
user_id	The actor.
action	create, update, delete, add_member, change_role, remove_member.
diff	JSONB. {"before": …, "after": …} — only the changed fields, housekeeping columns excluded.
payload	JSONB. The full SSE event for this change, stored so catch-up replays byte-for-byte.
group_id	nullable UUID. Ties multiple rows from one gesture together so undo can reverse them atomically.
created_at	clock_timestamp() — distinct, monotonic even within one transaction.

The diff is what undo reverses; the payload is what reconnecting clients replay. Both are explored further under Real-time sync and Concurrency.

Why clock_timestamp() shows up everywhere

updated_at (tasks, availability patterns and overrides), assigned_at (assignees), added_at (availability members), and created_at (memberships, audit log, comments) all default to clock_timestamp(), not now(). PostgreSQL's now() is fixed for an entire transaction, so two writes in one transaction would share a timestamp — which breaks the optimistic-concurrency and field-overlap checks that compare against these times. clock_timestamp() advances within a transaction, giving each write a distinct, ordered stamp. The full reasoning is in Concurrency.