You asked for a professional opinion on the substrate after reading the inventory. You said: "Make sure our database is solid, no duplicate elements, ensure we have a consistent and obvious entity model. Be realistic, we are in this together."
This is that review. It's not an audit and not a recommendation list — it's an honest read of what's there, what's good, what's drifting, and where I think the leverage is highest right now while data is still wipeable. Where I've made judgment calls, I've named them.
The visual treatments mark different kinds of finding: things working well that we should protect, things drifting that should be cleaned up, things that warrant active fix, and architectural shifts that deserve their own scoping.
The foundation is not broken. The core architectural commitment — memory_events as a canonical append-only event log with typed projections — is exactly right, and it's been protected consistently across 70 migrations. That's the strongest thing I can say about a codebase: the load-bearing decision was made well, and the discipline to honor it didn't slip.
What's drifted is around the core, not inside it. Surfaces that share a conceptual grammar but ended up with different shapes (FORAY's three siblings, four KV stores, eight job tables with four status vocabularies). Documentation-in-data placeholders that never got their consumer. Discipline gaps at the database boundary where state columns lack constraints and ORM stubs drift from migrations.
None of this is catastrophic, but the compounding cost grows with time and data. The window to address it cleanly is now, while you can wipe and recreate without operational loss. After real data accumulates, every cleanup becomes a migration with backward-compatibility cost.
My professional advice: do two architectural pieces of work now, defer the others. Specifically, unify FORAY into one substrate, and tighten the database-level type discipline so the substrate enforces what the application currently validates. The other items (KV store consolidation, job table base, identifier scheme) are smaller cleanups that can ride along or wait.
Before naming what to fix, name what's right. Three things stand out as load-bearing successes that should not be disturbed by any subsequent refactor.
Every change to every MemoryObject is a row in memory_events. Typed views (Assertion, ShapeEvent, RenderEvent, Manifestation, RenderComposition) project subsets into scalar columns for queryability, but the event log is the source of truth and everything else is rebuildable from it. This is the strongest design coherence in the substrate, and it's why "knowledge accumulates and never disappears" works as more than a slogan.
Whatever else changes, this stays. The pattern is correct.
Engagements are the spine of the substrate. Persons get memberships to them; events scope to them; the dashboard aggregates across them; the Companion participates within them. The decision to make Engagement the universal adapter (your phrase) has held up across 70 migrations.
The Engagement's table itself has accumulated cruft (more on that below), but the conceptual centrality of "engagement is the unit" is exactly right.
Nine empty-DDL migrations exist purely as lineage records when a Pydantic vocabulary was extended without a schema change. Heavy data migrations duplicate slices of append_event inline to stay self-contained against future code evolution. This is intentional discipline — the migration chain is documentation, not just schema state.
This pattern is unusual and correct. It means the substrate's history is interrogable. Don't lose it in any cleanup.
The honest framing of this section: you've protected the right things. The drift below is around these decisions, not inside them. That's a meaningfully better starting position than the alternative (a wrong core surrounded by surface cleanup).
The inventory surfaced 13 cross-cutting findings, plus dozens of inline observations across 15 conceptual areas. Rather than walk all of them, I'll group by category — drift looks different depending on its kind, and the right response depends on the category.
This is the most visible pattern. Multiple tables solve conceptually similar problems with structurally different shapes. The pattern recurs across at least four substrate areas.
The inventory makes this concrete. Surface 1 (credit.foray_action_flows) carries (asset_id, quantity, two polymorphic parties) with a balance-update trigger. Surface 2 (audit.foray_events) carries (event_type, person actor, free payload) with no trigger. Surface 3 (memory_events readiness columns) is mostly dormant — content_hash written but never read, foray_tx_ref never written, _foray sub-block injected for 22 event-kinds but never consumed.
This isn't an accident. Each surface was introduced at a different phase for a different problem (Phase 47 credit, Phase 50 audit, Phase 25 readiness). They each work in isolation. But they're solving the same conceptual problem — "record that a thing happened with provenance" — and the three shapes don't speak to each other.
This is the most important architectural piece to address. Recommendation in Section 4.
credentials (scope-keyed, Phase 2), engagement_api_keys (per-engagement, Phase 16), system_config (system-wide, 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. They're not unified.
The natural unification is one KV substrate with a polymorphic scope (system / engagement / person), Fernet-or-plain value storage by content type, and a key catalog table for documentation. Smaller architectural piece than FORAY but the same shape of problem.
Every operational agent got its own jobs table. Same broad shape (job_id, engagement_id, status enum, started_at, completed_at, agent identity), but no shared base and four different status enums: pending/running/complete/failed, queued/dispatched/completed/failed, opening/opened/failed, etc.
This is less urgent than FORAY because each job table works in isolation and the cost of inconsistency is mostly documentation, not behavior. But the pattern is the same: parallel solutions to one conceptual problem. Worth a unification pass at some point, lower priority than FORAY.
conversation_turns (Phase 42, general-purpose) and Phase 31's operator_turn/companion_turn event-kinds on memory_events. Both record Operator-Companion conversations. Different storage layers, different lifecycles.
The migration docstring is explicit that this is intentional ("the two systems were designed to coexist"). I'd push back gently on that. Coexistence is fine; two storage layers means two query paths, two consumer locations, two places to look when something is missing. If the candidate-engagement conversations could move to conversation_turns at signup completion, one storage layer would suffice.
Not urgent. Worth revisiting when the second editor surface or the multi-Operator question opens.
The Phase 25 FORAY readiness wiring is the clearest example. The substrate is carrying placeholders for an integration that hasn't landed. Some of those placeholders are documentation-in-data (intentional), some are dead code (just inertia).
Of the three Phase 25 columns: attestation is read and written (engagement-commit WebAuthn flow). content_hash is computed and written on every event but no source-code path reads it. foray_tx_ref is never written.
Plus the _foray JSONB sub-block injected into payloads for 22 event-kinds, never read.
This was intentional readiness — the migration docstrings are explicit that FORAY integration was deferred. But "intentionally deferred" five phases ago is different from "intentionally deferred indefinitely." Decision needed: either land the consumer (Phase 63's audit wiring + viewer is partial progress on this), or recognize the readiness as design pollution and remove it.
The right answer depends on the FORAY unification decision below. If FORAY unifies into one substrate, the readiness wiring on memory_events probably gets replaced rather than fulfilled.
This is the most fixable category and also the smallest individually but most numerous in aggregate. The substrate validates many invariants at the Pydantic boundary (application layer) that the database itself doesn't enforce. Each instance is small; the aggregate effect is that the database accepts more state than the application thinks it does.
The biggest single example: engagements.state is VARCHAR(16) with no DB CHECK. Application code inserts 'candidate' but the Pydantic Engagement.state Literal admits only (active, suspended, archived). Two enumerations, one column, neither describes the in-use set.
This pattern recurs across at least seven state columns (Engagement, Assertion, ShapeEvent, RenderEvent, Manifestation, RenderComposition, conversation_turns.role). The DB accepts a superset of what the application validates. Discipline gap.
Fix: add DB CHECK constraints to match the actual in-use vocabularies. Also pick one (substrate or Pydantic) as authoritative and make the other follow. Sub-day of work; large discipline improvement.
The engagements table has 10 columns. Six different ORM stub mappings declare different subsets across layers. memory_events.actor_kind ORM declares the CHECK as (contributor, agent, person) while the DB CHECK (after migration 0057) admits (contributor, agent, person, companion). The DB and the ORM are silently disagreeing.
Fix: one authoritative ORM mapping per table; all consumers import from it. Where stubs are needed for circular-import avoidance, generate them from the authoritative source. Half-day of work.
contributors (Phase 3, engagement-scoped, bearer-token actor with commit_authority) and persons (Phase 14, global identity with WebAuthn). They never merged. The Phase 15 founding-memory migration moved "who's a contributor" to memberships, but contributors is still queried for bearer-token auth at the router level.
The honest read: contributors is a vestigial Phase 3 artifact that should have been retired when persons + memberships landed. Some application code still calls into it; that code should be migrated to person-based auth.
This is partly hygiene, partly small architectural cleanup. Worth doing now, not blocking anything.
Engagements have two identifiers: the UUID (canonical) and the display_identifier (E####). The address resolver accepts either. URLs can carry either. The UUID is the FK target; the display identifier is for humans.
This is fine and probably necessary. UUIDs are unreadable; E#### is unforgettable. But it's a divergence from the rest of the substrate where UUID is the sole identifier. Worth being deliberate about: the dual-key is the convention going forward, or one of the two retires.
The Operator's planned identifier-reservation work (E1-E999 foundational, E1000+ regular) tightens this. Worth landing alongside the reservation work.
Two diagrams. The first shows the substrate as it is — load-bearing entities with their relationships. The second shows what a coherent version of the same picture would look like.
The current picture has clean bones — Person, Engagement, Membership, memory_events as the four spine entities. Around them, the parallel-solutions pattern shows up: three FORAY surfaces, four KV stores, eight job tables, two actor-identity concepts. The vestigial pieces (Contributor table, memory_events readiness columns) are documentation-in-data that hasn't been cleaned up.
The "should be" picture is structurally cleaner but not dramatically different. The core (Person, Engagement, Membership, memory_events) stays exactly the same. The differences:
foray.events table with one shape: (tx_id, event_type, actor, scope, payload, timestamp). Event types discriminate value flows from narrative events from anything else. The credit.foray_action_flows table and audit.foray_events table are both replaced by views or projections of the unified table.settings table with polymorphic scope (system / engagement / person). Replaces credentials, engagement_api_keys, system_config, person_settings.The "should be" picture is what foundation-solid looks like. Same load-bearing decisions, fewer parallel solutions to one conceptual problem, fewer discipline gaps at the database boundary.
I'm being deliberately conservative about what makes this list. Each item is real architectural work; each is justified by the inventory; each can be deferred without catastrophe. The order is by leverage (highest first) and by dependency (some items unblock others).
What it is: Collapse the three FORAY surfaces into one table with one shape. The unified table carries (tx_id, event_type, actor_id, actor_kind, scope, payload, timestamp). Event types discriminate (value_flow, narrative_event, lifecycle_change, etc.). The existing credit.foray_action_flows table becomes a view or projection. The audit.foray_events table merges in. The memory_events readiness columns (foray_tx_ref, _foray sub-block) get a decision: either they're for cross-referencing into the unified FORAY, or they retire.
Why first: Highest leverage. The three-surface situation isn't just untidy — it forces every future audit-shaped feature (Phase 63's audit viewer is a current example) to pick which surface to attach to and accept the resulting fragmentation. Unifying once removes that question permanently.
Why now: The data permission is real. All FORAY data can be wiped without operational loss. After production data accumulates, the migration becomes substantial.
What unblocks: Phase 63's Part 2 and Part 3 (audit wiring and audit viewer) execute against a coherent substrate instead of one of three parallel surfaces. Future audit-shaped work doesn't repeat the parallel-solution pattern.
What it is: A focused discipline pass. (a) Add DB CHECK constraints to every state column that has a Pydantic Literal so the database enforces the same vocabulary the application validates. (b) Eliminate ORM stub proliferation by establishing one authoritative ORM mapping per table; consumers import from it. (c) Reconcile the specific ORM/migration disagreement on memory_events.actor_kind (and any others discovered).
Why second: Smaller than FORAY unification but addresses a different category — discipline at the substrate boundary. Each gap is small individually; the aggregate effect is that the database accepts a meaningfully larger state space than the application thinks it does. Cleaning up is cheap and durable.
Why now: Same logic. Adding CHECK constraints to populated tables requires data cleaning; on test data, it's a one-shot migration.
What it is: The contributors table is a Phase 3 artifact that should have retired when persons + memberships landed in Phase 14. Application code at the router level still queries it for bearer-token auth. The work: identify every code path that uses contributors, migrate to person-based equivalents, drop the table.
Why third: Lower leverage than FORAY unification but removes a category-of-thing problem from the substrate. Two identity concepts become one.
Caveat: Bearer-token auth via contributors is the only non-WebAuthn auth path today. Retiring contributors might require establishing an equivalent person-based bearer-token mechanism. Worth a quick scoping conversation before execution.
What it is: Collapse credentials, engagement_api_keys, system_config, person_settings into one settings table with polymorphic scope. Same pattern shape as FORAY unification but smaller because the four KV tables are simpler than the three FORAY surfaces.
Why fourth: Real cleanup, but the parallel-solution cost is lower than FORAY's (each KV store works in isolation; there's no cross-store query pattern that suffers from fragmentation). Worth doing eventually, no urgency.
What it is: The work you previously named — drop zero-padding (E0001 → E1), reserve E1-E999 for foundational engagements, regular allocation starts at E1000. Small substrate change, voice recognizer regex update, dev data reset.
Why fifth: Lower leverage than the architectural items above, but small and clean. The substrate work is bounded and pairs naturally with creating the Specification engagement at E1.
What it is: Establish a shared base for the eight job tables — common status enum, shared base columns, per-agent specifics in subclass tables or discriminator columns.
Why sixth: The eight job tables work in isolation today. The parallel-solution cost is mostly documentation, not behavior. Worth a unification pass eventually; lower priority than the others.
What I'm not recommending: the two conversation logs, the dual-identifier addressing, the Phase 25 readiness wiring decision in isolation. These all surface in the inventory and they're all real. But they're either (a) intentional designs that work in their current form (dual-identifier addressing is genuinely useful) or (b) blocked by the architectural decisions above (the Phase 25 readiness wiring becomes coherent once FORAY unifies). Tackling them out of order would burn time.
The six priorities above are bounded. They're not the only things one could do. A few places where I'm choosing conservatism over ambition, with the reasoning surfaced.
Some substrate reviews end with "rebuild from scratch." This one doesn't, because the core architectural decisions are right. Building a new substrate would be six months of work to arrive at roughly the same place architecturally, minus the migration history that documents how you got there. Bad trade.
The right move is targeted refactor against a substrate whose foundations are already correct. That's what the six priorities above are.
Things like "engagements.state" carrying lifecycle states while the Engagement MemoryObject's own .state field carries version-chain state — same word, different concepts — are real terminology drift. The substrate vocabulary has accumulated overlapping terms across phases. A vocabulary-cleanup pass is justifiable.
I'm not making this a priority because it's mostly cosmetic. The overlap is documentation; the behaviors are correct. Worth doing as housekeeping when the methodology consolidation work (your v0.21 absorption) happens, not as a substrate priority.
The substrate review treats the engine as if it will be deployed eventually. Whether it should be deployed sooner or later, in what form, with what infrastructure — none of that is substrate work. It's deployment work, and it's its own scoping conversation.
Realistic estimate. FORAY unification is the biggest piece (~2-3 days). DB boundary tightening is one day. Contributor retirement is one-to-two days. KV unification is one-to-two. Identifier scheme is half a day. Job table base is one-to-two days, eventually.
That's substantial work — comparable to two phases of typical execution — but not endless. Done in priority order, each piece closes cleanly, the substrate gets meaningfully more coherent at each step, and the foundation is solid afterward.
I'd suggest treating each as its own phase: Phase 64 (FORAY unification), Phase 65 (boundary tightening), Phase 66 (contributor retirement), etc. Phase 63 (editor) ships independently. The substrate phases land sequentially. By the time real Operator data starts accumulating, the foundation is in the shape you want.
This is judgment, not certainty. Three options for sequencing.
The fastest path to addressing the immediate friction (editor) and then turning to the foundation work. The editor work is small and independent; it can land in a day or two. After that, the substrate phases run in priority order.
Pro: closes the immediate pain point. Con: defers the substrate work, which is more important architecturally.
The architecturally pure move. FORAY unification first; the editor friction stays for now. Phase 63 reopens after the substrate work is solid.
Pro: foundations first. Con: the editor friction is real and ongoing, and you'll feel it every time you edit a document during the substrate work.
Phase 63's editor improvements live entirely in record.dunin7.com with no engine dependency. The substrate work lives entirely in loomworks-engine. The two can proceed in parallel without conflict.
Pro: both pains get addressed without sequencing trade-off. Con: more context-switching for you between the two workstreams.
I'd lean Option C, with the caveat that "parallel" works only because the two workstreams don't touch. If the substrate work surfaces something that affects how the editor should work, parallel breaks down. But for the immediate scope — editor refinements vs FORAY unification — parallel is clean.
The conversation that should happen now is which of A/B/C you prefer. Once that's settled, the next step is scoping the first piece (Phase 63 editor v2 with comments absorbed, or FORAY unification scoping note). Either way, the picture is grounded enough now that the work that follows can be deliberate rather than reactive.