Database schema

This document describes HAICO’s persistent data model as it stands at migration 0011_feedback_issue_url. It lists every table, explains what each entity is for, and then covers the two derived concepts that the schema exists to support: snapshots (the immutable per-turn record of the workspace) and restore / branching (rewinding to, or forking from, any past turn). It is the schema-level companion to conversation-branching.md, which walks through the runtime mechanics, and to ADR-0003 and ADR-0004, which record why the design is shaped this way.

Two stores, one join key

A conversation’s state lives in two independent places.

The application database (Postgres). All tables described here. Managed by Alembic migrations under backend/alembic/versions/. This holds users, ownership, and the four user-visible workspace surfaces (document, objective, todos, preferences) plus their history.
The LangGraph checkpointer. A separate set of tables (checkpoints, checkpoint_blobs, checkpoint_writes, checkpoint_migrations) created by AsyncPostgresSaver at container startup (init_checkpointer.py), not by Alembic. This versions the chat transcript (the messages channel) per conversation. Application code touches it only through get_checkpointer_cm().

The single value that ties the two stores together is thread_id, a string of the form th_<uuid4hex>. Almost every application table carries a thread_id, and the checkpointer is keyed by the same value. There is deliberately no foreign key from thread_id columns to a conversations table: a conversation is identified by its thread id across both stores, and enforcing a cross-store FK would be impossible. Foreign keys are used only where both sides are ordinary SQL rows (user_id references users.id).

Schema conventions

Three idioms recur throughout the models (backend/app/db/models.py):

Portable big integers. Most primary keys use BigInteger().with_variant(Integer, "sqlite") so production runs on Postgres BIGINT while the in-memory SQLite test database (built from the ORM via create_all, never from migrations) stays compatible; the exception is users.id, a plain Integer.
Portable JSON. List-shaped columns use JSONField = JSONB().with_variant(JSON(), "sqlite"), giving Postgres JSONB in production and generic JSON under SQLite.
thread_id scoping. Every workspace table is scoped by thread_id and indexed on it, so a conversation’s whole workspace is one indexed lookup.

Entity reference

The tables fall into four groups: identity and access, the live per-thread workspace, turn-tagged outputs, and versioning / lineage.

Identity and access

`users`

A registered account. Supports both local (password) and Google OAuth sign-in.

Column	Type	Notes
`id`	INT, PK
`username`	VARCHAR(50)	unique, not null, indexed
`email`	VARCHAR(100)	unique, nullable, indexed
`hashed_password`	VARCHAR(255)	nullable (NULL for OAuth-only accounts)
`role`	VARCHAR(20)	not null, default `user`
`is_approved`	BOOL	not null, default true
`auth_provider`	VARCHAR(20)	not null, default `local` (`local` or `google`)
`google_id`	VARCHAR(255)	unique, nullable, indexed
`email_verified`	BOOL	not null, default false
`email_verification_token`	VARCHAR(255)	nullable, indexed
`email_verification_expires`	DATETIME	nullable
`recording_consent_at`	DATETIME	nullable; NULL means the consent gate has not yet been accepted
`research_publish_consent_at`	DATETIME	nullable; NULL means no separate, explicit consent to publish the user’s anonymized data for research. Added in migration 0012
`email_updates_opt_in`	BOOL	not null, default false; opt-in to release / research-update emails. Added in migration 0012
`created_at`	DATETIME	not null

Email verification applies only to local accounts; Google accounts are auto-verified because Google guarantees email ownership.

`user_activity`

Append-only audit log. One row per significant action (login, register, query, resend_verification, and similar).

Column	Type	Notes
`id`	BIGINT, PK
`user_id`	INT	FK to `users.id`, not null, indexed
`action`	VARCHAR(50)	not null; human-readable action name
`detail`	TEXT	nullable; free-text context (auth method, thread id)
`ip_address`	VARCHAR(45)	nullable; sized for IPv6
`timestamp`	DATETIME	not null

Programmatic access is attributable here too: minting and revoking a key are logged as api_key.create and api_key.revoke, with the key’s public prefix in detail.

`api_keys`

Personal access tokens: the credential programmatic clients use instead of the browser session JWT. A key is minted from a logged-in session, so it inherits the reCAPTCHA and email-verification gates that guard login rather than bypassing them. See ADR-0006.

Column	Type	Notes
`id`	BIGINT, PK
`user_id`	INT	FK to `users.id` `ON DELETE CASCADE`, not null, indexed
`name`	VARCHAR(100)	not null; user-supplied label, e.g. “analysis laptop”
`key_hash`	VARCHAR(64)	not null; SHA-256 hex digest of the plaintext
`key_prefix`	VARCHAR(20)	not null, unique, indexed; `haico_pat_` plus the first 8 secret chars
`last_four`	VARCHAR(4)	not null; display only, never matched against
`scopes`	VARCHAR(100)	not null, default `read,write`; `read` or `read,write`
`created_at`	DATETIME	not null
`last_used_at`	DATETIME	nullable; written at most once per minute per key
`expires_at`	DATETIME	nullable; NULL means the key never expires
`revoked_at`	DATETIME	nullable; NULL means still active

Only the digest is stored, so a lost key cannot be recovered, only revoked and replaced. The hash is SHA-256, not bcrypt: a password hash’s work factor exists to make offline brute force of a low-entropy human secret expensive, and against 256 bits of CSPRNG output it buys nothing while adding ~100 ms to every authenticated request. Digests are compared with hmac.compare_digest. key_prefix is unique and indexed because it is the sole lookup key on the authentication path: verification resolves the row by prefix and then verifies the digest, so the table is never scanned. Revocation is a soft delete (revoked_at) so the audit trail records that the key existed and when it stopped working; account deletion, by contrast, removes the rows outright, because a credential must never outlive the account it authenticates. Added in migration 0016.

`conversation_users`

Links a thread_id to the user account that owns it. This is the table that gives a conversation a human owner, a title, and a soft-delete flag, and it carries the per-conversation turn clock.

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	not null
`user_id`	INT	FK to `users.id`, not null
`title`	VARCHAR(255)	nullable; first 50 chars of the opening message
`message_count`	INT	not null, default 0
`turn_index`	INT	not null, default 0; monotonic per-conversation user-turn counter
`created_at`	DATETIME	not null
`deleted_at`	DATETIME	nullable; NULL is active, non-NULL is soft-deleted
`phoenix_url`	TEXT	nullable; cached Phoenix trace deep-link for this thread’s session, resolved lazily from the Phoenix REST API on first admin request. Added in migration 0010

Indexes: idx_conv_users_thread, idx_conv_users_user, and a unique index uq_conv_users_thread_user on (thread_id, user_id) so one user cannot own the same thread twice. turn_index is the backbone of the whole versioning story. It is incremented just before the agent runs, so every artifact and snapshot written during a turn shares that turn’s index. A turn is HAICO’s coarse clock: one tick per user message.

The live per-thread workspace

These four tables hold the current state of a conversation’s workspace, the state the agent reads at the start of a turn and writes during it. documents and objectives are unique(thread_id) (exactly one row per thread); todos and preferences are many-per-thread lists.

`documents` (one per thread)

The co-constructed working artifact rendered in the center panel. The agent writes it through update_document / append_to_document; the user edits it through the workspace API.

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	not null, unique, indexed
`title`	VARCHAR(255)	not null, default `Untitled`
`content`	TEXT	not null, default empty
`updated_at`	DATETIME	not null; refreshed on every write via `onupdate`

`objectives` (one per thread)

The agent’s evolving, single-paragraph understanding of the user’s goal, rendered at the top of the Planning panel and injected into the agent’s context every turn as an <objective> block. The agent updates it through set_objective; the user can edit it through the workspace API. Added in migration 0007 (see ADR-0004).

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	not null, unique, indexed
`text`	TEXT	nullable; NULL or empty until an objective is set
`updated_at`	DATETIME	not null; `onupdate`

`todos` (many per thread)

The construction-space plan. The todos form an arbitrarily nested tree serialised in pre-order (depth-first): rows are ordered by position ascending (the global pre-order index) then id, and depth records the nesting level (0 is top-level, 1 is a sub-step, and so on). The dotted numbering shown in the UI (1, 1.1, 1.1.1, 2) is derived from this ordering at render time. The agent maintains the list through add_todos / toggle_todos. The depth column was added in migration 0007.

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	not null, indexed
`text`	TEXT	not null
`done`	BOOL	not null, default false
`position`	INT	not null, default 0; global pre-order (DFS) index
`depth`	INT	not null, default 0; nesting level
`created_at`	DATETIME	not null

`preferences` (many per thread)

Decoded hard and soft constraint rules, displayed as structured cards. The agent records them through add_preferences.

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	not null, indexed
`title`	VARCHAR(100)	not null (for example, “Academic tone”)
`subtitle`	VARCHAR(255)	nullable
`kind`	VARCHAR(20)	not null, default `soft` (`hard` is non-negotiable, `soft` is flexible)
`locked`	BOOL	not null, default false; when true only the user can modify or delete it, never the agent
`position`	INT	not null, default 0; display order
`created_at`	DATETIME	not null

position was added in migration 0004 and locked in 0005.

Turn-tagged outputs

`artifacts` (many per thread)

A typed artifact produced by a tool during a turn (a line_chart, bar_chart, document, table, and so on). The artifact_type discriminator tells the frontend which React component renders the opaque payload. Every artifact is tagged with the turn_index of the user message that triggered it, so the UI can scrub through history and the agent can look artifacts up by turn through list_artifacts / get_artifact.

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	not null, indexed
`turn_index`	INT	not null, default 0
`artifact_type`	VARCHAR(64)	not null; renderer discriminator
`title`	VARCHAR(255)	nullable
`payload`	JSON	not null
`created_at`	DATETIME	not null

Composite index ix_artifacts_thread_turn on (thread_id, turn_index). Added in migration 0002.

Versioning and lineage

These two tables are what make the workspace addressable through time. Everything above describes the workspace now; these describe what it was and how threads relate.

`workspace_snapshots`

An immutable photograph of the whole workspace at the start of one user turn, plus the raw user message of that turn. One row per (thread_id, turn_index). This lets the system reconstruct the exact context the agent saw on any past turn without persisting the rendered system prompt into the checkpointer (which would duplicate the document body on every turn). Added in migration 0002; objective_text was added in 0007.

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	not null, indexed
`turn_index`	INT	not null
`user_message`	TEXT	nullable; the raw message that opened the turn
`document_title`	VARCHAR(255)	nullable
`document_content`	TEXT	nullable
`objective_text`	TEXT	nullable; the objective at the start of the turn (NULL for pre-0007 snapshots)
`preferences_json`	JSON	not null, default `[]`; array of `{title, subtitle, kind, locked}` dicts
`todos_json`	JSON	not null, default `[]`; array of `{text, done, position, depth}` dicts
`captured_at`	DATETIME	not null

Unique index uq_snapshots_thread_turn on (thread_id, turn_index). A design note worth internalising: the document and the objective are single scalar values, so they are stored as plain columns (document_content, objective_text). Preferences and todos are variable-length lists of multi-field records, so they are serialised as JSON arrays. A consequence is that the shape of a list item can grow without a schema migration: the todo depth field, added in 0007, simply became one more key inside the existing todos_json blob, whereas the objective, a brand-new scalar with no existing home in the snapshot row, required its own new column.

`conversation_branches`

Records that one thread is a fork of another. A branch is not a column on existing rows; it is a brand-new thread_id whose workspace was copied as-of a parent turn, with the parent link captured here.

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	the child (branch) thread; unique, so a thread has at most one parent (lineage is a tree, no cycles)
`parent_thread_id`	VARCHAR(255)	the thread it forked from; indexed
`branch_point_turn`	INT	the parent turn k the fork happened at; the child owns turns > k
`reason`	TEXT	nullable; optional user-supplied label
`user_id`	INT	FK to `users.id`, not null, indexed
`status`	VARCHAR(20)	not null, default `pending`; flipped to `active` only after seeding succeeds
`created_at`	DATETIME	not null; orders sibling branches into lanes in the graph view

Indexes: unique uq_branches_thread, plus ix_branches_parent and ix_branches_user. Added in migration 0006 (see ADR-0003). Only active rows are visible to readers; a pending row represents a branch whose creation has not yet committed.

User feedback

`feedback` (many per thread)

User feedback on the co-construction, anchored per turn. One unified table backs every feedback layer (message reactions/reports, the per-component dimension ratings on the objective/plan/artifact/preferences, and the conversation-level trajectory/completion feedback), discriminated by scope + aspect. See User feedback for the full taxonomy.

Column	Type	Notes
`id`	BIGINT, PK
`thread_id`	VARCHAR(255)	not null, indexed
`turn_index`	INT	not null; the turn the feedback pertains to (the universal anchor)
`user_id`	INT	FK to `users.id`, not null
`scope`	VARCHAR(24)	not null; `message` / `objective` / `plan` / `artifact` / `preferences` / `conversation`
`aspect`	VARCHAR(40)	not null; discriminator within the scope; also identifies a report
`sentiment`	VARCHAR(16)	nullable; valence `positive` / `neutral` / `negative` (NULL for reports)
`category`	VARCHAR(64)	nullable; report category, or an optional reason tag
`comment`	TEXT	nullable; free text
`artifact_id`	BIGINT	not null, default `0`; which artifact this row rates (`scope='artifact'`). `0` = no specific artifact (every non-artifact row, and an artifact rating of the live document). Part of the identity
`origin`	VARCHAR(16)	not null, default `user`; `branch_copy` for feedback carried onto a fork
`issue_url`	TEXT	nullable; GitHub issue an admin filed from a report row (idempotent). Added in migration 0011
`created_at`	DATETIME	not null
`updated_at`	DATETIME	not null; `onupdate` (refreshed on upsert)

Unique key uq_feedback_anchor on (thread_id, turn_index, user_id, scope, aspect, artifact_id) makes each write an idempotent upsert: one rating per aspect per turn, while a user may rate many different aspects of the same turn in parallel, and rate several artifacts produced in the same turn independently (each keyed by its artifact_id). artifact_id is NOT NULL with a 0 sentinel rather than nullable so it can sit in the unique key without the Postgres NULL ≠ NULL trap (a nullable unique column treats every NULL row as distinct, which would break per-aspect uniqueness for all the non-artifact rows). Plus ix_feedback_thread, ix_feedback_scope_aspect, ix_feedback_created. Added in migration 0008; artifact_id joined the unique key in migration 0009. The backend validates only the structural shape of a row (a known scope, a well-formed aspect slug, and the report/sentiment split); the actual aspect and category vocabulary is owned entirely by the frontend taxonomy, so the UI can evolve reasons and dimensions without a backend change or migration. On a branch, the parent’s prefix feedback is copied as origin='branch_copy' (alongside the workspace and artifacts); the fork’s copied artifacts get new ids, so per-artifact ratings are re-pointed through the artifact id map returned by copy_artifacts (otherwise two artifacts rated in one turn would collapse onto the 0 sentinel and collide). Admin statistics count only origin='user' so copies never inflate the figures.

Relationships

Only the users edges are enforced foreign keys. The thread_id edges are logical joins (a conversation is identified by its thread id across both stores, including the LangGraph checkpointer, which no SQL FK can reach). documents and objectives are one-to-one with a thread; the rest are one-to-many.

Migration history

The schema was built additively. No migration after 0001 drops or rewrites an existing column, which is why old conversations keep working as features land.

Revision	Adds
`0001_initial`	`users`, `user_activity`, `conversation_users`, `documents`, `todos`, `preferences`
`0002_artifacts_and_snapshots`	`artifacts` and `workspace_snapshots` tables; `conversation_users.turn_index`
`0003_recording_consent`	`users.recording_consent_at`
`0004_preference_position`	`preferences.position`
`0005_preference_lock`	`preferences.locked`
`0006_conversation_branches`	`conversation_branches` table
`0007_planning_objective`	`objectives` table; `todos.depth`; `workspace_snapshots.objective_text`
`0008_feedback`	`feedback` table (per-turn user feedback on the co-construction)
`0009_feedback_per_artifact`	`feedback.artifact_id` (joins the unique key so several artifacts in one turn can be rated independently)
`0010_conversation_phoenix_url`	`conversation_users.phoenix_url` (cached Phoenix trace deep-link)
`0011_feedback_issue_url`	`feedback.issue_url` (GitHub issue an admin filed from a report)
`0012_consent_fields`	`users.research_publish_consent_at`, `users.email_updates_opt_in`
`0013_study_mode`	`study_sessions` and `survey_responses` tables; `users.study_completed_at`
`0014_snapshot_model`	per-turn model/provider capture on `workspace_snapshots`
`0015_study_timing`	`study_sessions.study_thread_id`; `survey_responses.duration_ms`
`0016_api_keys`	`api_keys` table (personal access tokens for programmatic API access)

Migrations are applied automatically at container start by entrypoint.sh. The test suite builds the schema from the ORM with create_all rather than running migrations, so the model definitions are the source of truth that migrations must stay in step with.

Snapshots

A snapshot is the mechanism that turns a turn into an addressable, immutable point in history. Three properties matter. One snapshot per turn, captured before the agent acts. Snapshot N is written at the start of turn N, recording the workspace the agent is about to act on. This timing is the single most important invariant in the codebase. The crucial consequence: the workspace after turn k is not snapshot k; it is snapshot k+1, the state the next turn started from, which also absorbs any manual edits the user made between the two turns. The latest turn has no successor snapshot. When k is the most recent completed turn there is no snapshot k+1 yet, so the live workspace rows (documents, objectives, todos, preferences) are authoritative for “the state after k”. This “snapshot(k+1)-or-live” rule is implemented in resolve_restore_state and in the graph builder; getting it wrong is the most likely source of off-by-one bugs. Snapshots are self-contained and portable. Each row embeds the full document body and the full preference and todo lists as JSON, so a snapshot does not depend on the live ORM rows still existing or still looking the same. That independence is what lets a branch copy a past state cleanly. It is also why snapshots are never themselves copied onto a branch: each one embeds a whole document, so duplicating turns 0..k would be quadratic in storage.

Restore and branching: what is feasible

“Restore” and “branch” are two reads of the same underlying capability: reconstruct the workspace as-of any completed turn, then either preview it or fork from it.

Reconstructing a past state

resolve_restore_state(thread_id, k) (in backend/app/services/branching.py) returns the workspace after turn k as plain dicts: the document title and content, the objective text, the preferences (with their locked flag), and the todos (with their depth). It applies the snapshot(k+1)-or-live rule above. If turn k is not the latest and snapshot k+1 is missing (an older conversation that predates snapshotting, for instance), the state cannot be reconstructed and the caller returns HTTP 409. Per-field fallbacks fill in anything a pre-feature snapshot lacks: a missing objective_text becomes None, a missing locked defaults to unlocked, a missing depth defaults to 0.

What a branch is

A branch is a brand-new thread (th_<uuid4hex>) whose workspace is a copy of the parent’s as-of the branch point, with the parent link recorded in conversation_branches. Because every workspace table, every tool, the dynamic prompt, and the checkpointer config are all keyed by thread_id, a fresh thread flows through the entire system unchanged. Switching to a branch reuses the normal conversation-load path. The git analogy is git switch -c <new> <old-commit>: the original conversation is never truncated or deleted, and “Continue from here” in the Studio only creates a branch the moment the user sends a message at a previewed step.

What gets copied, and what does not

Branch creation (create_branch) runs in three phases (commit SQL copies with the branch row marked pending, seed the message prefix into the checkpointer, then flip the branch row to active); a failure in any phase triggers a compensating cleanup so a half-built branch never becomes visible. Copied onto the new thread:

the resolved document title and content (an empty body seeds no document row);
the objective text;
the preferences, with positions re-assigned by array order and the locked flag carried through so a locked constraint stays locked on the branch;
the todos, with their nesting depth;
every artifact with turn_index <= k, re-inserted in ascending original-id order so latest-artifact lookups stay correct;
the message transcript prefix up to the branch point, seeded into the new thread’s checkpointer.

Deliberately not copied:

snapshots (turns 0..k): each embeds a full document, so copying them would be quadratic; the inherited history for turns ≤ k is instead reconstructed lazily by the graph endpoint walking the lineage.

Edited-preview override

When a branch is forked from a restored preview that the user has edited, the frontend sends the displayed workspace (preferences, todos, objective, document) as an optional override on the branch request, and create_branch seeds from that instead of the raw snapshot. This is what makes a manual edit in a preview (a toggled todo, a renamed or added preference, a newly set lock, an edited objective or document) actually carry onto the branch. resolve_restore_state still runs to enforce the lineage and missing-snapshot 409 guard and to supply fallbacks for any field the override omits.

Feasibility summary

Action	Feasible when	Result
Preview / restore turn k	k is the latest turn, or snapshot k+1 exists	Read-only view of the workspace as-of k
Branch at turn k	the same reconstruction succeeds, and (for a thread that is itself a branch) k is after that thread’s own branch point	A new thread owning turns > k, workspace and transcript copied as-of k
Reconstruct turn k on an old conversation with no snapshot k+1	never (for non-latest k)	HTTP 409; the past state is unrecoverable

For the full runtime walkthrough, including the history-graph DAG, validation-to-HTTP-code mapping, and the compensation logic, see conversation-branching.md.

Get started

How it works

Data & recording

Developer guide

Operations

Reference

Database schema

Database schema

Two stores, one join key

Schema conventions

Entity reference

Identity and access

`users`

`user_activity`

`api_keys`

`conversation_users`

The live per-thread workspace

`documents` (one per thread)

`objectives` (one per thread)

`todos` (many per thread)

`preferences` (many per thread)

Turn-tagged outputs

`artifacts` (many per thread)

Versioning and lineage

`workspace_snapshots`

`conversation_branches`

User feedback

`feedback` (many per thread)

Relationships

Migration history

Snapshots

Restore and branching: what is feasible

Reconstructing a past state

What a branch is

What gets copied, and what does not

Edited-preview override

Feasibility summary

​Database schema

​Two stores, one join key

​Schema conventions

​Entity reference

​Identity and access

​users

​user_activity

​api_keys

​conversation_users

​The live per-thread workspace

​documents (one per thread)

​objectives (one per thread)

​todos (many per thread)

​preferences (many per thread)

​Turn-tagged outputs

​artifacts (many per thread)

​Versioning and lineage

​workspace_snapshots

​conversation_branches

​User feedback

​feedback (many per thread)

​Relationships

​Migration history

​Snapshots

​Restore and branching: what is feasible

​Reconstructing a past state

​What a branch is

​What gets copied, and what does not

​Edited-preview override

​Feasibility summary

Database schema

Two stores, one join key

Schema conventions

Entity reference

Identity and access

`users`

`user_activity`

`api_keys`

`conversation_users`

The live per-thread workspace

`documents` (one per thread)

`objectives` (one per thread)

`todos` (many per thread)

`preferences` (many per thread)

Turn-tagged outputs

`artifacts` (many per thread)

Versioning and lineage

`workspace_snapshots`

`conversation_branches`

User feedback

`feedback` (many per thread)

Relationships

Migration history

Snapshots

Restore and branching: what is feasible

Reconstructing a past state

What a branch is

What gets copied, and what does not

Edited-preview override

Feasibility summary