# Core Storage

Date: 2026-04-22
Status: current

This document is the canonical storage spec for AgentTeam.

## Supported Backend

AgentTeam core storage is TiDB-only.

- `AGENTTEAM_DATABASE_DSN` is required for runtime, schema, and admin commands.
- There is no storage-backend selector in active runtime, schema, or admin paths.

## Source Of Truth

The canonical schema lives in:

- `internal/tidb/bootstrap.sql`
- `internal/tidb/migrations/*.sql`

Fresh databases bootstrap from the snapshot and then apply any remaining versioned migrations above the bootstrap baseline.

There is no alternate core-storage migration timeline in the active repository.
Store domain packages must not embed, export, or merge their own TiDB migration registries. Historical organization/provider migrations are active upgrade inputs and live in `internal/tidb/migrations/*.sql` with the rest of the core schema timeline.

## Migration History

Committed TiDB migration history is immutable. A change may add migrations,
but it must not modify, delete, rename, or change the file type of an existing
file under `internal/tidb/migrations`.

Every migration added by a candidate must have a unique five-digit version
that is strictly greater than the current maximum version in the integration
target. This is a source-history coordination rule: it prevents a candidate
from reusing a version already present in the target while preserving
historical target files as immutable inputs.

`guardrails/storage/tidb-migration-history.sh` enforces this contract from Git
history. The candidate is committed `HEAD`, compared from its merge-base with
the target commit. `AGENTTEAM_TIDB_MIGRATION_BASE_REF` selects that target and must
resolve to a commit; shared and final integration gates must set the intended
current target explicitly. Backend integration validation requires that input
to be the target's immutable full commit SHA rather than a mutable ref. The
`origin/main` default is only a local convenience for direct guard execution.
The guard fails closed when it cannot compute the merge-base, cannot read
either Git tree, or finds untracked, staged, or unstaged changes in the
migration directory. It has no environment skip. An exceptional repair of a
failed, unapplied production migration, or removal of a newly discovered
destructive data mutation for databases that have not applied it, must be
pinned in `guardrails/storage/tidb-migration-repairs.tsv` by path plus the
exact target and candidate Git blob ids. A repair of already-recorded history
must not replay that migration or infer, restore, or otherwise rewrite the
affected data; any other content change remains forbidden. A candidate must
rerun the guard against the latest target before integration so parallel
branches that selected the same next version cannot both enter the target.

## Runtime Boundary

Application code must treat TiDB as the single storage implementation while keeping the runtime seam small and boring.

- Business code talks through `internal/coredb.DB` and `database/sql`-compatible behavior.
- Admin and schema operations go through `internal/coredb.NewAdmin()`.
- Upper layers must not introduce storage-engine branches or storage-specific compatibility fallbacks.

## Data Model

AgentTeam keeps one logical transactional storage model in TiDB for:

- auth and sessions
- workspace topology
- messaging hot state
- durable runtime and delivery facts
- tasks
- enabled organization-provider state

Cold message history remains in archive storage (`filesystem` or `S3`). TiDB stores the authoritative hot-window metadata and current transactional state.
Archived message content remains in archive storage, but TiDB may store narrow
lookup metadata for identifiers that are not derivable from `scope_id + seq`.
For example, `scope_archived_messages` maps archived message ids and reply-thread
short ids to the archived `seq` so runtime reads can derive the fixed archive
chunk without scanning all cold objects for a random message id.

TiDB is not the source of truth for temporary daemon runtime state. Daemon WebSocket presence, machine online/offline, running-agent sets, runtime session ids, status/activity/detail, live trajectory streams, and live instance mirrors must stay in daemon-local state, current server connection memory, browser realtime buffers, or a TTL/ephemeral store such as Redis when multi-server sharing is required. Persist only durable user intent, durable configuration, security credentials, business facts, bounded delivery ledgers, and low-frequency last-known diagnostic metadata whose value actually changed. During daemon upgrade windows, `agents.session_id` may remain as a sparse legacy mirror only for daemons that do not advertise `agent:local-session-resume`; it must not be used as the source of truth for capable daemons or MaaS/runtime readiness.

## Shared surfaces and owner-private context storage

Shared parent surfaces are part of the core TiDB schema, not a separate storage backend or UI-only projection. The shipped message model is the scope model:

- `scope_messages` is the hot message fact table for channel, DM, and reply-thread scopes.
- `scope_memberships` and per-member cursors are the dense read/unread facts for ordinary human timeline views; agent delivery does not use a server `/receive` cursor.
- `scope_activity_events` stores only non-message activities, such as reaction add signals and typed task activity projections that may need timeline ordering, diagnostics, or notification pipelines.
- task-native execution, context, discussion, and audit tables store task/execution ingress; task ingress must not be represented as a message delivery row.
- private visibility is derived from concrete scope facts: membership intervals, private grants, author/source intervals, message visibility from membership/grants plus non-message activity visibility, and task-native ingress.

Fresh databases receive this final schema through `internal/tidb/bootstrap.sql` plus forward migrations above the bootstrap baseline. Upgrades from pre-convergence data must use the normal TiDB migration path so existing channel scopes, DMs, reply thread scopes, read state, agent entries, non-message activity facts, and task/context audit rows land in the single canonical scope timeline. Do not introduce an alternate private-context migration path or a second storage representation for these rows.

Historical pre-cutover migrations may mention earlier table names because they preserve already-run development migration numbers. They do not define shipped runtime truth:

- `00093_agent_spaces_foundation.sql` is a historical pre-ship foundation migration whose rows are later converged into the scope model.
- `00104_provider_app_watch_service.sql` is a historical provider-watch migration kept in the shape already used by SG/dev databases.
- `00108_workspace_organization_ownership.sql` removes unshipped Agent Space schema remnants so upgraded databases match the final bootstrap schema.

### Scope read/fanout storage

Default dense/broad delivery must not write per-message/per-recipient rows. Ordinary channel, DM, reply-thread, and task discussion unread state is derived from `scope_memberships`, notification settings, and `(member, scope)` cursors. `@all` and `@online` are broad policies, not durable targeted snapshots; `@online` may trigger best-effort realtime wake at send time, but it must not create durable per-online-recipient rows.

Message-backed targeting does not write an activity or routing table. Direct mention, explicit recipient, explicit wake, task dispatch, and controlled broadcast all require the target to be an active scope member first; the reason changes the current fanout / notification priority and outbox retry state, not message visibility or daemon delivery eligibility.

Only non-message activity writes `scope_activity_events`. Reaction add and typed task activity projections may use this log when they must enter timeline, notification, wake, or diagnostics flows. Task typed activity writes must go through the task timeline writer; feature-local task code must not insert `scope_activity_events` directly. `scope_activity_events` is not membership, not private authority, not a message-created log, and not a dense-delivery snapshot.

Owner_quote source materialization writes ordinary message/source-ref audit facts. It must remain a separate read source and must not be copied into `scope_activity_events`.

The shipped model also removes hidden materialization paths. Hidden auto-flow copies, private-context creation backfill, routine parent mirrors, source-ref-as-dedupe pipelines, eager inherited participant/subscription rows, attachment copies, and human/reaction side ledgers must not become substitutes for bounded non-message activity or task-native audit.

Final-state stance: shipped read/unread/wake/diagnostics truth comes from `scope_messages`, `scope_memberships` and human cursors, `scope_activity_events`, `scope_fanout_outbox`, ordinary owner_quote source refs, and task-native discussion audit. Agent current-event delivery is the fanout payload/status handed to daemon, not a server-side receive backlog. Message-backed reasons such as direct mention, explicit recipient, and task dispatch may write bounded outbox status; reaction activity and typed task activities may write bounded activity rows; task/collaborator flows may write bounded task-native audit rows. Ordinary messages must not create hidden execution-context copies or parent mirror rows. Collaborator notification state must use explicit scope/task facts and must not set `notified_at` until a notification is actually delivered.

The detailed target optimization is defined in [`specs/message/delivery-ledger-optimization.md`](../message/delivery-ledger-optimization.md). Because the affected schema has not shipped, that spec is a pre-ship gate rather than a post-ship cleanup track.

### Task Collaborator People

Task / thread / channel collaborators are owner-scoped context eligibility grants, not delivery rows and not Task-only storage. V1 only ships explicit member collaborators ("People"): a collaborator can be a human member or an installed app service member, and is active while the owner+scope execution has an active `task_scope_participant_metadata` row for that member. App collaborator rows do not create ordinary source-scope membership and do not grant the app source history access. Whole channel/thread range sharing is not part of the shipped schema.

Storage invariants:

- no owner self rows;
- no empty list rows;
- no inherited task rows copied from thread/channel;
- no default execution row for owner-only scopes;
- no whole-scope boundary grant table or execution-row hint in V1;
- no collaborator-specific delivery ledger; add/remove/expire metadata or notifications stay cold and cannot become a second authorization truth source;
- ordinary channel/thread message writes do not query People tables.

Effective Task collaborators are a runtime union of the current task, parent thread, and parent channel grants. That union is loaded only for collaborator UI, collaborator management, or non-owner Task-bound discussion/context authorization. The default owner-only path does not need a collaborator query.

## Message Search

User-facing message search is part of the TiDB storage contract.

- Search reads from the TiDB projection tables managed by the TiDB admin flow.
- The runtime exposes search only when the TiDB projection is marked read-ready.

The detailed search contract is defined in `specs/message/search.md`.

## Dev, Test, And Build Paths

All repo-owned execution paths must use TiDB:

- local development
- regression test harnesses
- web E2E backend
- managed dev instances
- schema init and verify
- admin message-search maintenance

The repo must not require a second storage configuration, local database stack, export/import helper, or infra stack for core data.

## Operational Rules

Supported lifecycle:

1. Provision or point at a TiDB database.
2. Run `agentteam-schema init` or `agentteam-schema verify`.
3. For scope message search, run the TiDB admin commands when needed. Low-level
   repair/debug commands operate on one workspace-global projection selected by
   `AGENTTEAM_WORKSPACE_MESSAGE_SEARCH_WORKSPACE_ID`:
   - `agentteam-tidb-admin workspace-message-search-backfill`
   - `agentteam-tidb-admin workspace-message-search-finalize`
   - `agentteam-tidb-admin workspace-message-search-promote-ready`
   - `agentteam-tidb-admin workspace-message-search-status`
   High-level orchestration is available as:
   - `agentteam-tidb-admin workspace-message-search-ensure-ready`
4. Before enforcing the workspace owning-organization membership invariant in
   runtime reads, repair historical active human workspace members with the
   TiDB admin commands:
   - `agentteam-tidb-admin workspace-org-membership-verify` prints a read-only JSON
     report and fails while any active workspace human member lacks an active
     member in its owning organization.
   - `agentteam-tidb-admin workspace-org-membership-backfill` defaults to dry-run.
     Add `--apply` to create missing organization membership rows in bounded
     batches. It does not reactivate inactive organization members or inactive
     workspace members; those rows must be resolved before enforcing the
     owning-organization membership invariant.
5. Every release activation must run workspace placement maintenance after
   `agentteam-schema init` and before service restart or traffic:
   - `agentteam-tidb-admin workspace-placement-backfill --workspace-group-id workspace-default`
     idempotently creates only missing placement rows and never overwrites an
     existing placement.
   - `agentteam-tidb-admin workspace-placement-verify --workspace-group-id workspace-default`
     is the fail-closed cutover gate. Activation must stop if verification
     reports missing, non-default, or inactive placements.

Release activation stops `agentteam-server` before `agentteam-schema init` and starts the
new binary only after schema and placement verification succeed. This exclusive
cutover prevents the previous binary from writing rows against a schema it does
not understand while a migration backfills and contracts a required column or
constraint. A failed schema or placement gate leaves the service stopped for
operator repair; activation must not restart the previous binary against a
partially advanced schema. Running a contract migration through an out-of-band
`agentteam-schema init` while `agentteam-server` is accepting writes is not a supported
deployment path.

`agentteam-schema init` assumes a fresh database or a database already at the current schema.

## Acceptance Criteria

This spec is satisfied only if all of the following are true:

- TiDB is the only documented and tested core storage database
- organization-provider runtime wiring uses the same TiDB core seam
- message search, archive, and hot-history behavior are documented in TiDB terms
- docs, scripts, build recipes, and infra do not imply a second supported core-storage path