Read-only inspection produced 2026-05-24 against loomworks-engine main at commit 2f04e7a (70 migrations, 0001 … 0070).
This inventory is organised by conceptual area, not by migration order. Each entity carries the introducing migration and a flat column list. The "Observations" line at the bottom of each entity is the load-bearing part of this document — they mark places where the structure feels evolved-rather-than-designed. They do not propose action.
The FORAY substrate (§4) is the explicit examination point per the kickoff. It is given a full section that describes all three FORAY-shaped surfaces, their relationships, what is shared, and what isn't.
A small number of cross-cutting findings (ORM/migration drift, polymorphism by string, code-only vs DB-level enumerations) are gathered at §15.
The identity layer was introduced in Phase 14 (mig 0029). It coexists with the older contributors table from Phase 3, which was the engagement-scoped bearer-token actor before persons existed. Persons are global; contributors are per-engagement; the two were not collapsed.
What it is: Engagement-scoped operational row for the Phase 3 bearer-token actor model. Predates persons. Carries the commit_authority flag.
Introduced: 0005_phase_3_contributors.
Columns:
Observations:
persons. The two never merged. The seed records initial_contributors as a list of person ids per the Phase 15 founding-memory migration, so the in-engagement "who's a contributor" answer goes through memberships now, not through this table. The table is still queried for bearer-token auth at the contributor router level.kind IN ('human', 'agent') here vs. memory_events.actor_kind IN ('contributor', 'agent', 'person', 'companion') (per the Phase 41 widening) — the two enumerations are partial overlaps with different value sets. The relationship between a contributors row of kind='human' and a persons row is not represented relationally.What it is: Global identity record. One row per natural person across the system.
Introduced: 0029_phase_14_person_layer. Heavily extended: companion_name + personal_engagement_id (0057), referred_by + license_tier + account_status + expires_at + previous_status_change_at (0062 Phase 47 credit lifecycle), exhaustion_preference + near_exhaustion_{20,10,5}pct_seen_at + previous_responder_model (0064 Phase 49).
Columns (current state):
ck_persons_exhaustion_preference (migration 0064)Relationships:
Observations:
persons has accumulated 18+ columns across at least four phases (14, 41, 47, 49). The three near-exhaustion timestamps in particular are a Phase 49 ramp pattern (three columns rather than one JSONB or a child table). Same pattern recurs on engagement_archive in the Forge codebase per the user's standing memory; the rationale here is audit-of-first-cross.referred_by is a self-FK on persons but the ORM declares the column without an ORM-level relationship (only the DB FK created by migration 0062). The ORM comment is explicit about avoiding self-referential mapper resolution at module-import time.companion_name lives alongside personal_engagement_id on persons. The Companion identity (name) and the Companion's per-person memory store (the personal engagement) are conceptually distinct but co-resident.
Introduced: 0029. Columns: id, person_id (FK), credential_id (BYTEA unique), public_key (BYTEA), sign_count, transports (JSONB), display_name, created_at.
Observations:
memory_events.attestation (a JSONB column on the canonical event log) for engagement-committed events; the credential registration record lives here. There is no relational link between the two — the attestation payload includes credential_id as a value, walked back into webauthn_credentials by application code.What it is: Person <-> engagement join with stackable designations and a soft-delete flag.
Introduced: 0029. Extended with active (0031), operator_sequence_number (0065).
Columns:
Uniques: (person_id, engagement_id); (person_id, operator_sequence_number).
Observations:
active is the soft-delete column; the application filters active=TRUE on every read. Hard delete is forbidden by the R-A28 rule.operator_sequence_number is application-assigned under a per-person advisory lock (per migration 0065 docstring). The contract is that application code (create_membership) computes the value; raw-SQL inserts that omit it fail with NOT-NULL. This is a hard-coded discipline at the data layer, not enforceable via a database default.What it is: Stackable role flags on a membership. Composite PK on (membership_id, designation).
Introduced: 0029. active flag added (0031).
Columns:
Designation values (code-only, in persons/memberships.py):
ALLOWED_DESIGNATIONS = frozenset({"operator", "contributor", "domain_expert"})
Observations:
Introduced: 0029. Bcrypt-hashed single-use codes per person.
What it is: Append-only audit log of the Phase 14 person-layer migration (and any future identity-layer data fixups). Distinct from the substrate's main event log because the main log is engagement-scoped.
Introduced: 0032.
Columns: id, event_type (TEXT discriminator), payload (JSONB), created_at.
Observations:
credit.foray_action_flows and audit.foray_events). Event_type + JSONB-payload + actor + timestamp is the recurring shape; see §4 and §15.What it is: Pre-Phase-16 generic credential store. Scoped to system/operator/engagement.
Introduced: 0004.
Columns: id, scope CHECK in ('system','operator','engagement'), scope_id, provider, encrypted_key, created_at, updated_at. UNIQUE(scope, scope_id, provider).
Observations:
credentials, engagement_api_keys (mig 0040), and system_config (mig 0048) are three separate Fernet-encrypted-value tables introduced at three different phases for partially overlapping use cases. credentials was Phase 2 (operator-scoped before persons); engagement_api_keys is per-engagement; system_config is global system. They have not been unified.Already inventoried above; reiterated here to note that two actor-identity concepts (contributors + persons) coexist after Phase 14.
The engagement is the spine of everything. The engagements table started life with four columns in Phase 1 and has accumulated columns through 10+ migrations.
What it is: The root row for every engagement. Carries the version counter, the current state, the visibility flag, the display identifier, and a hodgepodge of denormalised label/lookup columns.
Introduced: 0001_phase_1_substrate_events. Extended by: 0007 (candidate_seed_id), 0030 (visibility), 0041 (next_display_number), 0047 (created_by_person_id), 0049 (title), 0065 (display_identifier).
Columns (current):
append_event (memory/events.py)E#### format backed by sequence engagement_display_identifier_seqState values observed in code:
'active' — default after commit_engagement'candidate' — pre-instantiation; written by POST /engagements in api/routers/engagements.py:257'addressed' — appears on findings, not engagements (engagement/creation.py:575)Engagement.state literal (engagement/types.py:61): ("active", "suspended", "archived") — does not include 'candidate' despite candidate-state rows existing in the table.Visibility values observed:
'private' — default'automatic' — Loomworks commons (mig 0034)'personal' — per-person personal engagements (mig 0058)'discoverable' — anticipated by mig 0030 docstring; never usedDisplay identifier format:
E#### zero-padded, 4 digits, starting at E0001 — sequence-backed via PostgreSQL sequenceE1-E999 reserved for foundational engagements, regular allocation starting at E1000)'E' || lpad(nextval(...)::text, 4, '0') enforces the four-digit paddingObservations:
state is VARCHAR(16) with no DB CHECK constraint; the Pydantic Engagement.state literal admits ("active", "suspended", "archived") but raw SQL in api/routers/engagements.py:257 inserts 'candidate'. The Pydantic enumeration does not model the wire-level set the table actually carries. The "Engagement" MemoryObject .state is a different concept from the engagements.state row column — they share a name but the row column tracks lifecycle (candidate/active/suspended/archived) while the MemoryObject .state tracks the engagement-MemoryObject's own version-chain.visibility is plain TEXT with no DB CHECK. Phase 41 explicitly added the value 'personal' without a constraint change (mig 0057 documents this). The set of in-use values is {'private','automatic','personal'}; 'discoverable' was anticipated but never introduced.next_display_number (per-engagement assertion display number counter) is colocated on the engagement row to share its row-lock with current_engagement_version. This means an engagement row is the lock target for two unrelated monotonic sequences.title cascades through (engagements.title → seed.additional_assertions.engagement_name → "Untitled engagement") because the engagement-name was originally inside the seed and Phase 31 lifted it out. The mig 0037 docstring records this evolution.display_identifier (E####) and the row's UUID id both identify the same engagement. Address resolver (api/address_resolver.py) accepts either form; routing layers internally use UUID. Migration 0065's choice was to backfill in created_at order so E0001 is the oldest engagement.engagements exist in code, one per layer's metadata (persons, contributors, files, api_keys, render_specialists, notifications, orchestration, memory/events). Each declares only the subset of columns it needs as FK target. The stubs are slightly different across layers — see §15.
engagements.candidate_seed_id was introduced at 0007 for the HTTP engagement-creation flow when the eid in the URL pre-dates instantiation. The migration docstring is explicit that this is "purely the HTTP-layer handle" and that "Engagement memory objects continue to carry seed_ref as the authoritative pointer after instantiation."
Observations:
candidate_seed_id is nullable; after instantiation it goes back to NULL (or stays nullable; both states observed). The authoritative seed pointer is the MemoryRef in the Engagement MemoryObject's payload. Two pointer-styles for the same relationship coexist (FK column + MemoryRef in JSONB), with the FK being transient.
Phase 1-11 established the substrate's "everything is a MemoryObject in memory_events, projected into one generic and several typed views" pattern. This is the dominant architectural commitment of the engine.
What it is: The canonical append-only event log. Every MemoryObject version is a row. Renamed from substrate_events in Phase 2 (mig 0003).
Introduced: 0001 (as substrate_events); renamed 0003.
Columns:
_foray blockIndexes: by (object_id, object_version), by (engagement_id, timestamp), by (engagement_id, engagement_version), by (engagement_id, object_type). Plus the two UNIQUE constraints.
Observations:
actor_kind is VARCHAR(16) declared at Phase 1; it now admits four values via the CHECK widening at mig 0039 (added 'person') and mig 0057 (added 'companion'). The longest value 'contributor' is 11 chars, so VARCHAR(16) holds. The ORM model in memory/events.py:96 declares the CHECK constraint as ('contributor', 'agent', 'person') — the Phase 16 state — and is out of sync with migration 0057's widening to include 'companion'. Functionally harmless (the DB accepts a superset of what the ORM declares), but the ORM mapping is documentation-in-data and it's currently lying about Phase 41's widening.event_kind is VARCHAR(64) and is free-text — no DB CHECK at any point. The set of in-use event_kind values has grown to roughly 70 (counted by the _ANCHOR_PRIORITY dict plus event-kind constants across types.py plus inline-string event_kind in writers). The whitelist that exists is the _MANIFESTATION_UPSERTING_KINDS frozenset in the projector and similar typed-projection filters; nothing comprehensive.object_type is VARCHAR(64) and is also free-text. The registry OBJECT_TYPE_REGISTRY in memory/registry.py has 16+ registered types. Like event_kind, the DB does not enforce a closed set.content_hash is computed and written on every event (compute_content_hash in memory/events.py:168). No source-code path reads it. It is documentation-in-data — exactly as the mig 0047 docstring anticipated. Same for foray_tx_ref: declared but never written.attestation IS read/written (engagement/commit_orchestration.py:128 writes it after WebAuthn verification on engagement-commit). It is the only one of the three Phase-25 FORAY-readiness columns that is fully wired. See §4._foray JSONB sub-block is injected into payloads for event kinds listed in _ANCHOR_PRIORITY (engagement_committed=critical, membership_created=high, seed_drafted=standard, …). The sub-block is excluded from content_hash computation to avoid circular reference. No downstream consumer reads it.
What it is: Generic materialized projection of the current version of every MemoryObject. Upserted by append_event inside the same transaction.
Introduced: 0002 (as current_substrate_objects); renamed 0003.
Columns: engagement_id, object_id, object_type, current_version, payload (JSONB), last_updated_at. Composite PK (engagement_id, object_id).
Observations:
memory_events (the "rebuild-from-TRUNCATE" pattern noted in multiple migration docstrings).
All five tables below are projector-maintained, rebuildable from memory_events, share the same architectural pattern (Path C), and project a subset of MemoryObject types into scalar columns for queryability.
| Table | Migration | Projects | Distinct shape note |
|---|---|---|---|
| shape_events_view | 0018 (extended 0026, 0042, 0044, 0054) | ShapeEvent | adds back-references to Manifestation + Shaping (0026); produced_at (0042); render_specialist_display_name + triggered_by_display_name on render_events_view (0044); display_number (0054) |
| render_events_view | 0022 | RenderEvent | content_kind discriminator + storage columns (0053); display_number + prior_render_ref + revision_strategy (0054) |
| manifestation_view | 0026 | Manifestation | state ('current' \| 'superseded') |
| external_production_records_view | 0051 | ExternalProductionRecord | extends with composition_object_id + composition_step_index (0056); render_job_id nullable after 0056 |
| render_compositions_view | 0056 | RenderComposition | state ('initiated' \| 'step_in_progress' \| 'waiting_for_operator_decision' \| 'completed' \| 'failed') |
Observations:
state column with its own enumeration. None of these enumerations have DB-level CHECK constraints; they're all maintained by Pydantic Literals at the model boundary. There are at least seven state enumerations in the substrate (Engagement, Assertion, ShapeEvent, RenderEvent, Manifestation, RenderComposition, RenderComposition step) using overlapping vocabularies (held / pending_confirmation / confirmed / amended_superseded / retired / produced / invalidated / current / superseded / initiated / step_in_progress / ...).external_production_records_view.render_job_id was NOT NULL at first, then made nullable in mig 0056 when composition-step linkage was added. The migration installed a CHECK constraint that exactly one of render_job_id or composition_object_id is non-null. This is the "one row owned by either A or B" disjoint-FK pattern.render_events_view.content_kind has values {inline_dict, binary_blob, external_reference, multi_file} (mig 0053). The migration is explicit about column-width: VARCHAR(32) chosen for "explicit width discipline" despite the surrounding render_format / state being plain TEXT.current_memory_objects (generic, no scalar columns). The Path-C pattern is the dominant projection style in the engine.00000000-0000-0000-0000-000000000001 — administrative (Phase 2). Seeds live here.00000000-0000-0000-0000-000000000002 — Loomworks commons (Phase 15, mig 0034).00000000-0000-0000-0000-0000000000e2 — E2E test sandbox (Phase 21, mig 0043).00000000-0000-0000-0000-0000000000a1 (operator-preapproval-agent for single-cycle inductions).These are deterministic UUIDs; downgrades target them precisely.
Three tables (or column groups) carry the FORAY name or concept. They were introduced at three different phases for three different purposes and have not been unified.
credit.foray_action_flows (value flows)
What it is: Append-only ledger of every credit movement. The substrate's value-flow log. Trigger-maintained materialized balance via credit.asset_balances.
Introduced: 0062_phase_47_credit_substrate. Created in its own credit schema.
Columns:
loomworks_credit_haiku, anthropic_haiku_4_input, loomworks_account_status)'dunin7', 'anthropic') for institutional partiesflow_metadata because SQLAlchemy reserves metadata
Trigger: credit.update_balance_on_flow() — AFTER INSERT, UPSERTs both sides into credit.asset_balances.
Companion table: credit.asset_balances — materialized; never written directly by application code.
Read paths: credit/balance.py (sum / check), credit/reconciliation_evaluator.py (drift detection), credit/proposal_applier.py (correction inserts).
Write paths (in credit/flows.py): write_issuance_flow, write_consumption_flows (5 rows per turn), write_suspension_flow, write_reactivation_flow, write_deletion_flow, write_balance_zeroing_flows, write_referral_credit_flow.
Observations:
from_party / to_party is VARCHAR(128) because the substrate has both UUID-typed parties (persons) and string-named institutional parties ('dunin7', 'anthropic'). This is a deliberate polymorphic-by-string choice — the migration docstring is explicit about it.loomworks_account_status and quantity=1. The FORAY substrate is doing double duty as both a value ledger and an account-lifecycle audit log.transaction_id groups multi-row writes (consumption is 5 rows: classifier input, classifier output, responder input, responder output, credit debit). This pattern doesn't exist in surface 2 or 3.audit.foray_events (narrative events)
What it is: Append-only narrative-event log. State changes that need an audit record but don't move value. v0.1 ships one event_type: setting_change written from the tune_setting handler.
Introduced: 0068_audit_foray_events. Created in its own audit schema, deliberately distinct from credit.
Columns:
'setting_change' only
Write path: audit/events.py:write_setting_change_event, called from orchestration/tune_setting.py. Best-effort discipline: failures are swallowed.
Observations:
role_change, identity_change, workspace_operation) but the substrate ships with exactly one consumer.actor_person_id is UUID-typed and NOT NULL — narrative events are always person-initiated, unlike credit flows where institutional parties ('dunin7') participate. This is a structural divergence from surface 1.signature (BYTEA) lives on the row for future Kaspa anchoring. It is never written today. Like Surface 3, parts of the FORAY substrate are wired ahead of integration.memory_events.{content_hash, attestation, foray_tx_ref} + _foray payload sub-block (readiness wiring)What it is: Phase 25 readiness wiring on the canonical event log. Three columns + an in-payload sub-block. Two of the three are dead today.
Introduced: 0047_phase_25_engagement_creation_ui.
Columns / fields:
content_hash TEXT — SHA-256 of canonicalized payload sans _foray sub-block. Written on every event in memory/events.py. Not read by any source-code path.attestation JSONB — WebAuthn assertion for engagement-committed events. Written by engagement/commit_orchestration.py:128 after WebAuthn verification. This one is wired.foray_tx_ref TEXT — reserved for the FORAY anchoring transaction id. Never written. Migration 0047 docstring is explicit: "FORAY integration itself is deferred."_foray JSONB sub-block inside payload — injected by append_event for event kinds in _ANCHOR_PRIORITY (currently 22 event kinds). Carries {anchorable, anchor_priority, content_hash, attestation_ref}. Not read.Observations:
content_hash is duplicated in two places — the DB column on memory_events and the in-payload _foray.content_hash field. The migration's comment says the DB column is the source of truth and the in-payload copy is for "downstream consumers that walk payloads directly." Neither is read today._foray is selectively injected per event-kind based on _ANCHOR_PRIORITY. The priority levels (critical, high, standard, low) are documentation in data — future-batching guidance encoded in events that don't currently get batched.All three surfaces share four conceptual elements:
transaction_id on credit, tx_id on audit, foray_tx_ref on memory_events. Three different column names. Two are UUID-typed, one is TEXT.from_party / to_party VARCHAR(128); audit uses UUID-typed actor_person_id; memory_events carries actor_id + actor_kind (its own taxonomy of contributor/agent/person/companion).metadata (JSONB), audit calls it payload (JSONB), memory_events has both payload and the _foray sub-block.credit, audit, public (memory_events).loomworks_account_status asset shows that lifecycle and value share the same table by convention; audit's setting_change shows that lifecycle would also be at home in audit. The boundary between "value movement" and "narrative state change" is editorial, not structural.If pursued, unification would have to settle:
transaction_id / tx_id / foray_tx_ref); three storage types (UUID / UUID / TEXT).credit / audit / public) is consistent with the layered metadata pattern but means cross-FORAY queries need joins across schemas.
The FORAY substrate today is best read as "three sibling surfaces that share a grammar but not a shape." The grammar is (transaction, asset-or-event-type, actor, payload, timestamp). The shapes are concrete, and currently divergent.
The credit substrate landed across three migrations: Phase 47 (mig 0062, foundation), Phase 48 (mig 0063, evaluators + SMTP), Phase 49 (mig 0064, persons lifecycle columns).
Covered in §4 Surface 1.
What it is: One row per claim_token. The grant lifecycle anchor.
Introduced: 0062.
Columns: id, claim_token UNIQUE, recipient_email (nullable post-claim), recipient_email_hash, grant_kind CHECK in (form_initiated, operator_curated, referrer_initiated), asset_id, credit_amount, initiated_by, campaign_ref, metadata (Python: grant_metadata), status CHECK in (pending_claim, claimed, expired, revoked), expires_at, claimed_at, claimed_by_person_id (DB FK; ORM-less), created_at.
Observations:
recipient_email is nullable post-claim; the plain text is dropped after claim, leaving only recipient_email_hash as the abuse-boundary memory.claimed_by_person_id has a DB-level FK (created by migration 0062) but no ORM-level FK — the ORM column declares no ForeignKey(). The mig and model comments are explicit about keeping the credit metadata independent of the persons metadata at mapping-resolution time.What it is: Per-email-hash issuance ledger for eligibility decisions.
Introduced: 0062.
Columns: email_hash (PK), email_normalized_hash (parallel hash over Gmail-aliased form), first_grant_at, last_grant_at, total_grants_issued, last_grant_status CHECK (pending_claim/claimed/expired/deleted), last_status_at.
Observations:
What it is: Per (credit_asset, provider_token_asset) conversion rate. rate_per_million credits debited per 1,000,000 provider tokens.
Introduced: 0062. Seeded with 10 (credit_asset, token_asset) pairs.
Observations:
effective_at and superseded_at are present so rates can be amended without losing history; supersession is reserved for a future change.What it is: Persistent state for the Phase 48 evaluators (suspension/deletion + reconciliation). Per-evaluator typed shape.
Introduced: 0063.
Observations:
credit schema rather than system_config because per-evaluator state is multi-column typed (timestamps that have to be compared) and a flat KV store would be awkward.
What it is: One row per send_email invocation. Observability and deduplication for deletion-warning emails.
Introduced: 0063. ORM in loomworks/email/models.py.
Observations:
credit or audit) despite being introduced in a credit-flow CR. The email module is its own layer with its own ORM Base.See §1.persons for the full column list. Migrations 0062 + 0064 added eleven columns covering tier, account_status (with CHECK), referral, expiration, near-exhaustion thresholds, exhaustion preference, and previous responder model. Two extensions of the same row by two phases.
Observations:
'active' | 'exhausted' | 'suspended' | 'deleted') is one of the few DB-enforced enumerations in the substrate. Most state-like values are application-validated only.near_exhaustion_{20,10,5}pct_seen_at columns model first-cross timestamps for three thresholds. A future change could turn this into a child table (near_exhaustion_crossings(person_id, threshold_pct, seen_at)) if more thresholds are needed; today it's three columns.
What it is: General-purpose per-person/per-engagement Companion conversation log. Distinct from Phase 31's seed-conversation events (which live on the candidate engagement's memory_events log under operator_turn / companion_turn event kinds — see Observations).
Introduced: 0059_phase_42_conversation_turns. Extended 0069 (input_mode), 0070 (completeness_check_prefix).
Columns: id, person_id (FK), engagement_id (FK, nullable), role TEXT ('operator' | 'companion'), content TEXT, classified_intent TEXT (nullable — only on companion turns), input_mode TEXT NOT NULL DEFAULT 'text' CHECK in ('text','voice'), completeness_check_prefix BOOLEAN NOT NULL DEFAULT FALSE, created_at.
Indexes: (person_id, engagement_id, created_at DESC).
Intent labels (Pydantic Literal in orchestration/classifier.py:47, ~15 values):
Observations:
conversation_turns (this table, Phase 42) is for general Companion conversation outside the seed-creation lifecycle. Phase 31 conversation events live on memory_events as operator_turn / companion_turn event kinds on a candidate engagement's event log. The two systems were designed to coexist; the migration 0059 docstring is explicit about it.input_mode is one of the few DB-enforced text enumerations in the substrate (CHECK constraint at mig 0069). Most other Literal-set columns are validated at the Pydantic boundary only.role is plain TEXT with no CHECK; the Pydantic Role = Literal["operator", "companion"] is the enforcement surface. The membership-derived "designation" enum (operator/contributor/domain_expert) is a different concept that happens to share the word "operator."completeness_check_prefix (added mig 0070) is a recursion-guard marker for the voice-listening silence-submit feature. It is the second example (with attestation) of the substrate carrying a "the system did something specific to this row" boolean.
The IntentLabel literal in orchestration/classifier.py:47 is the canonical list. The handlers that consume each intent live in orchestration/router.py. The intent list has grown across phases — most recently save_filter (engagement-nav phase two), tune_setting (voice listening), create_engagement_* cluster (Phase 55).
Observations:
What it is: One row per proactive Companion notification. A single row carries either an informational notification (Phase 44) or an approval card (Phase 45). The discriminator at projection time is whether approval_status is non-null.
Introduced: 0060_phase_44_companion_notifications. Extended 0061_phase_45_approval_card_columns.
Columns:
Indexes: (person_id, status, created_at DESC); (person_id, trigger_ref).
Observations:
approval_status is null. Phase 45 extended the Phase 44 table rather than introduce a separate approval_cards table. The trade-off is one less table to query for the dashboard list, at the cost of a row with 17 columns of which 6 are nullable-by-shape.approval_status IS NOT NULL, not a typed column. A future row shape (three kinds) would need either a kind discriminator column or a join-by-kind split.trigger_type and status are plain TEXT with no DB CHECK; same pattern as conversation_turns.role. The Pydantic Literal is the enforcement surface.
What it is: Per-Operator preference store, keyed by flat dotted-namespace string (e.g., voice.listening.blur_intensity).
Introduced: 0067_voice_person_settings.
Columns: person_id (FK CASCADE), setting_key TEXT, setting_value TEXT (JSON-encoded), updated_at. Composite PK.
Observations:
credentials (scoped), engagement_api_keys (per-engagement), system_config (system-wide). Each was introduced at a different phase for a different actor scope. They are not unified.setting_value is TEXT containing JSON. The application encodes/decodes; callers see typed Python values. This is the same pattern system_config uses for non-Fernet values (where applicable).audit.foray_events setting_change events — one writes the current state, the other writes the transition. The pair is value-now vs value-then.What it is: System-wide Fernet-encrypted KV store. Used for Loomworks-managed LLM keys, evaluator cadence keys, SMTP credentials, etc.
Introduced: 0048_phase_31_system_config. Heavily seeded by mig 0062 (9 keys), 0063 (13 keys), 0064 (3 keys).
Columns: id, config_key UNIQUE, encrypted_value TEXT (Fernet ciphertext), label, created_at, updated_at.
Observations:
5, 30, 21) because the Fernet round-trip is the same code path for keys and tunables. The label column is the only human-readable description.Covered in §4 Surface 2.
Observations beyond §4:
audit schema today has exactly one table. Its existence signals intent to be a sibling of the credit schema for non-value events. The CR docstring mentions future event types (role_change, identity_change, workspace_operation) — none are implemented.migration_events (§1) is the only other audit-shaped table in the substrate but lives in public schema and predates the audit schema convention.What it is: Operator-scoped engagement-grouping primitives backing the Phase-two navigation destinations.
Introduced: 0066_engagement_navigation_phase_two_substrate.
workspaces: id, owner_person_id (FK CASCADE), name, created_at, archived_at. UNIQUE(owner_person_id, name) WHERE archived_at IS NULL.
engagement_workspace: workspace_id (FK CASCADE), engagement_id (FK CASCADE), added_at. Composite PK.
engagement_tags: engagement_id (FK CASCADE), tag_value TEXT, added_at, added_by_person_id (FK). Composite PK (engagement_id, tag_value).
saved_filters: id, owner_person_id (FK CASCADE), name, criteria JSONB ({"all_of": [<predicate>, ...]}), created_at, archived_at, is_system_defined BOOLEAN. UNIQUE(owner_person_id, name) WHERE archived_at IS NULL.
Observations:
create_person calls seed_system_defined_filters), not via trigger. The migration docstring records the deliberate trigger-removal discipline.engagement_api_keys (per-engagement, per-service) and system_config (system).tag_value is free-form TEXT with no taxonomy table, no case-folding, no length cap beyond TEXT. The Phase-two CR's Q5 settlement.
These are all non-Memory operational state — mutable rows, no provenance, no version chain. The pattern is {job_id, engagement_id, status enum, started_at, completed_at, agent identity, optional result column or ref}. Eight tables follow the pattern.
| Table | Migration | Purpose | Status enum |
|---|---|---|---|
| deferral_queue | 0008 | Phase 4 boundary-deferral workflow | (no status column; uses resolved_by_record_id IS NULL) |
| retrieval_jobs | 0008 | Phase 4 RetrievalAgent dispatch | pending / running / complete / failed |
| summarization_jobs | 0009 | Phase 5 SummarizationAgent | pending / running / complete / failed |
| drift_detection_jobs | 0009 | Phase 5 DriftDetectionAgent (CHECK dropped 0016; drift_result_id added 0011) | pending / running / complete / failed |
| cadence_firings | 0012 | Phase 7 scheduler firing attempts (close_time 0014) | opening / opened / failed |
| seed_cadence_synthesis_jobs | 0013 | Phase 7 synthesis text production | pending / running / complete / failed |
| shaping_jobs | 0018 | Phase 9 ShapingAgent dispatch | queued / dispatched / completed / failed |
| render_jobs | 0022 | Phase 10 RenderDispatchAgent (status widened 0052) | queued / dispatched / awaiting_external / completed / failed |
Observations:
agent_actor_id + agent_instruction_version (pinning R-A24 — the agent's instruction-set version at dispatch time). This is one piece of consistency across the eight tables.drift_detection_jobs is the table that has been most heavily revised — mig 0009 introduced it, mig 0011 dropped drift_detected/drift_rationale in favour of drift_result_id (DriftResult-as-MemoryObject), mig 0016 dropped the triggering_action CHECK to widen the open vocabulary. Three migrations across two phases, all driven by the same "store the outcome as a MemoryObject not on the job row" trajectory.What it is: Operator-facing label per ShapeEvent. Keyed by the ShapeEvent's object id.
Introduced: 0050.
Columns: id, engagement_id (FK), shape_event_object_id (UUID, unique), title TEXT, updated_at.
Observations:
engagements.title (which sits on the engagements row). The two patterns are different — engagement titles live on the engagement row; shape titles live in a sibling table. The migration 0050 docstring is explicit about following the engagements.title pattern, but the placement diverges.
What it is: Durable per-(engagement, declared_render_type) specialist identity. Read by the lifespan bootstrap to register RenderSpecialist instances at startup.
Introduced: 0045. DRT version pin added 0046.
Observations:
Covered in §1 and §8. Three KV-shape secret/configuration tables, separately introduced.
What it is: Operational table for binary file storage. Bytes live on disk under data/files/{engagement_id}/; the table is for retrieval and metadata.
Introduced: 0038.
Observations:
metadata.source_file_id (JSONB) rather than via FK. The migration docstring is explicit: "The file table itself is for retrieval, not provenance."The substrate has three engagement IDs with fixed, deterministic UUIDs.
| UUID | Name | Introduced | Purpose |
|---|---|---|---|
| 00000000-0000-0000-0000-000000000001 | administrative | Phase 2 | Seeds live here; admin actions emit events against this engagement |
| 00000000-0000-0000-0000-000000000002 | Loomworks commons | mig 0034 (Phase 15) | The universal commons; visibility='automatic' |
| 00000000-0000-0000-0000-0000000000e2 | E2E test sandbox | mig 0043 (Phase 21) | Hosts Playwright mutation traffic; visibility='private' |
Plus the per-person Personal engagement (UUID is per-person, created at signup; mig 0058 backfills for existing persons; visibility='personal').
Observations:
01, 02, e2) is a small mnemonic but otherwise the IDs are opaque.OPERATOR_PREAPPROVAL_AGENT_ID = '...00a1') for its single-cycle convergent induction. The Phase 41 migration 0058 docstring is explicit about deliberately placing the Seed + InductionCycle on the personal engagement itself, not on ADMIN — a departure from the Phase 2 "Seeds live on ADMIN" convention because personal engagements are self-contained.Some patterns of the migration history itself are worth surfacing as the architecture of how the substrate evolved.
Migrations 0010, 0019, 0020, 0023, 0024, 0027 are no-op DDL migrations. They exist to keep the Alembic chain walkable as a lineage record when a Pydantic Literal vocabulary was extended without a corresponding DB change. The convention was introduced at 0010 and recurs roughly once per phase that adds a vocabulary term.
Observation:
pass. This is intentional discipline (per the docstrings) but means the migration count overstates the schema-change count.
Migrations 0034 (Loomworks induction), 0035 (founding memory), 0036 (Loomworks membership backfill), 0037 (seed v2 amendment), 0041 (display number backfill), 0043 (E2E sandbox), 0055 (specification_grammar backfill), 0058 (personal engagement induction) are data migrations — they write content to memory_events / engagements / current_memory_objects rather than altering schema.
Observation:
append_event and apply_event_to_view inline (with their own _append_event_sync helpers) so the migration is self-contained against future application-code evolution. Migration 0034's docstring is explicit about this trade-off. The duplication risk is that the inline helper drifts from the canonical writer.Most phases bundle their schema changes into one migration. Phase 16 explicitly split into mig 0038 (uploaded_files), 0039 (actor_kind widening), 0040 (engagement_api_keys), 0041 (assertion display number) — four migrations for one phase. The mig 0040 docstring is explicit about lateness ("the addendum was written after 0038/0039 landed").
Observation:
These are observations that don't fit a single conceptual area. They are not graded.
At least six different layers each declare their own _EngagementRow stub mapping in their respective models.py files (persons, contributors, files, api_keys, render_specialists, notifications, orchestration). The authoritative mapping is in memory/events.py:EngagementRow. Each stub declares a subset of the engagements row's columns depending on what the FK target actually needs to know. None of the stubs declare all 10 current columns of the engagements table.
| Layer | Declares | Missing | |---|---|---| | memory/events.py | id, created_at, current_engagement_version, state, candidate_seed_id | visibility, next_display_number, created_by_person_id, title, display_identifier | | persons/models.py | id, created_at, current_engagement_version, state, candidate_seed_id, visibility | next_display_number, created_by_person_id, title, display_identifier | | contributors/models.py | id, created_at, current_engagement_version, state, candidate_seed_id | 5 | | notifications/models.py | id only | 9 | | orchestration/models.py | id only | 9 | | (others similar) | | |
Observation: the ORM stubs are documentation-in-data of what each layer thinks it needs from engagements. They are silently drifting from the actual table shape; nothing enforces consistency. The authoritative writer (append_event in memory/events.py) is also one of the stubs.
The DB column engagements.state is VARCHAR(16) with no CHECK. Application code inserts 'candidate' (api/routers/engagements.py:257) but the Pydantic Engagement.state literal at engagement/types.py:61 admits only ("active", "suspended", "archived"). Two enumerations, one column, neither fully describes the in-use value set.
In-use: 'private', 'automatic', 'personal'. Anticipated but never used: 'discoverable' (mentioned in mig 0030 docstring). No DB CHECK; no Pydantic Literal centralising the set; readers branch on string comparisons (dashboard.py filters on visibility != 'personal').
memory_events.actor_kind ORM is behind migration
ORM MemoryEventRow.__table_args__ declares the CHECK as ('contributor', 'agent', 'person') (Phase 16 state). Migration 0057 widened the DB CHECK to also admit 'companion' (Phase 41). The DB and ORM disagree on the constraint value set.
memory_events.foray_tx_ref and content_hash are written-or-not-readcontent_hash: written every event, not read anywhere.foray_tx_ref: not written, not read.attestation: written by engagement-commit + WebAuthn path; read by callers checking attestation presence._foray payload sub-block: injected for 22 anchorable event kinds, not read.The Phase-25 FORAY readiness wiring is documentation-in-data for an integration that hasn't landed. Three of four surfaces have no consumers.
credentials (scope-keyed, Phase 2), engagement_api_keys (per-engagement, Phase 16), system_config (system, Phase 31), person_settings (per-person, Phase 47). Each was introduced at a different phase for a different actor scope. Each uses its own column convention (scope_id / engagement_id / config_key+global / person_id+key). They are not unified.
credit.foray_action_flows.from_party / to_party — VARCHAR(128) — polymorphic; UUID strings for persons, literal strings for institutional parties.audit.foray_events.actor_person_id — UUID NOT NULL — single actor kind.memory_events.actor_id + actor_kind — UUID + discriminator — typed-union encoding.Three actor conventions in three substrate surfaces.
Eight operational/job tables (§11) use four distinct status vocabularies (pending/running/complete/failed; queued/dispatched/completed/failed; opening/opened/failed; pending/running/complete/failed with awaiting_external). No common base.
Engagements are addressable by UUID (the canonical id) and by display_identifier (E####). The address resolver accepts either; the API uses UUID internally; URLs may carry either. Two identifier spaces for one entity.
companion_notifications carries two row shapes
One table accommodates informational notifications (Phase 44) and approval cards (Phase 45) by discriminating on whether approval_status is non-null. 17 columns, of which 6 are nullable-by-shape rather than nullable-by-data.
At least seven state columns across MemoryObject types and projection views use overlapping verbs (active / retired / confirmed / superseded / current / produced / held / committed / addressed / ...). The vocabularies are not contradictory but not coherent either — 'current' on Manifestation is the same concept as 'confirmed' on ShapeEvent (the live version), under different names.
conversation_turns (Phase 42, the general-purpose log) and the Phase 31 operator_turn / companion_turn event-kinds on the candidate engagement's memory_events. Both record conversations between an Operator and the Companion. Different storage layer, different lifecycle, deliberately not unified.
migration_events is a fourth audit-shape table
In addition to the three FORAY surfaces (§4), migration_events (§1) is a fifth-shape table — append-only, event_type discriminator, JSONB payload, no actor column, no transaction id. Five tables in the substrate carry the audit-log shape (event_type, payload, timestamp [+ actor]) with five different concrete shapes.
0001 substrate_events → 0003 rename → 0027 Phase 11 → 0046 Phase 22 specialist binding DRT version → 0061 Phase 45 approval cards → 0064 Phase 49 lifecycle columns → 0065-0070 engagement-navigation phases + voice + audit + voice provenance + completeness check prefix.
Empty-DDL migrations (lineage only): 0006, 0010, 0017, 0019, 0020, 0023, 0024, 0025, 0027.
Heavy data migrations: 0015, 0034, 0035, 0036, 0037, 0041, 0043, 0055, 0058, 0062 (seeds), 0063 (seeds), 0064 (seeds), 0065 (display_identifier backfill), 0066 (system-defined filter backfill).
Pure CHECK / constraint changes: 0016, 0039, 0052, 0057.
View / projection introductions: 0002 (current_memory_objects), 0018 (shape_events_view + shaping_jobs), 0022 (render_events_view + render_jobs), 0026 (manifestation_view), 0051 (external_production_records_view), 0056 (render_compositions_view).
End of inventory.