Database Schema Overview

Aset database — Supabase (PostgreSQL 15+) with RLS (Row-Level Security). v3.0 dimension model overlay on v2.17 schema.

✅ Synced with deployed Supabase DB

51 tables · 8 views · 28 enums · 8 functions · through migration 0205, counted against information_schema / pg_type / pg_proc on the dev instance 2026-08-21 (through 0205, applied and recorded 2026-08-21; verified against information_schema and pg_indexes, and the migration rows by exact name); the object-by-object verification behind the per-table sections below was done 2026-08-11 at 0174. The net since then: 0197 dropped the four external_pool_* tables and the external_pool_dpd_latest view, and 0195 added the three report_* tables that replaced them. public is now the whole database. Phase 6 of the money-flow refactor dropped deposits, redemption_requests, yield_claims and redemption_fills, which the ledger and its derived views replaced (0165), and the legacy schema — the pre-reset snapshot — went with it in 0174 after its live-pool history was carried over. What did not survive that move is listed in the migration, and it is not nothing. Recent migrations in the table below; the full per-migration log folds open under it, and the dated narrative lives in 17-changelog.

For the conceptual model (24 dimensions), see Pool Models.

Recent migrations

Migration	Applied (dev)	Change	Δ schema
0209	pending dev apply	Comment-only. Two column comments went stale on 2026-08-24: pools.start_date stated that the yield schedule counts from it, and pools.subscription_end_date described itself as the offering close and nothing more. The close is now the origin of the pool's timeline — maturity, the pool-wide lock-up and the yield grid all count from it (v3-145), so moving it moves all three, and a FIXED_TERM pool can no longer be published without one. No DDL, no row touched.	none
0205	applied 2026-08-21 (SQL editor) · tracking row recorded	pools.pool_implementation + pools.pool_factory — which contract generation a pool is pinned to (v3-142). A pool is a Clones proxy whose implementation is immutable, so its rules are frozen at creation, and nothing recorded which one — the only way to answer was to read the proxy bytecode. Written by pools.worker.deploy from factory.poolImplementation(). Existing rows stay NULL on purpose: every pool predating this also predates the 2026-08-21 reopen implementation, so NULL already means "cannot reopen".	+2 cols, +1 index
0204	applied 2026-08-20 (by hand, SQL editor) · tracking row recorded 2026-08-21	pools.subscription_end_date — the offering close stops sharing a column with maturity (v3-141). ⚠️ Numbered 0194 when it was applied; renumbered once ch/product turned out to hold 0194–0203. It was hand-applied, so it sat outside supabase_migrations.schema_migrations for a day and the tooling read it as pending; the row was added on 2026-08-21 alongside 0205. ⚠️ A check that says a row exists is only as good as its predicate — an earlier revision of this line claimed the row was already recorded, off a query whose version LIKE '%0204%' matched February timestamps. Match on name, not on the number.	+1 col, +1 constraint, +1 index
0203	applied 2026-08-20	pools.is_display_only → is_showcase. One column carried two meanings: its original spec was custody-level (now custody_mode, 0199) while the FE had layered v3-40's marketing tier on it. Renamed to the meaning the UI implements; showcase because preview is taken by B5 draft-preview tokens. No pool had ever set it (0 of 8), so no screen changed	rename
0202	applied 2026-08-20	data only: a money_deposit_workflow row for each seeded DEPOSITED event that had none (4). money_positions held holders the operator plane did not, so admin pool detail counted 2 investors above an empty tab. risk_ack left NULL — the pre-existing seeded rows carry a fabricated acknowledgement	+4 rows
0201	applied 2026-08-20	nav_history.attested_off_chain — a MIRROR pool's NAV is recorded without an on-chain updateNAV (D6), and an APPLIED row must not be mistakable for a chain-backed one. Its own column, not a new source value: the investor Performance tab branches on source = 'oracle'	+1 col
0200	applied 2026-08-19	funds.external_provider·external_fund_id dropped — 0 readers since 0028 moved the mapping to pool-level. ⚠️ pools.fx_rate/fx_rate_source were on this list and are kept: the earlier survey missed admin-web, which renders and edits them	−2 col
0199	applied 2026-08-19	pools.custody_mode (PLATFORM | MIRROR) — a mirrored pool's capital path is closed in both directions while its read model stays identical. Not the then-named is_display_only, whose FE meaning hides the Fund Data tab (see 0203)	+1 col, +1 check
0198	applied 2026-08-19	money_events.origin/origin_ref + a partial unique index for derived events; 25 non-chain rows backfilled to SEED. The two prior markings disagreed (6 rows vs 25)	+2 col, +1 idx
0197	applied 2026-08-19	🔴 the upsert-latest partner store retired: external_pool_data_snapshots·_data_cache·_dpd_buckets·_nonperforming + the _dpd_latest view dropped. Replaced by the report plane; loan_writeoffs deliberately kept	−4 tables, −1 view
0196	applied 2026-08-19	report_events holds attributes too: value nullable + text_value + a one-of CHECK, unit gains TEXT/TIMESTAMP. The read payload shows names and dates, and neither is a measurement	+2 col, +2 check
0195	applied 2026-08-19	the report plane: report_fetches (every API call, success and failure) · report_events (append-only observations) · report_pool_latest (projection). The provider rewrites history, so an upserted row destroys what a restatement replaces	+3 tables, +4 idx
0194	applied 2026-08-19	the guest fund-data API's master surface: pools.external_entity_type, nullable fund_value/realized_income + snapshot_trigger/composition_version on snapshots, pct_of_outstanding on DPD buckets, external_pool_nonperforming (the retired bucket=90), and the external_pool_dpd_latest view	+1 table, +1 view, +5 col, −2 NOT NULL
0193	applied 2026-08-18	A completed redemption stops claiming a payout of 0 when the view cannot read one (v3-140 sweep)	view only
0192	applied 2026-08-18	pools.epoch_cycle_mode marked unread — comment only, no DDL, no rows (v3-140)	comment only
0191	applied 2026-08-18	🔴 security_invoker restored on the two views that lost it; a funded epoch cycle stops keying a redemption row; audit_feed stops losing its sentence to a NULL	view only
0190	applied 2026-08-17	redemption_epochs.funding_date — the dates the chain was actually given, so a derived one is distinguishable (v3-133)	+1 col
0189	applied 2026-08-17	the repayment plan on pools: redemption_term_epochs · epoch_date_basis · epoch_roll_day (v3-132)	+3 col
0188	applied 2026-08-18 (carried by 0191)	money_redemption_list.payout_amount derivable before settlement, NULL for forward-priced requests	view only
0183	2026-08-13	the hold-back bucket dropped with its lever; the two ledger kinds made unwritable rather than removed	−1 col, +1 check
0182	2026-08-13	money_redemption_list: PENDING_RESERVE un-inverted, is_held added NULL-free	view only
0181	2026-08-12	money_positions gains the non-negative CHECK money_pool_state already had	+1 check
0180	2026-08-12	stablecoin_registry.decimals loses its DEFAULT 6; the deposit list stops guessing a scale	−1 default, view
0179	2026-08-12	the display-only pool's positions re-seeded as ledger events, not rows	data only
0178	2026-08-12	portfolio_positions dropped — positions are read from the projection	−1 table, +1 col
0177	2026-08-12	the projection stores its yield accumulator, so claimable yield is derivable at rest	+2 col
0176	2026-08-12	pools.deployed_block dropped with the replay it was added for	−1 col
0175	2026-08-12	the distribution joins the ledger: yield_distributions dropped for a workflow row + view	−1 table, +1 view
0173	2026-08-11	the pool hard-delete stops naming a table 0172 dropped, and clears pool_tvl_history	1 fn
0172	2026-08-11	money_tvl_daily dropped — TVL history stays a daily snapshot	−1 table
0171	2026-08-11	the failed-redemption alert counts reverted SUBMISSIONS, and is renamed for what it can see	view only
0170	2026-08-10	the validation replay's shadow schema dropped	−1 schema
0169	2026-08-10	three dead columns on pools; per-pool lifetime yield is derived instead	−3 col
0174	2026-08-11	the legacy schema dropped, after live-pool history was carried over	−1 schema
0168	2026-08-10	legacy is kept — superseded by 0174	comment only
0167	2026-08-10	the two 0166 drops, with the signatures they actually have	2 fn dropped
0166	2026-08-10	complete_deposit_atomic / process_deposit_with_tvl (wrong signatures — see 0167)	no-op
0165	2026-08-10	the four tables the ledger replaced, and five RPCs that wrote balances	−4 tables, 5 fn
0164	2026-08-10	the hard-delete guard and the epoch summary read the derived lists	2 fn
0163	2026-08-10	dashboard_alert_counts reads the ledger — the pending queue had been showing 0	view only
0162	2026-08-10	a cancelled redemption is REJECTED, not FAILED	view only
0161	2026-08-10	pools.maturity_date — maturity belongs to the pool, not to each holding	+1 col
0160	2026-08-10	money_pool_state.nav_per_token nullable: no price is not a price of par	nullable
0159	2026-08-10	the activity feed's CLAIM branch reads the ledger	view only
0158	2026-08-10	reinvestments return to the deposit list, on their own axis	view only
0157	2026-08-10	yield_claim_list	+1 view
0156	2026-08-10	epoch fills in the activity feed come from the ledger	view only
0155	2026-08-10	the redemption list derives its epoch fills	view only
0154	2026-08-10	money_redemption_workflow.epoch_id — the one fact the ledger cannot rebuild	+1 col
0153	2026-08-10	the redemption list's amount is LP, and never null	view only
0126	2026-08-06	the API deposit path records provenance, and two unreachable functions are dropped	2 fn dropped
0125	2026-08-06	cost basis on portfolio_positions.entry_price	+1 col
0124	2026-08-04	nav_proposals.escalate_flag comment	comment only
0123	2026-08-04	pools.is_hidden	+1 col
0122	2026-08-04	the manual NAV path takes a loss, so the buffer stops being a one-pool feature	+2 col
0121	2026-08-04	equity_buffer_prose_requires_layer CHECK on pools	CHECK only
0120	2026-08-04	nav_proposals.recovery_flag dropped	−1 col
0119	2026-08-04	buffer_requires_external_mapping CHECK on pools	CHECK only
0118	2026-08-04	partner-report configuration on pools	+6 col
0117	2026-08-04	pools.lp_total_supply	+1 col
0116	2026-08-04	pools.junior_depleted_at	+1 col
0115	2026-08-04	epoch_window_subscriptions	+1 table
0114	2026-08-04	dashboard_alert_counts.stalled_yield now requires deposit_tx_hash IS NOT NULL	view only
0113	2026-08-03	yield_distributions.status may never be PENDING	view + CHECK
0112	2026-08-03	redemption_epochs label correction	−1 col

Δ schema is counted from the migration SQL itself — only 0115 moved the table count in this window, so the 51/27 header holds across all fifteen.

Full per-migration log — 0001–0126, verbatim

Kept complete on purpose: 19 of these migrations are recorded nowhere else in the docs, so this is their only home. Newest first.

⚠️ This log stops at 0152 while the repo is at 0180 (verified 2026-08-12). Migrations 0153–0179 landed during the money-flow refactor and are not described here — several of them dropped tables this page still documents (portfolio_positions → 0178, deposits / redemption_requests → 0174). Treat the table listings below as accurate only where they agree with apps/infra/db/schema.sql, which is current. Bringing the two back in step is its own job, not a side effect of the next schema change.

0191 (written 2026-08-17, NOT yet applied): 🔴 apply this one first. CREATE OR REPLACE VIEW <name> AS with no WITH clause empties the view's reloptions, which turns security_invoker OFF and makes the view run as its owner (postgres, which has BYPASSRLS). 0180 did that to money_deposit_list (created with the flag in 0158) and 0182 did it to money_redemption_list. Nothing failed, no column changed, and both have been SECURITY DEFINER views ever since — Supabase's linter reports them at ERROR. Measured on dev with the publishable key that ships in the browser bundle: /rest/v1/money_redemption_list returns 9 of 9 rows, money_deposit_list 7 of 7 (4 distinct holders), investor_activity 16 of 16 (it has the flag, but it reads those two), audit_feed 18 of 248 (its deposit and redemption branches); money_distribution_list returns 0, which is what the others should do. That is user_id, investor_address, amounts, payouts and tx hashes to anyone holding a public key. Restoring the flag returns 0 rows to anon/authenticated because every base table has RLS ON with zero policies, which is the intended posture: the API reads through Lambda on the service role (BYPASSRLS), and no client uses PostgREST for data — admin-web's Supabase client is the Google OAuth broker and never calls .from(). 0188 was carrying the same omission and has been given the same WITH clause, so either apply order is safe; apps/infra/db/schema.sql had it right all along, which is the useful half of that file being a tool rather than a document.

0191, continued — the two defects the sweep was actually looking for, one theme — a view emitting an absent thing in the shape of a present one. (1) money_redemption_list was inventing a redemption out of every funded epoch cycle. fundRedemption's epoch branch ignores the request id it is handed and credits epochFundTopUp[currentEpochId] (RedemptionLib.sol:561-582), so the product passes 0 on purpose — and the keyed CTE, which filters only on the id being present, minted a key from it. Result on dev: QA Epoch 260812 carried a phantom row beside the real holder's, with no investor, no amount and a NULL created_at that sorted it to the top of the admin queue. It read PROCESSING because WHEN fund.id IS NOT NULL sits two branches above the epoch branch — which is the worse half: a partner funding a cycle with a real id (nothing stops them) would move a genuine holder's QUEUED request. Both halves are needed: the CTE excludes only requestId = '0' (never a real id — redemptionRequestCounter pre-increments, RedemptionLib.sol:275-276) and keeps REDEMPTION_FUNDED in the kind list, because an instant request whose request event never landed is keyed by its funding alone; the status branch narrows to instant pools while keeping the position 0182 fixed for it. 🔴 Passing a live id is not the fix — it attaches a cycle-sized transfer to one arbitrary queued request. (2) audit_feed lost whole sentences to 'text' || NULL: the DEPOSIT and YIELD branches concatenate nullable numerics (money_deposit_list.amount is NULL in flight and for an unregistered stablecoin per 0180; money_distribution_list.total_amount COALESCEs two nullable sources), so the row kept its id, actor and metadata and lost its only human-readable field — and the list search matches on description alone, so the row could not be found at all. Both now branch on NULL and say which absence it is. Verified read-only against dev: the phantom drop is exactly 1 row with 0 added and no legitimate row's status touched, and the audit_feed rewrite is bit-identical across all 248 rows and 17 columns on current data (0 rows carry a NULL description today, so that half is latent — the deposit case is transient by nature, which is why it was never noticed).

0188 (written 2026-08-14, NOT yet applied): money_redemption_list.payout_amount was NULL for every request awaiting funding, because both of its sources were settlement events (REDEMPTION_COMPLETED/_FALLBACK_CLAIMED, then REDEMPTION_CLAIMED) and a request awaiting funding has neither. Not just a blank cell: the admin funding flow guards on the value before computing the top-up it does not use it for, so a fund manager holding YIELD_DEPOSITOR_ROLE on a pool with every on-chain gate open was refused with "Redemption payout amount is missing or zero". Same lineage as 0182 one column over — the view inherited what was true of the dropped redemption_requests, where payout_amount was a column written at request time. A third COALESCE branch derives it from the request event, which already carries all three terms the columns beside it read: lp x nav - penalty (v3-84 puts every penalty on the principal). Verified against the chain, not reasoned: 5 LP at NAV 1.000000 less a 250000 penalty gives 4.75, which is the contract's stored stablecoinAmount; the one settled row in dev derives 1200 against its recorded 1200. ⚠️ Two traps the branch is shaped around. penaltyAmount is on two different axes under one name — stored as a share of an 18-decimal gross (RedemptionLib:413) but emitted through _denormalize, so the payload is raw stablecoin and this divides by the token's decimals, not by 1e18. And the branch yields NULL, never 0, when navAtRequest is 0: an epoch request is forward-priced and emitted with no price, so multiplying through would quote a holder of 5 LP a payout of $0 — the defect e145fa2c fixed and the reason 0160 chose NULL over par. Branch order is load-bearing: this one is LAST, so a partial fill or completion supersedes the original quote.

0180 (written 2026-08-12, NOT yet applied): two fixes to the money_redemption_list view, both from assumptions that were true of the dropped redemption_requests and false of money_redemption_workflow. (1) status had "awaiting partner funding" and "the partner already paid" swapped — the branch keyed on REDEMPTION_FUNDED, which fundRedemption() emits and which the instant path only accepts once the request is already PENDING_RESERVE on-chain, so it marked the moment the wait ended. The waiting state has no ledger event at all, only funding_shortfall, so the view now reads that; PROCESSING becomes reachable for the first time. (2) New is_held column, COALESCE(funding_status = 'HELD', false) — funding_status lost its DEFAULT 'FUNDED' in the move, so the list endpoint's funding_status <> 'HELD' filter evaluated to NULL for every ordinary request and the epoch Open/Rollover queues returned nothing. 🔴 Apply this before deploying the Lambdas: they filter on is_held, and PostgREST 400s on a column the view does not have.

✅ 0206 applied to dev 2026-08-21 — money_positions.accrued_yield, money_positions.yield_debt and money_pool_state.acc_yield_per_share are DEPRECATED with no writer, confirmed by reading col_description back. They mirrored the contract's per-share accumulator so a holder's claim could be recomputed off-chain; v3-131 (3) removed that mechanism and the read path asks the pool. 🔴 Commented, not dropped, and the comment is the whole protection: a column nothing writes still answers a SELECT, with the value it had when its writer stopped — the most plausible wrong number this schema can produce. The DROP is a separate change, after a real settlement has gone through. money_positions.yield_claimed STAYS: it is folded from YieldClaimed and is a real mirror of getPosition(investor).yieldClaimed, which is the one yield figure that can still drift and what yield.scheduler.reconcile now compares.

✅ 0208 applied to dev 2026-08-21 — pools.exit_window_anchor, confirmed against information_schema (timestamp with time zone, nullable, comment present). Counts unchanged: one column, no type, no table.

freeze_started_at could not carry both windows. emergencyFreeze() restamps it on every call — right for the DEPOSIT halt, since a fresh emergency deserves a fresh halt — and restamping the EXIT block made it renewable: one PAUSER_ROLE key calling every 71 hours kept value-OUT shut forever, claimRedemptionFallback included, and that is the only on-chain implementation of the v3-31 exit right. On chain the exit anchor advances only once two full windows have passed (LifecyclePolicy.armExitWindow); the indexer and pools.post.freeze apply the same rule through business/freeze-state.ts's armExitWindowAnchor. NOT restamped on re-freeze and NOT cleared on unfreeze — clearing it would let freeze → unfreeze → freeze re-arm instantly. NULL = not mirrored, and deriveFreezeState falls back to freeze_started_at, which for a pool pinned to an implementation without exitWindowAnchor IS the chain's behaviour.

✅ 0207 applied to dev 2026-08-21 — pools.accrual_rate_bps, confirmed against information_schema (integer, NOT NULL DEFAULT 0, CHECK 0..10000, comment present). Table and enum counts are unchanged: it adds a column, no type and no table.

⚠️ 0204 is still unrecorded. Its own header explains why — it was applied to dev by hand through the SQL editor while it was numbered 0194, so supabase_migrations.schema_migrations has no row for it and the tooling reads it as pending. pools.subscription_end_date does exist on dev, which matches that account. Every statement in the file is re-runnable precisely so that applying it through the normal path records the row; skipping it leaves an applied migration looking un-applied to the next person who checks. 🔴 0205 therefore records BEFORE 0204 — a real ordering gap in the tracking table, not a schema one, and it closes the moment 0204 is run.

This page reflects the deployed Supabase schema (51 tables · 28 enums · 8 views — the counts through 0187 were verified 2026-08-13; 0194–0197 (all applied 2026-08-19) then added the three report_* tables and dropped the four external_pool_* tables plus the external_pool_dpd_latest view, each confirmed against information_schema on apply; 0188–0193 recorded but not re-verified in full — 0187 (applied to dev 2026-08-13): the three dead money_event_kind labels are gone. HELD_FROM_PARTNER / HOLDBACK_RELEASED (the hold-back lever, 0183) and FUNDING_RETURNED (0184, superseded within the hour) were unreachable but stayed in the type because Postgres has no DROP VALUE: removing one means rebuilding the type and recreating every view that reads kind. 0183 and 0184 both declined for a reason that was about TRANSCRIPTION rather than the rebuild, since capturing 21KB of view SQL by hand puts a silently wrong copy in a migration. 0187 captures the definitions with pg_get_viewdef and derives the drop and recreate order from pg_depend, so nothing in it names a view's contents; reloptions, comments and grants are captured and restored too, because a recreated view keeps none of them and a lost GRANT reads as a 404 rather than an error. The money_events_no_holdback_kinds CHECK 0183 added in place of the removal went with it; — 0186 (applied to dev 2026-08-13): stablecoin_registry.contract_address is stored lowercased with a CHECK holding it there. Every address this codebase compares is lowercased at the boundary and this column was the exception, so each reader carried its own workaround (a lower() in four views, an ILIKE in findStablecoin); — 0185 (applied to dev 2026-08-13): money_distribution_list.status stops reading an event's absence. PROCESSING was the fallthrough — "no failure and no event, so it must still be in flight" — and absence has two meanings. A settlement that credits nobody (no eligible LP) still pays its fee legs, so the transaction succeeded and money moved while YieldDistributed was not emitted, and the row sat in PROCESSING for ever: un-retryable behind the one-way distribution_started_at claim, and swept after 24h as "an operator still needs to run this". The fix is on-chain — YieldDistributed is now emitted on every settlement, so crediting nobody is (0, 0) rather than silence, with no signature change and so no re-index — and this view reads it as the new SETTLED_NO_HOLDERS. Every column sourced from the event is gated on the same condition, because a zero-credit settlement has a transaction but no distribution to date; — 0151/0152 (applied to dev 2026-08-10): the activity and audit feeds read redemptions from the ledger. public.redemption_requests has been empty since the reset, so both branches were returning nothing; they now surface what money_redemption_list holds. Historical nav_at_request and payout_amount stay in legacy.redemption_requests until Phase 6 — the pre-redeploy contract emitted neither, which is why both were added to RedemptionRequested; — 0150 (applied to dev 2026-08-10): money_redemption_workflow gains a UNIQUE on (pool_id, onchain_request_id). That pair is what makes one redemption one row across its request, funding and settlement, and money_redemption_list joins on it — without the constraint nothing stopped duplicate rows, which would have multiplied every redemption in the list; — 0149 (applied to dev 2026-08-10): complete_redemption_atomic stops writing balances. It was a live second writer: portfolio_positions and pools.tvl have been derived from money_events since the deposit slice, and this function kept incrementing the same columns on every completed redemption, so whichever ran last won. It survived the earlier audit because that grepped TypeScript for .from('portfolio_positions') and this write lives inside a stored procedure — a writer reached through an RPC is invisible to a search for the table. The balance parameters are now ignored and the signature is deliberately unchanged, because the database and the Lambdas cannot be deployed in the same instant; — 0147/0148 (applied to dev 2026-08-10): money_redemption_list carries raw investor fields and the pool object. The redemption list gates PII by role — viewInvestor returns kyc_status to an operator and null to a FUND_MANAGER — and that depends on who is asking, which SQL run with the service key cannot know. Composing the name in SQL (right for the deposit list, which has no such gate) would have left investor_kyc_status null for everyone and looked like it worked. pools is an object for the same reason the deposit list builds one: PostgREST cannot embed a join from a view; — 0146 (applied to dev 2026-08-10): money_redemption_list — redemptions derived from money_events + money_redemption_workflow, the counterpart to the deposit list. Driven by the DISTINCT (pool, chain, requestId) across every redemption event rather than by the request event alone: the receipt-based backfill captured only transactions the old system had recorded a hash for, so some redemptions arrived with one half of their history, and driving from requests would have dropped four completions without showing that it had. nav_at_request and penalty_amount are NULL for backfilled rows because the old contract's RedemptionRequested carried neither — which is why both were added to the event; — 0145 (applied to dev 2026-08-10): audit_feed and investor_activity read the deposit list, not the deposits table. Removing process_deposit_atomic stopped that table gaining rows, and three views still read it — every new deposit would simply have been absent from the investor's activity feed and the admin's, with no error and no empty state; — 0143 (applied to dev 2026-08-10): money_deposit_list.amount was DOUBLE PRECISION, because power() returns a float. Money through binary floating point, in the column two screens read. 10::numeric ^ n is exact; — 0141/0142 (applied to dev 2026-08-10): money_deposit_list — the deposit list derived from money_deposit_workflow + money_events, in the shape the admin screen already reads, so search, sort, filter and pagination keep working as PostgREST operations over one relation. status is derived, never stored: a deposit is COMPLETED because the ledger holds its event. The nested wallets/pools/users objects are built in SQL because PostgREST cannot embed joins from a view; — 0139/0140 (applied to dev 2026-08-10): money_deposit_workflow keyed on the transaction, so a row can exist before the ledger event does. That window — confirmed on-chain, not yet ingested — is the PENDING state, and it is also the only moment the investor's risk acknowledgement is available to record. 0140 made the index non-partial: ON CONFLICT cannot infer a partial index, so every upsert had been failing at runtime; — 0138 (applied to dev 2026-08-10): money_deposit_workflow.risk_ack BOOLEAN → JSONB. It replaces deposits.risk_ack, which is JSONB and carries WHAT the investor agreed to; a boolean could say that something was acknowledged but not what, which is the whole content of the evidence; — 0137 (applied to dev 2026-08-10): Base Sepolia USDC registered. The registry held 8453 and 11155111 but never gained a row for the chain the product runs on in dev, and assetDecimals throws on an unregistered asset — the ledger ingest would have failed on the first Base Sepolia deposit; — 0134 (applied to dev 2026-08-10): the facts the chain does not carry. money_deposit_workflow, money_redemption_workflow and money_distribution_workflow hold what a replay can never produce — the investor's risk acknowledgement, an operator's decision and its reason, partner funding status and deadlines, the fee breakdown, and failures (a failed transaction emits no event at all). They point at money_events with a nullable FK, which is the point: a rejected request or a failed deposit has no ledger row to reference, and that is exactly why it needs a home. Putting any of this in money_events.payload would make "every row is a chain fact" false and cost the replay its meaning as a drift check. The fee split is the subtle one — it looks like gross minus net, but the contract holds no fee rates (0067), so fee_config_applied snapshots the config as it stood or an old distribution can only be re-derived under today's rates; — 0133 (applied to dev 2026-08-10): the projections — money_positions, money_pool_state, money_tvl_daily. New tables rather than new writers on portfolio_positions, which would have inherited its dead columns and left the cleanup as a chore that never happens. Every column is derived: anything a replay cannot rebuild belongs in a workflow table, because a projection holding un-derivable data turns "diff the replay" from a drift check into a comparison of two different things. rebuilt_from_event_id records the last ledger row folded in, so a replay resumes rather than restarts and staleness is observable. The backing invariant is a CHECK constraint, not a comment — promising more than the pool holds now fails at the write instead of surfacing later as a claim that reverts; +6 tables; — 0132 (applied to dev 2026-08-10): money_events, the money ledger, plus the money_event_kind enum (20 values). One append-only table whose contract is that every projection value can be recomputed by replaying it, so balances stop being incremented and start being derived — which is what makes drift both detectable (diff the replay) and fixable (replay again). One table rather than one per type, because splitting breaks the single idempotency key, the ordering WITHIN a transaction that carries deposit()'s atomic split, and the ability to ask for a pool's whole money history in one query. Order by (chain_id, block_number, log_index), never by id — id is insertion order, and the API fastpath routinely inserts ahead of the indexer that backfills around it. The kinds are derived from what MOVES money, not from the indexer's watched list: that list covers 24 of the contract's 73 events and omits ReleasedToPartner, FeesWithdrawn, the hold-back pair, RedemptionFunded and Reinvested, all of which move real value and are currently recorded nowhere. Empty until the ingest lands; +1 table, +1 enum; — 0131 (applied to dev 2026-08-09): legacy.pool_chain_deployments loses a column it inherited from a reverted migration. 0127 added deployed_block to the public table, 0128 copied that shape with LIKE, and 0129 removed it again from public only — leaving legacy one column wider. reset_to_legacy.sql copies with INSERT INTO legacy.x SELECT * FROM public.x, which needs the shapes to match; the table holds 0 rows so it would have passed silently, then aborted the whole reset transaction the moment anything wrote to it. Found by comparing column signatures across the two schemas rather than by running the reset. All 14 copied tables now match exactly; — 0130 (applied to dev 2026-08-09): the two things 0128 left without a home. legacy.indexer_cursor keeps how far the old log reader had actually got, which is what a replay coverage gap is measured against; legacy.pool_balances holds each deployed pool's TVL, NAV, LP supply and reserve. pools itself is NOT moved — the reset reuses each pool's config row and redeploys it, so the row stays live and only its balances are cleared, which left those four figures with nowhere to be snapshotted. Purpose-shaped rather than LIKE public.pools: copying ~80 config columns to preserve four numbers would make the baseline read as a second source of truth for pool configuration. Identity columns (address, chain, deployed block) travel with them because the reset clears those from pools, and chain_id is stored as TEXT so the snapshot carries no dependency on an enum whose values may be edited later. legacy only, no public change; — 0129 (applied to dev 2026-08-09): pools.deployed_block, and 0127 reverted. The block a pool's createPool tx landed in is the from-block a historical getLogs scan needs; deployed_at is a timestamp and cannot start one, and the indexer caps how wide a single range call may be, so scanning from zero is not a fallback. NULL means unknown, not genesis. 0127 had put it on pool_chain_deployments, which has no writer and 0 rows — so the capture script would have iterated nothing and exited successfully, a complete-looking freeze holding nothing. Backfilled from deploy_tx_hash by scripts/replay/capture-deployment-blocks.ts before the money-flow reset; 28 pools await it, 1 deployed pool has no tx hash to recover from; — 0128 (applied to dev 2026-08-09): a legacy schema to hold the pre-rework money rows as the diff baseline for the validation replay. Structure only, 13 tables mirroring the public money tables plus snapshot_meta (0130 adds two more, for 16); the rows are copied at the reset, not here. Not counted in the table total above — it is a separate schema. Separate schema rather than legacy_ prefixes in public because PostgREST exposes public and a snapshot of every historical position does not belong on the API surface, and because Phase 6 cleanup is then one DROP SCHEMA legacy CASCADE. No public table or enum count change; — 0126 (applied to dev 2026-08-06): the API deposit path records provenance, and two unreachable functions are dropped. process_deposit_atomic now sets portfolio_positions.source = 'DEPOSIT' on insert, which the indexer's deposit writer had always done — so the column read "provenance unknown" for the ordinary deposit and "known" only for the rare one the reconciler created first. Set on insert only (a top-up does not change how a holding was acquired) and backfilled for the 29 dev positions with completed deposits and no acquisition tx of their own; all 31 now read DEPOSIT. Dropped: revert_reinvest_atomic (0005), which compensated a DB claim whose on-chain mint then failed — 0086/H2 moved on-chain verification ahead of the DB write, so that window cannot open, and it had zero callers; and the 8-arg reinvest_yield_atomic overload left behind when 0086 added the 11-arg version, whose sole caller passes all 11. The overload mattered because it is the one copy that does not maintain the 0125 cost basis, so an 8-arg call would silently skip it. Both recoverable from 0005 / 0051. Function + data only, no column, table or enum count change; — 0125 (applied to dev 2026-08-06): cost basis on portfolio_positions.entry_price, so per-position P&L can be computed at all. Nothing recorded what a holder paid: effective_value is tokens × nav_per_token and is rewritten on every position change (always the current mark), entry_price was set only when a transfer CREATED a position, and the nav_at_investment named in 05-investment-lifecycle has never existed. process_deposit_atomic and reinvest_yield_atomic now maintain a weighted average from amount / tokens minted — the price the chain actually charged, which is deliberately not p_nav_per_token while a decrease sits in its 24h timelock (v3-111) — and the indexer's deposit / LP-transfer writers do the same, transfer-ins marked at the pool NAV since no consideration is observable on-chain. An unknown basis stays NULL rather than adopting the next acquisition's price, so it surfaces as no P&L instead of a zero-cost gain; partial redemption leaves it alone and a full exit deletes the row. Backfilled only where the reconstruction is unambiguous — no exit on record (deposits carries no per-row LP amount, so a partial exit makes it wrong) and cost ≤ tokens, since NAV is capped at 1.0 and a basis above that is proof tokens left by a path with no redemption row, e.g. an LP transfer out. On dev that filled 20 of 31 positions (all at exactly 1.000000) and left 10 NULL: 6 with an exit, 2 with no recorded cost, 2 caught by the ≤1.0 guard (9 LP against $10 in, and 49.95 against $50 — both worth a look, neither safe to reconstruct). Function + data only, no column, table or enum count change; — 0124 (applied to dev 2026-08-04): nav_proposals.escalate_flag comment names the escalation condition 전손 / total loss once (v3-110 C). Text only, no column or value change; issued as a new COMMENT ON COLUMN rather than an edit to the applied 0094; — 0123 (applied to dev 2026-08-04): pools.is_hidden — hides a pool from the default admin list with no capability change, no on-chain component and no effect on investors. It exists because archive (deleted_at) had been doing two unrelated jobs, retiring a finished pool and decluttering the console, and v3-110 A narrows archive to the first. is_showcase could not be reused twice over: it is in IMMUTABLE_FIELDS so PATCH rejects it in every lifecycle stage, and it means "custody-less external mirror with deposits disabled", so flipping it would disable deposits as a side effect. Additive with a default, no count change; — 0122 (applied to dev 2026-08-04): the manual NAV path takes a loss, so the buffer stops being a one-pool feature. POST /nav-changes now accepts cumulative_loss as an alternative to new_nav and derives the price through the same nav-formula.ts the suggestion sweep uses. That matters because the sweep visits externally mapped pools only — 1 of 13 standalone pools — so buffer_rate_bps had exactly one reader and was inert everywhere else. A hand-typed NAV has already had the loss applied by whoever did the arithmetic, which is why no first-loss layer could reach that path. Adds nav_history.loss_amount (the uncovered loss, after the buffer — what actually moved the price, not the gross figure) and loss_as_of; both NULL on a directly-entered NAV, which is itself the signal that a human priced the row rather than the formula. This is the minimum useful form of the decrease-cause metadata that had been spec-level. Retires 0119. That constraint banned a non-zero buffer outside mapped pools, and the reasoning held only while nothing else could read one — a rule forbidding a value is right up until the value acquires a reader. It is replaced by two narrower ones: buffer_not_on_tranche_pools (a tranche group prices through the waterfall engine, which does not run this formula) and cadence_requires_external_mapping (a reporting cadence is the partner's obligation, and a pool with no feed receives no reports). equity_buffer_prose_requires_layer (0121) is unchanged and now bites on all 13 pools instead of 1; — 0121 (applied to dev 2026-08-04): equity_buffer_prose_requires_layer CHECK on pools — equity_buffer_rule may be set only where buffer_rate_bps > 0. 0077 added that column as free prose when no structured buffer existed, and the investor pool page renders it as "Manager first-loss commitment"; 0118 gave the layer a real representation, which left sentence and configuration free to disagree, and 0119 sharpened the gap — the structured buffer is refused on a pool the sweep never visits, while the sentence could be PATCHed onto any pool at all. The constraint makes the text a description of the config rather than a substitute for it. Blank counts as unset; 0 of 28 dev pools held a value, so it was added validated, and the create handler rejects the field outright since a new pool has no rate; — 0120 (applied to dev 2026-08-04): nav_proposals.recovery_flag dropped, one day after 0118 added it. It flagged a proposal that RAISES NAV, on the reasoning that an increase applies with no timelock and so cannot be caught during a notice period — which describes the timelock, not a missing control. Product's call is that a NAV increase needs the approve button and nothing else. Dropped rather than left unread: its one producer and one reader are both gone, and an always-false column invites a future reader to treat it as meaningful. 1 row on dev, all false, never deployed; — 0119 (applied to dev 2026-08-04): buffer_requires_external_mapping CHECK on pools — buffer_rate_bps > 0 (and a non-NULL fund_report_cadence_days) is allowed only where external_fund_id IS NOT NULL and the pool is not in a tranche group. Both are consumed exclusively by the NAV suggestion sweep, which skips unmapped pools, so a buffer configured elsewhere is silently inert — the admin sees a first-loss layer on the pool page that no computation will ever apply. Mirrored in pools.patch.update so the API returns a message rather than a raw 23514; — 0118 (applied to dev 2026-08-04): partner-report configuration on pools (buffer_rate_bps, buffer_direction, buffer_basis, fund_report_cadence_days, apy_basis). Each replaces an assumption the NAV formula would otherwise make about a partner contract nobody has read. Defaults reproduce today's arithmetic exactly, so it ships inert and a contract answer becomes an UPDATE rather than a formula rewrite. See Shipped — v3-109 below for the full shape, and for the two planned columns (buffer_cap, buffer_balance) that were deliberately not built; — 0117 (applied to dev 2026-08-04): pools.lp_total_supply, the on-chain LP supply mirror R9 needs as a denominator. The number had nowhere to come from — no column held it, no code read it — which is what actually blocked that decision. Additive nullable, no count change. The formula is not switched to it in the same change: pointing the numerator at the DB and the denominator at the chain before the mirror is proven running turns every indexer lag into a NAV jump, and the indexer was silently dead for 13 days earlier this month. ⚠️ Most of v3-109 needed no migration at all — it changed what existing columns MEAN (nav_history.reserve_consumed is now always 0, nav_proposals.resolved_by IS NULL marks a TTL expiry) and confirmed two tables have no writer (pool_chain_deployments, loan_writeoffs); — 0116 (applied to dev 2026-08-04): pools.junior_depleted_at, so the operator banner can say WHY an impairment appeared. Junior depletion was computed per request and discarded (it lived only inside a POST response field no screen read), which left the authored copy unusable: impairment_proposed_at is also set by hand-proposed impairments that have nothing to do with tranches. Additive nullable column, no count change; — 0115 (applied to dev 2026-08-04): epoch_window_subscriptions (+1 table), the investor opt-in behind the new epoch_request_window_open notice. Deliberately NOT a reuse of pool_follows (0109): that list is consent for deposits opening, this one for the redemption request window opening, and both notices say "you asked to be notified" — one shared list would make that false for half of each notice's recipients. Resolved by a new epochWindowSubscribers audience and produced hourly by pools.scheduler.epoch-window-open, which reads the window boundaries on-chain rather than adding a fourth copy of the derivation; 0114 (applied to dev 2026-08-04): dashboard_alert_counts.stalled_yield now requires deposit_tx_hash IS NOT NULL so the badge counts exactly what the new yield_distribution_stalled notification sends (that notice prints the tx hash, so it cannot fire on a hash-less row; unnarrowed, the badge counted a superset and its drilldown listed rows nobody was notified about). Predicate-only, so CREATE OR REPLACE VIEW keeps the view's privileges; no table or enum count change; 0113 (applied to dev 2026-08-03): yield_distributions.status may never be PENDING — default moved to PROCESSING plus a CHECK constraint, because PENDING only ever came from the removed legacy server-key create path where a crash between the insert and the settle left an orphan no reconciler could heal; the yield_status enum keeps its PENDING value (shared with yield_distribution_investors, where it is legitimate), so no table or enum count change, and dashboard_alert_counts.pending_yield is renamed stalled_yield and repointed to stalled PROCESSING rows (v3-104); 0112 (applied to dev 2026-08-03): redemption_epochs label correction — epoch_end_at → settlement_allowed_at (it never meant "the epoch ends"; it is the funding/claim moment, recall_lead_days AFTER the request cutoff) and epoch_start_at dropped (no readers; duplicated the previous cycle's settled_at). No table or enum count change; the investor_activity view follows the rename automatically and the migration asserts that it did (v3-107); 0111 (applied to dev 2026-08-03): funding-date provenance — pools.next_funding_date_confirmed_at + next_funding_date_set_by, the two columns the investor 확정 / 예정 badge reads because the chain cannot distinguish a confirmed cycle date from a fail-open derived one (no new tables or enums) (v3-107); 0100–0110 are applied to dev (0110 on 2026-07-31, the rest 2026-07-30); the _(pending dev apply)_ markers below survive only on 0090–0094, which predate that batch. 0110 (applied to dev 2026-07-31): notifications rebuilt — notification_events / notifications / notification_deliveries + email_suppressions replace notification_logs, which was a single row acting as both the in-app inbox item and the email delivery log; notification_status and recipient_type dropped, delivery_status / recipient_kind / suppression_reason added (+3 → 50 tables, +1 → 27 enums) (v3-108). 0109 (applied to dev 2026-07-30): pool_follows table added (+1 → 47 tables) — the investor opt-in list behind the pool_lifecycle_active notification. It has to be an opt-in rather than a broadcast because the authored copy says "A pool you follow is open / You asked to be notified when it opened": sent to every investor that sentence is false for every recipient, and the notice becomes an unsolicited new-pool advert. This table is why the event could finally ship — it had been parked as V2 for exactly this reason, not for want of a producer. Shape: (user_id, pool_id) is the PK, so a re-follow is an idempotent upsert; no status column, because unfollow deletes the row and a "was following" history has no reader and would only invite sending to it. ON DELETE CASCADE on both FKs (a follow is meaningless once either side is gone, and a hard pool delete — 0090/0093 — must not be blocked by a stale follow); pools are normally soft-deleted, which CASCADE does not see, so the producer filters on the pool it is already iterating and the list endpoint joins through to a live pool. Extra index on pool_id alone: the PK leads with user_id, but the producer's access path is "who follows THIS pool" at activation. RLS on with no policies, matching the other Lambda-written tables (nav_proposals 0094, redemption_fills 0103) — no anon/authenticated grant exists, so a leaked publishable key cannot enumerate who follows what. +1 table, no enum change; 0108 (applied to dev 2026-07-30): investor notification preferences re-keyed on event_key — the investor half of what 0101 did for admins, and the change that makes investor preference enforcement possible at all. The Settings page had been writing two category names of its own invention: NAV_UPDATE, which is neither an event key nor a copy.ts category (the nearest real events, nav_change_proposed / _cancelled, are both critical, so even a correctly-keyed row could not have disabled them), and POOL_PERFORMANCE, which was worse than inert — no weekly-performance event exists in the registry or the copy sheet, so the switch advertised a feature that was never built. Neither can be translated to an event key, so the rows are deleted rather than migrated; keeping them would leave junk PK rows that the derived allowlist rejects on write while the read path still hands them to the send-time gate. Nothing about past sends changes: the worker never read investor preference rows before this, so every one of them had zero effect on delivery. 0 rows deleted on dev (the table is empty there) — written for prod safety. ADMIN rows untouched. Data-only, no table/enum count change; 0107 (applied to dev 2026-07-30): dashboard_alert_counts.failed_notifications now excludes INVALID_RECIPIENT — that failure_type means the audience had no deliverable address (usually an investor with no verified email): permanent, non-actionable, and the in-app row was still delivered. Counting it made the admin alert grow forever and bury the SES failures worth chasing. View-only, no table/enum count change; 0106: LIQUIDITY_WINDOWS removed from pools.redemption_type — never wired (deploy sent an empty liquidity-window array, so the contract's window loop could never match and every requestRedemption reverted NotInLiquidityWindow: redemption blocked forever, silently). v3-88 D5 closed the admin dropdown but left the value API-reachable, and pools.patch.update never validated it at all. The 4 pools deployed in that state (all Base Sepolia 84532, QA, no mainnet exposure) are soft-deleted rather than relabelled — rewriting them to ON_DEMAND would make the DB assert a capability the contract does not have, and they cannot be repaired (redemptionConfig is initialize-only and PlatformPool is not upgradeable). The CHECK exempts soft-deleted rows so the audit trail of what they were stays intact. Also renumbers the on-chain enum (ON_DEMAND 2 → 1), safe because pools are pinned to the implementation they were created with. Data + constraint, no table/enum count change; 0105: pools.epoch_cycle_mode comment scoped to its real effect (D2) — it gates an off-chain reminder, not contract behaviour, because the epoch schedule is fail-open either way (decision C2). Comment-only: no column, constraint, default or data change; 0104 (drift reconciliation, already live): 6 epoch schedule columns on pools (epoch_schedule_type, funding_anchor_date, recall_lead_days, request_window_days, epoch_cycle_mode, next_funding_date) were applied straight through the Supabase SQL Editor months ago and existed only in the live DB — no repo migration, no schema.sql entry, no supabase_migrations row. A fresh environment bootstrapped from the repo therefore came up without them, and the epoch schedule engine (v3-91 / v3-93, contract redesign pending) reads them. 0104 backfills the migration + schema.sql to match live verbatim (types, nullability, defaults, CHECKs, comments) and is a no-op where the columns already exist. No code reads these columns yet — pools types/validation and the create/edit pool API are still unwired. Column-only, no table/enum count change; 0103 (applied to dev 2026-07-30): redemption_fills added (+1 table) — per-fill ledger for epoch redemptions, one row per RedemptionClaimed. An epoch request settles pro-rata and rolls its remainder over, so one request can be paid across several epochs, but the request row keeps only cumulative totals: payout_amount was OVERWRITTEN per fill (a twice-filled request reported only its last fill as its payout) and the earlier fill's tx hash was lost, while epoch_id is re-pointed on rollover. Money already in the investor's wallet therefore appeared nowhere — the request stayed in-flight (it has a remainder) and History shows only terminal rows. The view gains a REDEEM_FILL branch and withholds a request row only when it is COMPLETED and has fills, so the ledger stays single-entry; payout_amount now accumulates. No backfill is possible (per-fill payouts/hashes were never recorded); 0102 (applied to dev 2026-07-30): admin_sessions.is_current dropped — dead column, never written by any handler; "this device" is derived per request from the caller's token sid. The API response keeps its derived field of the same name. Column-only, no table/enum count change; 0101 (applied to dev 2026-07-30): admin notification preferences re-keyed on event_key — the 9 dev rows written under the My Settings panel's invented category taxonomy (new_redemption, deposit_anomaly, …) are deleted. Those names matched no notification event, so the email toggles saved and changed nothing; category now holds notification_logs.event_type, which is what the send worker gates on. Data-only, no table/enum count change; 0100 (applied to dev 2026-07-30): notification_status gained SUPPRESSED — terminal state for an email deliberately not sent because every recipient opted out of that optional event (in-app row still delivered). Kept out of FAILED so an opt-out does not enter the failed-notification ops queue. Enum value only, type count unchanged; 0099 (applied to dev 2026-07-28): kyc_logs.status_result legacy vocabulary normalized — the column carried two vocabularies: the current writers emit GREEN/RED (apply-review) + IN_REVIEW/RESET/DEACTIVATED (webhook), while 29 pre-v3 rows still said APPROVED/REJECTED/IN_PROGRESS (no writer). The admin KYC drawer resolves the rejection reason, reject_type and attempt history (required by docs 03-kyc-identity) by matching RED, so those rows were invisible to it — one dev user showed 4 attempts against 14 real rejections. Backfilled to the current values; audit_feed output is unchanged for GREEN/RED (its ELSE branch already produced the same KYC_APPROVED/KYC_REJECTED strings) and IN_PROGRESS → IN_REVIEW additionally moves those rows out of the human write-stream into the Activity stream where SumSub-driven events belong. Data-only, no table/enum count change; 0098 — 0098 (applied to dev 2026-07-28): platform_stats scope widened — total_tvl / total_investors were restricted to lifecycle_status = 'ACTIVE', so a MATURED / CLOSED / IMPAIRED / WIND_DOWN pool's still-unredeemed capital (and its holders) vanished from the admin overview; both now cover every pool, and all three aggregates exclude soft-deleted pools to match pools.get.list. View-only, no table/enum count change; 0097 (applied to dev 2026-07-28): redemption_epoch_summary queues made disjoint — queued_count counted the whole open-demand set (QUEUED + PARTIALLY_FILLED, held included), so the admin Redemptions EPOCH tab counted a held request in both its "Open Queue" and "Hold Queue" (and a partially-filled one in both Open and Rollover). The three counts now partition the set: queued_count = QUEUED not held, rollover_count = PARTIALLY_FILLED not held, held_count = either status held. demand_lp / demand_usd unchanged. Function-only, no table/enum count change; 0096 (applied to dev 2026-07-28): pool_position_stats(uuid[]) aggregation function — holder count + LP supply per pool, so pools.get.list stops downloading every positive portfolio_positions row twice and reducing it in Lambda. Function-only, no table/enum count change; 0095 (applied to dev 2026-07-28): platform_stats.total_yield re-derived from yield_distributions — it summed pools.total_yield_distributed, a denormalized column nothing writes (same class of bug 0026 fixed for investor_count), so the admin dashboard "Total Yield" KPI read $0 no matter how much had been distributed. Now SUM(total_amount) over DISTRIBUTED distributions on non-soft-deleted pools; a lifetime figure, so unlike total_tvl it is not restricted to ACTIVE pools. pools.total_yield_distributed is left in place with a DEPRECATED comment (no readers) for a later drop. View-only, no table/enum count change; 0094 (pending apply): nav_proposals table (A4 NAV suggest/approve/override) — new table (+1 → 45 tables) decoupled from nav_history (state model B). The nav-proposals.scheduler.suggest sweep (EventBridge, ~12h) computes a suggested NAV for each fund-linked standalone pool from its latest external_pool_data_snapshots row + live on-chain reserve, and when it materially differs (|suggested − nav_per_token| ≥ 0.0001) with no OPEN proposal, persists an OPEN row + fires nav_proposal_pending to ADMIN. An ADMIN/SUPER_ADMIN then POST /nav-changes/proposals/{id}/approve (takes suggested_nav) or /override (a corrected new_nav), funneling into the existing propose path (simulate → updateNAV → nav_history APPLIED/PENDING) and marking the proposal APPROVED/OVERRIDDEN with applied_nav_history_id; approve/override write NAV_APPROVE/NAV_OVERRIDE to the audit log. escalate_flag = true when computed NAV ≤ 0 (surfaced for IMPAIRED/WIND_DOWN, never auto-applied). Tranche-group pools are excluded (their NAV is the POST /tranche-writedown engine's). RLS on, service-key only; +1 table, no enum change; 0093 (pending apply): hard_delete_pool_atomic(uuid) revised — an eligible pool is always hard-deletable — 0090's guards could strand a deletable pool permanently: an admin can propose a NAV change or a governance change on a DRAFT / deploy-failed pool, and either child row made DELETE /pools/{id}?hard=true 409 forever while the admin UI only ever offers hard delete (never the archive fallback) for those pools. The RPC now force-deletes whenever lifecycle_status = 'DRAFT' or deploy_status = 'DEPLOY_FAILED', removing all 8 NO ACTION children in FK dependency order (loan_writeoffs → nav_history; redemption_requests/yield_claims → portfolio_positions; plus yield_distributions, deposits, pool_governance_changes) with no guards. Safe because neither state can hold settled investor money: DRAFT is a one-way origin state (never investor-visible, never deployed, no transition back into DRAFT), and DEPLOY_FAILED is only ever written before a pool reaches DEPLOYED (publish gates on deploy_status !== DEPLOYED, retry-deploy requires DEPLOY_FAILED already, the deploy worker acts only on DEPLOYING) while isPoolHiddenFromInvestor hides DEPLOY_FAILED regardless of lifecycle (C1), so it can take no deposits. Every other state keeps the full 0090 guard set — unreachable via the API (the handler gates on eligibility first) but a safety net against a stray service-role RPC call wiping a live ACTIVE pool. Also locks the pool row FOR UPDATE up front and raises no_data_found (02000) for a missing pool, which the handler now maps to 404 instead of a blanket 500. No table/enum count change; 0091 (pending apply): apply_nav_change_atomic(p_nav_change_id, p_pool_id, p_nav_per_token) RPC — marks a nav_history row APPLIED and syncs pools.nav_per_token + recomputed has_pending_nav_update in ONE transaction, so applying a queued NAV change can no longer leave it on-chain-applied but the pool NAV stale (#1 chain↔DB drift). Idempotent (re-applying an APPLIED row just re-syncs the pool). Used by POST /nav-changes/{id}/activate and the new nav-changes.scheduler.apply-pending sweep (EventBridge, 15 min) which auto-applies queued NAV decreases past their 24h timelock (#2, no manual activate needed) and converges the DB to on-chain navPerToken for every deployed pool (on-chain is SoT). No table/enum count change; 0090 (pending apply): hard_delete_pool_atomic(uuid) RPC — admin hard-delete of a pool was 500ing ("Failed to delete pool") because a bare DELETE FROM pools hits the nav_history.pool_id NO ACTION FK, and pool creation always writes one genesis nav_history baseline row → a normally-created pool could never be hard-deleted. The RPC guards (rejects if ANY row exists in deposits / portfolio_positions / redemption_requests / yield_distributions / yield_claims / loan_writeoffs / pool_governance_changes, and tolerates at most the single genesis nav_history row), then deletes that row + the pool in one transaction (CASCADE FKs clear config children); guard rejections RAISE P0001 → handler maps to HTTP 409. No table/enum count change; 0089 (applied to dev 2026-07-24): settlement-NAV columns pinned to NUMERIC(18,6) — redemption_requests.settled_nav + redemption_epochs.settled_nav changed from unqualified NUMERIC to NUMERIC(18,6), matching every other NAV column (pools.nav_per_token, nav_history.old_nav/new_nav, redemption_requests.nav_at_request were already NUMERIC(18,6) in the deployed DB — schema.sql/docs had drifted to showing them unqualified and are now corrected). NAV is 6-dec fixed point on-chain (NAV_PRECISION = 1e6); pinning scale = 6 rounds every write to the on-chain precision at the DB layer so a JS float can't persist residue (e.g. 0.8500000000000001) that diverges from the exact 1e6 integer. Both settled_nav columns were 100% NULL, so nothing was rounded on cast. Not fill_ratio / LP-amount columns (different scale). No table/enum count change; 0086 (pending apply): yield-path hardening (H1/H2/H3) — added yield_distributions.distribution_started_at (H1: /distribute concurrency claim marker, compare-and-set before the on-chain distribute so two requests can't double-run distributeYield/withdrawFees); claim_yield_atomic now skips the accrued-sufficiency guard and clamps the deduction to ≥0 for on-chain-verified (COMPLETED) claims — the on-chain paid amount is SoT and can exceed the not-yet-reconciled accrued mirror (H3, same signature); reinvest_yield_atomic gained p_tx_hash/p_investor_address/p_chain_id (DEFAULT NULL, backward-compatible) recorded on the reinvest deposit for provenance + per-tx replay (H2, handler now verifies the on-chain Reinvested event); no table/enum count change; 0085 (pending apply): process_deposit_atomic — single-transaction deposit RPC replacing the process_deposit_with_tvl + complete_deposit_atomic pair (F2: TVL + portfolio position can no longer diverge on a mid-flow failure) and recording investor_address + chain_id (F1: multichain provenance vs the 8453 default); the two old functions are retained for deploy-ordering, dropped in a follow-up; no table/enum count change; 0088 (pending apply): pools.reserve_balance (live reserve) — added nullable pools.reserve_balance (human units / USD); the on-chain indexer mirrors PlatformPool.reserveBalance() for each deployed pool every run so the admin pool detail shows the actual reserve % of TVL against the reserve_bps target (NULL until first indexed → target-only fallback); no table/enum count change; 0082 (pending apply): audit append-only + cold archive (v3-86) — activity_events is now append-only (a BEFORE UPDATE/DELETE trigger blocks all mutation for every role incl. the Lambda service key; DELETE only inside archive_expired_activity_events()); new activity_events_archive cold table (+1 → 44 tables) receives rows aged past the 5y floor via the atomic archive RPC (INSERT+DELETE in one txn), replacing the hard-purge scheduler; 0081 (pending apply): audit record fields (v3-86) — added activity_events.actor_name/actor_role/actor_type/entity_label/before_state/after_state/reason/outcome; actor_name/actor_role are snapshotted from admin_users at write time (recordActivity) so records survive rename/deletion, before_state→after_state hold the structured change, reason is required on high-risk acts (handler-enforced), outcome = 'success'|'failure' (failed attempts audited); audit_feed re-created to surface them (appended after actor_type); no table/enum count change; 0080 (pending apply): audit feed actor resolution (v3-86) — audit_feed view gained actor_type ('admin'|'investor'|'system') and now sets actor_id = the investor's user_id on deposit/redemption rows (was NULL → always "SYSTEM"); GET /activity-events + CSV export resolve investors via users/wallets (name-only for FM PII rule) so the Audit Log shows who acted; view-only, no table/enum count change; 0079 (pending apply): session dedupe + unique key — collapsed duplicate admin_sessions / investor_sessions rows (kept the most-recently-seen per group) and added idx_admin_sessions_device_unique (admin_user_id, device_info, ip_address) + idx_investor_sessions_device_unique (user_id, device_info, ip_address), both NULLS NOT DISTINCT (PG15+); login now upserts on these so repeated logins from the same device+IP reuse one row/sid instead of piling up "duplicate" active sessions, and so the current-device row is unambiguous ("활성 세션 중복으로 뜸" QA fix); no table/enum count change; 0078 (applied to dev 2026-07-21): pools.pool_wallet dropped — legacy AS_POOL/v2 wallet column removed. In v3 the wallet roles split cleanly: FM capital = fund_wallet, platform operating/deploy wallet = the server key (ADMIN_PRIVATE_KEY) itself; pool_wallet was only a default operator/treasury fallback that resolved to the server-key address, so the per-pool override was dead. Verified pre-drop: 17 pools on the server-key default + 1 (already ACTIVE/DEPLOYED, on-chain treasury fixed) whose custom value equaled its own fund_wallet — drop changes no on-chain state. ⚠️ apply order: deploy the pool_wallet-free lambda code first, then run the migration. No table/enum count change; 0077: pools.equity_buffer_rule — added nullable TEXT for the FM first-loss / equity buffer disclosure (free text, e.g. "Manager absorbs NPL up to 5%") the admin create/edit wizard collects and the investor PDP overview renders; wired into create DB_FIELDS + PATCH whitelist (Notion Admin Pool Create/Edit B3); no table/enum count change; 0076: deposits per-investment risk-ack — added deposits.risk_acknowledged_at (TIMESTAMPTZ) + deposits.risk_ack (JSONB {mode,items,reg_s}) and threaded both through process_deposit_with_tvl (dropped + recreated with two DEFAULT NULL ack params, 0022 pattern) so the Reg S risk re-confirmation lands in the SAME atomic insert as the deposit; v3-72 handoff item D; no table/enum count change; 0075: net_yield_fee_config rates → basis points — JSONB fee-rate keys platform_yield_take_pct/spc_mgmt_pct/pool_mgmt_pct/perf_fee_pct/perf_hurdle_pct renamed *_bps (×100) in place; off-chain compute ×bps/10000 + validation integer 0..10000; no on-chain twin; no table/enum count change; 0074: external_pool_data_snapshots.cumulative_impairment → cumulative_loss (realized write-off past write_off_policy, embedded in NAV — distinct from the leading NPL signal; renamed with the BE readers in the same release; v3-72); 0073: DPD/NPL S2 additive — added external_pool_data_snapshots time-series columns (accrued_income, total_outstanding_principal, active_loan_count, total_overdue_loans, total_overdue_exposure, sector_breakdown, all nullable), new table external_pool_dpd_buckets (dynamic fund-reported DPD buckets, RLS on, +1 table → 43), and pools.npl_threshold_days (default 90) + pools.write_off_policy; powers npl_ratio = Σ exposure(lower_days ≥ threshold) / total_outstanding_principal; v3-72; 0072: investor_tier → investor_status + eligibility_mode — dropped the dormant US-centric investor_tier enum (ACCREDITED/QP) from users + pools; added users.investor_status (RETAIL/PROFESSIONAL) + qualification_country/qualification_basis/qualification_verified_by/_at/_reason/qualification_expires_at (Status Gate state + audit) and pools.eligibility_mode (STATUS/MIN_TICKET); non-US Reg S qualified-investor model, v3-74 (net +1 enum → 26); 0071: pools.tagline + pools.additional_disclosures added (create-pool content contract — hero tagline + per-pool risk disclosures, off-chain/display-only); 0070 — admin_sessions.expires_at added (absolute session expiry = created + 30d, mirroring the refresh-token lifetime; adminSessionExists rejects expired rows, existing rows backfilled; S-43); 0050 — dropped deprecated v3 columns pools.lp_issuance_model (+ the lp_issuance_model enum), deposits.fm_notified_at, redemption_requests.fm_notified_at/fm_accepted_at (v3-02 LP-model & v3-04/v3-34 FM-stage removal; no code/view/function reader; yield_distributions.fm_notified_at kept as a separate valid field); 0049 — deposit_status enum: REFUNDED value dropped (D+7 refund flow removed in v3-03; 0 rows used it, app+seed references purged; the two security_invoker views on deposits.status were dropped/recreated verbatim); 0048 — wallets uniqueness made case-insensitive: constraint unique_address_per_chain replaced by a functional unique index (lower(address), chain_id), and the never-attached lowercase_wallet_address() trigger function dropped (dead-code cleanup; the app already lowercases addresses at auth.post.verify); 0047 — users.email made nullable and legacy synthetic <address>@wallet.aset.io placeholders cleared: wallet-login signup no longer stores a fake email, admin/investor views show "not set" until a real address is verified; 0046 — dashboard_alert_counts: sbt_mint_failed excludes in-flight mints, kyc_pending counts IN_REVIEW only; 0045 — users.sbt_mint_queued_at in-flight SBT-mint marker; 0044 — uq_fund_members_one_primary partial unique index: exactly one ACTIVE primary fund manager per fund, first accepted member auto-promotes). v3.0 dimension columns are live on pools; legacy v2.x columns are retained for history. pools.signing_method dropped (migration 0015 — per-pool multisig removed); pools.issuer dropped (migration 0016 — fund identity is fund_id → funds.name); pools.escrow_address / pools.receipt_address dropped (migration 0017 — Escrow / Receipt NFT removed in v3.0); pools.investment_blocked dropped (migration 0018 — superseded by is_paused, 08 v3-29); pools.pool_type + the pool_type enum dropped (migration 0019 — AS_POOL/FUND_POOL binary removed, v3-01 dimension model); epoch-based redemption added (migration 0021 — pools.redemption_epoch_days + redemption_epochs table + redemption_status QUEUED/PARTIALLY_FILLED; FM_ACCEPTED removed, v3-26/31/32/34); deposits Receipt NFT / Escrow / D+7-refund columns dropped (migration 0022 — receipt_code, receipt_token_id, receipt_issued_at, receipt_tx_hash, receipt_burned_at, receipt_burn_tx_hash, escrow_status, refund_eligible_at + idx_deposits_refund; v3-03/v3-11); pools.freeze_started_at added (migration 0023 — time-bound emergency freeze, mirrors on-chain freezeStartedAt; v3-28); redemption_epochs.funding_shortfall added (migration 0024 — epoch funding deficit, indexer mirror of EpochFundingNeeded; powers the "Awaiting Funding $X" KPI alongside the existing redemption_requests.funding_shortfall; v3-26); fund_pool_assignments table dropped (migration 0025 — funds⟷pools is 1:N via pools.fund_id since v3-26; the M:N junction was obsolete and never written by app code); platform_stats.total_investors redefined to live distinct holders (migration 0026 — the summed pools.investor_count column is stale/never maintained; now derived from portfolio_positions); pool_tvl_history.recorded_date + UNIQUE (pool_id, recorded_date) added (migration 0027 — dedup key for the new daily TVL-snapshot scheduler, the table's first writer; powers the investor Performance tab's TVL chart); fund_data_snapshots / fund_data_cache renamed to external_pool_data_snapshots / external_pool_data_cache with fund_id→pool_id (migration 0028 — owner key is always a pool; pool-level mapping only, fund-level fallback dropped, funds.external_* deprecated); audit_feed view added (migration 0030 — unified activity/audit feed, UNION of activity_events human actions + economic event-log tables; GET /activity-events reads it; v3-41); notification_logs.payload + notification_logs.read_at added (migration 0032 — structured notification copy: payload->'in_app' feeds the in-app feed, payload->'email' feeds the SES send worker; read_at backs the in-app unread badge; Notification PRD, v3-44); users.email_verified_at / pending_email / email_verification_token / email_verification_expires_at added (migration 0033 — investor email registration + verification so material/legal notices reach a real deliverable address; wallet-login investors otherwise have a synthetic <address>@wallet.aset.io; v3-45); pool_governance_changes.change_type CHECK extended to allow JURISDICTION_WHITELIST (migration 0034 — jurisdiction whitelist is a 🟡 timelocked field, propose/execute/cancel via POST /pools/{id}/governance; new_value is JSON {country,allowed}; v3-43); pool_governance_changes.change_type CHECK further extended to allow REDEMPTION_GATING (migration 0035 — per-epoch redemption fill cap, 🟡 timelocked; new_value is a 0–100 percent; v3-43); pools.fx_rate_source added (migration 0036 — FIXED|EXTERNAL_FEED, how operating_currency→USD FX is determined; backend NAV-conversion only, DRAFT-only; v3-43); pools.collateral_type + the collateral_type enum dropped (migration 0037 — collateral is display-only collateral_description + collateral_ratio; risk signal moved to auto-computed Risk Tier; v3-35/v3-07); portfolio_positions.claimable_yield + claimable_yield_synced_at added (migration 0038 — yield reconciler mirrors on-chain pendingYield (settled + unsettled) into the DB so the FE shows an accurate claimable total instead of accrued_yield alone; v3-51); platform_settings table added (migration 0039 — server-persisted admin platform config: block-explorer URLs + auto-refresh interval as a key→JSONB blob, replacing the localStorage-only stub; GET/PUT /admin/settings); users.first_name + users.last_name added (migration 0040 — investor name captured from the already-verified SumSub applicant at KYC GREEN; GET /users/me returns the combined display name instead of company_name-only, so the app shows a real name instead of "Anonymous Investor"; v3 PII = mirror-only, no new collection); admin_user_wallets table added (migration 0041 — FM-proven signing wallets for non-custodial FM signing: a Fund Manager proves wallet ownership via SIWE and binds N wallets, used as the pre-sign guard / display / audit set while authorization SoT stays on-chain msg.sender == pool.fund_wallet; fm-wallet-signing-spec Phase 1); yield_distributions.deposit_tx_hash added (migration 0042 — FM client-signed depositYield tx; when present the distribution row is created in PROCESSING (reusing the unused yield_status value, no enum change) and a follow-up POST /yield-distributions/{id}/distribute runs the server-key distributeYield + withdrawFees after the deposit is indexed in yield_funding_events; fm-wallet-signing-spec Phase 2); external_pool_data_snapshots.total_rni → realized_income and total_npl → cumulative_impairment (migration 0043 — partner fund-data metrics renamed to asset-class-neutral names: "Realized Net Income" was mislabeled "Net" (the value is gross of fees) and "Non-Performing Loans" assumed a 90d-default loan book; both are ingest+display-only, not used in NAV (DPD-based) or on-chain; the Joob wire fields totalRni/totalNpl are unchanged — the provider mapper is the seam; v3-55); notification_status gained ESCALATED (migration 0052 — POST /notification-logs/{id}/escalate marks a FAILED notification terminal without flipping it to DELIVERED, which had inflated the delivered count / hidden the failure from audit metrics); 0053 — dropped 5 exactly-duplicate secondary indexes (idx_activity_events_created, idx_auth_nonces_wallet_address, idx_external_pool_data_snapshots_pool_date, idx_fund_members_fund_id, idx_users_sumsub_applicant_id), each redundant with a kept UNIQUE constraint / PRIMARY KEY / canonically-named btree (P-7 perf cleanup; query planning unaffected, table/enum counts unchanged); 0054 — enabled Row Level Security on admin_user_wallets, the only table still without RLS (deny-all for anon, no policies; the service_role key used by Lambdas bypasses RLS, so server access is unchanged; S-7 / get_advisors security); 0055 — investor_activity view added (P-5 — unified investor timeline, UNION of deposits INVEST + redemption_requests REDEEM + yield_distribution_investors YIELD + yield_claims CLAIM into one common column set; GET /investor-activity reads it investor-scoped by user_id, replacing 4 client-side fetches; security_invoker = true; no table/enum count change); 0057 — notification_logs.claimed_at added (S-14 — the SES send worker atomically CLAIMs rows before sending so two overlapping cron runs get disjoint work sets and never double-send; markFailed releases the claim so retries keep the normal cadence); 0058 — uq_redemption_requests_onchain_id partial unique index on (pool_id, on_chain_request_id) WHERE on_chain_request_id IS NOT NULL (S-22 — one on-chain redemption request maps to at most one mirrored DB row, preventing double-payout / misattribution); 0059 — positive-amount CHECK constraints on the fund tables (S-26 — deposits / redemption_requests / yield_distributions / yield_distribution_investors / yield_claims amounts > 0; fee / penalty / payout and loan_writeoffs >= 0; added NOT VALID so the migration skips pre-existing rows while enforcing all new/updated rows); 0060 — investor_sessions table added (S-9 — server-side revocable investor sessions mirroring admin_sessions but keyed on users.id; the row id rides in the investor JWT as sid and the token is honored only while the row exists, so logout / a future revoke can kill a leaked refresh token; RLS on with no policies, service_role bypasses; +1 table → 41); 0061 — admin_users.failed_login_attempts + locked_until added (S-11 — account-scoped brute-force lockout on /auth/admin-login: increments on each bad password, locks 15 min at the 5-attempt threshold, resets on success; IP-scoped WAF is a separate track). 0062 — added external_pool_data_snapshots.total_subscribed (fund-reported cumulative committed principal, nullable — older snapshots predate it) and new table fund_service_providers (fund-level service providers — auditor/administrator/custodian/legal — entered via admin create/edit fund, read-only in the pool Fund Data tab; +1 table → 42; renumbered 0054→0062 to avoid collision with the security-line 0054 RLS migration); 0063 — removed the pool-level KYC/KYB required-level restriction: dropped pools.kyc_level_required + pools.requires_institutional and the KYC_LEVEL value from the pool_governance_changes.change_type CHECK. Pools no longer distinguish individual (KYC) vs institutional (KYB) investors — gating is jurisdiction + valid SBT only. The on-chain PoolConfig.requiresInstitutional field, the InstitutionalOnly gate, and the KycLevelChange governance flow were removed in the contract in the same change; the SBT individual/institution attribute (users.kyc_level / PlatformKYCSoulbound.isInstitution) is unaffected (still used for KYC onboarding + jurisdiction resolution). Safe: 0 non-default rows before drop; no table/enum count change; 0064 — added pools.enforce_jurisdiction (BOOLEAN, default false) as an explicit jurisdiction-enforcement flag mirroring the on-chain PlatformPool.enforceJurisdiction bool 1:1 (Option 1 / v3-60), replacing the implicit "non-empty whitelist = enforce" rule so DB / backend checkKycGating / on-chain agree with no drift when the whitelist is emptied; the deploy worker now activates on-chain enforcement (setEnforceJurisdiction + per-country setJurisdictionAllowed, alpha-3) when the flag is set; backfilled true for pools that already had a non-empty whitelist (behavior-preserving); no table/enum count change; 0065 — pool_governance_changes.change_type CHECK extended to allow TREASURY + ENFORCE_JURISDICTION (#13 — both on-chain 7-day timelock flows already existed but had no app path; POST /pools/{id}/governance now exposes them: TREASURY mirrors treasury_wallet, ENFORCE_JURISDICTION mirrors enforce_jurisdiction; no contract change); 0066 — investor_activity view: appended lp_filled column (REDEEM branch = redemption_requests.lp_filled, NULL for INVEST/YIELD/CLAIM) so the investor app can pre-gate the epoch Claim action — a request can settle at 0% fill (fully gated / rolled over) with nothing to claim; CREATE OR REPLACE VIEW append-only, no table/enum count change (#16); 0067 — pool-mgmt fee split: added pools.fund_fee_wallet (pool-mgmt fee destination, contract fundFeeWallet, ≠ fund_wallet) + yield_distributions.pool_mgmt_fee_amount, and renamed the net_yield_fee_config key admin_fee_pct→platform_yield_take_pct on existing rows (+ new spc_mgmt_pct/pool_mgmt_pct keys, schemaless JSONB); the yield Lambda now computes platform take (gross×pct) + spc/pool mgmt (tvl×pct×days/365) off-chain and splits withdrawFees(treasuryAmount, poolMgmtAmount) on-chain (Pool mgmt → fund_fee_wallet); perf deferred; no table/enum count change; 0068 — ratio fields unified to integer basis points (bps): renamed pools.reserve_percentage→reserve_bps (percent NUMERIC → INTEGER NOT NULL DEFAULT 1000, CHECK 0..10000), pools.redemption_gating_pct→redemption_gating_bps (percent NUMERIC → INTEGER, CHECK null or 0..10000), pools.penalty_rate→penalty_rate_bps (0–1 fraction NUMERIC → INTEGER, CHECK null or 0..10000); 1% = 100 bps, 100% = 10000 bps. Old values migrated ×100 (reserve/gating) or ×10000 (penalty_rate); matches the on-chain amount × bps / 10000 convention. pool_governance_changes.change_type enum values (RESERVE_BPS, REDEMPTION_GATING) unchanged — only their new_value is now bps. No table/enum count change; 0069 — consolidated the two redundant early-penalty bps columns: dropped pools.early_redemption_penalty_bps; pools.penalty_rate_bps is now the single early-redemption-penalty field, and the backend feeds the on-chain RedemptionConfig.penaltyRateBps from it for all penalty types. The separate admin early-penalty input was removed. No table/enum count change; 0070 — DB↔contract name alignment (columns renamed, values unchanged): pools.redemption_epoch_days→epoch_duration_days (matches on-chain epochDurationDays) and pools.kyc_jurisdiction_whitelist→jurisdiction_whitelist (matches on-chain jurisdictionWhitelist). No type/semantics change; no table/enum count change. (The paired contract-side renames hardCap→capacity, flatFeeAmount→penaltyFeeAmount, noticePeriodDays→standardRedemptionDays are struct-field-only and touch no DB column — see 17-changelog.) See the v3.0 Migration Summary at the bottom.

🔐 Authentication (4 tables)

ℹ️ Investor-Side Schema

Authentication tables (users, auth_nonces, wallets) are Investor-side schema. Admin backend schema starts from Admin Management section below.

👤 users

Column	Type
id	UUID PK
email	TEXT UNIQUE (NULL until verified — 0047)
email_verified_at	TIMESTAMPTZ
pending_email	TEXT
email_verification_token	TEXT (sha-256 hash of the emailed token — S-42)
email_verification_expires_at	TIMESTAMPTZ
role	user_role DEFAULT 'GUEST'
investor_status	investor_status NOT NULL DEFAULT 'RETAIL' (0072 — replaces investor_tier; Status Gate result)
qualification_country	TEXT (0072 — ISO alpha-3 the PROFESSIONAL status was assessed under)
qualification_basis	TEXT (0072 — basis code, open set: SG_AI / HK_PI / EU_MIFID_PRO …)
qualification_verified_by	UUID → FK admin_users(id) (0072 — audit: admin who granted)
qualification_verified_at	TIMESTAMPTZ (0072 — audit: when granted)
qualification_verified_reason	TEXT (0072 — audit: evidence / note)
qualification_expires_at	TIMESTAMPTZ (0072 — re-verification expiry, rides on SBT expiry)
sumsub_applicant_id	TEXT UNIQUE
kyc_level	kyc_level
kyc_status	kyc_status DEFAULT 'NOT_STARTED'
sbt_status	sbt_status DEFAULT 'NOT_MINTED'
sbt_tx_hash	TEXT
sbt_token_id	INTEGER
sbt_error	TEXT
country	TEXT
risk_level	TEXT
first_name	TEXT — individual investor name, mirrored from verified SumSub applicant at KYC GREEN (0040)
last_name	TEXT — individual investor name (0040)
company_name	TEXT — KYB entity name (individuals use first_name/last_name)
company_registration_number	TEXT
company_country	TEXT
kyc_source	TEXT DEFAULT 'SELF' CHECK (SELF/PARTNER_REUSED) — v3-17
sbt_expires_at	TIMESTAMPTZ — v3-19 SBT validity mirror
sbt_mint_queued_at	TIMESTAMPTZ — in-flight mint marker (0045): stamped on enqueue, cleared on terminal write; fresh (<10 min) hides retry UIs
created_at	TIMESTAMPTZ DEFAULT now()
updated_at	TIMESTAMPTZ DEFAULT now()

🔑 auth_nonces

Column	Type
id	UUID PK
wallet_address	TEXT NOT NULL UNIQUE
nonce	TEXT NOT NULL UNIQUE
expires_at	TIMESTAMPTZ NOT NULL
used_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ DEFAULT now()

💳 wallets

Column	Type
id	UUID PK
user_id	FK → users (CASCADE)
address	TEXT NOT NULL
chain_id	chain_id NOT NULL
label	TEXT
created_at	TIMESTAMPTZ DEFAULT now()

UNIQUE(address, chain_id)

🎫 investor_sessions (0060)

Column	Type
id	UUID PK
user_id	FK → users (CASCADE)
device_info	TEXT
ip_address	INET
last_seen_at	TIMESTAMPTZ DEFAULT now()
expires_at	TIMESTAMPTZ (created + 30d, mirrors the refresh-token lifetime)
created_at	TIMESTAMPTZ NOT NULL DEFAULT now()

IDX: user_id · RLS on with no policies — only the service key (Lambdas) touches it, and service_role bypasses RLS.

Revocable investor sessions (S-9). Investor auth is stateless JWT, so without this a leaked 30-day refresh token could keep minting access tokens with no way to kill it. Each SIWE login inserts a row; its id rides in the token as sid and the token is honored only while the row exists, so logout is a delete. The investor-side mirror of admin_sessions, keyed on users.id.

---

🛡️ Admin Management (4 tables)

👤 admin_users

Column	Type
id	UUID PK
email	TEXT NOT NULL UNIQUE
name	TEXT
avatar_url	TEXT
wallet_address	TEXT
role	admin_role DEFAULT 'OPERATOR'
invite_code	TEXT UNIQUE
invited_by	FK → admin_users.id
last_active_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ (soft delete)
created_at	TIMESTAMPTZ DEFAULT now()
updated_at	TIMESTAMPTZ DEFAULT now()
password_hash	TEXT
auth_method	TEXT DEFAULT 'GOOGLE_OAUTH'
totp_secret	TEXT
totp_enabled	BOOLEAN NOT NULL DEFAULT false
totp_last_used_step	BIGINT
totp_enrolled_at	TIMESTAMPTZ

🔴 Soft delete keeps the email reserved

DELETE /admin-users/{id} sets deleted_at and keeps the row (soft since 2026-03-04; it was a hard delete before). admin_users_email_key is an unconditional UNIQUE (email), so a deleted account goes on owning its address — a second row for the same email is impossible, deleted or not.

Read that pairing before writing any query against this table. A duplicate check scoped to deleted_at IS NULL disagrees with the constraint about what "already exists" means, which is exactly how the invite endpoint shipped an unfixable 500: the guard passed, the INSERT hit the unique index, and no retry could ever clear it.

POST /admin-users/invite therefore restores a soft-deleted row instead of inserting (audited as ADMIN_USER_RESTORE, not ADMIN_USER_INVITE). Restoring wipes the account's previous life — admin_user_permissions rows first, then password_hash + the totp_* set + the login lockout — so a revived holder cannot arrive with the access or the credentials they were removed with. Keeping the same row is deliberate: audit events and invited_by references point at its id, and a second row would split one person's history in two.

Auth

Google OAuth or password. First admin seeded via env/DB.

Password login (/super, Super Admin) requires TOTP 2FA (migration 0009_admin_totp): password success returns a 5-min mfa_token, and /auth/admin-2fa/verify exchanges it + a 6-digit authenticator code for real JWTs. Enrollment is forced on first login. totp_last_used_step stores the last accepted 30s time step to block code replay. No backup codes — recovery is a manual reset of the four totp_* columns.

🔒 admin_user_permissions

Column	Type
admin_user_id	FK → admin_users (CASCADE)
page_key	TEXT NOT NULL
granted	BOOLEAN DEFAULT false
granted_by	FK → admin_users.id
granted_at	TIMESTAMPTZ DEFAULT now()

PK(admin_user_id, page_key). Operators only; Admins have full access.

🔑 admin_user_wallets

Column	Type
id	UUID PK
admin_user_id	FK → admin_users (CASCADE)
wallet_address	TEXT NOT NULL
verified_at	TIMESTAMPTZ DEFAULT now()
created_at	TIMESTAMPTZ DEFAULT now()

Migration 0041 (B3). FM-proven signing wallets — a Fund Manager proves wallet ownership via SIWE (POST /admin/wallet/nonce → /admin/wallet/verify), one FM may hold N wallets (per-pool fund_wallet differs). Authorization SoT stays on-chain (msg.sender == pool.fund_wallet); this is the proven-wallet set for the pre-sign guard + display + audit. UNIQUE (admin_user_id, lower(wallet_address)). SoT: fm-wallet-signing-spec.

🖥️ admin_sessions

Column	Type
id	UUID PK
admin_user_id	FK → admin_users (CASCADE)
device_info	TEXT
ip_address	INET
last_seen_at	TIMESTAMPTZ DEFAULT now()
expires_at	TIMESTAMPTZ (created + 30d — 0056)
created_at	TIMESTAMPTZ DEFAULT now()

Revocable admin sessions: one row per admin login (/auth/admin-2fa/verify, /auth/admin-oauth); the row id is embedded in the issued JWTs as the sid claim. A token is honored only while its row exists and expires_at has not passed (migration 0056, S-43 — absolute expiry mirroring the 30d refresh-token lifetime; NULL is treated as non-expiring for pre-0056 rows) — /auth/refresh and the withAuth/withRole guards reject tokens whose sid row is gone or expired. "Log out of all devices" (/auth/admin/logout-all) deletes all of an admin's rows; per-device logout (/auth/admin/sessions/{id}/revoke) deletes one. last_seen_at is bumped on refresh. Login upserts on (admin_user_id, device_info, ip_address) (idx_admin_sessions_device_unique, NULLS NOT DISTINCT, 0079) — repeated logins from the same device+IP reuse one row/sid instead of duplicating the session in the list. investor_sessions has the matching idx_investor_sessions_device_unique on (user_id, device_info, ip_address).

Expiry is applied on read as well as on auth (0102): GET /auth/admin/sessions filters expires_at with the same condition as the auth guard, so an expired row is no longer listed as an active device (it used to appear with a working-looking "Log out" button while the token it backed was already dead). There is no cleanup scheduler — /auth/refresh deletes the caller's own expired rows so they don't accumulate. is_current was dropped (0102): "this device" is derived per request from the caller's token sid (auth.get.admin-sessions) and nothing ever wrote the column; the API response keeps the derived field of the same name.

---

🏢 Fund Management (4 tables)

🏦 funds

Column	Type
id	UUID PK
name	TEXT NOT NULL
verified	BOOLEAN DEFAULT false
description	TEXT
established	SMALLINT
total_originated	NUMERIC DEFAULT 0
website_url	TEXT
logo_url	TEXT
primary_contact_name	TEXT
primary_contact_email	TEXT
status	fund_status DEFAULT 'ACTIVE'
notification_health	TEXT DEFAULT 'HEALTHY'
external_provider	TEXT — fund-data provider key (e.g. 'JOOB')
external_fund_id	TEXT — provider-side fund id
created_at	TIMESTAMPTZ DEFAULT now()
updated_at	TIMESTAMPTZ DEFAULT now()

👥 fund_members

Column	Type
id	UUID PK
fund_id	FK → funds (CASCADE)
wallet_address	TEXT
email	TEXT NOT NULL
is_primary	BOOLEAN DEFAULT false — exactly one ACTIVE primary per fund (0044); first accepted member auto-becomes primary; admin-managed, primary is not removable/deactivatable
name	TEXT
status	TEXT DEFAULT 'ACTIVE'
joined_at	TIMESTAMPTZ DEFAULT now()
created_at	TIMESTAMPTZ DEFAULT now()

UNIQUE(fund_id, wallet_address), UNIQUE(fund_id, email), UNIQUE(fund_id) WHERE is_primary AND status='ACTIVE' (uq_fund_members_one_primary, 0044)

📨 fund_invites

Column	Type
id	UUID PK
fund_id	FK → funds (CASCADE)
email	TEXT NOT NULL
invite_code	TEXT NOT NULL UNIQUE
invited_by	FK → admin_users.id
status	TEXT DEFAULT 'PENDING'
accepted_at	TIMESTAMPTZ
expires_at	TIMESTAMPTZ DEFAULT now() + 7 days
created_at	TIMESTAMPTZ DEFAULT now()

UNIQUE(fund_id, email)

🧾 fund_service_providers

Fund-level service providers (auditor, administrator, custodian, legal, etc.), entered via admin create/edit fund and surfaced read-only in the pool Fund Data tab (0054). Providers belong to the manager/fund and are inherited by its pools.

Column	Type
id	UUID PK
fund_id	FK → funds (CASCADE)
role	TEXT NOT NULL — e.g. Auditor, Administrator, Custodian, Legal
name	TEXT NOT NULL
created_at	TIMESTAMPTZ DEFAULT now()

0110: RLS enabled (on, no policies). These rows are rendered on the public investor pool page, so a publishable key must not be able to enumerate or edit them.

⚠️ role is free text with no allowlist, despite an intended vocabulary. A typo (Audtior) reaches the investor-facing Fund Data tab as-is, and nothing groups two spellings of the same role. name and the array itself are also uncapped (parseServiceProviders trims but does not bound length or count).

⚠️ The full-replace write is not atomic. replaceFundServiceProviders DELETEs every row for the fund and then INSERTs the new set as two separate statements. If the insert fails the delete has already committed, so the fund's providers are gone while the handler returns "Failed to update fund service providers" — which reads as "nothing changed". Recovery is re-entering them by hand. The repo already uses Postgres functions for exactly this (increment_accrued_yield, complete_redemption_atomic); moving the pair into one is the fix. Not bundled into 0110 — it is unrelated to the notification rebuild.

INDEX(fund_id) (idx_fund_service_providers_fund_id)

---

📡 Fund Data Sync (3 tables)

External fund-data provider (e.g. Joob) history/summary mirroring for externally-mapped pools — Hybrid serving: history from DB, on-demand data via short-TTL cache. pool_id is the owner key (pools.id; mapping lives on pools.external_*), so no FK constraint (pool may be pre-deploy). Renamed from fund_data_* in migration 0028 (pool-level only; fund-level fallback dropped).

📈 external_pool_* — dropped (0197)

external_pool_data_snapshots · external_pool_data_cache · external_pool_dpd_buckets · external_pool_nonperforming and the external_pool_dpd_latest view are gone. They were the upsert-latest store for partner fund data, replaced by the three tables below.

🔴 Why they had to go rather than be extended. The provider rewrites history — confirmed live: master history carries trigger: "backfill" rows (pro-forma reconstruction of the period before the master existed), attach/detach rows whose values move when a sub-fund joins or leaves, and 20 of 225 days with a null fundValue that can be filled in later. external_pool_data_snapshots was keyed (pool_id, snapshot_date) and upserted, so each restatement destroyed the value it replaced. Two things became impossible at that moment: answering "what did the partner say when we wrote this NAV down", and re-deriving after a formula fix, because the input was gone.

loan_writeoffs was not dropped with them — see its own section. It is the schema for loan-level records the partner does not supply yet, it never had a writer, and the deferred v3-13 DPD → NAV pipeline is what would fill it.

SoT for the design: joob-feed-architecture.md.

📞 report_fetches

One row per external report API call, success and failure. "When did this last work" becomes a queryable fact instead of CloudWatch archaeology — the pipeline this replaced caught every error and returned 200 {"pools":0}, so a revoked key, a 403, a kill switch and a quiet day were indistinguishable and the feed sat frozen for two days.

Column	Type
id	BIGSERIAL PK
run_id	UUID NOT NULL — one scheduler invocation
source	TEXT NOT NULL ('JOOB')
entity_type / entity_id	TEXT — FUND/1, MASTER/2; NULL for the discovery call
endpoint	TEXT NOT NULL
envelope_code	INTEGER — 🔴 the status that matters. The provider answers HTTP 200 for logical failures and encodes the outcome in the body's code
envelope_type	TEXT (SUCCESS | VALIDATION_ERROR | FORBIDDEN …)
http_status	INTEGER — only catches an edge/proxy answering instead of the application
provider_request_id	TEXT — what the provider asks for when reporting an issue
latency_ms · response_bytes · observation_count	INTEGER
error	TEXT
started_at · finished_at	TIMESTAMPTZ NOT NULL

IDX: (source, started_at DESC) · (source, entity_type, entity_id, started_at DESC) · partial (started_at DESC) WHERE error IS NOT NULL · RLS on, no policies

🧾 report_events

The observation log. One row = "this source said this metric, for this entity, as of this business date, was this value". Append-only — a restatement is a new row, never an overwrite.

Column	Type
id	BIGSERIAL PK
source · entity_type · entity_id	TEXT NOT NULL
pool_id	UUID FK pools(id), nullable — an entity can be observed before a pool maps to it
scope	TEXT NOT NULL CHECK IN ('ENTITY','POOL') — ENTITY = the fund/master the partner reports on, POOL = attributed to us. Keeping them apart is what stops a partner's fund value being served as this pool's TVL
metric	TEXT NOT NULL — FUND_VALUE · REALIZED_INCOME · CUMULATIVE_LOSS · TOTAL_SUBSCRIBED · DPD_EXPOSURE · DPD_LOAN_COUNT · DPD_PCT_OF_OUTSTANDING · NPL_PENDING_* · NPL_WRITTEN_OFF_* · FX_RATE · OWNERSHIP_SHARE · GROSS_RETURN · SUBSCRIBED · MASTER_* · CONVERTED_RETURN + attributes (NAME · MANAGER · STATUS_CODE · STATUS_LABEL · CURRENCY_CODE · START_DATE · END_DATE · DURATION_SECONDS · SUBFUND_COUNT · COMPOSITION_VERSION · DAYS_ELAPSED)
dimension	TEXT NOT NULL DEFAULT '' — DPD bucket lower bound. NOT NULL because Postgres treats NULLs as DISTINCT in a UNIQUE, which would let the same dimensionless observation insert on every poll
unit	TEXT NOT NULL CHECK IN ('CURRENCY','RATIO','PERCENT','COUNT','DAYS','TEXT','TIMESTAMP') — 🔴 load-bearing: the NAV that came out at −1782 was 1.02bn rupiah subtracted from 573k dollars
currency	TEXT (when unit=CURRENCY)
value	NUMERIC, nullable (0196)
text_value	TEXT, nullable (0196) — CHECK exactly one of the two is set
as_of	DATE NOT NULL — business date; a month key is stored as that month's first day
as_of_grain	TEXT NOT NULL CHECK IN ('DAY','MONTH') — master DPD is monthly
observed_at	TIMESTAMPTZ NOT NULL — when WE saw it; orders restatements
qualifier	JSONB NOT NULL DEFAULT {} — the provider's own description: source / trigger / compositionVersion / computedAt / explicit / mixed
fetch_id	BIGINT NOT NULL FK report_fetches(id)
raw	JSONB — the response as received, so an unmodelled field is a re-derive rather than a migration plus a refetch
digest	TEXT NOT NULL — idempotency key, prefixed with a version

UNIQUE (source, entity_type, entity_id, metric, dimension, as_of, digest) — the analogue of the ledger's (chain_id, tx_hash, log_index): a value already recorded is a no-op on re-read, a restated one hashes differently and lands as a new row. Deliberately not a plain UNIQUE on the identity columns, which would make a restatement a conflict. IDX: (pool_id, scope, metric, dimension, as_of DESC, observed_at DESC) · (source, entity_type, entity_id, as_of DESC) · (fetch_id) · RLS on, no policies

🔭 report_pool_latest

Projection: latest observation per (pool_id, scope, metric, dimension), rebuilt from report_events rather than incremented. A row here with no event behind it is a row waiting to be overwritten, so nothing else writes this table. Same columns as the log plus event_id and rebuilt_at; PK is the four key columns.

Time series need no table: DISTINCT ON (as_of) over the log is the series.

---

🏊 Pools (7 tables)

🏊 pools

Column	Type
id	UUID PK
fund_id	FK → funds.id — required (v3-26), enforced at app layer (pools.post.create); column still nullable in DB pending NOT NULL migration after no-fund_id rows are cleaned
name	TEXT NOT NULL
description	TEXT
lifecycle_status	lifecycle_status DEFAULT 'DRAFT'
is_paused	BOOLEAN DEFAULT false
category	TEXT NOT NULL → FK pool_categories(name)
capacity	NUMERIC DEFAULT 0
min_investment	NUMERIC NOT NULL
apy_rate	NUMERIC NOT NULL. 🔴 Decided 2026-08-27 (v3-154) (not yet reflected — no migration, still NOT NULL): exclusive with accrual_rate_bps by accrual mode. A TARGET pool carries apy_rate and leaves accrual_rate_bps empty; a FIXED pool the reverse
term	TEXT DEFAULT ''
eligibility_mode	eligibility_mode NOT NULL DEFAULT 'MIN_TICKET' (0072 — replaces investor_tier; STATUS = Status Gate / MIN_TICKET = Ticket Gate)
asset_count	INTEGER DEFAULT 0
chain_id	chain_id NOT NULL
accepted_currencies	currency[] DEFAULT '{USDC}'
reserve_bps	INTEGER NOT NULL DEFAULT 1000 (0068) — basis points (1% = 100 bps); CHECK 0..10000. Renamed from reserve_percentage (percent NUMERIC) in 0068
accrual_rate_bps	INTEGER NOT NULL DEFAULT 0 (0207) — annual accrual rate for the FIXED yield driver, bps; CHECK 0..10000. 🔴 Decided 2026-08-27 (v3-154) (not yet reflected): exclusive with apy_rate by accrual mode — a FIXED pool carries this one and leaves apy_rate empty. It is already bps, so a hurdle comparison against perf_hurdle_bps takes it directly, with no × 100 (why). 🔴 Create-only: deployed into PlatformPool.initialize, which has no setter, because changing the rate would rewrite liability that has already accrued (v3-131 (3)). The column is a record of what was deployed, not a control surface. DEFAULT 0 is right for existing rows (their implementations have no accrual driver at all) and is NOT a safe default for a new pool, so pools.post.create requires the field — a pool at 0 deploys cleanly, accrues nothing, and does not revert to say so
reserve_balance	NUMERIC (0088) — live on-chain reserve (human units / USD), mirrored by the indexer from PlatformPool.reserveBalance() (raw 1e6 ÷ 1e6) each run. reserve_bps is only the target ratio; this is the actual held reserve. NULL = not yet indexed (DRAFT/undeployed stay NULL; admin UI falls back to a target-only view).
lockup_days	INTEGER DEFAULT 0
lockup_label	TEXT DEFAULT ''
maturity_days	INTEGER
penalty_type	penalty_type DEFAULT 'NO_EARLY'
penalty_fee_amount	NUMERIC
penalty_rate_bps	INTEGER (0068) — basis points (1% = 100 bps); CHECK null or 0..10000. Renamed from penalty_rate (0–1 fraction NUMERIC) in 0068. Single early-redemption-penalty field: since 0069 the backend feeds the on-chain RedemptionConfig.penaltyRateBps from this for all penalty types (the redundant early_redemption_penalty_bps column was dropped)
standard_redemption_days	INTEGER DEFAULT 7
redemption_notes	TEXT
collateral_type	🗑 DROPPED — migration 0037_drop_pool_collateral_type (v3-35) — collateral is display-only; replaced by collateral_description (free text) + collateral_ratio. Risk signal = auto-computed Risk Tier (v3-25).
collateral_ratio	NUMERIC (display-only; may exceed 100%)
yield_frequency	TEXT DEFAULT 'MONTHLY'
yield_trigger	🗑 DROPPED — migration 0010_drop_yield_trigger (v3-20, applied 2026-06-12) — yield is claim-based / manual only; AUTO removed. FE + pools create/update Lambdas no longer reference it. ⚠️ Redeploy the pools Lambdas if not yet deployed.
custom_interval_value	INTEGER — migration 0011_custom_yield_interval (v3-20, applied 2026-06-12). CUSTOM yield interval value (CHECK > 0); used with custom_interval_unit when yield_frequency = 'CUSTOM'. Missing/invalid ⇒ backend falls back to MONTHLY.
custom_interval_unit	TEXT — migration 0011 (v3-20). CHECK ∈ days / weeks / months.
allow_rollover	BOOLEAN DEFAULT false. ⚠️ reinvest is not offered on any screen in MVP (2026-08-27, not yet reflected, v3-151) — column kept; the false default is what closes the path
min_reinvest_amount	NUMERIC DEFAULT 50. ⚠️ same — not offered in MVP (2026-08-27, v3-151), column kept
next_yield_due	TIMESTAMPTZ — estimated next distribution date, computed automatically (v3-20 Estimated → Funded, no manual "scheduled" step): anchor + interval(yield_frequency) where anchor = the last DISTRIBUTED row's period_end?? itsdistributed_at(rows predating derived periods) ??subscription_end_date??start_date`` (v3-145: a pool must not owe a coupon while it is still raising, so the grid starts at the offering close and start_date survives only for a pool with no close). Anchoring on the settlement time made every later due date inherit however late that settlement was, which on a fixed-term pool could push the final due date past maturity while the period was still unpaid. Recomputed (a) right after every successful distribution (yield-distributions.post.create) and (b) by the daily pools.scheduler.yield-due sweep. UTC month arithmetic with end-of-month clamp (Jan 31 + 1mo = Feb 28/29); past dates are NOT rolled forward (FE renders D+n overdue). NULL for DRAFT, and for a MATURED pool once the date would fall past its own maturity. ⚠️ CLOSED and MATURED used to be cleared outright, which removed an unpaid period from every surface at once (the due queue selects on next_yield_due IS NOT NULL, the admin list filters on yield_overdue, and the dashboard has no overdue metric) while yield-distributions.post.create went on accepting distributions for both states. A closed pool still owes its coupons and a matured pool still owes its final period; the schedule now ends by the maturity cap in resolveNextYieldDue, which self-terminates when the final settlement re-anchors past maturity. 🔴 A matured pool with a repayment plan leaves the cadence entirely (v3-147): its next date is the earliest redemption_epochs.funding_date after the anchor, so a coupon and its cycle's repayment land on the same day and a late payment shows as late instead of moving the next one. The cap runs first — a period that came due while the term was still running stays where it is, since handing it to the plan would move it forward and un-late it. Only cycles whose date the chain was actually GIVEN count; a NULL funding_date is the chain deriving one, and a derived date may not become a promise.
yield_overdue	BOOLEAN DEFAULT false — set by the daily sweep when next_yield_due < now(); reset to false on every successful distribution.
start_date	DATE
end_date	DATE — 🔴 maturity, not the offering close. resolveMaturityAt reads this as the second-choice term end for any pool with no on-chain maturity_date yet, so moving it earlier moves the pool's term and the yield-due cap drops obligations with it. The offering close is the column below (0204 / v3-141).
subscription_end_date	DATE — when the offering closes: the last day a deposit is accepted (0204). The lifecycle sweeper's ACTIVE → CLOSED pass keys on this column and nothing else. NULL = no scheduled close, which is every pool created before 0204 and every pool whose subscriptions run to the end of its term; such a pool is closed by hand or simply matures. The ceiling that keeps at least one yield period between this date and maturity is enforced by the create wizard, not by a constraint — the inline editors on the pool page write one column per request, so a CHECK spanning this and end_date would reject the first half of a legitimate two-step edit. Asked for on every pool shape; what the shape decides is the RULE, not the question — a pool with a maturity must set one and is held to the ceiling, an OPEN_ENDED pool may set one and has no ceiling to be held to (the axis is maturity_model, not redemption_type).
pool_implementation	TEXT — the PlatformPool implementation this pool's proxy is pinned to, read from factory.poolImplementationFixed() at deploy (0205 / v3-142). 🔴 Immutable for the pool's lifetime. A pool is a Clones proxy with the target baked into its bytecode, and the factory's poolImplementation is immutable, so two pools with identical config can behave differently and a capability its implementation lacks can never be added. Read it through canReopenOffering (@aset/types) rather than comparing addresses by hand: the rule is a CLOSED historical list of implementations that CANNOT, so a future redeploy needs no code change and cannot silently withdraw a capability. 🔴 Three states since 2026-08-31 (W-M). 0x… is the generation; 'UNKNOWN' is a pool that IS deployed whose read failed, and it raises pool_generation_unreadable; NULL is a pool that has not been deployed (or predates 0205). 'UNKNOWN' exists because NULL used to carry the middle case too — the read is non-fatal by design, so its failure arrived silently, and it did: the getter was renamed, the old name reverted rather than returning zero, and every pool deployed afterwards recorded nothing.
pool_factory	TEXT — the factory that created this pool, i.e. what FACTORY_ADDRESS_{chainId} held at deploy (0205). Not redundant with the column above: that one says what the pool can do, this one says which factory the backend was actually pointed at. After a factory redeploy the env swap and API redeploy can be missed silently, and new pools then keep coming out of the old implementation with nothing looking wrong — this is the first thing that would show it.
display_after_close	BOOLEAN DEFAULT true
published_at	TIMESTAMPTZ
tvl	NUMERIC DEFAULT 0
nav_per_token	NUMERIC(18,6) NOT NULL — 6-dec fixed point, on-chain NAV_PRECISION = 1e6
has_pending_nav_update	BOOLEAN DEFAULT false
total_yield_distributed	NUMERIC DEFAULT 0 (deprecated 0095 — never written by any handler/trigger; platform_stats.total_yield now derives from yield_distributions)
investor_count	INTEGER DEFAULT 0 (deprecated — stale, never maintained; GET /pools + platform_stats derive the count live from portfolio_positions)
last_nav_update	TIMESTAMPTZ
avg_asset_size	NUMERIC
avg_tenor	TEXT
historical_default_rate	NUMERIC
npl_threshold_days	INTEGER NOT NULL DEFAULT 90 (a loan past this DPD is non-performing; npl_ratio threshold; 0073/v3-72)
write_off_policy	TEXT (admin-only: DPD past which a loan is written off; not shown to investors; 0073/v3-72)
npl_threshold_days	INTEGER (fund NPL definition — DPD at which a loan is non-performing; default 90)
write_off_policy	TEXT (fund's discretionary write-off policy, e.g. "write off at 180 DPD")
tagline	TEXT (0071 — short hero summary on the pool page; distinct from description)
additional_disclosures	JSONB (0071 — per-pool risk disclosures [{title, body}], appended below platform boilerplate)
equity_buffer_rule	TEXT, nullable (0077) — prose describing the R6 first-loss layer, rendered to investors as "Manager first-loss commitment". Since 0118 the layer itself is structured (buffer_rate_bps/buffer_direction/buffer_basis), so this is its description, not its definition: equity_buffer_prose_requires_layer (0121) refuses the text unless buffer_rate_bps > 0. Not exposed in the admin create wizard while every pool runs at rate 0
pool_address	TEXT
lp_token_address	TEXT
deploy_status	TEXT DEFAULT 'NOT_DEPLOYED'
deploy_error	TEXT
deploy_tx_hash	TEXT
operating_currency	TEXT
fx_rate	NUMERIC
supported_chains	INTEGER[] DEFAULT '{8453}'
primary_chain_id	INTEGER DEFAULT 8453
external_provider	TEXT — pool-level fund-data provider override
external_fund_id	TEXT
custody_mode	TEXT NOT NULL DEFAULT 'PLATFORM', CHECK IN ('PLATFORM','MIRROR') — 0199. MIRROR = the positions exist outside the platform and we mirror them from a partner report: no capital path in or out (deposits · reinvest · redemption requests · yield claims all refuse), while the read model stays identical to any other pool's. ⚠️ NOT is_showcase, which means pre-launch preview and collapses the pool to an Overview-only "Coming soon" page, hiding the Fund Data tab
external_entity_type	TEXT NOT NULL DEFAULT 'FUND', CHECK IN ('FUND','MASTER') — 0194. Which provider-side entity external_fund_id names: FUND → /api/guest/fund/{id}, MASTER → /api/guest/master/{id}. Switching to MASTER changes what the reported value covers (a master consolidates every active sub-fund, including ones this pool has no direct grant to), so it is a deliberate config decision, not a default
net_yield_fee_config	JSONB (0067; bps 0075) — {platform_yield_take_bps, spc_mgmt_bps, pool_mgmt_bps, perf_fee_bps, perf_hurdle_bps}; platform_yield_take = gross×bps/10000, spc/pool mgmt = tvl×bps/10000×days/365 (spc→treasury, pool→fund_fee_wallet), perf deferred; NULL → net = gross
fund_wallet	TEXT — partner-remainder release target (contract fundWallet)
treasury_wallet	TEXT — settleYield treasury fee target
fund_fee_wallet	TEXT (0067) — pool-mgmt fee target (contract fundFeeWallet), ≠ fund_wallet
is_showcase	BOOLEAN DEFAULT false
maturity_model	maturity_model DEFAULT 'FIXED_TERM'
jurisdiction_whitelist	TEXT[] DEFAULT '{}' — ISO 3166-1 alpha-3 (v3-60; renamed from kyc_jurisdiction_whitelist in 0070 to match on-chain jurisdictionWhitelist)
enforce_jurisdiction	BOOLEAN DEFAULT false (0064) — mirrors on-chain enforceJurisdiction; true = restrict to whitelist
tranche_group_id	UUID (v3-14, NULL = standalone)
tranche_role	tranche_role enum (v3-14)
partner_id	TEXT
collateral_description	TEXT (v3-07)
is_hidden	BOOLEAN NOT NULL DEFAULT false — 0123. Hides the pool from the default admin list only (?include_hidden=true to include). No capability change, no on-chain component, and no effect on investor surfaces — a holder must still be able to reach the pool to redeem. Independent of deleted_at and is_paused; toggleable in any lifecycle stage
is_emergency_frozen	BOOLEAN DEFAULT false (v3-05 hard freeze)
freeze_started_at	TIMESTAMPTZ (0023/v3-28; on-chain freezeStartedAt mirror, NULL = not frozen). Anchors the deposit halt and the 7-day auto-expiry only — restamped on every re-freeze, by design
exit_window_anchor	TIMESTAMPTZ (0208) — anchors the 72h exit block, mirroring on-chain exitWindowAnchor. Advances only after two full windows, so exits can never be blocked for more than 72h out of any 144h whatever pattern the freezes arrive in. NOT restamped on re-freeze, NOT cleared on unfreeze. NULL = not mirrored → falls back to freeze_started_at
redemption_gating_bps	INTEGER (0068) — basis points (1% = 100 bps); CHECK null or 0..10000. Renamed from redemption_gating_pct (percent NUMERIC) in 0068. ⚠️ deprecated — not included in MVP (2026-08-27, not yet reflected, v3-150): removed from the screens and the API, column kept
apy_disclosure	TEXT
target_size_max	NUMERIC
wind_down_proposed_at	TIMESTAMPTZ (v3-06)
wind_down_executed_at	TIMESTAMPTZ (v3-06)
impairment_proposed_at	TIMESTAMPTZ (v3-12)
junior_depleted_at	TIMESTAMPTZ, nullable — 0116. When a loss waterfall wiped the junior tranche (wipedOut). NULL = never. Written by tranche.post.writedown in the same UPDATE as impairment_proposed_at, never auto-cleared. Exists so operator copy can name junior depletion as the cause: impairment_proposed_at alone cannot, because impairments are also proposed by hand for unrelated reasons. Read by the admin pool-detail "Impairment proposed. Deposits still open" banner
buffer_rate_bps	INTEGER NOT NULL DEFAULT 0, CHECK 0–10000 — 0118. The partner's first-loss commitment as bps of total_deposited (R6). 0 on every pool, the confirmed launch condition: at 0 the buffer term vanishes and the first realized dollar of loss reaches investors. ⚠️ Since 0122 the only exclusion is a tranche group (buffer_not_on_tranche_pools) — the manual NAV path applies it on every other standalone pool
buffer_direction	TEXT NOT NULL DEFAULT 'FIRST_LOSS', CHECK IN (FIRST_LOSS, EXCESS) — 0118. FIRST_LOSS = the partner absorbs up to the cap; EXCESS = investors absorb up to it and the partner takes only what exceeds it. Same rate, reversed liability — 5% cap and an 8% loss leaves investors with 3% or 5% depending solely on this column. Not symmetric at 0 (EXCESS with a zero cap means investors absorb nothing), which is why the default is the direction whose zero value preserves current behaviour
buffer_basis	TEXT NOT NULL DEFAULT 'GROSS', CHECK IN (GROSS, NET) — 0118. Whether the partner's reported cumulative_loss is before their absorption (GROSS, we deduct the cap) or already net of it (NET, cap forced to 0). Deducting from a net figure repeats the R8 double-count one layer up
fund_report_cadence_days	INTEGER, nullable, CHECK > 0 — 0118. The contractually committed reporting interval. NULL = none on record, and forward-looking investor copy stays hidden while NULL. Observed cadence is not a promise: dev shows 7 reports in 7 months at irregular intervals. Requires external_fund_id (cadence_requires_external_mapping, 0122) — it is the partner's obligation, and a pool with no feed receives no reports
apy_basis	TEXT NOT NULL DEFAULT 'GROSS_DEPOSIT', CHECK IN (GROSS_DEPOSIT, DEPLOYED) — 0118. Whether apy_rate is a rate on the full investor deposit or only the portion actually remitted to the partner. Under DEPLOYED the withheld share earns nothing, so the displayed rate overstates what a holder realizes. Inert while the reserve is effectively 0
lp_total_supply	NUMERIC, nullable — 0117. Indexer mirror of on-chain LP totalSupply(), the R9 denominator. ⚠️ NULL means not mirrored, which is not a supply of 0 — the formula does not read it yet
max_investment	NUMERIC
allows_us_persons	BOOLEAN DEFAULT false
redemption_type	TEXT DEFAULT 'ON_DEMAND' CHECK (FIXED_MATURITY/ON_DEMAND) — 0106 removed LIQUIDITY_WINDOWS: it was never wired (deploy always sent an empty window array, so every request reverted NotInLiquidityWindow — redemption blocked forever). The 4 Base-Sepolia QA pools that used it are retired, not relabelled: their contracts stay blocked and are unfixable (redemptionConfig is initialize-only, pools are not upgradeable). The CHECK exempts soft-deleted rows so their history survives
epoch_duration_days	NUMERIC NOT NULL DEFAULT 0 — 0021/v3-26, 0 = instant, >0 = epoch (settle every N days); renamed from redemption_epoch_days in 0070 to match on-chain epochDurationDays
nav_deviation_cap_bps	NUMERIC — 0021/v3-32, epoch anomaly auto-hold (NAV deviation cap), nullable
nav_staleness_seconds	NUMERIC — 0021/v3-32, epoch anomaly auto-hold (NAV staleness), nullable
epoch_schedule_type	TEXT CHECK (MONTHLY/QUARTERLY), nullable — 0104/v3-93 epoch schedule. The period the C2 fail-open default advances by when the admin enters no funding date; coexists with epoch_duration_days (which keeps the instant-vs-epoch switch + the duration itself)
funding_anchor_date	DATE, nullable — 0104/v3-93 anchor the cycle boundaries are reverse-derived from (cutoff = fundingDate − recall_lead_days, windowOpen = cutoff − request_window_days). ⚠️ its live column comment still describes the superseded v3-91 model (boundary(n)=anchor + n*duration) — see the note in 0104
recall_lead_days	INTEGER CHECK (NULL OR ≥ 0), nullable — 0104/v3-93 request-cutoff → funding-arrival lead time; absorbs the old notice period / settlement delay. executeEpoch is gated on cutoff + recall_lead_days
request_window_days	INTEGER CHECK (NULL OR ≥ 0), nullable — 0104/v3-93 Model B request-window length. Load-bearing: requests are accepted only inside it (see v3-93)
epoch_cycle_mode	TEXT NOT NULL DEFAULT 'SEMI_AUTO' CHECK (SEMI_AUTO/AUTO) — 0104/v3-93; 0105 scoped it to an off-chain reminder gate. 🔴 DEPRECATED, read by no runtime path — 0192/v3-140 (applied 2026-08-18, comment-only). It was: SEMI_AUTO opts into the "confirm the next funding date" nudge before the request window opens (after which setEpochFundingDate reverts), AUTO opts out. It never changed on-chain behaviour (the schedule is C2 fail-open either way), and AUTO turned off the only thing watching for a cycle nobody had dated without appointing a replacement — so the pool fell through to the chain's previous + epoch_duration_days derivation, which settlement then freezes into storage. The reminder now fires on state for every epoch pool. ⚠️ Kept, not dropped: every row carries a value under the NOT NULL default, and dropping the column to express "we stopped caring" would rewrite history for the pools created under the old reading. Retirement is its own slot; the honest state is present, populated, unread. 🔴 Do not reintroduce a read — "stop reminding me about this pool" is a notification preference, not a schedule column
next_funding_date	TIMESTAMPTZ, nullable — 0104/v3-93 this cycle's confirmed funding/claim date, admin-entered per cycle. Operationally mutable, deliberately separate from the immutable schedule knobs above. ⚠️ Holding a value here does not mean the cycle was confirmed — see the provenance rows below
next_funding_date_confirmed_at	TIMESTAMPTZ, nullable — 0111/v3-107 (applied). Set only after the on-chain setEpochFundingDate tx confirms; nulled when settlement advances the cycle, so provenance re-arms per cycle. This column existing (with the one below) is what makes the investor-facing 확정 / 예정 badge answerable — the chain cannot answer it, because settlement materializes the fail-open date into the same on-chain slot with no event (v3-105)
next_funding_date_set_by	UUID FK → admin_users, nullable — 0111/v3-107 (applied). Who confirmed it. Sourced from the authenticated caller of the confirm endpoint, not from the chain: EpochFundingDateSet carries no actor and the tx sender is the shared ORACLE key. Pairs with the timestamp above; the badge reads 확정 iff both are present for the displayed cycle
redemption_term_epochs	INTEGER CHECK (NULL OR > 0), nullable — 0189/v3-132 (applied 2026-08-17). How many funding dates a post-maturity repayment plan covers, as a cycle count even where the wizard asks in months (months divide evenly only under the calendar basis). NULL = open-ended cycles, which is every on-request epoch pool and therefore every pool deployed before this. 🔴 NULL and "no cycles recorded" are different answers and consumers must not collapse them: the deploy's write list, the reminder and GET /pools/{id}/repayment-cycles all key off this to decide whether a pool has a plan at all. Spec name in older text: redemption_window_epochs
epoch_date_basis	TEXT CHECK (NULL OR CALENDAR/FIXED_DAYS), nullable — 0189/v3-132 (applied 2026-08-17). How the plan spaces its payouts: a fixed day of the month, or a cadence apart
epoch_roll_day	SMALLINT CHECK (NULL OR 1–28), nullable — 0189/v3-132 (applied 2026-08-17). Day of the month payouts land on under CALENDAR. Stops at 28 because February does, and every rule that covers for a missing 29th–31st moves a payout the partner agreed to. 🔴 This and epoch_date_basis are the rule, and v3-136 says what may be done with a date the rule produces: show it to a person, never write it to the chain and never let it decide a schedule. Reading them is allowed and three readers do (wizard preview, confirmation diff, the reminder's suggestion); a scheduler deriving a cycle's date from them and acting on it would restore the silent fallback this design removes. Column comment: "no runtime path may DERIVE A SCHEDULED DATE from these; a human-facing suggestion may read them."
deleted_at	TIMESTAMPTZ (soft delete)
created_at	TIMESTAMPTZ DEFAULT now()
updated_at	TIMESTAMPTZ DEFAULT now()

Resolved — the 6 epoch-schedule columns, and the lesson they left

epoch_schedule_type · funding_anchor_date · recall_lead_days · request_window_days · epoch_cycle_mode · next_funding_date were originally added straight to production through the SQL Editor (Supabase main, single project → dev and prod are the same DB), ahead of the v3-93 engine work. For a while they existed only in the live DB: no repo migration, no supabase_migrations row, and no code reading them.

Both halves are now closed. 0104_epoch_schedule_cols.sql backfills the columns (ADD COLUMN IF NOT EXISTS, a no-op against production) and schema.sql carries them, so a fresh environment comes up correct; and the engine reads them — pools.post.create / pools.patch.update accept them and pools.scheduler.epoch-funding-date drives the semi-auto cycle (v3-91 / v3-93 shipped).

The lesson worth keeping, because it outlives this instance: supabase_migrations is not a reliable record of what is applied. Anything run through the SQL Editor changes the schema without recording a history row — these 6 columns and pools.reserve_balance (0088) both did. So the (pending apply) labels in this page's header are statements of intent, not verified state. Confirm against information_schema before trusting any of them, and when applying by hand, write the history row too.

Constraints

• nav_per_token_range: nav_per_token > 0 AND nav_per_token <= 1.0
• chk_start_before_end: end_date IS NULL OR start_date IS NULL OR start_date < end_date
• chk_subscription_end_after_start (0204): subscription_end_date IS NULL OR start_date IS NULL OR start_date < subscription_end_date. Same shape as the one above. The relation to end_date is deliberately not constrained — see the column note.

Derived pool response fields (v3-20 — NOT columns)

GET /pools / GET /pools/{id} merge two derived fields (computed per request from yield_distributions, batched via idx_yield_distributions_pool_status):

• last_distribution_date = MAX(distributed_at) of DISTRIBUTED rows for the pool — a "last paid" display.
• schedule_anchor = that same row's period_end (falling back to its distributed_at, then subscription_end_date, then start_date) — the anchor for next_yield_due. Split from the line above because the two answer different questions: when a distribution was sent, versus what stretch of time it paid for. Only the second one may drive a schedule.
• next_yield_funded (boolean) = a PENDING / PROCESSING yield_distributions row exists — the Funded signal (FE renders the date solid vs muted ~ estimated). ⚠️ Limitation: in the current single-shot distribution flow this window lasts seconds; it becomes meaningful when partner depositYield is split from settleYield.

✅ v3.0 Dimension Columns — deployed

The v3.0 dimension columns above (from external_provider down to redemption_type) are live in the DB. Type corrections vs. the original design draft: partner_id is TEXT (not UUID FK), tranche_role is an enum (not TEXT CHECK), maturity_model values are FIXED_TERM / OPEN_ENDED (not REVOLVING). kyc_level_required / requires_institutional were dropped in 0063 (pools no longer distinguish KYC/KYB level). nav_data_source and tranche_structure were removed in v3-15 / v3-14 before implementation.

Deprecated Fields (v3.0)

Field	Status
pool_type	Dropped (migration 0019). AS_POOL/FUND_POOL binary removed — derived from dimensions (fund_id, fund_wallet, etc.). Enum type also dropped.
lp_issuance_model	Dropped (migration 0050). Always PLATFORM_ISSUED in v3.0 (v3-02); the lp_issuance_model enum type was dropped too.
escrow_address / receipt_address	Dropped (migration 0017). Escrow / Receipt NFT removed in v3.0 (v3-11/v3-03).
investment_blocked	Dropped (migration 0018). Superseded by is_paused (08 v3-29); "Fully Subscribed" derives from tvl >= capacity.
early_redemption_penalty_bps	Dropped (migration 0069). Redundant with penalty_rate_bps, which is now the single early-redemption-penalty field feeding the on-chain RedemptionConfig.penaltyRateBps for all penalty types.
escrow_model, yield_distribution_model, receipt_token_address	Never existed in the deployed DB (design-draft fields only).

Deprecated columns are retained for historical pools. New pools use v3.0 dimensions only.

🏷️ pool_categories

Column	Type
id	UUID PK
name	TEXT NOT NULL UNIQUE
created_at	TIMESTAMPTZ DEFAULT now()

🌐 pool_chain_deployments

Column	Type
id	UUID PK
pool_id	FK → pools (CASCADE)
chain_id	INTEGER NOT NULL
pool_contract_address	TEXT
lp_token_address	TEXT
operator_wallet	TEXT
reserve_balance	NUMERIC DEFAULT 0
total_deposited	NUMERIC DEFAULT 0
is_active	BOOLEAN DEFAULT true
deployed_at	TIMESTAMPTZ DEFAULT now()
created_at	TIMESTAMPTZ DEFAULT now()
updated_at	TIMESTAMPTZ DEFAULT now()

UNIQUE(pool_id, chain_id)

⚠️ No writer, 0 rows. Deploys write pools directly (pool_address, lp_token_address, chain_id, deploy_tx_hash), and no live pool has more than one entry in supported_chains. Recorded in 0088, 0117 and 0119, and again in 0129 after deployed_block was briefly added here by mistake. A per-chain ledger has to be built and populated before it can be used; a column on a table nobody writes is the appearance of a head start, not one.

📈 pool_tvl_history

Column	Type
id	UUID PK
pool_id	FK → pools (CASCADE)
tvl	NUMERIC NOT NULL
recorded_at	TIMESTAMPTZ DEFAULT now()
recorded_date	DATE (migration 0027)

IDX: pool_id, recorded_at DESC · UNIQUE (pool_id, recorded_date)

Written by the daily TVL-snapshot scheduler (pools.scheduler.tvl-snapshot.ts, CDK rate 24h) — the sole writer. recorded_date (UTC calendar day of recorded_at) is the dedup key: the sweep upserts one row per visible pool per day on (pool_id, recorded_date), so an at-least-once re-fire overwrites the same-day row instead of duplicating it. Before migration 0027 the table had no writer (only the GET reader + seed), so the investor Performance tab's TVL chart / Inflow-Outflow rendered empty.

🧩 pool_asset_compositions

Column	Type
id	UUID PK
pool_id	FK → pools (CASCADE)
label	TEXT NOT NULL
percentage	NUMERIC NOT NULL

Constraints

CHK: percentage >= 0 AND percentage <= 100

📄 pool_documents

Column	Type
id	UUID PK
pool_id	FK → pools (CASCADE)
name	TEXT NOT NULL
type	document_type NOT NULL
size	TEXT
storage_url	TEXT
docusign_view_url	TEXT
created_at	TIMESTAMPTZ DEFAULT now()

📦 underlying_assets

Column	Type
id	UUID PK
pool_id	FK → pools (CASCADE)
category	TEXT
external_asset_id	TEXT
borrower_name	TEXT
principal_value	NUMERIC
maturity_date	TIMESTAMPTZ
status	asset_status DEFAULT 'ACTIVE'
last_synced_at	TIMESTAMPTZ

---

💰 Deposits (1 table)

🧾 money_events

The ledger (0132). Append-only. Every projection value must be recomputable by replaying it.

Column	Type
id	BIGSERIAL PK
chain_id	chain_id NOT NULL
tx_hash	TEXT NOT NULL
log_index	INTEGER NOT NULL
block_number	BIGINT NOT NULL
occurred_at	TIMESTAMPTZ NOT NULL (block timestamp)
kind	money_event_kind NOT NULL
pool_id	FK → pools
user_id	FK → users (nullable — pool-level facts have no holder)
asset	TEXT NOT NULL
amount	NUMERIC NOT NULL
payload	JSONB NOT NULL DEFAULT '{}'
origin	TEXT NOT NULL CHECK IN ('CHAIN','REPORT','SEED') — 0198, no default. CHAIN = a contract log (keyed by chain_id/tx_hash/log_index); REPORT = derived from a partner observation by the report bridge (keyed by origin_ref, partial unique index); SEED = a migration or a hand insert. 🔴 fold.ts does not read it — a mirror pool's projection must be indistinguishable from any other pool's — but audit, display and risk may
origin_ref	TEXT — REPORT: the report_events.id. SEED: the migration number, or NULL for the 19 rows seeded outside db/migrations/. CHAIN: NULL
ingested_at	TIMESTAMPTZ NOT NULL DEFAULT now()

UNIQUE(chain_id, tx_hash, log_index) · INDEX(pool_id, chain_id, block_number, log_index) · INDEX(user_id, occurred_at DESC) WHERE user_id IS NOT NULL · INDEX(pool_id, kind, occurred_at DESC)

🔴 Replay orders by (chain_id, block_number, log_index). Never by id. id is insertion order: the API fastpath ingests a specific transaction as it is mined while the indexer sweeps for the rest minutes later, so the two orders diverge routinely rather than exceptionally. occurred_at cannot break the tie either — it is the block timestamp, identical across a block.

Why one table. A table per event type would break three things: the single idempotency key (UNIQUE (chain_id, tx_hash, log_index), which replaced four different conventions); ordering within one transaction, where deposit()'s DEPOSITED at log_index 3 and RELEASED_TO_PARTNER at 4 are the atomic split; and reconciliation, since "every movement for this pool" has to be one query.

Volume. A private-fund platform, not an exchange — events track investor actions and pool cycles. 100 pools x 500 investors x ~15 actions/year plus NAV and settlement is ~1.5M rows (~1 GB) a year; ten years is 15M rows. Small for one Postgres table, and not worth partitioning at this size.

payload holds decoded arguments, not the raw log: re-storing topics and data would double the table to keep what the decode already produced.

📐 money_positions · money_pool_state · money_tvl_daily

The projections (0133). Derived from money_events and nothing else.

money_positions — PK (pool_id, user_id): lp_balance, principal, accrued_yield, yield_debt (0177), yield_claimed, invested_at, source (0178), rebuilt_from_event_id.

money_pool_state — PK pool_id: the contract's own counters, named to match so the two reconcile by eye — total_deposited, reserve_balance, unclaimed_yield, redemption_committed, total_epoch_top_up, lp_total_supply, settled_unclaimed_lp, nav_per_token, acc_yield_per_share. (held_fund_releases was here until 0183 removed the hold-back.)

money_tvl_daily — PK (pool_id, as_of): a day's state, so a re-run overwrites rather than duplicating. Idempotent by construction rather than by a guard bolted on later.

Every column is derived. A value the replay cannot rebuild belongs in a workflow table below. The moment a projection holds something un-derivable, "diff the replay against the projection" stops being a drift check and becomes a comparison of two different things.

rebuilt_from_event_id records the last ledger row folded in, so a replay resumes instead of restarting and a stale projection is visible rather than suspected.

The backing invariant is a CHECK constraint on money_pool_state, not a comment. A write that would leave the pool promising more than it holds fails there, instead of surfacing later as a claim that reverts on a missing balance.

🗂️ money_deposit_workflow · money_redemption_workflow · money_distribution_workflow

The facts the chain does not carry (0134).

"The chain is the ledger" holds for money that MOVED. It says nothing about a risk acknowledgement, an operator's rejection and its reason, a funding deadline, or a transaction that failed — a failed transaction emits no event, so no replay can produce one.

Table	Holds
money_deposit_workflow	risk_ack, risk_acknowledged_at, failure_type, error_message
money_redemption_workflow	admin_id, admin_decided_at, rejection_reason, funding_status, funding_shortfall, funding_deadline, pending_reserve_at, failures
money_distribution_workflow	gross_amount, fee_amount, pool_mgmt_fee_amount, fee_config_applied, distributed_by, period*, apy_rate, failures

The FK to money_events is nullable, and that is the point. A rejected request, a failed deposit and an unsubmitted intent have no ledger event — which is precisely why they cannot be reconstructed. A NOT NULL reference would make these tables unable to hold the rows they exist for.

Not money_events.payload. The moment the ledger carries an admin's note, "every row is a chain fact" is false and the replay stops being a drift check.

⚠️ fee_config_applied is load-bearing. The fee split looks like gross minus net, but the contract holds no fee rates at all (0067) — the Lambda computes them off-chain and sends results, so the event carries the net and two transfers, never the rates. Recomputing later needs the net_yield_fee_config as it stood, and that config is editable. Without the snapshot an old distribution can only be re-derived under today's rates: a different number wearing the same name.

💰 deposits — dropped (0165)

Gone. Deposits are Deposited events in money_events, and the row the screens read is money_deposit_list, derived from the ledger plus money_deposit_workflow (the risk acknowledgement and failure detail no chain event carries).

Reinvestments are in that list too, on their own axis: Deposited.amount is raw stablecoin and Reinvested.yieldAmount is normalized to 18, so the view divides by the axis the decoder recorded rather than by the registry's decimals (0158).

Pre-reset deposits: 17 of the 35 were replayed into the ledger, which is every one the chain still had a decodable log for. The rest went with the legacy schema in 0174.

💼 portfolio_positions

Column	Type
id	UUID PK
user_id	FK → users.id
pool_id	FK → pools.id
tokens	NUMERIC NOT NULL
effective_value	NUMERIC NOT NULL
accrued_yield	NUMERIC DEFAULT 0
invested_at	TIMESTAMPTZ NOT NULL
created_at	TIMESTAMPTZ DEFAULT now()
updated_at	TIMESTAMPTZ DEFAULT now()
source	TEXT — State C provenance, CHECK DEPOSIT/TRANSFER_IN/SECONDARY_PURCHASE/LEGACY_SEEDED (0013/0014)
entry_price	NUMERIC — 0125: weighted-average cost per LP token (cost-basis P&L). NULL = basis unknown
last_reconciled_at	TIMESTAMPTZ — 0013 State C last on-chain reconcile
entry_tx_hash	TEXT — 0014 State C acquisition tx
claimable_yield	NUMERIC NOT NULL DEFAULT 0 — 0038 reconciled on-chain pendingYield (settled+unsettled, human USD); v3-51
claimable_yield_synced_at	TIMESTAMPTZ — 0038 last yield reconciler sync; NULL → FE falls back to accrued_yield

UNIQUE(user_id, pool_id) · CHECK portfolio_positions_source_check (0014)

Cost basis (0125). entry_price is the only field from which per-position P&L can be computed: effective_value is tokens × nav_per_token and is rewritten on every position change, so it is always the current mark. Every capital-in path now maintains a weighted average — deposit and reinvest from amount / tokens minted (the price the chain actually charged, which during a queued NAV decrease is the announced NAV, not nav_per_token — v3-111), transfer-in marked at the pool NAV. Partial redemption leaves it alone; a full exit deletes the row. An unknown basis stays NULL and surfaces as no P&L, never as a zero-cost gain. Documented nav_at_investment never existed and is not being added.

State C (docs 21-holder-verification): both deposit paths tag a new position source='DEPOSIT' — the indexer writer and, since 0126, process_deposit_atomic (insert only; a top-up does not change provenance). The indexer's LP holder↔holder Transfer writer reconciles positions to the wallet's absolute on-chain balanceOf() (not incremental) — recipient upsert (new row → source='TRANSFER_IN', entry_price, entry_tx_hash), sender sync. Absolute-balance writes make reorg re-processing idempotent (no ledger needed).

---

🔄 Redemptions (3 tables)

🔄 redemption_requests — dropped (0165)

Gone. A redemption is its chain events — RedemptionRequested, RedemptionFunded, RedemptionCompleted, RedemptionClaimed — and money_redemption_list derives the row from them, joined to money_redemption_workflow for the decisions the chain does not make (who approved, the rejection reason, the funding deadline, the shortfall).

Status is derived from which events exist, so a redemption is COMPLETED because the ledger holds its completion. Three of those derivations are worth knowing:

• amount is LP tokens, and never null (0153). It was briefly lp × nav, a USD figure under an LP label, which reads as correct for as long as NAV is 1.0.
• Epoch fills are the sum of RedemptionClaimed; COMPLETED against PARTIALLY_FILLED is filled LP compared to requested LP (0155). RedemptionRolledOver used to decide that by downgrading a stamp written moments earlier in the same transaction.
• REJECTED covers a cancellation too, split by failure_type (INVESTOR_CANCELLED against the operator's category), so a withdrawal is never summed with a refusal (0162).

epoch_id is the one fact the ledger cannot rebuild — RedemptionRequested does not carry it and acceptingEpochAt returns a different cycle either side of the cutoff — so it is read from the chain at request time and stored on the workflow row (0154).

Pre-reset redemptions: 8 of 11 were replayed into the ledger. One of the three that were not is worth knowing about — a QUEUED request on 0723 Epoch 1 emitted before RedemptionRequested was widened, so its topic0 does not match the current ABI and the live pipeline cannot decode it.

🔄 redemption_epochs

0021 / v3-26. Per-pool epoch settlement ledger — one row per (pool, on-chain epochId). Mirrors the EpochSettled event: the fill ratio and forward-pricing settle NAV that drive every claimRedemption in that epoch. Only the indexer / Lambdas write it (RLS on, no policies).

🔴 The line 0189 and 0190 draw together: the plan lives on pools, what happened lives on redemption_epochs

pools.redemption_term_epochs / epoch_date_basis / epoch_roll_day are the rule an operator agreed to at creation. redemption_epochs.funding_date is the record of a transaction that landed. Putting an intended date in this table would collapse the distinction the whole design rests on: funding_date IS NULL is what the reminder (v3-135) and GET /pools/{id}/repayment-cycles read to mean nobody set this cycle, and if it could also mean someone planned to, neither could answer honestly.

Column	Type
id	UUID PK
pool_id	FK → pools.id (ON DELETE CASCADE)
epoch_index	INTEGER NOT NULL — on-chain epochId
settlement_allowed_at	TIMESTAMPTZ — 0112 (applied to dev 2026-08-03), renamed from epoch_end_at. Earliest moment executeEpoch may settle this cycle: the funding/claim date including the setEpochSettleAfter delay and its fundingDate + recall_lead_days cap. Mirrors PoolCommonLib.settlementAllowedAt (currentEpochEndsAt()); written by the indexer only. ⚠️ NOT the request deadline — requests close at the cutoff, recall_lead_days earlier, which is derived from the chain (readEpochSchedule) and deliberately not stored. Kept as a column rather than moved to the chain because the investor_activity view feeds next_settle_at from it and a SQL view cannot call an RPC
epoch_start_at	Dropped in 0112 (applied). Stored the moment the PREVIOUS cycle settled — already on that cycle's own row as settled_at — and had no reader anywhere. The name also invited the reading it must never have: under Model B the request window opens at cutoff − request_window_days, unrelated to the previous settlement
total_demand_lp	NUMERIC — total redemption demand (LP)
fill_ratio	NUMERIC — decimal 0..1 (on-chain 1e18 / 1e18), CHECK 0..1
settled_nav	NUMERIC(18,6) — forward-pricing NAV at settlement; 6-dec fixed point (0089)
funding_date	TIMESTAMPTZ, nullable — 0190/v3-133 (applied 2026-08-17). The date the chain was actually given for this cycle. Written from EpochFundingDateSet and from nothing else. 🔴 NULL does not mean "no date": PoolCommonLib.epochFundingDateAt answers for every cycle, returning previous + epochDurationDays when nothing was stored — a date as confidently stated as a real one, and one that a settlement then freezes into storage (RedemptionLib.sol:894). This column is the only way to tell a set date from a derived one, which is why it holds observed facts only and never an intended date. ⚠️ It counts from cycle 2: cycle 1 is written by setEpochSchedule, which emits EpochScheduleSet and never EpochFundingDateSet (GovernanceLib.sol:154,171), so cycle 1 has no row by construction and counting from 1 would report every plan as one cycle short for ever
funding_shortfall	NUMERIC — 0024/v3-26. Epoch deficit (reserve+top-up short of demand); indexer mirror of EpochFundingNeeded. Powers the "Awaiting Funding $X" KPI. ⚠️ Structurally NULL on epoch pools — it and pending_reserve_at belong to the instant path's PENDING_RESERVE state, which an epoch pool never enters. A NULL here is not an indexer gap and must not be cited as evidence of one
settled_at	TIMESTAMPTZ
chain_id	chain_id — 0021 indexer provenance
tx_hash	TEXT — 0021 indexer provenance
block_number	BIGINT — 0021 indexer provenance
log_index	INTEGER — 0021 indexer provenance
created_at	TIMESTAMPTZ DEFAULT now()
updated_at	TIMESTAMPTZ

IDX: UNIQUE(pool_id, epoch_index) (upsert-on-settle + lookups), (chain_id, tx_hash, log_index) unique partial WHERE log_index IS NOT NULL (indexer idempotency). RLS ON, no policies. fill_ratio is stored as a decimal in [0,1] (e.g. 0.25 = 25%), the on-chain 1e18 fillRatio divided by 1e18.

---

🧾 redemption_fills — dropped (0165)

Gone, and it never worked. The writer that filled it was never dispatched by the indexer, so the table was always empty while looking like the per-fill record of epoch settlement.

Its rows were a per-claim copy of RedemptionClaimed, which is in the ledger. The activity feed's REDEEM_FILL branch reads that event directly (0156), and money_redemption_list sums it for lp_filled and the cumulative payout.

📊 yield_distributions

Column	Type
id	UUID PK
pool_id	FK → pools.id
total_amount	NUMERIC NOT NULL — net distributed (after fees)
gross_amount	NUMERIC — v3.0 gross deposited before fees
fee_amount	NUMERIC DEFAULT 0 — fee withheld to treasury (platform take + spc mgmt + perf)
pool_mgmt_fee_amount	NUMERIC DEFAULT 0 (0067) — pool-mgmt fee withheld to fund_fee_wallet
distributed_by	FK → admin_users.id
distributed_at	TIMESTAMPTZ DEFAULT now()
distribution_type	TEXT DEFAULT 'MANUAL'
apy_rate	NUMERIC
period_start	TIMESTAMPTZ
period_end	TIMESTAMPTZ
status	yield_status DEFAULT 'PROCESSING' + CHECK status <> 'PENDING' (0113) — see the note below
period	TEXT
investor_count	INTEGER DEFAULT 0
tx_hash	TEXT
deposit_tx_hash	TEXT — 0042 FM client-signed depositYield tx (PROCESSING → /distribute)
chain_id	chain_id — 0013 indexer provenance (NULL on legacy admin rows)
block_number	BIGINT — 0013 indexer provenance
log_index	INTEGER — 0013 indexer provenance
yield_per_share	NUMERIC — 0013 on-chain YieldDistributed yieldPerShare (1e18)
fm_notified_at	TIMESTAMPTZ
tx_submitted_at	TIMESTAMPTZ
claimed_at	TIMESTAMPTZ
distribution_started_at	TIMESTAMPTZ — 0086 (H1) /distribute concurrency claim marker (compare-and-set before the on-chain distribute so two requests can't double-run)
failure_type	TEXT
error_message	TEXT
created_at	TIMESTAMPTZ DEFAULT now()
updated_at	TIMESTAMPTZ

IDX: pool_id · status · composite (pool_id, status) — migration 0011 (v3-20), serves the pool read-model aggregate probe · (chain_id, tx_hash, log_index) unique partial — 0013 indexer idempotency 0013: the on-chain indexer upserts a DISTRIBUTED row on YieldDistributed (the fix for empty-table → perpetual-overdue) and recomputes pools.next_yield_due / clears yield_overdue. Reconciles with the admin POST /yield-distributions row by tx_hash (update in place, no duplicate).

0113 — status may never be PENDING (v3-104)

PENDING only ever came from the legacy server-key create path (POST /yield-distributions without deposit_tx_hash), which signed depositYield itself and then settled inside the same invocation. PENDING was a sub-second insert state there — so a row sitting in it is a crash orphan, and nothing could heal it: the indexer reconciles by tx_hash, which run-distribution writes only after the settle call. The period then reads as un-distributed forever with no way to tell from the DB whether holders were paid.

That path is removed (the endpoint now 400s without deposit_tx_hash), so the recorded-but-undistributed state is PROCESSING — which carries the FM's deposit_tx_hash and has a recovery path in POST /yield-distributions/{id}/distribute. The column default moved to PROCESSING as well, because an INSERT that merely omitted status would otherwise reintroduce the orphan silently.

⚠️ The yield_status enum value is NOT dropped — the type is shared with yield_distribution_investors.status, where PENDING is legitimate (an allocation not yet claimed). The ban is a CHECK constraint on this table only.

A period awaiting recording is therefore not a row at all: it is a due row synthesized from pools.next_yield_due by GET /yield-distributions?include_due=true. Materialising it as a status='DUE' ledger row was rejected — money-less rows in this table would have to be excluded by platform_stats total yield, the investor lists, the indexer tx_hash reconcile and the /{id}/distribute guard, and one missed exclusion overstates what was paid to holders.

📊 yield_distribution_investors

Column	Type
id	UUID PK
distribution_id	FK → yield_distributions (CASCADE)
user_id	UUID
investor_address	TEXT
investor_name	TEXT
share_percentage	NUMERIC
amount	NUMERIC NOT NULL
tx_hash	TEXT
status	yield_status DEFAULT 'PENDING'
claimed_at	TIMESTAMPTZ
claim_tx_hash	TEXT
claim_type	TEXT
reinvest_lp_amount	NUMERIC
created_at	TIMESTAMPTZ DEFAULT now()

IDX: distribution_id, user_id

💎 yield_claims — dropped (0165)

Gone. A claim is a YieldClaimed event; yield_claim_list (0157) is the row the screens read.

It had two writers — the API handler through claim_yield_atomic and the indexer's writeYieldClaimed — and both are gone with it. The stored procedure took a row lock to guard a race the contract settles first: a second claim for the same yield reverts on-chain, and the lock could only ever disagree with the chain about what was left.

The old PENDING status is not reproduced. It existed for a front-end fallback that recorded a claim with no transaction behind it, and a claim row with nothing on-chain behind it asserts that money moved when none did. Every derived row has a transaction, so every row is COMPLETED.

🧭 indexer_cursor

Column	Type
chain_id	chain_id (PK)
contract_address	TEXT (PK) — sentinel 'ALL' (one cursor per chain)
last_block	BIGINT NOT NULL — last fully-processed block
updated_at	TIMESTAMPTZ DEFAULT now()

PK (chain_id, contract_address). Poller resumes from last_block + 1; advances only after a chunk fully applies (partial progress safe). First run seeds at head − confirmations; history is covered by a separate backfill path.

💵 yield_funding_events

Column	Type
id	UUID PK
pool_id	FK → pools.id (CASCADE)
chain_id	chain_id NOT NULL
depositor	TEXT NOT NULL
stablecoin	TEXT NOT NULL
gross_amount	NUMERIC NOT NULL
tx_hash	TEXT NOT NULL
block_number	BIGINT NOT NULL
log_index	INTEGER NOT NULL
deposited_at	TIMESTAMPTZ NOT NULL
created_at	TIMESTAMPTZ DEFAULT now()

UNIQUE(chain_id, tx_hash, log_index) · IDX pool_id. Durable record of partner/treasury YieldDeposited events (gross funds landing before distribution).

---

📊 NAV History & Governance (4 tables)

📊 nav_history

Column	Type
id	UUID PK
pool_id	FK → pools.id
old_nav	NUMERIC(18,6) NOT NULL — 6-dec fixed point
new_nav	NUMERIC(18,6) NOT NULL — 6-dec fixed point
reserve_consumed	NUMERIC DEFAULT 0 (v3-16) — always 0 since R8; the reserve is redemption liquidity, not a loss layer. Column retained for history
loss_amount	NUMERIC, nullable, CHECK ≥ 0 — 0122. The uncovered loss that produced this NAV (after the partner buffer), in the pool's currency — not the gross reported figure, because what moved the price is the part that reached investors. NULL when the NAV was typed directly, which is itself how you tell a human-priced row from a formula-derived one
loss_as_of	DATE, nullable — 0122. As-of date of that loss figure. NULL for a directly-entered NAV
attested_off_chain	BOOLEAN NOT NULL DEFAULT false — 0201. true when the NAV was recorded with no on-chain updateNAV, because the pool's capital is not in our custody (custody_mode = MIRROR). An attested DECREASE is applied immediately rather than queued: the 24h timelock protects an exit a mirrored pool does not have, and no contract would hold the pending value
source	TEXT DEFAULT 'admin_override' (e.g., JOOB_DPD_AUTO v3-13)
proposed_by	FK → admin_users.id
proposed_at	TIMESTAMPTZ DEFAULT now()
effective_at	TIMESTAMPTZ NOT NULL
status	nav_update_status DEFAULT 'PENDING'
reason	TEXT

Constraints

• nav_positive: new_nav > 0
• nav_max: new_nav <= 1.0
• reserve_consumed >= 0

IDX: pool_id, status

🤖 nav_proposals (0094, A4)

Automated NAV suggestion awaiting a human, decoupled from nav_history (state model B). The suggest scheduler writes an OPEN row when the computed NAV materially differs from pools.nav_per_token; approve/override funnels the value into the propose path and links the resulting nav_history row.

Column	Type	Notes
id	UUID PK
pool_id	UUID NOT NULL (FK-less, mirrors snapshots)
suggested_nav	NUMERIC NOT NULL	Scheduler-computed value
current_nav	NUMERIC NOT NULL	pools.nav_per_token at proposal time
source_snapshot_date	DATE	Snapshot the suggestion was derived from
status	TEXT DEFAULT 'OPEN' CHECK (OPEN | APPROVED | OVERRIDDEN | DISMISSED)
escalate_flag	BOOLEAN DEFAULT false	rawNav ≤ 0 → IMPAIRED/WIND_DOWN, not auto-applied
reason	TEXT
created_at	TIMESTAMPTZ DEFAULT now()
resolved_by	UUID	admin_users.id who approved/overrode
resolved_at	TIMESTAMPTZ
applied_nav_history_id	UUID	nav_history row produced on approve/override

IDX: pool_id, status · RLS on (service-key only)

📋 loan_writeoffs (v3-13)

Audit trail for automated per-loan write-offs from Joob DPD API (and future partner equivalents). Each row records when Aset Lambda applied OJK schedule to a specific underlying loan.

Column	Type	Notes
id	UUID PK
pool_id	FK → pools.id
loan_id	TEXT NOT NULL	Partner's internal loan/eNote ID (e.g., Joob loan_id 1345)
dpd_bucket	TEXT CHECK	DPK (1-90) / KURANG_LANCAR (91-120) / DIRAGUKAN (121-180) / MACET (181+)
dpd_days	INTEGER NOT NULL	Actual DPD on snapshot date
rate_applied	NUMERIC NOT NULL	OJK rate: 0.05 / 0.15 / 0.50 / 1.00
principal_amount	NUMERIC NOT NULL	Loan principal (in pool's operating_currency)
writeoff_amount	NUMERIC NOT NULL	principal_amount × rate_applied
snapshot_date	DATE NOT NULL	When DPD was measured
nav_history_id	FK → nav_history.id NULL	Linked NAV update (when aggregated writeoff hit NAV)
created_at	TIMESTAMPTZ DEFAULT now()

Constraints

CHK: dpd_bucket IN ('DPK', 'KURANG_LANCAR', 'DIRAGUKAN', 'MACET')

IDX: pool_id, snapshot_date · pool_id, loan_id · nav_history_id

🏛️ pool_governance_changes (v3-18)

7-day timelocked governance config changes (fund_wallet / reserve_bps / kyc_level).

Column	Type
id	UUID PK
pool_id	FK → pools.id
change_type	TEXT CHECK ('FUND_WALLET' | 'RESERVE_BPS' | 'JURISDICTION_WHITELIST' | 'REDEMPTION_GATING' | 'TREASURY' | 'ENFORCE_JURISDICTION')
new_value	TEXT NOT NULL
proposed_by	FK → admin_users.id
proposed_at	TIMESTAMPTZ DEFAULT now()
effective_at	TIMESTAMPTZ NOT NULL — timelock expiry
executed_at	TIMESTAMPTZ
cancelled_at	TIMESTAMPTZ
tx_hash	TEXT

IDX: pool_id · partial (pool_id, change_type) WHERE pending

---

📣 Pool Updates (2 tables, WO-6)

📣 pool_updates

Per-pool announcement feed (admin/FM-authored + SYSTEM auto-generated). Investor visibility is holder-gated in the API layer (fair-disclosure).

Column	Type
id	UUID PK
pool_id	FK → pools.id (CASCADE)
author_type	TEXT CHECK ('ADMIN' | 'FUND_MANAGER' | 'SYSTEM')
author_admin_user_id	FK → admin_users.id — NULL when SYSTEM
author_label	TEXT NOT NULL — server-set "Aset · {fund_name}" / "Aset"
title	TEXT NOT NULL
body	TEXT NOT NULL — markdown
category	TEXT CHECK ('INFO' | 'IMPORTANT' | 'MATERIAL_EVENT')
published_at	TIMESTAMPTZ DEFAULT now()
edited_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ — soft delete
created_at	TIMESTAMPTZ DEFAULT now()

IDX: partial (pool_id, published_at DESC) WHERE deleted_at IS NULL

📜 pool_update_revisions

Prior version snapshots taken before every edit of a MATERIAL_EVENT update (audit trail).

Column	Type
id	UUID PK
pool_update_id	FK → pool_updates (CASCADE)
title	TEXT NOT NULL
body	TEXT NOT NULL
category	TEXT NOT NULL
edited_by	FK → admin_users.id
created_at	TIMESTAMPTZ DEFAULT now()

IDX: pool_update_id

---

🪪 KYC / Identity (1 table)

🪪 kyc_logs

Column	Type
id	UUID PK
user_id	FK → users (CASCADE)
provider_id	TEXT
status_result	TEXT NOT NULL — one of GREEN / RED (SumSub review answer, written by apply-review) or IN_REVIEW / RESET / DEACTIVATED (webhook lifecycle, kyc.post.webhook). Not a DB enum on purpose: SumSub may introduce an answer we have not seen and logging it must not fail the webhook. The pre-v3 spellings APPROVED / REJECTED / IN_PROGRESS were normalized away in 0099 — the admin KYC drawer resolves rejection reason + attempt history by matching RED, so a second vocabulary silently under-counted attempts
risk_score	NUMERIC
reviewed_at	TIMESTAMPTZ DEFAULT now()
reject_type	reject_type
reject_reason	TEXT

The two vocabularies are not interchangeable when reading. Only GREEN/RED rows can carry a reject_type, so "how was this holder last judged?" must filter to those (kyc/review-log.ts → readLatestReviewLog). Taking the latest row of any kind was a live bug: a lifecycle log written after a RED/FINAL (e.g. applicantDeactivated) sat on top with a NULL reject_type, so isFinalRejected reported false and the terminal ban stopped applying.

IDX: (user_id, reviewed_at DESC) · (reviewed_at DESC) — 0183. Both read paths are newest-first: per holder (latest verdict + attempt history) and across all holders (the admin list pages the whole table with no filter, which previously had no usable index at all). The composite replaces the single-column user_id index, whose lookups it already covers.

---

🔔 Notifications (7 tables)

Rebuilt in migration 0110 (v3-103). notification_logs was one row doing two jobs — the in-app inbox item and the email delivery log — and is dropped, along with the notification_status and recipient_type enums. Old rows are not migrated. Three tables now answer one question each.

🔔 notification_events — what happened

Column	Type
id	UUID PK
event_key	TEXT NOT NULL
idempotency_key	TEXT NOT NULL UNIQUE
variables	JSONB NOT NULL DEFAULT '{}'
subject_type	TEXT NOT NULL
subject_id	UUID
occurred_at	TIMESTAMPTZ DEFAULT now()

One row per occurrence, independent of the audience. idempotency_key UNIQUE is the entire dedup mechanism — it replaced four separate fail-open lookups (dedupByEntity, recentlyNotified, and two hand-rolled queries) that let duplicates through whenever the lookup itself errored. Recurring alerts put their cadence in the key (epoch_gated:<pool>:<epoch>:2026-07-31) rather than passing a time window.

IDX: event_key + occurred_at DESC, subject_type + subject_id

🔔 notifications — who is told (the in-app inbox)

Column	Type
id	UUID PK
event_id	UUID NOT NULL → notification_events
recipient_kind	recipient_kind NOT NULL
recipient_id	UUID NOT NULL
variables	JSONB (per-recipient overrides)
in_app	JSONB NOT NULL
category	TEXT NOT NULL
read_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ DEFAULT now()

UNIQUE(event_id, recipient_kind, recipient_id) — a person hears about an event once, which also makes a partially-failed fan-out safe to retry.

No status column, on purpose. An inbox item exists or it does not; whether the email went out belongs to the delivery. Previously an item the investor could read carried status = 'FAILED' because the status described the email.

recipient_id is always a person — INVESTOR → users.id, ADMIN → admin_users.id (which covers FMs). The old column meant user_id / pool id / fund id depending on the row, which is why the send worker needed an 80-line resolver and why an FM notice for a pool with no fund_id silently reached nobody.

in_app is rendered at write time and stored: the feed query does not depend on the catalog, and editing copy does not rewrite a message someone already read. The email is the opposite — rendered at send time — so a copy fix reaches anything still queued, and a thousand-holder fan-out stores variables rather than a thousand copies of the same HTML.

IDX: recipient_kind + recipient_id + created_at DESC, recipient_kind + recipient_id (partial on read_at IS NULL)

📤 notification_deliveries — how it went out

Column	Type
id	UUID PK
notification_id	UUID NOT NULL → notifications
channel	notification_channel NOT NULL
destination	TEXT NOT NULL
status	delivery_status DEFAULT 'PENDING'
attempts	INTEGER NOT NULL DEFAULT 0
next_attempt_at	TIMESTAMPTZ NOT NULL DEFAULT now()
provider_message_id	TEXT
failure_type	notification_failure_type
error_message	TEXT
sent_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ DEFAULT now()

UNIQUE(notification_id, channel). Adding push or webhook later is another row, not another copy of the payload — the old single channel column is why the WEBHOOK enum value was never usable.

next_attempt_at is the backoff clock (1/5/15/60/240 min). The old worker had only a counter and released a failed row immediately, so three attempts burned in nine minutes and a brief SES outage permanently failed everything caught in it.

provider_message_id is the SES MessageId. The bounce/complaint webhook arrives minutes later carrying only that, so without it a bounce cannot be attributed to the send that caused it.

status = PENDING | SENT | BOUNCED | COMPLAINED | FAILED | SKIPPED. SENT means SES accepted it, not that it was delivered. SKIPPED is a deliberate non-send (opt-out / suppressed address) and is not a failure — which is why dashboard_alert_counts.failed_notifications counts only FAILED, replacing migration 0104's INVALID_RECIPIENT exception.

IDX: next_attempt_at (partial on PENDING/FAILED), provider_message_id (partial on NOT NULL), status + created_at DESC

🚫 email_suppressions

Column	Type
email	TEXT PK (lower-cased)
reason	suppression_reason NOT NULL
detail	TEXT
suppressed_at	TIMESTAMPTZ DEFAULT now()

Addresses SES reported as a permanent bounce or a complaint. Nothing populated this before 0110: the enum values and the SES configuration set both existed, but the set had no event destination and SendEmailCommand never named it, so the feedback was generated and discarded. A suppressed address overrides even a critical event — the mail would not arrive, and continuing damages a sending reputation shared by every email the platform sends. A transient bounce does not suppress (a full mailbox should not silence an investor forever).

⚙️ notification_preferences

Column	Type
user_type	TEXT NOT NULL
user_id	UUID NOT NULL
category	TEXT NOT NULL
email_enabled	BOOLEAN DEFAULT true

PK(user_type, user_id, category). Absent row = email enabled (opt-out model). Columns unchanged by 0110; RLS enabled there (on with no policies, like the rest of the notification tables).

⚠️ user_id has no FK: it points at users or admin_users depending on user_type, so the constraint cannot be expressed. Nothing enforces that the two agree — a bug writing ('ADMIN', <investor uuid>) would sit there matching nothing — and there is no ON DELETE CASCADE. Inert today: admin accounts are soft-deleted and there is no hard-delete path for users, so nothing is orphaned. notifications.recipient_id makes the same trade for the same reason (one feed table beats two).

category holds the notification copy registry's event_key for BOTH user_type values — ADMIN since 0101, INVESTOR since 0108. decideChannels() gates on exactly notification_preferences.category === notification_events.event_key, with no mapping layer, and only events the catalog marks optional may be stored: critical events are always sent (D4) and are rejected on write rather than coerced. Both accepted sets are derived from the notification catalog (notifications/preferences/catalog.ts — one list filtered by app since 0110, where the two near-identical modules collapsed), not hand-listed.

• ADMIN (0101) — deleted the 13 rows written under the panel's invented taxonomy (new_redemption, deposit_anomaly, kyc_submission, …). Gate: TOGGLEABLE_ADMIN_EVENT_KEYS on write, decideChannels() on send.
• INVESTOR (0108) — deleted NAV_UPDATE + POOL_PERFORMANCE. Gate: TOGGLEABLE_INVESTOR_EVENT_KEYS on write, the same decideChannels() on send. Only pool_lifecycle_active is togglable today; every other investor event is critical.

Both taxonomies failed the same way and it is worth naming: the toggles persisted and read back into the switches while every email still went out, because nothing outside this table's own CRUD handlers ever read it. A toggle not keyed on a real event_key cannot be enforced, and its failure mode is silent — do not reintroduce a settings-only vocabulary. Since 0110 the preference applies at produce time, where the channels are chosen: fan-out writes one row per person, so an opt-out means no delivery row at all and cannot appear in a failure count. That is what retired SUPPRESSED (0100), which existed only because a broadcast row resolved its audience at send time.

👣 pool_follows (0109)

Column	Type
user_id	UUID NOT NULL → users(id) ON DELETE CASCADE
pool_id	UUID NOT NULL → pools(id) ON DELETE CASCADE
created_at	TIMESTAMPTZ NOT NULL DEFAULT now()

PK(user_id, pool_id). Row exists = following; unfollow deletes it, so a re-follow is an idempotent upsert. No status column — a "was following" history has no reader and would only invite sending to it.

The opt-in list behind pool_lifecycle_active, queued by pools.scheduler.lifecycle at the UPCOMING → ACTIVE transition. It has to be an opt-in because the copy says "you asked to be notified", which is false of anyone who did not — broadcasting it would turn a product notice into an unsolicited advert. Endpoints: GET / POST / DELETE /pool-follows. See 22-notifications.

IDX: pool_id (the PK leads with user_id, but the producer asks "who follows THIS pool"). RLS on, no policies — service-key handlers scope every read/write to the caller's user_id. ⚠️ CASCADE does not fire on the soft delete that pools normally use, so the producer filters on the pool it is iterating and the list endpoint joins through to a live pool.

🔔 epoch_window_subscriptions (0115)

Column	Type
user_id	UUID NOT NULL → users(id) ON DELETE CASCADE
pool_id	UUID NOT NULL → pools(id) ON DELETE CASCADE
created_at	TIMESTAMPTZ NOT NULL DEFAULT now()

PK(user_id, pool_id). Row exists = subscribed; unsubscribe deletes it, so a re-subscribe is an idempotent upsert. Shape is deliberately identical to pool_follows above.

The opt-in list behind epoch_request_window_open, resolved by the epochWindowSubscribers audience and queued by pools.scheduler.epoch-window-open (hourly). Why a second table rather than reusing pool_follows: that one is the consent for deposits opening, this one for the redemption request window opening. Both notices say "you asked to be notified", so a shared list would make that sentence false for half of each notice's recipients. Holding a position is not this consent either — an epoch window opens on a cadence, so deriving recipients from portfolio_positions would mail every holder every cycle with no off switch. Endpoints: GET / POST / DELETE /window-subscriptions.

POST rejects non-epoch pools (epoch_duration_days = 0): an instant pool has no request window, so the notice could never fire and the toggle would be a switch wired to nothing.

IDX: pool_id (same producer access path as pool_follows). RLS on, no policies. ⚠️ Same soft-delete caveat as pool_follows.

---

⚡ Platform Config & Activity (5 tables + 1 view)

⚙️ platform_config

Column	Type
key	TEXT PK
value	TEXT NOT NULL
updated_by	FK → admin_users.id
updated_at	TIMESTAMPTZ DEFAULT now()

⚙️ platform_settings (0039)

Column	Type
key	TEXT PK
value	JSONB NOT NULL
updated_at	TIMESTAMPTZ DEFAULT now()

Server-persisted admin platform config (block-explorer URLs per chain + auto-refresh interval + the large-deposit alert threshold), replacing the prior localStorage-only stub. Whole config blob lives under key='platform_config'. API: GET/PUT /admin/settings (platform-settings.get / .put.update). Distinct from platform_config above (TEXT KV, admin-attributed) — this is a JSONB blob store for the Admin Settings UI.

The PUT rebuilds the blob from recognized fields only, so a caller must send every field it wants kept — the admin-web Platform Configuration form submits all of them together. Recognized keys: explorerUrls, refreshInterval (integer 5–3600s), largeDepositThreshold (positive integer, or null to clear). largeDepositThreshold has no default: while it is absent the large_deposit notification never fires (docs 22-notifications). No migration — it is a new key inside the existing JSONB blob.

📋 activity_events

Column	Type
id	UUID PK
event_type	TEXT NOT NULL
description	TEXT NOT NULL
status_badge	TEXT NOT NULL
actor_id	UUID
actor_name	TEXT (0081 snapshot)
actor_role	TEXT (0081 snapshot)
actor_type	TEXT ('admin'|'system', 0081)
entity_label	TEXT (0081)
before_state	JSONB (0081)
after_state	JSONB (0081)
reason	TEXT (0081)
outcome	TEXT NOT NULL DEFAULT 'success' (0081)
metadata	JSONB
related_entity_type	TEXT
related_entity_id	UUID
created_at	TIMESTAMPTZ DEFAULT now()

IDX: created_at DESC, related_entity_type + related_entity_id 0081 (v3-86): actor_name/actor_role snapshotted at write time so records survive admin rename/deletion; before_state→after_state = structured change diff; reason required on high-risk acts; outcome = 'success'|'failure' (failed attempts audited). 0082 (v3-86): append-only — a BEFORE UPDATE/DELETE trigger (activity_events_enforce_append_only) blocks all mutation for every role (BYPASSRLS does not skip triggers); DELETE is permitted only inside archive_expired_activity_events(p_cutoff) (SECURITY DEFINER, sets a txn-local flag the trigger honors). 🔴 event_type is TEXT, not an enum — the value set is the ActivityEventType union in apps/infra/lib/shared/audit/activity.ts, so adding a value is a code change, not a migration. POOL_ARCHIVE / POOL_RESTORE are pending (v3-110): archive and permanent delete currently share POOL_DELETE (distinguished only by metadata.mode), and restore is logged only as a generic POOL_UPDATE (no reason, no dedicated type), so it cannot be told apart from an ordinary edit.

📋 activity_events_archive

Cold archive (0082, v3-86) — same columns as activity_events plus archived_at TIMESTAMPTZ DEFAULT now(). Rows aged past the 5-year retention floor are moved here by archive_expired_activity_events() (atomic INSERT+DELETE), so audit records are relocated out of the hot table + audit_feed view, never destroyed. Deny-all RLS (service_role bypasses). Replaces the old hard-purge scheduler.

🪙 stablecoin_registry

Column	Type
id	UUID PK
chain_id	INTEGER NOT NULL
symbol	TEXT NOT NULL
contract_address	TEXT NOT NULL
decimals	INTEGER DEFAULT 6
is_active	BOOLEAN DEFAULT true

UNIQUE(chain_id, symbol)

---

Enums Reference

Deployed DB — 27 enum types (verified against Supabase pg_enum, 2026-08-06). redemption_status rebuilt in migration 0021 (0021/v3-26 + v3-34); enum count unchanged. collateral_type dropped in migration 0037 (v3-35). 0110 replaced the notification pair: notification_status and recipient_type were dropped, and delivery_status, recipient_kind and suppression_reason added in their place (v3-103) — net +1.

Enum	Values	Notes
admin_role	OPERATOR, ADMIN, SUPER_ADMIN, FUND_MANAGER
asset_status	ACTIVE, SETTLED, OVERDUE
chain_id	8217, 8453, 11155111, 84532	Kaia, Base, Sepolia, Base Sepolia
currency	USDC, USDT, DAI
delivery_status	PENDING, SENT, BOUNCED, COMPLAINED, FAILED, SKIPPED	0110 — per-channel delivery outcome on notification_deliveries. Replaces notification_status: SKIPPED is the opt-out case (not a failure) and BOUNCED / COMPLAINED are now live states, not V2 placeholders
deposit_status	PENDING, PROCESSING, COMPLETED, FAILED	REFUNDED dropped (0049); atomic deposits, no refund flow
document_type	PPM, SUBSCRIPTION, AUDIT, RISK_DISCLOSURE
eligibility_mode	STATUS, MIN_TICKET	0072 — pool gating mode (Status Gate / Ticket Gate)
fund_status	ACTIVE, INACTIVE
investor_status	RETAIL, PROFESSIONAL	0072 — replaces investor_tier (ACCREDITED/QP); non-US Reg S model
kyc_level	INDIVIDUAL, INSTITUTION	SBT attribute (KYC onboarding + jurisdiction resolution); pools no longer gate by level (0063)
kyc_status	NOT_STARTED, IN_REVIEW, APPROVED, REJECTED
lifecycle_status	DRAFT, UPCOMING, ACTIVE, IMPAIRED, WIND_DOWN, CLOSED, MATURED	v3.0 adds IMPAIRED (v3-12) + WIND_DOWN (v3-06); DISTRESSED removed
maturity_model	FIXED_TERM, OPEN_ENDED	v3.0
nav_update_status	PENDING, APPLIED, CANCELLED	v3.0 adds CANCELLED (admin cancel during timelock)
notification_channel	EMAIL, WEBHOOK	WEBHOOK added in 0087. ⚠️ The value exists; no outbound webhook sender does — see Not in the DB
notification_failure_type	BOUNCED, SPAM_FILTERED, SERVER_ERROR, TIMEOUT, INVALID_RECIPIENT, RATE_LIMITED
penalty_type	YIELD_BASED, PRINCIPAL_BASED, NO_EARLY, FLAT_FEE
recipient_kind	INVESTOR, ADMIN	0110 — replaces recipient_type, which also carried FM. An FM is an admin row scoped by fund, so the third value described a scope rather than a kind
redemption_status	REQUESTED, QUEUED, PARTIALLY_FILLED, PENDING_RESERVE, PROCESSING, COMPLETED, REJECTED, FAILED	0021: + QUEUED / PARTIALLY_FILLED (epoch, v3-26); − FM_ACCEPTED (v3-34). PENDING_RESERVE = instant pools (v3-04)
reject_type	RETRY, FINAL
sbt_status	NOT_MINTED, MINTED, FAILED
suppression_reason	HARD_BOUNCE, COMPLAINT, MANUAL	0110 — why an address sits on email_suppressions
tranche_role	SENIOR, MEZZANINE, JUNIOR	v3-14
transfer_source	PLATFORM, FUND
user_role	GUEST, INVESTOR
yield_status	PENDING, PROCESSING, DISTRIBUTED, FAILED	0113: PENDING is valid only for yield_distribution_investors.status; yield_distributions forbids it by CHECK. The enum value stays because both tables share the type

Not in the DB

escrow_model, yield_distribution_model, nav_data_source, tranche_structure, risk_tier, pool_category — design-draft enums that were never created (or removed before deployment).

---

Views

dashboard_alert_counts

🔴 The definition below is 0113's, and 0113 is no longer the effective one (corrected 2026-08-27). 0175_distribution_joins_the_ledger re-issued this view with CREATE OR REPLACE, and the change was not cosmetic: five of the eight counts now read the ledger views (money_deposit_list, money_distribution_list, money_redemption_list, money_redemption_workflow) rather than the tables the ledger replaced. Read the snapshot in apps/infra/db/schema.sql, which carries 0175's body and 0163's / 0171's notes on failed_redemptions; the SQL kept below is retained as the shape 0113 introduced, not as what runs.

SELECT
  COUNT(*) FROM notification_deliveries WHERE status = 'FAILED' AS failed_notifications, -- 0110: deliveries, not
                                                            -- inbox rows. BOUNCED/COMPLAINED are actioned by the
                                                            -- suppression list and SKIPPED was never attempted, so
                                                            -- only FAILED counts; this retires 0107's
                                                            -- INVALID_RECIPIENT exception.
  COUNT(*) FROM users WHERE sbt_status = 'FAILED'
    AND (sbt_mint_queued_at IS NULL OR sbt_mint_queued_at < now() - interval '10 minutes')
                                                            AS sbt_mint_failed, -- 0046: in-flight mints excluded
  COUNT(*) FROM deposits WHERE status = 'FAILED'            AS failed_deposits,
  COUNT(*) FROM redemption_requests WHERE status = 'FAILED'  AS failed_redemptions,
  COUNT(*) FROM yield_distributions WHERE status = 'FAILED'  AS failed_yield,
  COUNT(*) FROM users WHERE kyc_status = 'IN_REVIEW'           AS kyc_pending, -- 0046: NOT_STARTED excluded (nothing to review)
  COUNT(*) FROM redemption_requests WHERE status = 'REQUESTED' AS pending_redemptions,
  COUNT(*) FROM yield_distributions WHERE status = 'PROCESSING'
    AND deposit_tx_hash IS NOT NULL                          -- 0114
    AND created_at < now() - interval '24 hours'              AS stalled_yield; -- 0113: renamed
                                                            -- from pending_yield, and was
                                                            -- status = 'PENDING', now impossible.
                                                            -- Counts a stalled distribution: the
                                                            -- FM's deposit landed but nobody ran
                                                            -- POST /{id}/distribute.

0113 — pending_yield → stalled_yield (renamed) (v3-104) The alert used to count PENDING distributions, which the table now forbids — it would read 0 forever, and a permanently-quiet alert reads as a working safety net. It counts the state that does need a human instead: deposited on-chain, never distributed.

The column was renamed, not just repointed, because "pending" is what the Yield screen calls periods still to record — a different queue — so the old name pointed readers at the wrong thing. CREATE OR REPLACE VIEW cannot rename a column, so 0113 drops and recreates the view, and every reader moves in the same change: dashboard.get.stats.ts, admin-web shared/api/dashboard.ts (alerts.stalled_yield), routes/dashboard.tsx (ALERT.stalledYield → "Stalled Distributions") and routes/fund-detail.tsx ("Stalled Yield").

⚠️ The literal 24 is written down in FOUR places. STALLED_YIELD_HOURS (lib/shared/yield/stalled.ts) owns it for the backend; admin-web repeats it as STALLED_PROCESSING_HOURS (shared/api/yield-distributions.ts) for isStalledProcessing; the schema.sql snapshot states it; and the live migration states it. Change all four together, or the alert badge, its drilldown, the FM dashboard and the Yield screen report different numbers.

✅ Not a fifth: dashboard.get.stats.ts → countStalledYieldByPool IMPORTS STALLED_YIELD_HOURS rather than repeating it. The fund-scoped dashboard is a second reader, not a second copy — worth stating, because the comment in admin-web still says "all three must agree" and counts the readers instead of the copies.

🔴 In SQL the live copy is 0175, not 0113. 0113 wrote the first, 0114 narrowed it, 0175 re-issued the whole view and carried the interval across. A change made against 0113's or 0114's text would be edited into a superseded migration and would never reach the database.

✅ The guard DOES see the live shape, through schema.sql.lib/shared/yield/__tests__/stalled-threshold.test.ts reads two hard-coded paths, db/schema.sql and db/migrations/0114_stalled_yield_requires_deposit_tx.sql. The first is post-0175 and current, so a threshold drifting in the live view still fails. The 0114 half is inert rather than blinding: a migration is immutable history, so that assertion can only fail if someone rewrites the past. Tidy it up, but it is not a hole.

🔴 The hole is one level up: nothing checks that schema.sql is current. The guard's whole authority rests on every migration regenerating the snapshot, and that convention is enforced by habit alone. A migration that moves the threshold and leaves schema.sql alone passes the guard. The two are in sync today; the same gap is what let seven columns live only in migrations (see the note at the top of this page).

0114 — stalled_yield requires deposit_tx_hash IS NOT NULL 0113 relied on PROCESSING implying a confirmed deposit. The yield_distribution_stalled notification cannot: its copy states the yield "was deposited on-chain" and prints the tx hash, so it filters on the hash. Left unnarrowed, the badge counted a superset of what the notice sends (badge 3 / notices 2) and the drilldown listed rows nobody was told about. A hash-less PROCESSING row is not actionable there anyway — POST /{id}/distribute verifies that hash on-chain and 400s without it, and the UI gates its Distribute button on the same field.

Predicate-only change (same column name and type), so CREATE OR REPLACE VIEW suffices and the view keeps its privileges — unlike 0113, which had to DROP to rename a column. Moves with it: dashboard.get.stats.ts (countStalledYieldByPool), admin-web isStalledProcessing, and the new lambda/yield.scheduler.stalled.ts.

platform_stats

total_investors is the count of distinct current holders (portfolio_positions.tokens > 0), not a sum of the stale pools.investor_count column (migration 0026).

total_yield is the net (post-fee) sum of DISTRIBUTED yield_distributions (migration 0095). It previously summed pools.total_yield_distributed, which — like investor_count — no handler, trigger, or RPC ever writes, so the admin dashboard KPI was pinned at $0.

Scope (migration 0098): every non-soft-deleted pool, not just ACTIVE ones. A MATURED / CLOSED / IMPAIRED / WIND_DOWN pool still holds investor capital until it is redeemed, so the old ACTIVE-only filter made that capital (and its holders) disappear from the admin overview the moment the pool left ACTIVE. Soft-deleted pools are excluded everywhere, matching pools.get.list.

SELECT
  COALESCE(SUM(p.tvl), 0)                    AS total_tvl,
  COALESCE((
    SELECT SUM(yd.total_amount)
    FROM yield_distributions yd
    JOIN pools yp ON yp.id = yd.pool_id
    WHERE yd.status = 'DISTRIBUTED' AND yp.deleted_at IS NULL
  ), 0)                                       AS total_yield,
  COALESCE((
    SELECT COUNT(DISTINCT pp.user_id)
    FROM portfolio_positions pp
    JOIN pools ap ON ap.id = pp.pool_id
    WHERE ap.deleted_at IS NULL AND pp.tokens > 0
  ), 0)                                       AS total_investors
FROM pools p
WHERE p.deleted_at IS NULL;

audit_feed

Unified activity/audit feed (migration 0030, decision v3-41; actor + record fields re-scoped by v3-86 in 0080/0081). Two streams joined at read time, projected to the activity_events row shape plus the 0080/0081 audit columns (… actor_id, related_entity_type, related_entity_id, created_at, metadata, actor_type, actor_name, actor_role, entity_label, before_state, after_state, reason, outcome):

1. activity_events — human admin discretionary actions written at the API boundary (actor_id = JWT): freeze/pause/impair/wind-down/publish/governance/redemption-decision. Carries the 0081 snapshot fields (actor_name/actor_role/…).
2. economic event-log tables (READ, not re-copied → no double-write): deposits, redemption_requests (request + completion only — the admin decision is already in stream 1), yield_distributions, nav_history, kyc_logs.

id is prefixed per source (deposit:, redreq:, redcomp:, yield:, nav:, kyc:) so feed rows stay unique across the union. actor_type (0080) discriminates the acting party so GET /activity-events resolves actor_id against the right table: 'admin' → admin_users, 'investor' → users (deposit/redemption rows now carry the investor's user_id in actor_id, not NULL), 'system' → unresolved. Snapshot fields (0081) are populated for stream 1 and NULL for economic streams. GET /activity-events reads this view (snapshot wins, live join is the fallback). security_invoker = true.

CREATE VIEW audit_feed WITH (security_invoker = true) AS
SELECT ae.id::text AS id, ae.event_type, ... FROM activity_events ae
UNION ALL SELECT 'deposit:' || d.id::text, 'DEPOSIT', ... FROM deposits d
UNION ALL SELECT 'redreq:'  || r.id::text, 'REDEMPTION_REQUEST',   ... FROM redemption_requests r
UNION ALL SELECT 'redcomp:' || r.id::text, 'REDEMPTION_COMPLETED', ... FROM redemption_requests r WHERE r.completed_at IS NOT NULL
UNION ALL SELECT 'yield:'   || y.id::text, 'YIELD_DISTRIBUTED', ... FROM yield_distributions y
UNION ALL SELECT 'nav:'     || n.id::text, 'NAV_CHANGE',        ... FROM nav_history n
UNION ALL SELECT 'kyc:'     || k.id::text, 'KYC_' || k.status_result, ... FROM kyc_logs k;

investor_activity

Unified investor activity timeline (migration 0055, P-5; a 5th source added in 0103). UNION of the sources the web app used to fetch separately and merge client-side, projected to one common column set. Each row keeps its source's own uuid PK as id (the PKs are distinct uuids, so no collision). GET /investor-activity reads this view, investor-scoped by user_id. security_invoker = true.

• INVEST ← deposits (occurred_at = created_at)
• REDEEM ← redemption_requests (occurred_at = COALESCE(requested_at, created_at); carries the redeem-only extras). 0103: withheld when the request is COMPLETED and has redemption_fills rows — its payouts are then listed as those fills, and showing both would double-count the same USDC. Open (QUEUED/PARTIALLY_FILLED), REJECTED/FAILED, instant-pool and pre-0103 requests are unaffected
• REDEEM_FILL ← redemption_fills (0103; occurred_at = settled_at, amount = payout_amount, status hardcoded 'COMPLETED' — a fill is money already transferred, lp_filled = this fill's filled_lp, epoch_id = the epoch it settled out of). One row per settled fill of an epoch redemption, so a multi-epoch settlement appears as the payouts it actually made
• YIELD ← yield_distribution_investors joined to yield_distributions for pool_id / timestamp (amount = yield_distribution_investors.amount, occurred_at = COALESCE(yd.distributed_at, ydi.created_at))
• CLAIM ← yield_claims (occurred_at = COALESCE(completed_at, created_at); no investor_address column → NULL)

Columns: id (uuid), activity_type (text: INVEST|REDEEM|REDEEM_FILL|YIELD|CLAIM), occurred_at (timestamptz), amount (numeric), pool_id (uuid), user_id (uuid), investor_address (text), status (text), tx_hash (text), plus REDEEM-only nullable extras payout_amount, penalty_amount, nav_at_request, transfer_source, epoch_id, funding_status. P-5 follow-up added on_chain_request_id, next_settle_at (= redemption_epochs.epoch_end_at for the request's epoch), epoch_settled_at (= redemption_epochs.settled_at), error_message, failure_type, rejection_reason — populated on REDEEM rows (INVEST rows also carry error_message/failure_type from deposit failures) so the investor activity feed can render epoch Claim/Cancel/Settle actions + FAILED/REJECTED detail; NULL for the other types. Migration 0066 appended lp_filled (= redemption_requests.lp_filled, cumulative LP settled across partial fills) on REDEEM rows so the app can pre-gate the epoch Claim action (a request can settle at 0% fill / roll over with nothing to claim); NULL for the other types. Enums cast to text.

CREATE VIEW investor_activity WITH (security_invoker = true) AS
SELECT d.id, 'INVEST',  d.created_at, d.amount, d.pool_id, d.user_id, d.investor_address, d.status::text, d.tx_hash, NULL::numeric, NULL::numeric, NULL::numeric, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::timestamptz, NULL::timestamptz, d.error_message, d.failure_type, NULL::text, NULL::numeric FROM deposits d
UNION ALL SELECT r.id, 'REDEEM', COALESCE(r.requested_at, r.created_at), r.amount, r.pool_id, r.user_id, r.investor_address, r.status::text, r.tx_hash, r.payout_amount, r.penalty_amount, r.nav_at_request, r.transfer_source::text, r.epoch_id, r.funding_status, r.on_chain_request_id, e.epoch_end_at, e.settled_at, r.error_message, r.failure_type, r.rejection_reason, r.lp_filled FROM redemption_requests r LEFT JOIN redemption_epochs e ON e.pool_id = r.pool_id AND e.epoch_index = r.epoch_id WHERE NOT (r.status = 'COMPLETED' AND EXISTS (SELECT 1 FROM redemption_fills f WHERE f.request_id = r.id))
UNION ALL SELECT f.id, 'REDEEM_FILL', f.settled_at, f.payout_amount, f.pool_id, f.user_id, f.investor_address, 'COMPLETED'::text, f.tx_hash, f.payout_amount, NULL, NULL, NULL, f.epoch_id, NULL, NULL, NULL, f.settled_at, NULL, NULL, NULL, f.filled_lp FROM redemption_fills f
UNION ALL SELECT ydi.id, 'YIELD', COALESCE(yd.distributed_at, ydi.created_at), ydi.amount, yd.pool_id, ydi.user_id, ydi.investor_address, ydi.status::text, ydi.tx_hash, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM yield_distribution_investors ydi JOIN yield_distributions yd ON yd.id = ydi.distribution_id
UNION ALL SELECT yc.id, 'CLAIM', COALESCE(yc.completed_at, yc.created_at), yc.amount, yc.pool_id, yc.user_id, NULL::text, yc.status, yc.tx_hash, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM yield_claims yc;

---

Data Types Reference

Type	Description
PK	Primary Key — Unique ID, no duplicates
FK	Foreign Key — Links to another table's PK
UUID	Unique ID — Random 128-bit, globally unique
ENUM	Fixed Options — Only predefined values
TEXT	Variable-length string
NUMERIC	Precise Number — Exact, used for money
BOOLEAN	True / False flag
TIMESTAMPTZ	Date + Time with timezone
INTEGER	Whole Number — No decimals
SMALLINT	Small Whole Number — 2 bytes
JSONB	JSON Binary — Structured data, queryable
INET	IP Address — IPv4 or IPv6
DATE	Date Only — No time component

---

Relationship Map

• users <-1:N-> wallets
• users <-1:N-> kyc_logs
• users <-1:N-> deposits
• users <-1:N-> portfolio_positions
• users <-1:N-> redemption_requests
• users <-1:N-> yield_claims
• admin_users <-1:N-> admin_user_permissions
• admin_users <-1:N-> admin_sessions
• admin_users <-1:N-> fund_invites (invited_by)
• funds <-1:N-> fund_members
• funds <-1:N-> fund_invites
• funds <-1:N-> pools (via pools.fund_id; every pool has exactly one fund — v3-26)
• pools <-FK-> funds (fund_id)
• pools <-FK-> pool_categories (category → name)
• pools <-1:N-> pool_chain_deployments
• pools <-1:N-> pool_tvl_history
• pools <-1:N-> pool_asset_compositions
• pools <-1:N-> pool_documents
• pools <-1:N-> underlying_assets
• pools <-1:N-> nav_history
• pools <-1:N-> deposits
• pools <-1:N-> portfolio_positions
• pools <-1:N-> redemption_requests
• pools <-1:N-> yield_distributions <-1:N-> yield_distribution_investors
• pools <-1:N-> yield_claims
• pools <-1:N-> loan_writeoffs (v3-13)
• pools <-1:N-> pool_governance_changes (v3-18)
• pools <-1:N-> pool_updates <-1:N-> pool_update_revisions (WO-6)
• pool_updates / pool_update_revisions <-N:1-> admin_users (author_admin_user_id / edited_by)
• loan_writeoffs <-N:1-> nav_history (nav_history_id)
• portfolio_positions <-1:N-> redemption_requests (position_id)
• portfolio_positions <-1:N-> yield_claims (position_id)
• yield_distributions <-N:1-> admin_users (distributed_by)
• redemption_requests <-N:1-> admin_users (admin_id)
• nav_history / pool_governance_changes <-N:1-> admin_users (proposed_by)
• admin_users <-1:N-> activity_events (actor_id)
• auth_nonces — standalone (SIWE authentication)
• external_pool_dpd_buckets — standalone (owner key = pools.id, no FK; 0073/v3-72)
• notification_events <-1:N-> notifications <-1:N-> notification_deliveries (0110; the recipient on notifications is polymorphic — recipient_kind + recipient_id, no FK)
• email_suppressions — standalone (keyed on the address, not a user)
• notification_preferences — standalone (polymorphic user)
• platform_config <-N:1-> admin_users (updated_by)
• stablecoin_registry — standalone
• external_pool_data_snapshots / external_pool_data_cache — standalone (owner key = pools.id, no FK)

---

v3.0 Migration Summary

Deployed. v3.0 replaces the binary pool_type with a 24-dimension config model (see Pool Models).

New columns on pools (live)

fund_wallet, treasury_wallet, is_showcase, maturity_model, jurisdiction_whitelist, tranche_group_id (v3-14), tranche_role (v3-14), partner_id, collateral_description, is_emergency_frozen, freeze_started_at (0023/v3-28), redemption_gating_bps (0068), apy_disclosure, target_size_max, wind_down_proposed_at / wind_down_executed_at (v3-06), impairment_proposed_at (v3-12), max_investment, allows_us_persons, redemption_type, net_yield_fee_config, epoch_duration_days (0021/v3-26), nav_deviation_cap_bps / nav_staleness_seconds (0021/v3-32).

New tables (live)

loan_writeoffs (v3-13 DPD writeoff audit), pool_governance_changes (v3-18 timelocked governance), external_pool_data_snapshots / external_pool_data_cache (external pool-data sync), pool_updates / pool_update_revisions (WO-6 announcements feed), redemption_epochs (0021/v3-26 epoch settlement ledger), platform_settings (0039 admin platform-config JSONB store), nav_proposals (0094 A4 NAV suggest/approve), redemption_fills (0103 per-fill epoch ledger), pool_follows (0109 investor pool-open opt-in), epoch_window_subscriptions (0115 investor redemption-window opt-in).

New enums (live)

maturity_model, tranche_role. Plus values added: lifecycle_status.IMPAIRED (v3-12), lifecycle_status.WIND_DOWN (v3-06), redemption_status.PENDING_RESERVE (v3-04), redemption_status.QUEUED / redemption_status.PARTIALLY_FILLED (0021/v3-26), nav_update_status.CANCELLED. Value removed: redemption_status.FM_ACCEPTED (0021/v3-34 — enum rebuilt via create-new-type-and-swap; legacy rows → REQUESTED).

nav_data_source and tranche_structure enums were drafted in v3.0 design but removed by v3-15 and v3-14 respectively before implementation.

✅ Shipped — v3-109 NAV finalization (migrations 0117–0122)

Applied to dev on 2026-08-04. This section replaces the spec that stood here: three of its five column groups shipped in a different shape than planned, and the differences are the point.

pools — buffer config (R6), migration 0118. Planned as four columns; shipped as three, and one of the four turned out to be a bug.

Column	Type	Notes
buffer_rate_bps	INTEGER NOT NULL DEFAULT 0, CHECK 0..10000	Manager first-loss rate. bufferCap = total_deposited × buffer_rate_bps / 10000. 0 on every pool, which is the confirmed launch condition, not a placeholder
buffer_direction	TEXT NOT NULL DEFAULT 'FIRST_LOSS', CHECK (FIRST_LOSS | EXCESS)	Who takes the loss. The spec listed the direction as an open question to Joob; it shipped as a column instead, because a wait with no end date on a formula that runs every 12 hours is not a safeguard — 5% cap, 8% loss, investors take 3% or 5% with nothing on any screen to distinguish them. Not symmetric at 0, so the default is the direction whose zero value preserves current behaviour
buffer_basis	TEXT NOT NULL DEFAULT 'GROSS', CHECK (GROSS | NET)	The planned buffer_reporting_basis, renamed for the buffer_* prefix. GROSS = the partner reports total loss and Aset subtracts the buffer; NET = already net of their absorption, so the cap is forced to 0 rather than double-counting — R8 one layer up
fund_report_cadence_days	INTEGER NULL, CHECK > 0	The contractually committed reporting interval. NULL = none on record, and forward-looking investor copy stays hidden while NULL. Observed cadence is not a promise: dev shows 7 reports in 7 months at irregular intervals
apy_basis	TEXT NOT NULL DEFAULT 'GROSS_DEPOSIT', CHECK (GROSS_DEPOSIT | DEPLOYED)	Whether apy_rate is a rate on the full deposit or only the portion remitted to the partner. Inert while the reserve is effectively 0

🔴 buffer_cap and buffer_balance were not built, deliberately. The plan described a depleting ledger — buffer_balance = max(0, buffer_cap − absorbed), then uncovered = loss − buffer_balance. That subtracts the absorbed amount twice: at loss = cap = 100 it reports 100 uncovered when the correct answer is 0. The NAV formula is absolute, so the buffer needs no state at all — uncovered = max(0, loss − cap) under FIRST_LOSS, min(loss, cap) under EXCESS. A regression test pins the wrong version out, and bufferAbsorbed survives only as a display value. Materializing the cap was rejected for a second reason: a stored cap and a live total_deposited drift silently, and nothing would report which one a past NAV used.

Constraints that make inert configuration impossible.

Constraint	Rule	Why
buffer_requires_external_mapping (0119, retired by 0122)	was: a non-zero buffer required an external mapping	Correct while the sweep was the buffer's only reader. Once the manual NAV path started deriving from a loss, the ban blocked a legitimate first-loss layer on 12 of 13 pools
buffer_not_on_tranche_pools (0122)	buffer_rate_bps > 0 requires tranche_group_id IS NULL	A tranche group's NAV comes from the loss waterfall, which does not run this formula, so a buffer there would still be inert
cadence_requires_external_mapping (0122)	fund_report_cadence_days requires external_fund_id	Unlike the buffer, this describes the partner's obligation to send reports. A pool with no feed receives none
equity_buffer_prose_requires_layer (0121)	equity_buffer_rule requires buffer_rate_bps > 0 (blank counts as unset)	The prose renders to investors as "Manager first-loss commitment". 0077 added it when no structured buffer existed; now it must describe the configured layer rather than substitute for one

Both are mirrored in pools.patch.update so the API returns a sentence instead of a raw 23514; create rejects equity_buffer_rule outright, since a new pool has no rate.

nav_proposals — dismiss + TTL (D2): shipped with no new columns. The planned dismiss_reason and ttl_expires_at were not added. A dismissal writes its reason into the existing reason field (the server requires one), and expiry is derived — created_at + 48h with resolved_by IS NULL marking a TTL expiry rather than a human decision, which a separate timestamp column could not distinguish anyway. Escalated proposals never auto-expire.

⚠️ recovery_flag existed for one day. 0118 added it to flag a proposal that RAISES NAV; 0120 dropped it. The argument for it — an increase applies with no timelock, so a bad one cannot be caught during a notice period — describes the timelock rather than a missing control, and product's call is that a NAV increase takes the approve button and nothing else. Dropped rather than left unread: an always-false column reads to the next author as a signal.

pools.lp_total_supply — on-chain mirror (R9), migration 0117. Added nullable, written by the indexer, and not yet used by the formula. NULL means "not mirrored", which is not the same as a supply of zero, so the switch waits until the mirror is observed running on a deployed pool: numerator in the DB and denominator on the chain converts every indexer stall into a NAV jump, and the indexer was silently dead for 13 days this month.

nav_history / nav_proposals — decrease-cause metadata (FE). Still spec-level, unchanged from the plan: the write-off delta, its as-of date, and the reporting period, most likely one JSONB since it is display provenance rather than queried state.

settledUnclaimedLp — on-chain only (R10). ✅ Shipped: a contract state variable (PlatformPoolStorage.sol) with a same-named getter, + when a fill is reserved into redemptionCommitted, − at claim/burn, wind-down denominator computed on-chain — deployed to dev 2026-08-04 (factory 0xE1E2E974…DA90 → pool implementation 0x27D9948F…f968, commit 0abe168). ⚠️ New pools only: pools are Clones with the implementation fixed at creation, so every pool created before that deploy still runs the old one (the getter reverts on them). No DB column; mirror it only if a screen needs it.

✅ Shipped — v3-110 pool close / archive / hide (migrations 0123–0124)

Applied to dev 2026-08-04. This replaces the spec that stood here; where the shipped shape differs from it, the difference is stated rather than quietly overwritten.

pools.is_hidden — BOOLEAN NOT NULL DEFAULT false (0123). Takes a pool off the default admin list while it keeps operating: no capability change, no on-chain component, freely reversible, and independent of both deleted_at and is_paused. ?include_hidden=true brings it back into the response — a hidden pool with no way to ask for it is indistinguishable from a deleted one.

🔴 Narrowed from the spec, which said "investor list and PDP". It does not touch investor surfaces. A holder has to reach the pool detail to redeem, claim yield and read a write-down notice, so hiding it there would put someone else's money behind a decision made for the operator's convenience. If a pool genuinely must leave the investor's view that is a lifecycle question — CLOSED, or archive — not a display flag. Distinct from deleted_at (archive: gated on zero positions, mirrors an on-chain pause(), restore needs a reason) and from is_showcase (immutable, custody-level). Three columns, three meanings.

⚠️ There is no ARCHIVED value in the lifecycle_status enum and none was added. Archive is deleted_at; the ARCHIVED label is derived in the admin mapper.

activity_events.event_type — POOL_ARCHIVE and POOL_RESTORE now exist. No migration: the column is TEXT and the union lives in lib/shared/audit/activity.ts. The point of splitting them out is that archive (reversible) and hard delete (permanent) were both POOL_DELETE, separated only by metadata.mode — a JSONB field cannot be filtered, counted or alerted on the way a type can, so the log could not answer "what was permanently destroyed". POOL_DELETE now means the irreversible one only. audit_feed needed no change: it passes ae.event_type through and has no POOL_* branch.

pools.end_date — ⚠️ superseded by 0204 / v3-141; the paragraph below records what v3-110 did and why it was reversed. v3-110 added a second job to this column: the lifecycle scheduler read it as the auto-close trigger (ACTIVE → CLOSED) in a pass that runs after the maturity pass, so a FIXED_TERM pool past both its end date and its maturity left as MATURED and was never demoted to CLOSED. That ordering was deliberate and still is: MATURED is what makes redemption penalty-free, and demoting such a pool would reinstate early-exit penalties on holders who had earned their way out.

🔴 The second job is what 0204 took back. Because both passes compared the same date and the maturity pass ran first, the auto-close pass never actually fired — and the reason the two dates were the same is that end_date was simultaneously being read as maturity by resolveMaturityAt. One column answering both questions meant a pool took deposits until the day it matured. The auto-close pass now reads subscription_end_date; end_date is maturity only. The maturity pass gained CLOSED in its status filter in the same change, because a pool that closes before its term and cannot then mature is a pool whose redemptions never open.

nav_proposals.escalate_flag — comment only (0124), naming the condition 전손 / total loss in one place. Written as a fresh COMMENT ON COLUMN rather than an edit to the applied 0094, since this repo treats applied migrations as history (0119 was retired by 0122 and its file left intact).

Deprecated columns (retained for history)

• pools.escrow_model, pools.yield_distribution_model, pools.receipt_token_address (pools.lp_issuance_model + its enum were dropped in migration 0050)
• deposits.receipt_* / escrow_status / refund_eligible_at were dropped in migration 0022; deposits.fm_notified_at dropped in migration 0050
• redemption_requests.contract_validated_at, .auto_released_at, .approval_requested_at, .cosigned_at, .fund_* (.fm_notified_at / .fm_accepted_at dropped in migration 0050)
• Status values: deposit_status.REFUNDED dropped (migration 0049); redemption_status.FM_ACCEPTED was removed in migration 0021/v3-34, no longer in the enum

Migration approach

• Add new columns nullable; backfill from existing values where possible (e.g., fund_wallet = pool_wallet for AS_POOL legacy)
• Mark deprecated columns with COMMENT ON COLUMN ... IS 'deprecated v3.0' rather than dropping (preserve history)
• No data destruction; reads of legacy pools work unchanged

---

Current Version

v3.0 (deployed) on v2.17 · R18 base — 51 tables · 27 enums · v3.0 dimension columns live · legacy columns retained. Synced with Supabase 2026-08-06, through migration 0126. Latest: 0126 deposit-path provenance + two unreachable functions dropped, 0125 portfolio_positions.entry_price (cost basis), 0124 nav_proposals total-loss wording. Counts verified against pg_class / pg_type on the dev instance. The recent-migration table is in the header callout at the top of this page and the full history is in 17-changelog — this line is the count, not the history.