Database Schema

IMPORTANT

TL;DR: LoopTroop persists durable state in two SQLite databases plus ticket-owned files: one app DB for global settings and attached-project identity, one per-project DB for workflow records, and .ticket/** files for review documents, logs, and runtime/recovery metadata.

LoopTroop does not treat model transcripts as source of truth. Durable workflow state is split deliberately:

• the app DB stores global configuration and the attached-project registry
• each attached repository has a project DB for tickets, attempts, artifacts, session ownership, and error history
• the ticket worktree filesystem stores user-facing documents, durable logs, and ticket-owned runtime/recovery files that do not belong in relational tables

1. Storage Layout At A Glance

Layer	Default location	Owns	Notes
App DB	~/.config/looptroop/app.sqlite	Profile defaults, app metadata, attached projects	Override with LOOPTROOP_CONFIG_DIR or LOOPTROOP_APP_DB_PATH
Project DB	<project>/.looptroop/db.sqlite	Project row, tickets, artifacts, phase attempts, OpenCode session ownership, status/error history	Derived from the attached project root
Ticket filesystem	<project>/.looptroop/worktrees/<externalId>/.ticket/**	Canonical docs, runtime logs, bead files, ticket meta, rebuildable projections	Lives inside the ticket worktree, not in the root repo tree

Both SQLite connections use WAL mode plus SQLite busy timeouts. The app DB connection and path resolution live in server/db/index.ts; the app schema is bootstrapped in server/db/init.ts. The project DB is created and evolved in server/db/project.ts, which also cleans foreign-key orphans before enabling PRAGMA foreign_keys=ON so old or manually edited project databases do not start with dangling references.

Manual QA evidence locking is a separate persistent SQLite transaction database at .ticket/manual-qa/vN/evidence/index.json.lock. Each acquisition attempt opens its own connection, uses BEGIN IMMEDIATE with busy_timeout=0, retries asynchronously within a bound, and rolls back or closes before release. Process death releases the native transaction; LoopTroop never unlinks this lock database or treats a stale JSON marker as ownership proof. SQLite may create adjacent -journal, -wal, or -shm files while it is active.

2. Identity And Ownership Boundaries

The main thing to understand is that LoopTroop has public IDs, local row IDs, and filesystem paths, and they are intentionally different:

Identifier	Stored in	Meaning
attached_projects.id	App DB	Public project id used by the API
projects.id	Project DB	Local numeric row id inside that project DB only
tickets.id	Project DB	Local numeric foreign-key target inside that project DB
tickets.external_id	Project DB + filesystem paths	Human-facing per-project ticket id such as AUTH-12
projectId:externalId	API/public refs	Composite ticket ref returned by the API, built from attached_projects.id + tickets.external_id

Important consequences:

• there are no cross-database foreign keys between the app DB and project DB
• the bridge between them is the attached project root path (attached_projects.folder_path / projects.folder_path)
• projects.id and tickets.id are local implementation details; the API exposes composite refs instead

3. App Database

The app database is the global control-plane store.

Tables

Table	Purpose	Notes
profiles	Baseline workflow/profile settings	Treated as a singleton row by the API
app_meta	Small app-level key/value metadata	Used for lightweight UI/runtime flags
attached_projects	Registry of attached project roots	Provides the public project id

profiles

This row is the default configuration source. Its Advanced values seed concrete settings for future projects; editing the profile does not rewrite projects that are already attached.

Important columns:

• model selection: main_implementer, main_implementer_variant, council_members, council_member_variants
• workflow budgets and limits: min_council_quorum, interview_questions, max_iterations, structured_retry_count
• timeout settings in milliseconds: per_iteration_timeout, execution_setup_timeout, council_response_timeout
• coverage controls: coverage_follow_up_budget_percent, max_coverage_passes, max_prd_coverage_passes, max_beads_coverage_passes
• Manual QA baseline: manual_qa_enabled (non-null boolean, default true)
• AI question baseline: ai_questions_enabled (non-null boolean, default true) and ai_question_window (milliseconds, default 300000)
• internal Git behavior: git_hook_policy (non-null text, default validate_advisory)
• LoopTroop folder ignore destination: ignore_mode (non-null text, default local)
• OpenCode retry controls: opencode_retry_limit, opencode_retry_delay, opencode_steps
• tool log truncation limits: tool_input_max_chars, tool_output_max_chars, tool_error_max_chars

Operational notes:

• the table shape allows multiple rows, but the API treats it as a singleton: POST /api/profile rejects a second profile and normal reads use the first row
• council_members is stored as a JSON array string; council_member_variants is a JSON object string keyed by model id
• defaults come from server/db/defaults.ts
• validation ranges are enforced by the API layer in server/routes/profiles.ts, not by SQLite column constraints alone

NOTE

Current behavior. Dirty form snapshots, hydration state, and unsaved modal drafts stay in the browser; only a successful profile write changes this row, and a reload is not a promise of recovering an unsaved draft.

app_meta

app_meta is intentionally small and generic: key, value, and updated_at.

Today it is used for startup/UI metadata such as startup.restore_notice.dismissed_at in server/startupState.ts, and it is the right place for tiny app-wide flags that do not justify a dedicated table.

attached_projects

This table is the app-level registry of attached repositories:

• folder_path is unique and stores the canonical Git repository root, so one repository cannot be attached through multiple paths
• id is the public project id used by the API
• deleting or detaching an attached project removes this registry row, not necessarily the project-local .looptroop state

Project names and short names are kept in each repository's project database, so their cross-project uniqueness is enforced by the attachment and rename paths rather than by a single SQLite constraint. Names compare case-insensitively after trimming; short names compare in uppercase form. Removing an attachment leaves local state recoverable, but adding a repository that is still present in this registry is rejected.

4. Project Database

The project database is the operational store for one attached repository. LoopTroop expects one logical projects row per attached repo and many ticket-owned rows underneath it.

Tables

Table	Purpose
projects	Project metadata, concrete Advanced choices, and other project-level configuration overrides
tickets	Ticket records, workflow status, progress counters, and serialized machine snapshot
phase_artifacts	Phase-scoped structured artifacts, reports, approvals, UI companions, and read models
manual_qa_operations	Durable Manual QA submit/skip operation journals
manual_qa_improvement_tickets	Deterministic mapping from one Improvement origin to one child Draft ticket
interview_batch_claims	One active interview-answer batch claim per ticket
ticket_phase_attempts	Archived/active phase-version history for non-implementation phases
opencode_sessions	Exact OpenCode session ownership records
ticket_status_history	Append-only status transition log
ticket_error_occurrences	Append-only blocked-error history plus resolution state
bead_execution_metrics	One row per completed bead; powers throughput/ETA forecasting
question_waits	One row per stretch a ticket spent waiting for a human answer to an AI question
ticket_ai_turn_metrics	One idempotent row per newly completed OpenCode assistant message; powers AI/model details
execution_log_projection	Rebuildable, query-oriented rows projected from the three durable JSONL log channels
execution_log_projection_cursors	Per-ticket/channel byte offsets used for incremental projection catch-up
execution_log_native_*	Native OpenCode file generations, indexed entries, and four-retained cursor snapshots for complete DEBUG history

projects

Important columns:

• display/identity: name, shortname, icon, color, folder_path
• saved project settings: manual_qa_override, git_hook_policy, ignore_mode
• nullable overrides: council_members, max_iterations, per_iteration_timeout, execution_setup_timeout, council_response_timeout, min_council_quorum, interview_questions, ai_questions_override, ai_question_window_override
• sequencing: ticket_counter
• metadata: profile_id

Operational notes:

• ticket_counter is the source for tickets.external_id; new tickets are generated as <shortname>-<counter>
• council_members is a JSON array string when present
• profile_id is not a cross-database foreign key; SQLite cannot enforce a foreign key into the separate app DB, so this column is metadata only
• project-level settings are read directly from this row at runtime; they do not require joining back into the app DB
• new projects store concrete Manual QA, Git-hook, and ignore choices copied from the profile defaults unless the attach request supplies explicit values; legacy nullable values remain readable
• project git_hook_policy is the only editable Git-hook policy for that project; Start freezes it for the ticket run and never rewrites the target repository's Git configuration
• ignore_mode records whether attach-time rules were appended to .gitignore (repo), this clone's Git exclude (local), or nowhere (skip)

Reattaching Existing Project State

Selecting a repository with an existing .looptroop/db.sqlite exposes three storage operations:

• Restore preserves the project row and all ticket-owned rows/files. Current visible form edits are applied, and projects.folder_path plus the app-level attached_projects.folder_path are aligned to the repository root on the current machine.
• Clear tickets preserves the complete project row, including its short name, appearance, creation timestamp, profile association, saved Advanced settings, and nullable overrides. It removes every ticket and all dependent records, artifacts, attempts, QA operations/metrics, status/error history, OpenCode session ownership, ticket files, and managed worktrees. It then sets ticket_counter to 0, applies current visible form edits, updates folder_path, and advances updated_at.
• Start fresh removes managed worktrees, prunes Git worktree registrations, deletes the entire .looptroop directory, and creates a new project database from the current form.

Clear/start-fresh cleanup includes active tickets; it is not constrained by the normal project-deletion rule. Repository source files, commits, and branches remain outside these deletion boundaries. Resetting ticket_counter makes the next ticket <SHORTNAME>-1, so a surviving old branch can share the restarted ticket identifier.

tickets

This is the operational center of a ticket.

Important columns:

• identity and status: external_id, project_id, title, description, priority, status
• persisted machine state: xstate_snapshot
• execution progress: branch_name, current_bead, total_beads, percent_complete
• failure surface: error_message
• cancellation: cancel_reason
• Manual QA and reconciliation: nullable Draft-only manual_qa_override, frozen locked_manual_qa_enabled, frozen locked_manual_qa_source, and monotonic workflow_revision
• AI questions: nullable Draft-only ai_questions_override and ai_question_window_override, frozen locked_ai_questions_enabled, locked_ai_questions_source, locked_ai_question_window, and locked_ai_question_window_source
• Git-hook behavior: frozen locked_git_hook_policy and locked_git_hook_policy_source; fresh schemas have no ticket-level Git-hook override
• frozen-on-start settings: locked_main_implementer, locked_main_implementer_variant, locked_council_members, locked_council_member_variants, locked_interview_questions, locked_coverage_follow_up_budget_percent, locked_max_coverage_passes, locked_max_prd_coverage_passes, locked_max_beads_coverage_passes, locked_structured_retry_count
• lifecycle times: started_at, planned_date, created_at, updated_at

Operational notes:

• xstate_snapshot is a serialized XState snapshot used to restore non-terminal tickets on startup
• external_id is the stable human-facing identifier; the API turns it into a public ticket ref by prefixing the public project id
• locked configuration columns freeze the profile/project settings that were in force when the ticket started
• manual_qa_override uses SQL NULL for Inherit and booleans for Enabled/Disabled; resolution order is ticket → project → profile, and missing locked values on older started tickets mean disabled
• the two AI-question overrides follow the same NULL-means-inherit convention and the same ticket → project → profile order, but resolve independently of each other, so a ticket can hold its own window while inheriting the on/off answer. Missing locked values on older started tickets mean the run may not ask at all: a ticket already in flight should not silently gain the ability to stop and wait for a person
• Start snapshots the project's git_hook_policy and its project source for execution-setup planning. Older databases may retain an obsolete ticket git_hook_policy column and data, but current create/update/read resolution ignores it; no compatibility migration is required.
• cancel_reason holds why the operator cancelled, and is a ticket column rather than a phase artifact on purpose: cancelling with Delete AI-generated artifacts removes every phase_artifacts row for the ticket, so a receipt would be erased by the same action that wrote it. Cancelling with Delete the ticket completely leaves nothing, including this.
• workflow_revision increases on status transitions and lets polling/SSE consumers reject stale state even when the workflow moves backward from Manual QA to Coding
• branch_name = '__looptroop_display_only_mock__' is reserved for board-only mock/demo tickets; these rows are returned for display, projected through the API with isDisplayOnlyMock: true, excluded from startup hydration and runnable workflow actions, and expose only Cancel while non-terminal (except final cleanup in development, where no actions are available)
• runtime details shown in the UI are enriched from both this row and ticket-owned files under .ticket/**

phase_artifacts

NOTE

Receipt identity includes action, item, phase, and phase attempt. Duplicate writes within one attempt remain idempotent, while a later attempt receives a new receipt. The existing artifact uniqueness boundary is reused; no schema migration or separate action-claim table is needed. Receipt rollback, cancellation-content cleanup, and ticket deletion use the existing artifact cleanup. Other artifact types and non-JSON content are outside the receipt key.

This table stores structured workflow artifacts and related UI/read-model payloads.

Columns:

• ticket_id
• phase
• phase_attempt
• artifact_type
• content
• created_at
• updated_at

Operational notes:

• content is typically a JSON string, even when the user-facing canonical document also exists as YAML/JSONL on disk
• phase_attempt versions artifacts across retries, regenerations, and post-approval restarts for tracked phases
• the database does not have a file_path column; API artifact payloads may expose filePath, but DB-backed artifacts currently return null
• this table stores more than just final docs: examples include interview, prd, beads, execution_setup_plan, coverage artifacts, approval_snapshot:*, ui_state:error_attention, cleanup_report, merge_report, final_test_report, and pull_request_report
• skip_receipt:<surface> rows are the append-only record of everything that got skipped. The surface is part of the artifact type: interview_question, interview_all, interview_approval_mark_skipped, approval_with_gaps, close_unmerged, cancel_ticket, and opencode_question. Each row carries a schema version, an idempotent action_id, the item, the phase and attempt, the ticket status before the action, the timestamp, and the reason as it read at that moment. Receipt identity is attempt-scoped: duplicate writes in one phase attempt are ignored, while the same action/item on a later attempt gets a new receipt. An interview bulk skip writes one summary row plus one row per item, all in a single transaction, which is what makes a forty-question Interview Skip All count as one action rather than forty-one skips. Manual QA is not in that list: it already wrote its own skip and waiver records, and the shared read API adapts those rather than adding a duplicate.
• The AI question panel's Skip all sends a separate rejection for each OpenCode request. Each request has its own action_id and writes its summary plus question rows in one transaction. Requests can fail independently, so the whole panel operation is not a single transaction.
• receipts are at schema version 2. skipped_by widened from the literal user to user | timeout | system, because a question the wait ran out on was refused by nobody and filing that under a person's name is a lie the trail cannot walk back. Rows written before the field existed report user, which is what they meant. opencode_question rows additionally carry a question_context object with the request and session IDs, the configured window, the armed and deadline times, how many times another model reset the shared clock, the elapsed wall and active time, the sibling requests the same refusal covered, an expiry reason, and a quorum-impact note where one applies. It is on the receipt because the request itself is gone the moment OpenCode is told, leaving no current state to read.
• opencode_question:<sessionId>:<requestId> and opencode_question_timer:<phase>:<attempt> are the durable copies of live AI-question state. Memory is the cache and these are the record: a daemon restart rebuilds from them, or refuses what it cannot rebuild. Failing to write one costs a restart's worth of recovery, not the wait itself, so the write is best-effort and never blocks a run.
• Manual QA keeps compact append-only checklist, coverage, results, draft snapshot, and summary artifacts here; live editing exists only as ui_state:manual_qa_draft:vN with a server-owned compare-and-set revision
• council companion artifacts may embed draft/vote metadata and attempt diagnostics in content; malformed model text is intentionally kept out of structured fields

manual_qa_operations

This table is the durable operation journal for a final Manual QA Submit or Skip batch.

Columns:

• id: auto-incrementing primary key
• ticket_id: source ticket foreign key with cascade deletion
• action_id: caller-stable idempotency identity
• version: checklist round reserved by the operation
• checklist_hash and draft_revision: immutable optimistic-concurrency guards
• state: durable journal stage (initially staged, then advanced as results, improvements, beads, receipts, and transition effects become durable)
• payload: serialized operation/journal data used to resume incomplete stages
• created_at, updated_at

(ticket_id, action_id) has a unique index. A retry with the same identity resumes the existing state; it cannot create a second operation for that ticket/action pair or silently change the guarded checklist/draft.

Deleting the source ticket cascades this journal. Project-wide Clear tickets also deletes the table explicitly as part of its whole-project reset path, so partially staged Manual QA operations do not survive a deliberate ticket wipe.

manual_qa_improvement_tickets

This table maps one deterministic Manual QA Improvement origin to exactly one Draft child ticket.

Columns:

• id: auto-incrementing primary key
• origin_id: deterministic, globally unique improvement origin
• destination_ticket_id: created Draft ticket foreign key with cascade deletion
• action_id: parent submission identity
• created_at

origin_id is unique. The mapping is created in the same SQLite transaction as the Draft child ticket, so a restart after database creation but before filesystem provenance/evidence writes finds the same child and repairs the missing receipts instead of creating a duplicate.

The only SQL foreign key here is the destination child ticket. The parent submission is linked by action_id, not by a separate foreign key, and the parent source ticket is tracked in the operation payload/receipts rather than on this row. Deleting the child ticket cascades the mapping; startup orphan cleanup removes any leftover row whose destination ticket no longer exists, and project-wide Clear tickets deletes the table explicitly before resetting tickets.

interview_batch_claims

NOTE

Current behavior. Batch-claim lease recovery and the separate non-expiring pending-stop safety marker described below are part of the current implementation.

This table is the one durable lock that says an interview answer batch is being processed right now.

Columns:

• ticket_id: primary key and ticket foreign key with cascade deletion
• token: claim identity, used to distinguish one acquisition from a later replacement
• claimed_at
• expires_at

Operational notes:

• ticket_id being the primary key means there can be only one live claim row per ticket
• token identifies the holder, so a stale process cannot release a claim that expired and was reacquired by someone else
• expires_at makes crash recovery self-healing: a foreign claim can be reclaimed once its ordinary lease expires (the fallback when liveness cannot be checked), and a recorded PID proven gone is a separate conservative reclaim path
• A live lease protects live, invalid, or otherwise unverified owners. A non-expiring pending-stop marker is separate safety ownership and takes precedence over ordinary lease expiry until the matching remote stop is confirmed, so a delayed timeout cannot reclaim a newer batch.

ticket_phase_attempts

This table tracks active and archived phase versions.

Columns:

• ticket_id
• phase
• attempt_number
• state
• archived_reason
• created_at
• archived_at

Operational notes:

• it is used for non-implementation phases
• CODING does not create new phase attempts; coding retries use bead/worktree reset history instead
• archived attempts are read-only and are what power prior-version artifact/log views

opencode_sessions

NOTE

Current behavior. Ticket-contained OpenCode ownership markers and the two-storage restart limit described below are part of the current release.

This table is the primary durable record for OpenCode ownership. Restart recovery is possible when it or the ticket-contained pending-session marker is available and the exact remote session still matches. If both storage layers are unavailable, only the current process guard remains.

Columns:

• session_id
• ticket_id
• phase
• phase_attempt
• member_id
• bead_id
• iteration
• step
• state
• last_event_id
• last_event_at

Operational notes:

• the ownership slot is the full tuple of ticket + phase + phase attempt + optional member/bead/iteration/step
• reconnect/continue logic validates the exact project-local owned active session record, not just “some session for this ticket”; blocked-error restart recovery also requires the unresolved occurrence, previous phase, and diagnostic session id to match
• a failed ownership write can use the ticket-contained runtime/opencode-pending-sessions.json marker; startup can replay that marker even when this table has no row
• transient OpenCode verification failures preserve active, while only confirmed remote absence or stale ownership changes the row to abandoned
• state is currently active, completed, or abandoned
• ticket_id is nullable and becomes NULL if a referenced ticket is removed

ticket_status_history

This is an append-only transition log with:

• ticket_id
• previous_status
• new_status
• reason
• changed_at

It records explicit status changes, not every internal machine detail. In normal patch flows, reason is typically populated from the error message that accompanied the transition.

ticket_error_occurrences

This table records blocked errors as explicit occurrences instead of mutating one blob in place.

Columns:

• ticket_id
• occurrence_number
• blocked_from_status
• error_message
• error_codes
• diagnostic_details
• occurred_at
• resolved_at
• resolution_status
• resumed_to_status

Operational notes:

• each new blocked incident increments occurrence_number
• error_codes is stored as a JSON array string
• diagnostic_details stores normalized diagnostic payloads used for recovery decisions and UI detail, including available error type, sanitized bounded stack, and compact failed readiness checks (name, category, message). Full check evidence remains in sanitized debug records and the pre-flight report, avoiding repeated check stacks in ticket payloads. These fields use the existing JSON payload; there are no separate columns. The last five debug records shown in Technical details are read from the occurrence's failed-phase time window rather than copied into this table.
• resolution is modeled explicitly with resolved_at, resolution_status, and resumed_to_status

bead_execution_metrics

One row is written per completed bead (best-effort; a failure here can never break an execution run). It is the deterministic throughput store behind the execution percent-done + ETA forecast.

Columns:

• ticket_id
• bead_id
• size_bucket: ticket size class by total bead count (S 1 to 5, M 6 to 12, L 13+)
• effort_tier: the ticket's locked main-implementer reasoning variant (e.g. medium)
• iterations: attempts including retries
• active_duration_ms: bead completion time, excluding windows where the ticket was outside CODING and any time spent waiting on an answer to an AI question
• wall_clock_ms: completed_at - started_at (diagnostic only)
• completed_at
• schema_version
• input_tokens, output_tokens, cost_usd: reserved for the future Cost Management feature; nullable and intentionally left unset by the ETA feature

Operational notes:

• active_duration_ms is measured from bead start to bead completion, minus any window the ticket spent outside CODING and minus any time it spent waiting for a human answer to an AI question. This keeps local finalization in the ETA because the forecast represents time until the bead is actually complete. It also keeps a wait for a person out of the ETA: a question does not change the ticket's status, so without question_waits the wait would be indistinguishable from coding and would train the forecast on throughput that never happened.
• rows with no usable timing (active_duration_ms <= 0) are skipped so they cannot poison future medians
• ETA is computed read-time in buildRuntime from these rows (rich bucketed history with a (size+effort) -> effort -> any fallback, current-run samples while the ticket is building its own signal, sparse history before the hardcoded default); nothing about the forecast itself is persisted
• the reserved token/cost columns let Cost Management extend the same per-bead record later without changing existing readers

question_waits

One row per stretch a ticket spent waiting for a person to answer an AI question.

Columns:

• ticket_id
• started_at, ended_at

Operational notes:

• an interval opens when the ticket's first question arrives and closes when its last one is resolved, so two overlapping questions record one stretch of wall time rather than two that would each subtract the same minutes
• the in-memory work budget holds the same number for the running phase timeouts; this table is the copy that survives a restart and can be asked about a window in the past
• read by the ticket's implementation timing (activeDurationMs, workspacePreparationDurationMs, finalTestingDurationMs, each net of the overlap, plus questionWaitingMs reported on its own) and by bead_execution_metrics
• written best-effort; a lost row costs accuracy in a reported duration, never correctness in a run

ticket_ai_turn_metrics

One row is upserted per newly completed OpenCode assistant message. The unique ticket/session/message identity makes prompt completion and reconnect handling idempotent without scanning or migrating historical OpenCode sessions.

Columns:

• ownership and scope: ticket_id, phase, phase_attempt, session_id, assistant_message_id
• provenance: model_id, variant, agent, finish_reason
• timing: started_at, completed_at, duration_ms
• usage: cost_usd, input_tokens, output_tokens, reasoning_tokens, cache_read_tokens, cache_write_tokens
• metadata: created_at, updated_at, schema_version

Operational notes:

• missing provider usage or timing remains NULL; aggregate readers report coverage separately instead of treating missing values as zero
• phase details use the selected phase attempt, while lifecycle details aggregate all rows for the ticket and can optionally filter one model
• metric persistence and live invalidation are best-effort diagnostics and never block model execution
• ticket deletion cascades to these rows; clear-tickets and orphan cleanup remove them explicitly

Execution log projection tables

execution_log_projection is a rebuildable read model over the ticket's normal, debug, and AI JSONL files. Its composite primary key is (ticket_id, channel, identity); query columns include ordinal, timestamp, phase/attempt, classification, model/bead identifiers, canonical entry JSON, and source byte range. Stable identities fold streaming upserts/finalizations into one projected row without changing what the JSONL writer stores.

execution_log_projection_cursors stores the last indexed byte offset for each (ticket_id, channel). A truncated/replaced file resets only that channel. Cold catch-up reads the remaining suffix cooperatively in bounded batches, and concurrent readers share one catch-up promise per ticket. Both tables cascade with ticket deletion and can be reconstructed from the filesystem logs.

Phase debug events redact recognized credentials before formatting, shortening, broadcasting, or saving to JSONL. This applies to every workflow phase and retains model, session, process, timing, and numeric token-count metadata. Existing persistence and mirroring choices remain unchanged; the projection reads those already-redacted rows, and existing source files are not rewritten.

NOTE

Current behavior. Native OpenCode history is indexed separately from the bounded diagnostic reader. Complete DEBUG/history actions can scan the full available file set; initial views remain paginated and do not eagerly load the archive.

The native projection stores file identities, generations, indexed entries, snapshot pointers, and snapshot-to-file ranges in execution_log_native_index_files, execution_log_native_index_versions, execution_log_native_index_entries, execution_log_native_snapshots, and execution_log_native_snapshot_files. Four recent complete snapshots are retained for cursor stability across append and rotation. An expired cursor returns LOG_CURSOR_EXPIRED instead of a partial page. Appends index only new ranges, but a cold or unseen session scans its needed prefix; upstream-deleted files cannot be recovered. Native page rows are LIMIT-bounded, while lineage visibility checks grow with ancestry depth.

5. Relationship Overview

Within a project DB, the relational shape is:

Deletion behavior:

• deleting a ticket cascades through phase_artifacts, ticket_phase_attempts, ticket_status_history, ticket_error_occurrences, bead_execution_metrics, ticket_ai_turn_metrics, and both execution-log projection tables
• opencode_sessions.ticket_id uses ON DELETE SET NULL
• app DB rows and project DB rows are linked logically by project root path, not by SQL foreign key

6. What Lives Outside SQLite

SQLite is not the whole system. Some ticket state is intentionally filesystem-backed:

Path	Role	Source-of-truth note
.ticket/relevant-files.yaml	Canonical relevant-files document	Filesystem artifact
.ticket/interview.yaml	Current reviewable interview document	Filesystem artifact
.ticket/prd.yaml	Current reviewable PRD document	Filesystem artifact
.ticket/beads/<baseBranch>/.beads/issues.jsonl	Bead plan and bead runtime status/history	Filesystem artifact
.ticket/meta/ticket.meta.json	Ticket metadata such as base branch and locked model selection	Filesystem artifact
.ticket/opencode-steps-restore.json	Recovery sidecar for temporarily capped opencode.json	Operational filesystem artifact
.ticket/runtime/execution-log.jsonl	Main execution log	Filesystem log
.ticket/runtime/execution-log.debug.jsonl	Folded forensic/debug log	Filesystem log
.ticket/runtime/execution-log.ai.jsonl	AI-detail log channel	Filesystem log
.ticket/runtime/execution-setup-profile.json	Reusable execution-setup profile	Filesystem runtime artifact
.ticket/runtime/state.yaml	UI-friendly runtime projection	Rebuildable projection, not the primary source of truth
.ticket/manual-qa/vN/checklist.yaml	Immutable generated checklist for one round	Canonical versioned artifact
.ticket/manual-qa/vN/results.yaml and summary.yaml	Submitted results and round outcome	Results exist for Submit; summary exists for every completed round
.ticket/manual-qa/vN/coverage.yaml	Code-computed PRD criterion coverage	Advisory canonical report
.ticket/manual-qa/vN/fix-beads.yaml	Complete validated AI-planned QA-fix bead candidates	Written before any child ticket/bead side effect
.ticket/manual-qa/vN/model-capability.json	Immutable locked-model image capability snapshot	Captured for evidence delivery auditing
.ticket/manual-qa/vN/evidence/**	Contained evidence binaries plus metadata index	Disk-only binaries; database/UI state stores refs only
.ticket/manual-qa/generation-reservation-vN.json	Restart-safe version reservation	Reused after generation retry/restart
.ticket/manual-qa/workspace-baseline-vN.json and drift receipts	Git baseline and audited include/discard decisions	Submission/skip safety records
.ticket/manual-qa/events.jsonl	Idempotent versioned generation, evidence, drift, submission, child-work, and completion events	Append-only Manual QA audit stream
<current-temp>.proof beside a YAML temp	Byte length and SHA-256 proof for a complete YAML write	Required before YAML promotion; missing or mismatched proofs remain visible
<current-temp>.recovery and .recovery.write-*	Fallback-copy ownership marker and its staging file	A complete matching marker is required to resume or clean up a fallback copy; an unresolved in-progress marker blocks startup and preserves the affected files
Retained <current-temp>.remove-*	Private cleanup sidecar from an interrupted restoration race	May be retained at a recovery blocking point when ownership is uncertain; never treated as an unrelated target, and subject to cleanup/worktree deletion scope
.ticket/manual-qa/vN/evidence/index.json.lock plus SQLite transient files	Persistent native transaction lock for evidence index publication	Outside CLEANING_ENV's transient roots and not unlinked by lock or recovery code; explicit worktree deletion removes the containing worktree

Manual QA also writes immutable draft snapshots, skip receipts, submission-operation journals, and origin/source receipts where needed. A skipped round intentionally has draft + skip receipt + summary rather than results.yaml, because Skip does not submit item results. Evidence is capped at 250 MiB per file with no count or round-total limit. Filenames are sanitized, traversal and symlinks at every contained ancestor are rejected, and bytes are streamed through contained temporary files, hashed, and atomically renamed. Evidence index publication uses the persistent SQLite transaction lock above, while stable evidence/action IDs reconcile a restart between file rename or unlink, index persistence, and the final upload/remove receipt. Invalid Manual QA event shapes are skipped with diagnostics while their raw lines remain available for inspection. These files remain under ticket-owned .ticket storage, so normal bead commits, candidate diffs, and PRs exclude them.

The important split is:

• the database stores indexed workflow records and ownership relationships
• the filesystem stores review documents, append-only logs, and ticket-owned runtime/recovery files
• some filesystem files, especially runtime/state.yaml, opencode-steps-restore.json, and the recovery sidecars above, are operational projections or sidecars rather than primary review documents

7. Indexes And Runtime Behavior

LoopTroop creates a small set of runtime-focused indexes rather than a large generic index set.

App DB indexes

• idx_attached_projects_folder_path on attached_projects(folder_path)

Project DB indexes

• ticket lookup: tickets(project_id), tickets(status), tickets(external_id)
• skip-receipt uniqueness: a partial expression index on phase_artifacts(ticket_id, receipt_id from content) for skip_receipt:* rows
• artifact lookup: phase_artifacts(ticket_id), phase_artifacts(ticket_id, phase, phase_attempt)
• phase-attempt lookup: ticket_phase_attempts(ticket_id, phase, state, attempt_number) plus a uniqueness index on (ticket_id, phase, attempt_number)
• OpenCode session lookup: opencode_sessions(session_id), opencode_sessions(ticket_id, phase, state), opencode_sessions(ticket_id, phase, phase_attempt, member_id, bead_id, iteration, step, state)
• blocked-error lookup: unique (ticket_id, occurrence_number) plus (ticket_id, resolved_at, occurrence_number)
• throughput/ETA lookup: bead_execution_metrics(size_bucket, effort_tier, completed_at) and bead_execution_metrics(ticket_id)
• AI detail lookup: unique ticket_ai_turn_metrics(ticket_id, session_id, assistant_message_id), phase/model scope (ticket_id, phase, phase_attempt, model_id), and lifecycle/model scope (ticket_id, model_id, updated_at)
• projected history lookup: execution_log_projection(ticket_id, classification, phase, phase_attempt, model_id, ordinal DESC)

These match the hot runtime paths: ticket board/status queries, phase-attempt version browsing, session reconnect, blocked-error recovery, and read-time ETA throughput sampling.

8. Changing The Schema Safely

NOTE

This section is for working on LoopTroop from a checkout. The db:* scripts it uses live in the repository and are not part of an installed copy. An installed LoopTroop checks its database schema at startup, and looptroop doctor reports the schema state.

Fresh project databases are the supported starting point. LoopTroop does not migrate or deduplicate older project databases that already contain duplicate skip receipts. If startup reports a duplicate skip-receipt index error, back up the repository, remove the old LoopTroop project database, and attach the repository again.

LoopTroop uses Drizzle table definitions, but runtime bootstrap code is the real startup contract.

For app DB changes:

1. update server/db/schema.ts
2. update the app bootstrap/evolution logic in server/db/init.ts

For project DB changes:

1. update server/db/schema.ts
2. update the project bootstrap/evolution logic in server/db/project.ts

Do not treat db:push or db:push:app as the normal app-schema workflow. Likewise, do not assume a project schema change is complete just because Drizzle can generate or push it; the server still needs matching runtime bootstrap logic.

Useful explicit commands:

npm run db:generate:app
npm run db:generate:project
npm run db:push:project

db:generate / db:generate:app are primarily for migration artifact review or external tooling. db:push:project is the explicit project-target push command when you intentionally set LOOPTROOP_PROJECT_DB_PATH. Verify generated output against server/db/schema.ts before committing it.

Related Docs

• System Architecture
• Ticket Flow
• API Reference
• OpenCode Integration
• Operations Guide