Treasury & Cash Management — Combined Requirements & Build Specification

Single file. Screenshots, requirements, data model, APIs, .NET and React structure, seed data.

How to use this document

It is written to be handed to several audiences at once and to be fed directly to an AI coding assistant in VS Code.

Conventions used throughout

• Connection = one provider serving one module. It is the unit of verification: freshness, sync health and variance are all evaluated per connection. Never called "integration" in code.
• TreasuryAccount = a real account at a provider. Never called "account" alone — the platform already has customer accounts.
• Movement = a transfer between two accounts we own. Never called "payment" or "transaction".
• Every new table is prefixed Treasury* or lives in a treasury schema, so it is obvious what belongs to this feature.
• Bold rules are non-negotiable invariants. They are enforced in the domain layer, not the UI.
• Amounts: DECIMAL(28,8) in SQL, decimal in .NET, string in JSON. Never float, never JS number.

Before writing any code, produce TREASURY-CONVENTIONS.md per §11. This feature goes into a live system; its structure, auth, logging, error shape, migrations and React components are already decided. Find those decisions and copy them.

1. Executive summary

The platform moves customer money through eleven third parties across five modules. Customers see wallets, cards, payments and bank accounts, and those work. But the money backing those features is spread across wallet custody on three blockchains, collateral at three card issuers, bank accounts at four banking partners, prefunded balances at four payment providers, and trading balances at two exchanges.

Twenty-six accounts. Eleven providers. Fourteen connections. Nobody can open one screen and say how much the company holds, whether customer balances are covered, or which account is about to run dry.

Today that picture is assembled by hand from provider portals. It takes two to four hours and is always as at yesterday. Every funding decision is made on stale data.

The consequence is not theoretical. When card collateral runs low, customer card authorisations start declining within minutes, and nobody connects the support tickets to the collateral balance for another day.

This feature answers four questions continuously, and closes the loop without a human noticing something:

A worked example. At 08:02 card spend has drawn collateral down to 185,000 USDC against a 200,000 minimum. Without treasury, an analyst notices around 11:00, funds go out at 14:30, and customers see declines for five hours. With treasury, the threshold rule fires at 08:02, checks the connection is reconciled, checks the source keeps its own floor, and creates a movement for approval. Funds are on-chain by 08:11. No customer sees a decline.

2. Standards this must satisfy

3. Scope

3.1 What treasury owns

3.2 What treasury must NEVER do

3.3 There is no reconciliation module — what that means

Treasury polls each provider through the module solutions and stores what it gets. That is the whole data pipeline.

Polling gives you freshness, not correctness. Knowing that Rain reports 185,000 USDC is not the same as knowing 185,000 is right. A stale balance and a wrong balance are different failures, and polling only solves the first.

So treasury runs its own verification, built from data it already has. See §6 for the mechanics.

The two real losses are transaction-level matching and attestation. Both matter for audit. Neither is what the gate is for — the gate exists to stop automation acting on bad data, and the three checks in §6.2 do that.

BR-25 MUST Every screen showing verification state says it is a self-check against our own records, not an independent reconciliation. An operator who believes this is reconciliation will trust it further than it deserves, and misplaced confidence is more dangerous than no check at all.

3.4 Keep reconciliation on the roadmap

Build treasury now — it delivers on its own and the gate is real. But when reconciliation arrives the change is small: ConnectionSyncState and BalanceCheck remain as an operational health layer, and the gate gains a fourth condition reading reconciliation's certification. Design IGateEvaluator so a fourth check can be added without touching the other three.

4. Roles in the process

TREASURY_ADMIN cannot approve. The person who configures a funding rule must not be the sole approver of the movements that rule produces. This separation is the reason both roles exist.

Map these onto the existing role/claim model. Do not build a parallel permission system.

5. Data model

5.1 The four-layer model

Everything hangs off four nested concepts. Learn these and the database, the API and the screens all become predictable.

MODULE       wallets · cards · banks · payments · exchange
   ↓
PROVIDER     DFNS · Rain · StraitsX · Wasabi · Noah · Tazapay
             OpenPayd · Thunes · Mesta · Kraken · Binance
   ↓
CONNECTION   one provider serving one module — 14 of them
             SHARED WITH RECONCILIATION
   ↓
ACCOUNT      a real bank account or wallet at that provider
             carries a classification and thresholds
   ↓
POSITION     asset × network × state

Noah, Tazapay and Thunes each serve two modules. Those are two separate connections, two separate balance checks, and two separate positions with separate thresholds. Never sum them, never net them, never show them as one balance. A healthy-looking combined figure hides a payout rail about to fail.

5.2 Account classification

Every account carries exactly one. This drives the segregation control and the coverage calculation, so it is enforced logic, not a display label.

5.3 The five position states

Not interchangeable. Showing the wrong one is the most common and most damaging bug in a treasury system.

Movable is different again: MAX(0, available − minimumThreshold). The New Movement screen validates against movable, not available.

Any screen offering a funding action uses available. Any screen reporting holdings uses total. Never display a single unlabelled "balance" anywhere in the product.

5.4 Existing tables — read only, not modified

No existing table is altered. No new columns, no changed types, no new constraints. This database is live.

5.5 New tables

Full DDL is in §18. This is the map.

6. Verification and the policy engine

6.1 Why a gate exists at all

An automated rule decides how much to move by reading a balance. If that balance has not been verified, the system moves real money on a number nobody checked. A balance reading high causes under-funding and card declines; reading low causes over-funding and idle capital. At machine speed, on a five-minute timer, either compounds before a human notices.

That is the entire justification for everything in this section.

6.2 The verification gate

Three checks, all computed by treasury from its own data.

Manual movements remain allowed against a blocked connection with +1 approver, exactly as before. Automation is refused, exactly as before.

There is still no override. Clearing a block means fixing the sync, or explaining the variance. A button that waves it through would be used routinely and the control would be worth nothing.

6.2.1 Gate evaluation

public GateResult Evaluate(ConnectionSyncState s, BalanceCheck check, Connection c, TimeProvider clock)
{
    class="cm">// 1 — freshness
    var age = clock.GetUtcNow() - s.LastSuccessAtUtc;
    if (s.LastSuccessAtUtc == default)
        return GateResult.Blocked(class="str">"No balance has ever been retrieved for this connection.");
    if (age.TotalSeconds > c.StalenessThresholdSeconds)
        return GateResult.Blocked(
            $class="str">"Last successful sync was {Humanise(age)} ago, beyond the {c.StalenessThresholdSeconds}s threshold.");

    class="cm">// 2 — sync health
    if (s.ConsecutiveFailures >= 2)
        return GateResult.Blocked(
            $class="str">"{s.ConsecutiveFailures} consecutive sync failures. Last error: {s.LastError}");

    class="cm">// 3 — variance
    if (c.VerificationMode == VerificationMode.FreshnessOnly)
        return GateResult.Degraded(                       class="cm">// allowed, but with reduced caps
            class="str">"Variance checking is unavailable for this connection.");

    if (check == null)
        return GateResult.Blocked(class="str">"No balance check has been performed.");

    if (clock.GetUtcNow() - check.CheckedAtUtc > TimeSpan.FromSeconds(c.StalenessThresholdSeconds * 2))
        return GateResult.Blocked(class="str">"The last balance check is too old to rely on.");

    if (Math.Abs(check.VarianceBase) > c.VarianceToleranceBase)
        return GateResult.Blocked(
            $class="str">"Provider balance differs from our records by {check.VarianceBase:N0}, " +
            $class="str">"beyond the {c.VarianceToleranceBase:N0} tolerance.");

    return GateResult.Clean(s.LastSuccessAtUtc, check.VarianceBase);
}

6.2.2 Degraded mode — be deliberate about it

Some providers will not support the activity feed the variance check needs. For those, set VerificationMode = FRESHNESS_ONLY.

The failure to avoid is degraded mode quietly becoming the norm because it is easier. Report the count of degraded connections on S1 and review it monthly.

6.3 The variance check

The one piece of genuine verification treasury can do alone.

expected = lastVerifiedBalance
         + Σ module-recorded inflows since lastVerifiedAt
         − Σ module-recorded outflows since lastVerifiedAt
         + Σ treasury movements settled since lastVerifiedAt

variance     = providerReportedBalance − expected
varianceBase = variance × fxRate

Your module solutions already record every customer transaction at each provider. That record is the second opinion the variance check needs.

Worked example. Rain reports 185,000 USDC. Our last verified figure was 250,000. Since then the cards module recorded 66,250 of settled card spend, and treasury settled no movements. Expected is 183,750. Variance is +1,250 — inside a 50,000 tolerance, so the gate stays clean and the figure becomes the new verified baseline.

If instead Rain reported 130,000, the variance would be −53,750: outside tolerance, automation blocked on rain-cards, and an alert raised. That is precisely the case where a funding rule reading 130,000 would over-fund by 55,000.

6.3.1 What it does not catch

A matching error on both sides is invisible. If the module failed to record a transaction and the provider never processed it, provider and expected agree and the check passes. Real reconciliation matches transaction by transaction and would catch it.

State this on the screen. An operator who believes the variance check is reconciliation will trust it further than it deserves.

6.3.2 Baseline management

6.4 Polling and refresh

6.4.1 Cadence

Set per connection via PollIntervalSeconds. Do not run every provider on one global timer — you will hit rate limits on the slow ones and under-poll the fast ones.

6.4.2 Rate limits are a first-class concern

Provider APIs are your only source of balances, and you are now calling them continuously.

6.4.3 The Refresh button

Manual refresh is genuinely useful — an analyst about to fund an account wants the current number, not one from four minutes ago.

6.4.4 Webhooks where available

Where a provider pushes balance changes, take them — but treat them as an accelerator, not a replacement.

6.5 Segregation

public PolicyResult CheckSegregation(TreasuryAccount source, TreasuryAccount dest,
                                     MovementType type)
{
    if (source.Classification != Classification.ClientSafeguarded)
        return PolicyResult.Pass();

    if (dest.Classification == Classification.ClientSafeguarded)
        return PolicyResult.Pass();

    class="cm">// The single documented exception.
    if (type == MovementType.FeeSweep &&
        source.Classification == Classification.FeeRevenue)
        return PolicyResult.Pass();

    return PolicyResult.Block(class="str">"SEGREGATION_VIOLATION",
        $class="str">"Client funds cannot move to a {dest.Classification} account.");
}

6.5.1 Approval quorum — frozen at creation

requiredApprovals = amountBase > settings.ApprovalThresholdBase ? 2 : 1;
if (!sourceGate.IsClean || !destGate.IsClean) requiredApprovals += 1;

If a movement needs two approvers because it was worth $150k at 09:00, it still needs two at 15:00 even if FX moved and it is now worth $95k. Freeze both RequiredApprovals and AmountBase at creation. Recomputing them on read is a subtle and serious bug.

6.6 Client liabilities — where the coverage denominator comes from

This is less affected than it appears. Liabilities always came from your books, never from reconciliation — reconciliation verified them, it did not produce them.

Each module solution knows what it owes customers: the wallets module holds every customer wallet balance, the banks module every customer account balance. Sum them.

6.6.1 New module contract endpoint

class="cm">// GET /api/treasury/client-liabilities
public record ClientLiabilityDto(
    string   ModuleCode,          class="cm">// class="str">"wallets"
    string   AssetCode,           class="cm">// class="str">"USDC"
    decimal  TotalOwedToCustomers,
    int      CustomerAccountCount,
    DateTime AsOfUtc);            class="cm">// when the module computed it

Aggregated into ClientLiabilitySnapshot, which becomes the K-2 denominator.

6.6.2 What changes on S15

BR-26 MUST If any module fails to report its liabilities, the coverage ratio shows an em dash and states which module is missing. Never compute a ratio from a partial denominator — an incomplete liability figure makes coverage look better than it is, which is the most dangerous possible error on that screen.

This preserves the behaviour of T-12, which remains the test to gate every release on. Only the failing dependency changes.

6.7 Movement lifecycle

DRAFT ─→ PENDING_APPROVAL ─→ APPROVED ─→ SUBMITTED ─→ CONFIRMING ─→ SETTLED
              │        │                      │             │
              │        └──→ CANCELLED         └──→ FAILED ──┴──→ MANUALLY_RESOLVED
              └──→ REJECTED

No state may be skipped. Every transition writes a MovementEvent with actor, timestamp and reason.

6.8 The six rule-engine guards

The highest-risk code in this feature — it moves real money unattended. Each guard is unit tested independently.

Every evaluation writes a RuleExecution row, including evaluations that decided to do nothing. Without skip records, "why didn't the rule fire last night?" is unanswerable — and that question is asked after every incident.

7. Screens

Every screenshot below is the working prototype. For each screen: what it is for, the acceptance criteria, the APIs it calls, the React components to build, and the tables it touches.

The layouts show information hierarchy. Colours, spacing, typography and component appearance come from your existing design system — see §10 and §11.

S1 · Overview

Purpose. The five-second test. An analyst opening this screen knows within five seconds whether anything is wrong and whether anything needs them. Everything else here is a drill-down.

Requirements

APIs

GET /api/v1/treasury/positions/summary
GET /api/v1/treasury/coverage
GET /api/v1/treasury/alerts?status=OPEN&limit=8
GET /api/v1/treasury/movements?limit=5&sort=createdAt:desc
GET /api/v1/treasury/accounts?hasThreshold=true&sort=thresholdRatio:asc&limit=9

React TreasuryOverviewPage → KpiCard ×4, ModuleTile ×5, AlertRow, ThresholdBar, MovementRow

Tables

vw_PositionBase R · vw_ConnectionGate R · AlertInstance R · CoverageSnapshot R · Movement R · AccountThreshold R

The coverage KPI reads CoverageSnapshot, whose liability figure came from the module ledgers. If any module is silent, the card shows an em dash — never a ratio from a partial denominator.

S2 · By module

Purpose. How each business area sits, side by side, without drilling in. One card per module, each containing the same account table used on S4 so users learn one table.

Requirements

APIs

GET /api/v1/treasury/positions/by-module
GET /api/v1/treasury/accounts

React ModulesPage → ModuleCard, AccountTable

Tables

vw_PositionBase R · vw_ConnectionGate R

Group client-side by moduleCode. Do not issue five separate account queries.

S3 · Module detail

Purpose. One business area, grouped by connection. This grouping is the point of the screen.

Requirements

APIs

GET /api/v1/treasury/positions/by-module?moduleCode=cards
GET /api/v1/treasury/connections?moduleCode=cards
GET /api/v1/treasury/accounts?moduleCode=cards

React ModuleDetailPage → KpiCard ×3, ConnectionCard, GateBanner, AccountTable

Tables

vw_PositionBase R · vw_ConnectionGate R · Connection R · ConnectionSyncState R · BalanceCheck R

The provenance line is mandatory. Without it, people raise treasury bugs for provider sync problems.

S4 · Accounts

Purpose. The working list. Every account in the platform, filterable three ways. An analyst who knows roughly what they are looking for starts here.

Requirements

APIs

GET /api/v1/treasury/accounts?q=&moduleCode=&classification=&sort=&page=1&pageSize=50

React AccountsPage → FilterBar, AccountTable, PositionCell, ClassificationPill, HealthPill, GatePill

Tables

vw_PositionBase R · vw_ConnectionGate R

PositionCell exists so available-vs-total cannot be got wrong by accident. Use it everywhere a balance appears.

S5 · Account detail

Purpose. One account in full: the five position states, thresholds, the gate, and the actions to fund it or move out of it.

Requirements

APIs

GET   /api/v1/treasury/accounts/{id}                 → returns ETag
GET   /api/v1/treasury/accounts/{id}/positions
GET   /api/v1/treasury/movements?accountId={id}&limit=20
PATCH /api/v1/treasury/accounts/{id}/thresholds      → If-Match required

React AccountDetailPage → KpiCard ×3, GateBanner, DefinitionList, ThresholdBar, ThresholdModal, MovementList

Tables

TreasuryAccount R · AccountThreshold R/W · vw_CurrentPosition R · vw_ConnectionGate R · Movement R · AuditLog W

PATCH requires If-Match. On mismatch return 409 and tell the user someone else changed the account.

S6 · New movement

Purpose. The most safety-critical screen in the product. It must make a correct movement easy and an incorrect movement impossible.

Requirements

APIs

POST /api/v1/treasury/movements/preview      # debounced 300ms, cancel in-flight
POST /api/v1/treasury/movements              # Idempotency-Key: the modal's UUID

React NewMovementModal → AccountSelect ×2, AmountInput, PreviewPanel, BlockerList, WarningList

Tables

R vw_CurrentPosition · AccountThreshold · TreasuryAccount · vw_ConnectionGate · Destination · vw_LatestFxRate · SystemSetting · Asset · W Movement · MovementEvent · IdempotencyRecord · AuditLog

Preview is the authority on validity. Duplicating its rules in React guarantees they drift, and the server is the one that matters.

S6b · New movement — blocked

Purpose. The same screen refusing. Two blockers are shown together, Submit is disabled, and the classification route is rendered in a danger tone so the reason is visible before the message is read.

Requirements

APIs

POST /api/v1/treasury/movements/preview      → { valid: false, blockers: [...] }

React PreviewPanel → BlockerList

Tables

R TreasuryAccount (classification) · AccountThreshold · vw_CurrentPosition

Server-side enforcement is the control. The disabled button is a convenience.

S7 · Movements

Purpose. Every transfer, filterable. The finance team's audit-adjacent view.

Requirements

APIs

GET /api/v1/treasury/movements?status=&from=&to=&moduleCode=&initiatedBy=&sort=&page=1&pageSize=50

React MovementsPage → FilterBar, MovementTable, AccountRoute, MovementStatusPill

Tables

Movement R · TreasuryAccount R · Connection R · Provider R · AppUser R · LiquidityRule R

Export CSV honours the current filter and is capped at 100,000 rows.

S8 · Movement detail

Purpose. What this movement did, who decided, and what happens next.

Requirements

APIs

GET  /api/v1/treasury/movements/{code}
POST /api/v1/treasury/movements/{code}/approve   # Idempotency-Key, optional X-Step-Up-Token
POST /api/v1/treasury/movements/{code}/reject    # { reason }
POST /api/v1/treasury/movements/{code}/cancel
POST /api/v1/treasury/movements/{code}/retry
POST /api/v1/treasury/movements/{code}/resolve   # { reason }

React MovementDetailPage → DefinitionList, LifecycleTimeline, GateBanner, ReasonModal, ConfirmDialog

Tables

R Movement · MovementEvent · MovementApproval · AppUser · vw_ConnectionGate · W MovementApproval · Movement · MovementEvent · AuditLog

After any mutation invalidate: this movement, the movements list, the approvals queue, positions, summary and the nav badges.

S9 · Approvals

Purpose. The queue of decisions waiting on the signed-in user. This screen is the most likely to be under-designed and the most likely to cause a control failure: if an approver cannot decide from the row alone, they will approve on autopilot and maker–checker becomes theatre.

Requirements

APIs

GET  /api/v1/treasury/approvals/queue            # refetch 30s
POST /api/v1/treasury/movements/{code}/approve
POST /api/v1/treasury/movements/{code}/reject

React ApprovalsPage → ApprovalRow, AccountRoute, ReasonModal, StepUpChallenge

Tables

R Movement (filtered index) · MovementApproval · AppUser · TreasuryAccount · vw_ConnectionGate · W MovementApproval · Movement · MovementEvent · AuditLog

In the seed data MV-4473 is initiated by Anita K. Log in as Anita and try to approve it — that is the test of the whole control.

S10 · Funding rules

Purpose. What runs without a human, and whether it currently can. A rule that cannot run must look different from one that is merely switched off.

Requirements

APIs

GET   /api/v1/treasury/rules
POST  /api/v1/treasury/rules
PATCH /api/v1/treasury/rules/{id}                # If-Match
POST  /api/v1/treasury/rules/{id}/dry-run
POST  /api/v1/treasury/automation/kill-switch    # { halted: true|false }

React RulesPage → RuleRow, RuleStatePill, DryRunModal, KillSwitchToggle, ConfirmDialog

Tables

R LiquidityRule · TreasuryAccount · RuleExecution · vw_ConnectionGate · vw_CurrentPosition · SystemSetting · W LiquidityRule · SystemSetting · AuditLog

CK_LiquidityRule_DryRun enforces AC-10.4 in the database as well as the service.

S10b · Dry run — blocked rule

Purpose. Dry run against live balances, moving nothing. Shown here for a rule the gate has blocked: the calculation still runs so the operator can see what would happen, but the action to create the movement is withheld.

Requirements

APIs

POST /api/v1/treasury/rules/{id}/dry-run

React DryRunModal → GateBanner, DefinitionList

Tables

R LiquidityRule · vw_CurrentPosition · vw_ConnectionGate · RuleExecution · W LiquidityRule (dry-run stamp)

Dry run never writes a Movement and never moves money.

S11 · Providers

Purpose. What we are connected to, how fresh each balance is, whether recent polls succeeded, whether the provider's figure agrees with our records — and therefore whether automation is permitted.

Requirements

APIs

GET  /api/v1/treasury/connections?moduleCode=&gate=
POST /api/v1/treasury/connections
POST /api/v1/treasury/connections/{id}/refresh      → 429 REFRESH_TOO_SOON when too soon

React ConnectionsPage → ConnectionTable, SyncPill, VarianceCell, GatePill, RefreshButton, AddProviderModal

Tables

Connection R/W · ConnectionSyncState R · BalanceCheck R · vw_ConnectionGate R · vw_PositionBase R · AuditLog W

A provider cannot be automated against on trust. The gate enforces the first-certified rule, not process discipline.

S12 · Connection detail

Purpose. One provider × module in full: its gate, its configuration, what the module contract says it can do, and the accounts and rules that depend on it.

Requirements

APIs

GET   /api/v1/treasury/connections/{id}
GET   /api/v1/treasury/accounts?connectionId={id}
GET   /api/v1/treasury/movements?connectionId={id}&limit=10
PATCH /api/v1/treasury/connections/{id}          # tolerance, ADMIN, If-Match

React ConnectionDetailPage → KpiCard ×4, GateBanner, CapabilityList, AccountTable, RuleList

Tables

Connection R/W · ConnectionSyncState R · BalanceCheck R · vw_ConnectionGate R · TreasuryAccount R · LiquidityRule R · Movement R · AuditLog W

Capabilities are reported by the module solution, which knows what its provider client can actually do.

S13 · Forecast

Purpose. What will need funding, and when. The chart is only useful if the operator trusts it, which is why the inputs panel is mandatory.

Requirements

APIs

GET /api/v1/treasury/forecast/{accountId}?horizonDays=14

React ForecastPage → AccountSelect, KpiCard ×3, ForecastChart, ShortfallBanner, InputsPanel

Tables

ForecastPoint R · AccountThreshold R · vw_CurrentPosition R · Movement R (history)

ForecastPoint is deleted and reinserted per run, not merged. The nightly job runs at 17:00 UTC.

S14 · FX exposure

Purpose. Are we over-exposed to any single asset? And the reminder that the same asset on two networks is not one pool.

Requirements

APIs

GET /api/v1/treasury/positions/exposure

React FxExposurePage → ExposureTable, ConcentrationBar, CrossNetworkTable

Tables

vw_PositionBase R · Asset R (limits) · vw_LatestFxRate R

This is the one screen that groups by asset across networks. Everywhere else, asset × network is the position key.

S15 · Client coverage

Purpose. Do the assets we hold for customers cover what we owe them? Treasury computes the numerator from provider balances and reads the denominator from our own module ledgers.

Requirements

APIs

GET /api/v1/treasury/coverage
GET /api/v1/treasury/coverage/history?days=30

React CoveragePage → KpiCard ×3, ShortfallBanner, CompositionTable, LiabilitySourceTable, CoverageChart

Tables

CoverageSnapshot R · ClientLiabilitySnapshot R · vw_PositionBase R (client accounts)

Liabilities always came from your books — a reconciliation module would have verified them, not produced them. What is missing here is attestation, not the figure.

S15b · Coverage — a module is not reporting

Purpose. The failure path, which is the point of the screen. When any module cannot report its client liabilities, the ratio is not computed, not estimated, and not shown.

Requirements

APIs

GET /api/v1/treasury/coverage    → 200 { status: 'UNAVAILABLE', ratioPct: null, missingModules: ['wallets'] }

React CoveragePage → KpiCard (unavailable variant), UnavailableBanner

Tables

CoverageSnapshot W (status UNAVAILABLE) · ClientLiabilitySnapshot R · AlertInstance W

This is the single most important failure behaviour in the product. A treasury system that quietly invents a client-liability number when its source is down is more dangerous than one that shows nothing. Test T-12 gates every release.

S16 · Exceptions

Purpose. Everything abnormal, from five sources, in one queue. This must be a real screen, not a notification bell — otherwise operators learn about problems in chat and the workspace becomes a place they only visit to execute decisions made elsewhere.

Requirements

APIs

GET  /api/v1/treasury/alerts?severity=&sourceType=&status=&page=1&pageSize=50
POST /api/v1/treasury/alerts/{id}/acknowledge    # { note? }

React ExceptionsPage → AlertRow, SeverityPill, AckModal

Tables

AlertInstance R/W · AuditLog W

UX_AlertInstance_OpenDedup is a filtered unique index on DedupKey where status is not resolved.

S17 · Audit log

Purpose. Who did what, permanently. All roles can read it. No role can change it.

Requirements

APIs

GET /api/v1/treasury/audit?from=&to=&actorUserId=&action=&resourceType=&page=1&pageSize=100

React AuditPage → FilterBar, AuditTable, ExpandableRow, ExportButton

Tables

AuditLog R · AppUser R

The application SQL login has no UPDATE or DELETE grant on this table, and a trigger throws 50002 as a second line of defence.

S18 · Balance verification

Purpose. The evidence behind the gate. What each provider reports, what our own records expect, and the difference. This is what an operator opens when a connection is blocked on variance.

Requirements

APIs

GET  /api/v1/treasury/balance-checks
GET  /api/v1/treasury/balance-checks/{accountId}/history
POST /api/v1/treasury/balance-checks/refresh-all
POST /api/v1/treasury/balance-checks/{id}/accept      # ADMIN, { reason }

React BalanceVerificationPage → KpiCard ×4, VarianceTable, VarianceDrawer, AcceptVarianceModal

Tables

BalanceCheck R/W · TreasuryAccount R/W (baseline) · Connection R · ConnectionSyncState R · AlertInstance W · AuditLog W

Route /treasury/verification, under Providers in the sidebar. It is evidence, not a daily destination.

8. API reference

8.1 Conventions

8.2 Endpoint list

# ── Positions ───────────────────────────────────────────
GET    /positions/summary                      # S1
GET    /positions/by-module                    # S2, S3
GET    /positions/exposure                     # S14

# ── Accounts ────────────────────────────────────────────
GET    /accounts                               # S2, S3, S4
GET    /accounts/{id}                          # S5   → ETag
GET    /accounts/{id}/positions                # S5
PATCH  /accounts/{id}/thresholds               # S5   → If-Match

# ── Movements (Idempotency-Key on every POST) ───────────
POST   /movements/preview                      # S6   highest-value endpoint
POST   /movements                              # S6
GET    /movements                              # S7
GET    /movements/{code}                       # S8
POST   /movements/{code}/approve               # S8, S9  + optional X-Step-Up-Token
POST   /movements/{code}/reject                # S8, S9  { reason }
POST   /movements/{code}/cancel                # S8
POST   /movements/{code}/retry                 # S8   clears approvals
POST   /movements/{code}/resolve               # S8   { reason }
GET    /approvals/queue                        # S9

# ── Rules and automation ────────────────────────────────
GET    /rules                                  # S10
POST   /rules                                  # S10  segregation checked at save
PATCH  /rules/{id}                             # S10  → If-Match
POST   /rules/{id}/dry-run                     # S10b
POST   /automation/kill-switch                 # global

# ── Connections ─────────────────────────────────────────
GET    /connections                            # S11
POST   /connections                            # S11  starts gate-blocked
GET    /connections/{id}                       # S12
PATCH  /connections/{id}                       # S12  tolerance, ADMIN

# ── Verification ────────────────────────────────────────
GET    /balance-checks                         # S18
GET    /balance-checks/{accountId}/history     # S18
POST   /balance-checks/refresh-all             # S18  rate-limited per connection
POST   /balance-checks/{id}/accept             # S18  ADMIN, { reason }
POST   /connections/{id}/refresh               # S11, S12  → 429 REFRESH_TOO_SOON

# ── Coverage, forecast, alerts, audit ───────────────────
GET    /coverage                               # S1, S15
GET    /coverage/history                       # S15
GET    /forecast/{accountId}                   # S13
GET    /alerts                                 # S1, S16
POST   /alerts/{id}/acknowledge                # S16
GET    /audit                                  # S17

8.3 Key payloads

POST /movements/preview — the endpoint the whole New Movement screen depends on.

// Request
{
  "sourceAccountId": 4,
  "destinationAccountId": 8,
  "assetCode": "USDC",
  "networkCode": "ethereum",
  "amount": "315000.00000000"
}

// Response 200 — valid
{
  "valid": true,
  "sourceAvailable": "5400000.00000000",
  "sourceMovable": "3900000.00000000",
  "sourceAfter": "5085000.00000000",
  "destinationAfter": "500000.00000000",
  "estimatedFee": { "amount": "11.80000000", "assetCode": "USDC" },
  "allInCostBase": "315011.80000000",
  "fxRate": null,
  "requiredApprovals": 2,
  "approvalReason": "Amount exceeds the 100,000 base threshold",
  "classificationRoute": { "from": "CORPORATE_OPERATING", "to": "COLLATERAL" },
  "gate": { "sourceStatus": "CLEAN", "destinationStatus": "CLEAN",
            "extraApproverRequired": false },
  "blockers": [],
  "warnings": []
}

// Response 200 — blocked (S6b)
{
  "valid": false,
  "blockers": [
    { "code": "SEGREGATION_VIOLATION", "field": "destinationAccountId",
      "message": "Client funds cannot move to a corporate operating account." },
    { "code": "SOURCE_BELOW_MINIMUM", "field": "amount",
      "message": "This would leave the source below its minimum of 1,500,000 USDC." }
  ],
  "warnings": []
}

blockers disable Submit. warnings do not, but may raise requiredApprovals. A connection that is not verified is a warning for a manual movement and a blocker for a rule.

GET /positions/summary

{
  "baseCurrency": "USD",
  "businessDate": "2026-07-31",
  "generatedAtUtc": "2026-08-01T09:14:02.000Z",
  "totalBase": "86339500.00000000",
  "movableBase": "41208300.00000000",
  "oldestDataAgeSeconds": 372,
  "byModule": [
    { "moduleCode": "cards", "moduleName": "Cards",
      "totalBase": "6281000.00000000", "accountCount": 4, "connectionCount": 3,
      "accountsBelowMinimum": 1, "connectionsBlocked": 1,
      "providers": ["Rain", "StraitsX", "Wasabi"] }
  ],
  "counts": { "accountsBelowMinimum": 2, "connectionsBlocked": 3,
              "movementsPendingApproval": 3, "openAlerts": 8 }
}

POST /movements/{code}/approve — the refusal that matters most

// 403
{
  "errorCode": "SELF_APPROVAL_FORBIDDEN",
  "status": 403,
  "detail": "You initiated MV-4473. Segregation of duties requires a different approver."
}

A refused self-approval writes an AuditLog row with action APPROVAL_BLOCKED. A control that refuses silently cannot be evidenced to an auditor.

8.4 Error contract

Use your existing error envelope. What matters is the machine-readable errorCode — the frontend switches on it, never on message text.

9. .NET backend structure

9.1 The central architectural decision

Do not build provider connectors in the Treasury solution.

Provider integration already exists and works inside each module solution. Duplicating it creates two code paths to the same provider, two sets of credentials, and guaranteed drift.

Instead, each module solution gains a thin, additive treasury-facing controller that wraps what it already does. Treasury composes across those five contracts and integrates nothing.

Treasury itself is a new solution alongside the module solutions, not code inside one of them. Treasury is inherently cross-module; placing it inside Cards would make Cards depend on Payments, Banks, Wallets and Exchange, inverting the dependency direction and coupling five deployables.

9.2 The module treasury contract

Define once in your shared package; each module implements it over its existing provider clients. Namespaces and response envelope must match what §11 discovery found.

class="cm">// GET /api/treasury/positions
public record TreasuryPositionDto(
    string   ProviderCode,        class="cm">// class="str">"rain"
    string   ModuleCode,          class="cm">// class="str">"cards"
    string   ProviderAccountRef,  class="cm">// the module's own identifier
    string   AccountName,
    string   AssetCode,
    string?  NetworkCode,         class="cm">// null for fiat
    decimal  Available,
    decimal  PendingIn,
    decimal  PendingOut,
    decimal  Reserved,
    bool     ReservedIsEstimate,  class="cm">// true if derived, not reported by the provider
    string   SourceType,          class="cm">// PROVIDER_API | WEBHOOK | CHAIN_RPC | CACHED
    DateTime AsOfUtc);            class="cm">// the PROVIDER's timestamp, not now()

class="cm">// GET /api/treasury/capabilities
public record TreasuryCapabilityDto(
    string ProviderCode,
    bool   SupportsRealtimeBalance,
    bool   SupportsProgrammaticTransfer,
    bool   ExposesReservedSeparately,
    bool   SupportsWebhooks,
    int    TypicalSettlementSeconds);

class="cm">// POST /api/treasury/transfers
public record TreasuryTransferRequest(
    string IdempotencyKey, string SourceProviderAccountRef,
    string DestinationProviderAccountRef, string AssetCode,
    string? NetworkCode, decimal Amount, string Reference);

public record TreasuryTransferResult(
    string Status,                class="cm">// ACCEPTED | REJECTED | DUPLICATE
    string? ExternalRef, string? FailureReason);

class="cm">// GET /api/treasury/transfers/{externalRef}
public record TreasuryTransferStatusDto(
    string Status,                class="cm">// PENDING | CONFIRMING | SETTLED | FAILED
    string? ExternalRef, string? FailureReason, DateTime? SettledAtUtc);

class="cm">// GET /api/treasury/client-liabilities
class="cm">// What this module owes customers right now. The coverage denominator.
public record ClientLiabilityDto(
    string   ModuleCode,
    string   AssetCode,
    decimal  TotalOwedToCustomers,
    int      CustomerAccountCount,
    DateTime AsOfUtc);

class="cm">// GET /api/treasury/account-activity?ref={providerAccountRef}&sinceUtc={iso}
class="cm">// The module's own record of what moved. Feeds the variance check.
public record AccountActivityDto(
    string   ProviderAccountRef,
    string   AssetCode,
    decimal  TotalIn,
    decimal  TotalOut,
    int      TransactionCount,
    DateTime SinceUtc,
    DateTime UntilUtc,
    bool     IsComplete);      class="cm">// false if the full window cannot be guaranteed

9.3 Project layout

Mirror the structure of whichever existing module solution is cleanest. Identify it in discovery and state which one you copied. Do not blend structures from several.

Treasury.sln
├── Treasury.Api/              controllers — thin: validate, dispatch, map
├── Treasury.Application/      commands, queries, validators
├── Treasury.Domain/           entities, policies, NO infrastructure references
│   └── Policies/              PolicyEngine, GateService — pure, unit tested
├── Treasury.Infrastructure/   persistence, module clients, recon client
└── Treasury.Jobs/             Azure Functions

If a shared package is referenced by every module solution, reference it — do not copy its contents.

9.4 Where the invariants live

Put every rule in §11's table in the domain layer, as pure logic with no infrastructure dependencies, and unit test them first.

If testing the segregation rule requires spinning up a DbContext, the logic is in the wrong layer.

Specifically pure: segregation policy · gate evaluation · approval quorum calculation · movement state machine · rule amount calculation and its six guards.

9.5 Concurrency — two approvers at the same instant

Two managers press Approve on a movement requiring two approvals, at the same moment. Both read approvalsRecorded = 1.

• UQ_MovementApproval (MovementId, ApproverUserId) stops the same person counting twice.
• The transition to APPROVED re-reads the approval count inside the transaction and takes an update lock on the Movement row.
• RowVer on Movement catches the lost update; return 409 CONCURRENCY_CONFLICT and let the client refetch.

9.6 Background jobs

Azure Functions in the existing Function App, following its existing structure, DI, logging and cron conventions.

Universal rules: idempotent · singleton (two rule-engine instances could double-fund) · fail per item not per run · record every run · call domain services, never raw SQL.

9.7 The highest-probability incident

Position sync setting its own timestamp. If AsOfUtc = DateTime.UtcNow at ingestion, every balance looks fresh even when the provider returned a six-hour-old figure. The data-age indicator becomes a lie, the staleness guard stops firing, and rules act on stale numbers at machine speed. Assert on this in the sync tests.

10. React frontend structure

10.1 The rule

Zero new visual primitives.

Every button, input, table, modal, spinner, shimmer, badge, toast and empty state must be an existing component from your library. If treasury appears to need something that does not exist, check how an existing screen solved the same problem. Complete the component map in §11 before writing any JSX.

What treasury adds is composition: the components in §10.3, and new screens built from existing parts.

10.2 Folder layout

Mirror exactly the shape an existing feature occupies. State which one you copied.

features/treasury/
├── api/          one module per resource, matching the existing API-module style
├── components/   the composites in §10.3
├── hooks/        query hooks, matching the existing server-state convention
├── pages/        one per screen in §7
├── types/        generated or hand-written per your convention
└── utils/        treasury-specific ONLY — no formatters, those already exist

10.3 The composites to build once

10.4 Data layer rules

10.5 Frontend rules that are not negotiable

10.6 Loading patterns

11. Discovery — run before writing any code

This feature goes into a live system whose conventions are already decided. Produce TREASURY-CONVENTIONS.md at the repo root first. Every entry must cite a real file path and include a real code excerpt from these repos; an entry with no file path is assumption, not discovery.

11.1 What to record

11.2 Output format

### Response envelope

**Found at:** src/Cards.Api/Common/ApiResponse.cs
**Used in:**  src/Cards.Api/Controllers/CardController.cs:42

public class ApiResponse<T> { bool Success; T Data; string Message; List<ValidationError> Errors; }

**Treasury will:** return ApiResponse<T> from every endpoint, matching this exactly.
**Divergence:** Payments uses Result<T> — src/Payments.Api/Common/Result.cs.
Treasury follows the Cards pattern (4 of 5 solutions). **Confirm.**

End the file with Open questions blocking the build and a Divergences table.

11.3 Stop and ask when

• Two solutions show different patterns for the same concern and you cannot tell which is current
• A change would modify an existing table, endpoint contract or shared component
• A module solution has no working provider method for something treasury needs
• Existing money columns cannot hold the precision this feature requires
• An existing table already covers something in §5.5

12. KPI definitions

If a KPI is not in this list, it must not appear on a screen.

K-1 excludes pendingOut — already committed away. K-2's denominator comes from our own module ledgers; if any module fails to report, show an em dash and never a fallback. K-3 uses available, not total — reserved funds cannot prevent a decline; the related near state is available < minimum × 1.15 and is not counted in K-3.

Colour is never the only carrier of meaning. Every coloured state also has a text label or pill.

12.1 What verification still does not give you

The variance check is a strong control and a poor substitute for reconciliation. Two things remain genuinely missing:

Transaction-level matching. A variance tells you the totals disagree. It does not tell you which transaction is wrong. When Tazapay is 554,054 SGD out, someone still has to find out why, and without matching that is manual work through two systems.

Attestation. Nothing produces a signed daily statement that customer assets covered customer liabilities. If you are subject to safeguarding rules, an auditor will ask for that, and "we polled the provider and the totals looked close" is not an answer.

See §3.4 for how reconciliation slots in later without disturbing what you build now.

13. Test matrix

13.1 Invariants — one test each, named after the rule

T-12 is the test that matters most. Automate it and gate every release on it.

13.2 Concurrency — against a real database, not mocks

13.3 End-to-end journeys

13.4 Boundary values

14. Delivery plan

Each slice is: migration → domain → application → API → tests → merged. Do not build all entities, then all services, then all controllers.

Do not invert domain and UI. Building the UI first produces a screen that looks finished with validation logic living in React. That logic then gets duplicated, imperfectly, in the API. The server is the only place a control actually exists.

Definition of done, per slice: follows TREASURY-CONVENTIONS.md with no new patterns · every touched invariant has a passing negative test · server-side enforcement verified independently of the UI · audit entries asserted in test · loading, empty, error and no-permission states implemented · existing components used, no new primitives · migration has a tested rollback · no existing table, endpoint or shared component modified.

15. Failure modes — read before writing code

16. Decisions needed

Items 1, 2 and 3 block the most. Item 1 decides how much of the estate can be automated at all: a connection with no activity feed runs on freshness only, at halved caps.

17. Generation guide for VS Code

Work in this order. Each step is a prompt you can paste; each assumes the previous step is complete and compiling. Attach this document and the folder in question as context.

Step 0 — Discovery

Read §11 of the attached spec, then analyse every repository provided.
Produce TREASURY-CONVENTIONS.md following §11.1 exactly.

Every convention must cite a real file path and include a real code excerpt
from these repos. Where solutions disagree, document both and recommend one.
List every provider integration and flag any missing balance or transfer
capability, and any provider that cannot separate reserved from available.

Write NO feature code. Stop when the document is complete.

Step 1 — Module treasury contracts

Using §9.2, implement the treasury contract in the <module> solution.

- Additive only: new controller, new DTOs, wrapping the EXISTING provider clients.
- Match that solution's controller style, response envelope, auth and logging
  exactly as recorded in TREASURY-CONVENTIONS.md.
- AsOfUtc MUST come from the provider response. If the provider gives no
  timestamp, return the fetch time and set SourceType accordingly. Never
  DateTime.UtcNow presented as provider truth.
- ExposesReservedSeparately must be honest. If the module cannot distinguish
  authorisation holds, return false and put the best estimate in Reserved.
- If a provider has no transfer method, return SupportsProgrammaticTransfer=false.
  Do not throw and do not fake it.
- Do not modify any existing file except the DI registration.

Step 2 — Database

Using §18, create the treasury migration for the EXISTING database.

- ADDITIVE ONLY. No existing table is altered. No existing data is modified.
- Translate every type and name to the conventions in TREASURY-CONVENTIONS.md:
  money type, datetime type, PK style, table naming, schema placement.
- If the existing money column type cannot hold 18-decimal crypto amounts,
  STOP and report it rather than truncating.
- Before creating any table, check §5.4 and the conventions doc for an existing
  table that already covers it. Reuse rather than duplicate.
- Include a tested rollback.

Step 3 — Domain

Using §5, §6 and §12, create the Treasury.Domain project per §9.3.

- GateService, PolicyEngine and RuleEvaluator are PURE: no I/O, no DbContext,
  no async. Given state in, decision out.
- Every invariant in §13.1 lives in the domain, never in a controller.
- Quorum and AmountBase are computed once at creation and stored. Never
  recomputed on read.
- The movement state machine rejects every skipped transition.
- Use TreasuryAccount, Connection and Movement as the type names. Never
  "Account", "Integration" or "Transaction".
Include xunit tests for every rule in §13.1, named after the rule id.

Step 4 — Application and API

Using §8, create the Application and Api layers.

- POST /movements/preview is the authority on validity. It returns blockers and
  warnings; blockers make the movement uncreatable, warnings may raise quorum.
- POST /movements re-runs the same policy evaluation server-side. Never trust
  a preview result passed back by the client.
- Idempotency middleware on every money-moving POST.
- Self-approval returns 403 AND writes an AuditLog row with action
  APPROVAL_BLOCKED. The audit row is part of the acceptance criteria.
- Error codes exactly as in §8.4, inside the existing error envelope.
Include integration tests against a real database for §13.2.

Step 5 — Jobs

Using §9.6, add the treasury functions to the EXISTING Function App,
following its existing structure, DI, logging and cron conventions.

- RuleEngine implements all six guards from §6.5, each independently testable.
- EVERY rule evaluation writes a RuleExecution row, including skips.
- Idempotency key is rule id + minute bucket, so a double run cannot double-fund.
- PositionSync skips writing when the position is unchanged.
- CoverageCalc: if any module fails to report liabilities, record UNAVAILABLE and raise
  an alert. NEVER substitute a locally computed liability figure.
- Singleton on every timer function.

Step 6 — Frontend foundations

Using §10, create features/treasury in the existing React app.

- Mirror the folder shape of the feature named in TREASURY-CONVENTIONS.md.
- Use the EXISTING api client instance, the existing server-state library,
  the existing formatters, the existing permission hook.
- Build only the composites in §10.3. Create no new visual primitives.
- PositionCell must render available and reserved separately. There is no prop
  that produces a combined balance.
- Add treasury to the existing sidebar file using the existing grouping and
  badge components.

Step 7 — Screens, one at a time

Build screen <Sn> from §7 of the attached spec.

- Use only existing components from <component library path>.
- Match the loading, shimmer, button-loading, table and toast patterns recorded
  in TREASURY-CONVENTIONS.md.
- Implement every acceptance criterion listed for that screen.
- The APIs and tables for the screen are listed in its section and in §19.
- Take all styling from the existing design system. The screenshot shows
  information hierarchy, not visual design.
- Implement loading, empty, error and no-permission states, not just the
  happy path.

18. Table DDL

Neutral dialect. Translate types and names to your conventions before running — see §11.1 and Step 2 above.

-- ═══════════════════════════════════════════════════════════
--  REFERENCE DATA — check first whether a registry exists
-- ═══════════════════════════════════════════════════════════
CREATE TABLE Module (
    ModuleId    INT IDENTITY(1,1) PRIMARY KEY,
    ModuleCode  VARCHAR(24)  NOT NULL UNIQUE,   -- 'cards'
    Name        NVARCHAR(80) NOT NULL,
    SortOrder   TINYINT      NOT NULL DEFAULT 0
);

CREATE TABLE Provider (
    ProviderId   INT IDENTITY(1,1) PRIMARY KEY,
    ProviderCode VARCHAR(32)   NOT NULL UNIQUE,  -- 'rain'
    Name         NVARCHAR(120) NOT NULL,
    Kind         NVARCHAR(200) NOT NULL,
    IsActive     BIT           NOT NULL DEFAULT 1
);

-- One provider serving one module. The unit of verification.
-- Noah / Tazapay / Thunes each appear TWICE.
CREATE TABLE Connection (
    ConnectionId              INT IDENTITY(1,1) PRIMARY KEY,
    ConnectionCode            VARCHAR(48)   NOT NULL UNIQUE,   -- 'rain-cards'
    ProviderId                INT           NOT NULL REFERENCES Provider(ProviderId),
    ModuleId                  INT           NOT NULL REFERENCES Module(ModuleId),
    AdapterVersion            VARCHAR(32)   NOT NULL,
    LiveSince                 DATE          NULL,
    StalenessThresholdSeconds INT           NOT NULL DEFAULT 300,
    AutomationToleranceBase   DECIMAL(28,8) NOT NULL DEFAULT 50000.00,
    IsActive                  BIT           NOT NULL DEFAULT 1,
    RowVer                    ROWVERSION    NOT NULL,
    CONSTRAINT UQ_Connection_ProviderModule UNIQUE (ProviderId, ModuleId)
);

CREATE TABLE Asset (
    AssetCode             VARCHAR(12)  PRIMARY KEY,
    Name                  NVARCHAR(80) NOT NULL,
    AssetType             VARCHAR(16)  NOT NULL,   -- FIAT | CRYPTO | STABLECOIN
    Decimals              TINYINT      NOT NULL,   -- drives rounding everywhere
    ConcentrationLimitPct DECIMAL(5,2) NULL
);

CREATE TABLE Network (
    NetworkCode           VARCHAR(24)  PRIMARY KEY,
    Name                  NVARCHAR(60) NOT NULL,
    NativeAssetCode       VARCHAR(12)  NOT NULL,
    ConfirmationsRequired INT          NOT NULL DEFAULT 12
);

-- ═══════════════════════════════════════════════════════════
--  VERIFICATION STATE — treasury computes all of this itself
-- ═══════════════════════════════════════════════════════════
-- One row per connection, updated in place by the position sync job.
CREATE TABLE ConnectionSyncState (
    ConnectionId        INT           PRIMARY KEY REFERENCES Connection(ConnectionId),
    LastAttemptAtUtc    DATETIME2(3)  NULL,
    LastSuccessAtUtc    DATETIME2(3)  NULL,
    LastStatus          VARCHAR(16)   NOT NULL DEFAULT 'NEVER_RUN',  -- OK|FAILED|RATE_LIMITED|NEVER_RUN
    ConsecutiveFailures INT           NOT NULL DEFAULT 0,
    LastError           NVARCHAR(500) NULL,
    LastDurationMs      INT           NULL,
    AccountsRetrieved   INT           NOT NULL DEFAULT 0,
    CONSTRAINT CK_SyncState_Status CHECK (LastStatus IN ('OK','FAILED','RATE_LIMITED','NEVER_RUN'))
);

-- The variance check. Append-only: the history is the evidence.
CREATE TABLE BalanceCheck (
    BalanceCheckId     BIGINT IDENTITY(1,1) PRIMARY KEY,
    TreasuryAccountId  INT           NOT NULL REFERENCES TreasuryAccount(TreasuryAccountId),
    AssetCode          VARCHAR(12)   NOT NULL,
    ProviderBalance    DECIMAL(28,8) NOT NULL,
    ExpectedBalance    DECIMAL(28,8) NOT NULL,
    Variance           DECIMAL(28,8) NOT NULL,   -- provider − expected
    VarianceBase       DECIMAL(28,8) NOT NULL,
    ToleranceBase      DECIMAL(28,8) NOT NULL,   -- what it was measured against, frozen
    Status             VARCHAR(16)   NOT NULL,   -- WITHIN|OUTSIDE|NO_DATA|STALE
    BaselineBalance    DECIMAL(28,8) NULL,       -- LastVerifiedBalance used
    BaselineAtUtc      DATETIME2(3)  NULL,
    ModuleInflow       DECIMAL(28,8) NOT NULL DEFAULT 0,
    ModuleOutflow      DECIMAL(28,8) NOT NULL DEFAULT 0,
    TreasuryNet        DECIMAL(28,8) NOT NULL DEFAULT 0,
    AcceptedByUserId   INT           NULL,       -- admin rebaseline
    AcceptedReason     NVARCHAR(500) NULL,
    AcceptedAtUtc      DATETIME2(3)  NULL,
    CheckedAtUtc       DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_BalanceCheck_Status CHECK (Status IN ('WITHIN','OUTSIDE','NO_DATA','STALE'))
);
CREATE INDEX IX_BalanceCheck_Latest ON BalanceCheck(TreasuryAccountId, CheckedAtUtc DESC);

-- Coverage denominator, per module so provenance and gaps are visible.
CREATE TABLE ClientLiabilitySnapshot (
    ClientLiabilitySnapshotId BIGINT IDENTITY(1,1) PRIMARY KEY,
    ModuleCode           VARCHAR(24)   NOT NULL,
    AssetCode            VARCHAR(12)   NOT NULL,
    TotalOwedToCustomers DECIMAL(28,8) NOT NULL,
    TotalOwedBase        DECIMAL(28,8) NOT NULL,
    CustomerAccountCount INT           NOT NULL,
    ModuleAsOfUtc        DATETIME2(3)  NOT NULL,   -- when the MODULE computed it
    FetchedAtUtc         DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_ClientLiability_Latest ON ClientLiabilitySnapshot(ModuleCode, AssetCode, FetchedAtUtc DESC);


-- ═══════════════════════════════════════════════════════════
--  ACCOUNTS AND POSITIONS
-- ═══════════════════════════════════════════════════════════
CREATE TABLE TreasuryAccount (
    TreasuryAccountId  INT IDENTITY(1,1) PRIMARY KEY,
    AccountCode        VARCHAR(24)   NOT NULL UNIQUE,
    ConnectionId       INT           NOT NULL REFERENCES Connection(ConnectionId),
    Name               NVARCHAR(120) NOT NULL,
    ProviderAccountRef NVARCHAR(160) NOT NULL,   -- matches the module contract
    Classification     VARCHAR(24)   NOT NULL,
    AssetCode          VARCHAR(12)   NOT NULL REFERENCES Asset(AssetCode),
    NetworkCode        VARCHAR(24)   NULL REFERENCES Network(NetworkCode),
    LegalEntity        NVARCHAR(120) NULL,
    IsActive           BIT           NOT NULL DEFAULT 1,
    CreatedAtUtc       DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    RowVer             ROWVERSION    NOT NULL,
    CONSTRAINT CK_TreasuryAccount_Class CHECK (Classification IN
        ('CLIENT_SAFEGUARDED','COLLATERAL','PREFUND','CORPORATE_OPERATING',
         'CORPORATE_RESERVE','GAS_TANK','FEE_REVENUE'))
);
CREATE INDEX IX_TreasuryAccount_Conn  ON TreasuryAccount(ConnectionId);
CREATE INDEX IX_TreasuryAccount_Class ON TreasuryAccount(Classification);

CREATE TABLE AccountThreshold (
    AccountThresholdId INT IDENTITY(1,1) PRIMARY KEY,
    TreasuryAccountId  INT           NOT NULL REFERENCES TreasuryAccount(TreasuryAccountId),
    AssetCode          VARCHAR(12)   NOT NULL,
    CriticalMinimum    DECIMAL(28,8) NULL,
    MinimumAmount      DECIMAL(28,8) NOT NULL DEFAULT 0,
    TargetAmount       DECIMAL(28,8) NOT NULL DEFAULT 0,
    MaximumAmount      DECIMAL(28,8) NOT NULL DEFAULT 0,   -- 0 = unbounded
    UpdatedByUserId    INT           NULL,
    CONSTRAINT UQ_AccountThreshold UNIQUE (TreasuryAccountId, AssetCode),
    CONSTRAINT CK_AccountThreshold_Order CHECK (
        MinimumAmount >= 0 AND TargetAmount >= MinimumAmount
        AND (MaximumAmount = 0 OR MaximumAmount >= TargetAmount))
);
-- Keep history: temporal table if this database uses them, otherwise your
-- existing audit-history approach.

-- APPEND-ONLY. The historical series is a regulatory asset.
CREATE TABLE PositionSnapshot (
    PositionSnapshotId BIGINT IDENTITY(1,1) PRIMARY KEY,
    TreasuryAccountId  INT              NOT NULL REFERENCES TreasuryAccount(TreasuryAccountId),
    AssetCode          VARCHAR(12)      NOT NULL,
    NetworkCode        VARCHAR(24)      NULL,
    Available          DECIMAL(28,8)    NOT NULL,
    PendingIn          DECIMAL(28,8)    NOT NULL DEFAULT 0,
    PendingOut         DECIMAL(28,8)    NOT NULL DEFAULT 0,
    Reserved           DECIMAL(28,8)    NOT NULL DEFAULT 0,
    ReservedIsEstimate BIT              NOT NULL DEFAULT 0,
    SourceType         VARCHAR(16)      NOT NULL,
    AsOfUtc            DATETIME2(3)     NOT NULL,   -- the PROVIDER's timestamp
    IngestedAtUtc      DATETIME2(3)     NOT NULL DEFAULT SYSUTCDATETIME(),
    CorrelationId      UNIQUEIDENTIFIER NULL,
    CONSTRAINT CK_PositionSnapshot_NonNeg CHECK (
        Available >= 0 AND PendingIn >= 0 AND PendingOut >= 0 AND Reserved >= 0)
);
CREATE INDEX IX_PositionSnapshot_Latest
    ON PositionSnapshot (TreasuryAccountId, AssetCode, NetworkCode, AsOfUtc DESC)
    INCLUDE (Available, PendingIn, PendingOut, Reserved, ReservedIsEstimate, SourceType);

GO
CREATE TRIGGER TR_PositionSnapshot_NoMutate ON PositionSnapshot
INSTEAD OF UPDATE, DELETE AS
BEGIN THROW 50001, 'PositionSnapshot is append-only.', 1; END;
GO

-- ═══════════════════════════════════════════════════════════
--  MOVEMENTS AND APPROVALS
-- ═══════════════════════════════════════════════════════════
CREATE TABLE Destination (
    DestinationId     INT IDENTITY(1,1) PRIMARY KEY,
    Label             NVARCHAR(120) NOT NULL,
    TreasuryAccountId INT           NULL,
    AddressOrRef      NVARCHAR(200) NOT NULL,
    NetworkCode       VARCHAR(24)   NULL,
    IsApproved        BIT           NOT NULL DEFAULT 0,
    ApprovedByUserId1 INT           NULL,
    ApprovedByUserId2 INT           NULL,
    ApprovedAtUtc     DATETIME2(3)  NULL,
    CONSTRAINT UQ_Destination UNIQUE (AddressOrRef, NetworkCode),
    -- adding an allowlist entry is itself a dual-approval act
    CONSTRAINT CK_Destination_DualApproval CHECK (
        IsApproved = 0 OR (ApprovedByUserId1 IS NOT NULL
                       AND ApprovedByUserId2 IS NOT NULL
                       AND ApprovedByUserId1 <> ApprovedByUserId2))
);

CREATE TABLE LiquidityRule (
    LiquidityRuleId       INT IDENTITY(1,1) PRIMARY KEY,
    RuleCode              VARCHAR(48)   NOT NULL UNIQUE,
    Name                  NVARCHAR(160) NOT NULL,
    SourceAccountId       INT           NOT NULL REFERENCES TreasuryAccount(TreasuryAccountId),
    DestinationAccountId  INT           NOT NULL REFERENCES TreasuryAccount(TreasuryAccountId),
    AssetCode             VARCHAR(12)   NOT NULL,
    NetworkCode           VARCHAR(24)   NULL,
    Strategy              VARCHAR(24)   NOT NULL,   -- TO_TARGET|FULL|EXCESS_OVER_MAX|FIXED
    FixedAmount           DECIMAL(28,8) NULL,
    MaxAmountPerRun       DECIMAL(28,8) NOT NULL,
    MaxDailyValue         DECIMAL(28,8) NOT NULL,
    MaxExecutionsPerDay   TINYINT       NOT NULL DEFAULT 4,
    ApprovalThresholdBase DECIMAL(28,8) NOT NULL DEFAULT 0,
    SourceMinRemaining    DECIMAL(28,8) NOT NULL DEFAULT 0,
    MaxDataAgeSeconds     INT           NOT NULL DEFAULT 900,
    IsEnabled             BIT           NOT NULL DEFAULT 0,
    LastDryRunAtUtc       DATETIME2(3)  NULL,
    LastDryRunConfigHash  VARCHAR(64)   NULL,
    CreatedByUserId       INT           NULL,
    RowVer                ROWVERSION    NOT NULL,
    CONSTRAINT CK_LiquidityRule_NotSelf CHECK (SourceAccountId <> DestinationAccountId),
    -- a rule cannot be armed without a dry run
    CONSTRAINT CK_LiquidityRule_DryRun CHECK (IsEnabled = 0 OR LastDryRunAtUtc IS NOT NULL)
);

CREATE TABLE Movement (
    MovementId           BIGINT IDENTITY(1,1) PRIMARY KEY,
    MovementCode         VARCHAR(20)    NOT NULL UNIQUE,   -- 'MV-4472'
    MovementType         VARCHAR(20)    NOT NULL,
    Status               VARCHAR(24)    NOT NULL,
    SourceAccountId      INT            NOT NULL REFERENCES TreasuryAccount(TreasuryAccountId),
    DestinationAccountId INT            NOT NULL REFERENCES TreasuryAccount(TreasuryAccountId),
    DestinationId        INT            NULL REFERENCES Destination(DestinationId),
    AssetCode            VARCHAR(12)    NOT NULL,
    NetworkCode          VARCHAR(24)    NULL,
    Amount               DECIMAL(28,8)  NOT NULL,
    FeeAmount            DECIMAL(28,8)  NULL,
    FeeAssetCode         VARCHAR(12)    NULL,
    FxRate               DECIMAL(28,10) NULL,
    AmountBase           DECIMAL(28,8)  NOT NULL,   -- FROZEN at creation
    RequiredApprovals    TINYINT        NOT NULL,   -- FROZEN at creation
    GateStatusAtCreation VARCHAR(12)    NOT NULL,
    IdempotencyKey       VARCHAR(64)    NOT NULL UNIQUE,
    InitiatedByUserId    INT            NULL,       -- NULL when a rule created it
    LiquidityRuleId      INT            NULL REFERENCES LiquidityRule(LiquidityRuleId),
    Reference            NVARCHAR(200)  NULL,
    ExternalRef          NVARCHAR(128)  NULL,
    FailureReason        NVARCHAR(500)  NULL,
    RejectReason         NVARCHAR(500)  NULL,
    CreatedAtUtc         DATETIME2(3)   NOT NULL DEFAULT SYSUTCDATETIME(),
    SubmittedAtUtc       DATETIME2(3)   NULL,
    SettledAtUtc         DATETIME2(3)   NULL,
    RowVer               ROWVERSION     NOT NULL,
    CONSTRAINT CK_Movement_Positive CHECK (Amount > 0),
    CONSTRAINT CK_Movement_NotSelf  CHECK (SourceAccountId <> DestinationAccountId),
    CONSTRAINT CK_Movement_Actor    CHECK (InitiatedByUserId IS NOT NULL OR LiquidityRuleId IS NOT NULL),
    CONSTRAINT CK_Movement_Type   CHECK (MovementType IN
        ('INTERNAL','TOP_UP','SWEEP','FX','FEE_SWEEP','MINT','BURN')),
    CONSTRAINT CK_Movement_Status CHECK (Status IN
        ('DRAFT','PENDING_APPROVAL','APPROVED','SUBMITTED','CONFIRMING',
         'SETTLED','FAILED','REJECTED','CANCELLED','MANUALLY_RESOLVED'))
);
CREATE INDEX IX_Movement_Status ON Movement(Status, CreatedAtUtc DESC);
CREATE INDEX IX_Movement_Pending ON Movement(CreatedAtUtc DESC)
    WHERE Status = 'PENDING_APPROVAL';   -- the approvals queue

-- The UNIQUE constraint is what enforces one approval per person.
CREATE TABLE MovementApproval (
    MovementApprovalId BIGINT IDENTITY(1,1) PRIMARY KEY,
    MovementId         BIGINT        NOT NULL REFERENCES Movement(MovementId),
    ApproverUserId     INT           NOT NULL,
    Decision           VARCHAR(12)   NOT NULL,   -- APPROVED | REJECTED
    Reason             NVARCHAR(500) NULL,
    StepUpVerified     BIT           NOT NULL DEFAULT 0,
    DecidedAtUtc       DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_MovementApproval UNIQUE (MovementId, ApproverUserId)
);

CREATE TABLE MovementEvent (
    MovementEventId BIGINT IDENTITY(1,1) PRIMARY KEY,
    MovementId      BIGINT        NOT NULL REFERENCES Movement(MovementId),
    FromStatus      VARCHAR(24)   NULL,
    ToStatus        VARCHAR(24)   NOT NULL,
    ActorUserId     INT           NULL,
    ActorType       VARCHAR(12)   NOT NULL,   -- USER | RULE | SYSTEM | PROVIDER
    Reason          NVARCHAR(500) NULL,
    OccurredAtUtc   DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME()
);

-- Every evaluation, INCLUDING skips. Without skip rows, "why didn't the rule
-- fire last night?" is unanswerable — and that is asked after every incident.
CREATE TABLE RuleExecution (
    RuleExecutionId BIGINT IDENTITY(1,1) PRIMARY KEY,
    LiquidityRuleId INT           NOT NULL REFERENCES LiquidityRule(LiquidityRuleId),
    TriggeredAtUtc  DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    Outcome         VARCHAR(24)   NOT NULL,   -- CREATED|NOT_TRIGGERED|GATE_BLOCKED|
                                              -- STALE_DATA|SOURCE_FLOOR|DAILY_CAP|
                                              -- KILL_SWITCH|ERROR
    Reason          NVARCHAR(500) NULL,
    ComputedAmount  DECIMAL(28,8) NULL,
    MovementId      BIGINT        NULL REFERENCES Movement(MovementId),
    DurationMs      INT           NULL
);

-- ═══════════════════════════════════════════════════════════
--  DERIVED AND SUPPORTING — check first for existing equivalents
-- ═══════════════════════════════════════════════════════════
CREATE TABLE FxRate (
    FxRateId   BIGINT IDENTITY(1,1) PRIMARY KEY,
    BaseAsset  VARCHAR(12)    NOT NULL,
    QuoteAsset VARCHAR(12)    NOT NULL,
    Rate       DECIMAL(28,10) NOT NULL CHECK (Rate > 0),
    Source     VARCHAR(32)    NOT NULL,
    ValidAtUtc DATETIME2(3)   NOT NULL
);
CREATE INDEX IX_FxRate_Lookup ON FxRate(BaseAsset, QuoteAsset, ValidAtUtc DESC) INCLUDE (Rate);

CREATE TABLE CoverageSnapshot (
    CoverageSnapshotId BIGINT IDENTITY(1,1) PRIMARY KEY,
    LiabilitiesBase    DECIMAL(28,8) NULL,   -- NULL when a module is not reporting
    LiabilitySourceAt  DATETIME2(3)  NULL,   -- provenance
    SegregatedBase     DECIMAL(28,8) NOT NULL,
    RatioPct           DECIMAL(9,4)  NULL,
    Status             VARCHAR(20)   NOT NULL,
    ShortfallBase      DECIMAL(28,8) NULL,
    ComputedAtUtc      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_CoverageSnapshot_Status CHECK (Status IN
        ('HEALTHY','INSIDE_BUFFER','SHORTFALL','UNAVAILABLE'))
);

CREATE TABLE ForecastPoint (
    ForecastPointId   BIGINT IDENTITY(1,1) PRIMARY KEY,
    ForecastRunId     UNIQUEIDENTIFIER NOT NULL,
    TreasuryAccountId INT           NOT NULL REFERENCES TreasuryAccount(TreasuryAccountId),
    AssetCode         VARCHAR(12)   NOT NULL,
    ForecastDate      DATE          NOT NULL,
    ProjectedBalance  DECIMAL(28,8) NOT NULL,
    ProjectedInflow   DECIMAL(28,8) NOT NULL DEFAULT 0,
    ProjectedOutflow  DECIMAL(28,8) NOT NULL DEFAULT 0,
    BreachesMinimum   BIT           NOT NULL DEFAULT 0,
    GeneratedAtUtc    DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE AlertInstance (
    AlertInstanceId      BIGINT IDENTITY(1,1) PRIMARY KEY,
    AlertType            VARCHAR(48)   NOT NULL,
    Severity             VARCHAR(12)   NOT NULL,   -- INFO|WARNING|CRITICAL|PAGE
    DedupKey             VARCHAR(160)  NOT NULL,
    Message              NVARCHAR(500) NOT NULL,   -- server-composed
    TargetRoute          NVARCHAR(200) NULL,       -- server tells the UI where to go
    SourceType           VARCHAR(24)   NOT NULL,
    RelatedEntityId      BIGINT        NULL,
    Status               VARCHAR(16)   NOT NULL DEFAULT 'OPEN',
    RaisedAtUtc          DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    AcknowledgedByUserId INT           NULL,
    AcknowledgedAtUtc    DATETIME2(3)  NULL,
    ResolvedAtUtc        DATETIME2(3)  NULL
);
-- one open alert per condition
CREATE UNIQUE INDEX UX_AlertInstance_OpenDedup ON AlertInstance(DedupKey)
    WHERE Status <> 'RESOLVED';

-- USE THE EXISTING AUDIT LOG IF ONE EXISTS.
CREATE TABLE AuditLog (
    AuditLogId    BIGINT IDENTITY(1,1) PRIMARY KEY,
    ActorUserId   INT              NULL,
    ActorType     VARCHAR(12)      NOT NULL,   -- USER | SYSTEM | RULE
    ActorName     NVARCHAR(120)    NOT NULL,
    Action        VARCHAR(64)      NOT NULL,
    ResourceType  VARCHAR(48)      NOT NULL,
    ResourceId    NVARCHAR(64)     NULL,
    Description   NVARCHAR(600)    NOT NULL,
    BeforeJson    NVARCHAR(MAX)    NULL,
    AfterJson     NVARCHAR(MAX)    NULL,
    CorrelationId UNIQUEIDENTIFIER NULL,
    OccurredAtUtc DATETIME2(3)     NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
CREATE TRIGGER TR_AuditLog_NoMutate ON AuditLog
INSTEAD OF UPDATE, DELETE AS
BEGIN THROW 50002, 'AuditLog is immutable.', 1; END;
GO

CREATE TABLE IdempotencyRecord (
    IdempotencyKey VARCHAR(64)   PRIMARY KEY,
    Endpoint       VARCHAR(120)  NOT NULL,
    RequestHash    VARCHAR(64)   NOT NULL,
    ResponseStatus INT           NOT NULL,
    ResponseJson   NVARCHAR(MAX) NULL,
    CreatedAtUtc   DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE SystemSetting (
    SettingKey      VARCHAR(64)   PRIMARY KEY,
    SettingValue    NVARCHAR(400) NOT NULL,
    UpdatedByUserId INT           NULL,
    UpdatedAtUtc    DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE JobRun (
    JobRunId       BIGINT IDENTITY(1,1) PRIMARY KEY,
    JobName        VARCHAR(64)   NOT NULL,
    StartedAtUtc   DATETIME2(3)  NOT NULL,
    FinishedAtUtc  DATETIME2(3)  NULL,
    ItemsProcessed INT           NOT NULL DEFAULT 0,
    ItemsFailed    INT           NOT NULL DEFAULT 0,
    Status         VARCHAR(16)   NOT NULL,
    ErrorSummary   NVARCHAR(MAX) NULL
);

18.1 The three views that back most of the API

-- The single most-used object in the system.
CREATE VIEW vw_CurrentPosition AS
SELECT p.TreasuryAccountId, p.AssetCode, p.NetworkCode,
       p.Available, p.PendingIn, p.PendingOut, p.Reserved, p.ReservedIsEstimate,
       p.Available + p.PendingIn + p.Reserved AS Total,
       CASE WHEN t.MinimumAmount IS NULL THEN p.Available
            WHEN p.Available - t.MinimumAmount < 0 THEN 0
            ELSE p.Available - t.MinimumAmount END AS Movable,
       t.MinimumAmount, t.TargetAmount, t.MaximumAmount,
       CASE WHEN t.MinimumAmount IS NULL OR t.MinimumAmount = 0 THEN 'NONE'
            WHEN p.Available <  t.MinimumAmount        THEN 'BELOW'
            WHEN p.Available <  t.MinimumAmount * 1.15 THEN 'NEAR'
            WHEN t.MaximumAmount > 0
             AND p.Available >  t.MaximumAmount        THEN 'ABOVE'
            ELSE 'OK' END AS HealthStatus,
       p.AsOfUtc, p.SourceType,
       DATEDIFF(SECOND, p.AsOfUtc, SYSUTCDATETIME()) AS DataAgeSeconds
FROM (SELECT *, ROW_NUMBER() OVER (
         PARTITION BY TreasuryAccountId, AssetCode, NetworkCode
         ORDER BY AsOfUtc DESC, PositionSnapshotId DESC) AS rn
      FROM PositionSnapshot) p
LEFT JOIN AccountThreshold t
  ON t.TreasuryAccountId = p.TreasuryAccountId AND t.AssetCode = p.AssetCode
WHERE p.rn = 1;

-- Position in base currency with account, provider and module context.
CREATE VIEW vw_PositionBase AS
SELECT a.TreasuryAccountId, a.AccountCode, a.Name AS AccountName, a.Classification,
       a.ProviderAccountRef, c.ConnectionId, c.ConnectionCode,
       pr.Name AS ProviderName, m.ModuleCode, m.Name AS ModuleName,
       cp.AssetCode, cp.NetworkCode, cp.Available, cp.Reserved, cp.PendingIn,
       cp.PendingOut, cp.Total, cp.Movable, cp.MinimumAmount, cp.TargetAmount,
       cp.MaximumAmount, cp.HealthStatus, cp.DataAgeSeconds,
       cp.ReservedIsEstimate, cp.AsOfUtc,
       cp.Total     * ISNULL(fx.Rate,1) AS TotalBase,
       cp.Movable   * ISNULL(fx.Rate,1) AS MovableBase,
       cp.Available * ISNULL(fx.Rate,1) AS AvailableBase
FROM TreasuryAccount a
JOIN Connection c ON c.ConnectionId = a.ConnectionId
JOIN Provider  pr ON pr.ProviderId  = c.ProviderId
JOIN Module    m  ON m.ModuleId     = c.ModuleId
JOIN vw_CurrentPosition cp ON cp.TreasuryAccountId = a.TreasuryAccountId
LEFT JOIN (SELECT BaseAsset, QuoteAsset, Rate FROM
             (SELECT *, ROW_NUMBER() OVER (PARTITION BY BaseAsset, QuoteAsset
                        ORDER BY ValidAtUtc DESC) rn FROM FxRate) x
           WHERE rn = 1) fx
  ON fx.BaseAsset = cp.AssetCode AND fx.QuoteAsset = 'USD'
WHERE a.IsActive = 1;

-- THE GATE, in SQL. Mirrors GateService exactly.

19. API → table reference

R = reads · W = writes. Use this to scope a ticket, trace a bug from screen to table, and know what a schema change will break.

19.1 Positions and accounts

19.2 Movements

19.3 Decisions

A refused self-approval writes to AuditLog even though nothing else changes. That row is the evidence the control works.

19.4 Rules and automation

19.5 Connections, coverage, alerts, audit

19.6 Background jobs

19.7 Table → consumer

What breaks if you change it.

20. Seed data

Reproduces exactly the state in the §7 screenshots so QA can compare screen against script. Non-production only.

20.1 The deliberate test conditions

These are fixtures, not data errors.

20.2 Expected values after seeding

Every screen must show these. The script ends with verification queries that print the same figures; a mismatch is an application defect, not a data problem.

20.3 Reference and connections

SET IDENTITY_INSERT Module ON;
INSERT INTO Module (ModuleId, ModuleCode, Name, SortOrder) VALUES
 (1,'wallets','Wallets',1),(2,'cards','Cards',2),(3,'banks','Banks',4),
 (4,'payments','Payments',3),(5,'exchange','Exchange',5);
SET IDENTITY_INSERT Module OFF;

SET IDENTITY_INSERT Provider ON;
INSERT INTO Provider (ProviderId, ProviderCode, Name, Kind) VALUES
 (1,'dfns','DFNS','MPC wallet custody'),
 (2,'rain','Rain','Card issuing and processing'),
 (3,'straitsx','StraitsX','Card issuing, SGD stablecoin rails'),
 (4,'wasabi','Wasabi','Card issuing'),
 (5,'noah','Noah','Banking rails and payment settlement'),
 (6,'tazapay','Tazapay','Cross-border banking and payments'),
 (7,'openpayd','OpenPayd','Banking-as-a-service, virtual IBANs'),
 (8,'thunes','Thunes','Global payout network and banking'),
 (9,'mesta','Mesta','Payment corridors'),
 (10,'kraken','Kraken','Exchange and liquidity'),
 (11,'binance','Binance','Exchange and liquidity');
SET IDENTITY_INSERT Provider OFF;

-- Noah, Tazapay and Thunes each appear TWICE — independent positions.
SET IDENTITY_INSERT Connection ON;
INSERT INTO Connection (ConnectionId, ConnectionCode, ProviderId, ModuleId,
                        AdapterVersion, LiveSince, StalenessThresholdSeconds,
                        AutomationToleranceBase) VALUES
 (1, 'dfns-wallets',     1,1,'dfns-v2.0',    '2025-11-04', 60,50000.00),
 (2, 'rain-cards',       2,2,'rain-v2.8',    '2025-09-18',300,50000.00),
 (3, 'straitsx-cards',   3,2,'straitsx-v3.2','2026-01-22',300,50000.00),
 (4, 'wasabi-cards',     4,2,'wasabi-v1.4',  '2026-03-10',300,50000.00),
 (5, 'noah-banks',       5,3,'noah-v1.6',    '2025-10-02',900,50000.00),
 (6, 'noah-payments',    5,4,'noah-v1.6',    '2025-10-02',900,50000.00),
 (7, 'tazapay-banks',    6,3,'tazapay-v2.1', '2025-08-14',900,50000.00),
 (8, 'tazapay-payments', 6,4,'tazapay-v2.1', '2025-08-14',900,50000.00),
 (9, 'openpayd-banks',   7,3,'openpayd-v1.9','2025-12-01',900,50000.00),
 (10,'thunes-banks',     8,3,'thunes-v2.3',  '2026-02-11',900,50000.00),
 (11,'thunes-payments',  8,4,'thunes-v2.3',  '2026-02-11',900,50000.00),
 (12,'mesta-payments',   9,4,'mesta-v1.2',   '2026-04-08',900,50000.00),
 (13,'kraken-exchange', 10,5,'kraken-v1.1',  '2026-05-19',120,50000.00),
 (14,'binance-exchange',11,5,'binance-v1.0', '2026-06-24',120,50000.00);
SET IDENTITY_INSERT Connection OFF;

INSERT INTO Asset (AssetCode,Name,AssetType,Decimals,ConcentrationLimitPct) VALUES
 ('USD','US Dollar','FIAT',2,60.00),        ('SGD','Singapore Dollar','FIAT',2,25.00),
 ('EUR','Euro','FIAT',2,15.00),             ('GBP','Pound Sterling','FIAT',2,10.00),
 ('MYR','Malaysian Ringgit','FIAT',2,5.00), ('USDC','USD Coin','STABLECOIN',6,40.00),
 ('USDT','Tether','STABLECOIN',6,15.00),    ('XSGD','StraitsX SGD','STABLECOIN',6,10.00),
 ('ETH','Ether','CRYPTO',18,2.00),          ('POL','Polygon','CRYPTO',18,2.00),
 ('TRX','Tron','CRYPTO',6,2.00);

INSERT INTO Network (NetworkCode,Name,NativeAssetCode,ConfirmationsRequired) VALUES
 ('ethereum','Ethereum','ETH',12),('polygon','Polygon','POL',128),('tron','Tron','TRX',19);

-- Fixed rates so every test is deterministic. Do not randomise.
INSERT INTO FxRate (BaseAsset,QuoteAsset,Rate,Source,ValidAtUtc) VALUES
 ('USD','USD',1.0000000000,'SEED','2026-08-01T00:00:00'),
 ('SGD','USD',0.7400000000,'SEED','2026-08-01T00:00:00'),
 ('EUR','USD',1.0800000000,'SEED','2026-08-01T00:00:00'),
 ('GBP','USD',1.2700000000,'SEED','2026-08-01T00:00:00'),
 ('MYR','USD',0.2100000000,'SEED','2026-08-01T00:00:00'),
 ('USDC','USD',1.0000000000,'SEED','2026-08-01T00:00:00'),
 ('USDT','USD',1.0000000000,'SEED','2026-08-01T00:00:00'),
 ('XSGD','USD',0.7400000000,'SEED','2026-08-01T00:00:00'),
 ('ETH','USD',3500.0000000000,'SEED','2026-08-01T00:00:00'),
 ('POL','USD',0.4500000000,'SEED','2026-08-01T00:00:00'),
 ('TRX','USD',0.1300000000,'SEED','2026-08-01T00:00:00');

20.4 Sync state and balance checks — three deliberately blocked

-- 11 connections syncing normally; 3 deliberately broken in different ways.
INSERT INTO ConnectionSyncState
 (ConnectionId,LastAttemptAtUtc,LastSuccessAtUtc,LastStatus,ConsecutiveFailures,LastError) VALUES
 (1, '2026-08-01T09:15:30','2026-08-01T09:15:30','OK',          0,NULL),
 (2, '2026-08-01T09:12:00','2026-08-01T09:12:00','OK',          0,NULL),
 (3, '2026-08-01T09:13:00','2026-08-01T09:13:00','OK',          0,NULL),
 (4, '2026-08-01T09:15:00','2026-07-31T18:40:00','FAILED',      7,'401 Unauthorized — API credential rejected'),  -- BLOCKED: sync
 (5, '2026-08-01T09:08:00','2026-08-01T09:08:00','OK',          0,NULL),
 (6, '2026-08-01T09:08:00','2026-08-01T09:08:00','OK',          0,NULL),
 (7, '2026-08-01T09:06:00','2026-08-01T09:06:00','OK',          0,NULL),  -- BLOCKED below, on variance
 (8, '2026-08-01T09:06:00','2026-08-01T09:06:00','OK',          0,NULL),
 (9, '2026-08-01T09:04:00','2026-08-01T09:04:00','OK',          0,NULL),
 (10,'2026-08-01T09:07:00','2026-08-01T09:07:00','OK',          0,NULL),
 (11,'2026-08-01T09:07:00','2026-08-01T09:07:00','OK',          0,NULL),
 (12,'2026-08-01T09:15:00','2026-08-01T05:10:00','RATE_LIMITED',1,'429 Too Many Requests — provider rate limit'), -- BLOCKED: freshness
 (13,'2026-08-01T09:15:00','2026-08-01T09:15:00','OK',          0,NULL),  -- FRESHNESS_ONLY
 (14,'2026-08-01T09:15:00','2026-08-01T09:15:00','OK',          0,NULL);

-- The variance check. Every account is checked; only the fixtures below differ from zero.
-- Tazapay SGD is the deliberate breach: -554,054.05 SGD = -410,000 base, past the 50,000 tolerance.
INSERT INTO BalanceCheck
 (TreasuryAccountId,AssetCode,ProviderBalance,ExpectedBalance,Variance,VarianceBase,
  ToleranceBase,Status,ModuleInflow,ModuleOutflow,TreasuryNet,CheckedAtUtc) VALUES
 (1, 'USDC',14200000.00,14200000.00,      0.00,       0.00,50000,'WITHIN',      0.00,      0.00,      0.00,'2026-08-01T09:15:30'),
 (4, 'USDC', 5400000.00, 5400000.00,      0.00,       0.00,50000,'WITHIN',      0.00,      0.00,      0.00,'2026-08-01T09:15:30'),
 (8, 'USDC',  185000.00,  186250.00,  -1250.00,   -1250.00,50000,'WITHIN',      0.00,  66250.00, 250000.00,'2026-08-01T09:12:00'),
 (10,'XSGD', 5000000.00, 5000000.00,      0.00,       0.00,50000,'WITHIN',      0.00,      0.00,      0.00,'2026-08-01T09:13:00'),
 (13,'USD',  9800000.00, 9818400.00, -18400.00,  -18400.00,50000,'WITHIN', 412000.00, 430400.00,      0.00,'2026-08-01T09:08:00'),
 (14,'SGD',  8500000.00, 9054054.05,-554054.05, -410000.00,50000,'OUTSIDE',1200000.00,645945.95,     0.00,'2026-08-01T09:06:00'),
 (20,'USD',  1850000.00, 1850000.00,      0.00,       0.00,50000,'WITHIN',      0.00,      0.00,      0.00,'2026-08-01T09:08:00'),
 (22,'USD',   620000.00,  620000.00,      0.00,       0.00,50000,'WITHIN',      0.00, 180000.00,      0.00,'2026-08-01T09:07:00'),
 (23,'USD',  1240000.00, 1233600.00,   6400.00,    6400.00,50000,'STALE',       0.00,   6400.00,      0.00,'2026-08-01T05:10:00');
-- Account 11 (Wasabi collateral) deliberately has NO row: its connection cannot sync.

-- Client liabilities from our own module ledgers. Sum = 47,262,000 = the K-2 denominator.
INSERT INTO ClientLiabilitySnapshot
 (ModuleCode,AssetCode,TotalOwedToCustomers,TotalOwedBase,CustomerAccountCount,ModuleAsOfUtc) VALUES
 ('wallets','USD',26270000.00,26270000.00,18422,'2026-08-01T09:10:00'),
 ('banks',  'USD',20992000.00,20992000.00, 6104,'2026-08-01T09:10:00');

20.5 Accounts, positions and thresholds

SET IDENTITY_INSERT TreasuryAccount ON;
INSERT INTO TreasuryAccount (TreasuryAccountId,AccountCode,ConnectionId,Name,
                             ProviderAccountRef,Classification,AssetCode,NetworkCode,LegalEntity) VALUES
 (1, 'ACC-W01', 1,'Client custody USDC',       'wa-3f81c092','CLIENT_SAFEGUARDED','USDC','ethereum','PlatformCo Pte Ltd'),
 (2, 'ACC-W02', 1,'Client custody USDT',       'wa-77b1d410','CLIENT_SAFEGUARDED','USDT','tron',    'PlatformCo Pte Ltd'),
 (3, 'ACC-W03', 1,'Client custody USDC (Poly)','wa-2c94ff10','CLIENT_SAFEGUARDED','USDC','polygon', 'PlatformCo Pte Ltd'),
 (4, 'ACC-W04', 1,'Treasury hot wallet',       'wa-91ae0071','CORPORATE_OPERATING','USDC','ethereum','PlatformCo Pte Ltd'),
 (5, 'ACC-W05', 1,'Gas tank Ethereum',         'wa-gas-eth01','GAS_TANK','ETH','ethereum','PlatformCo Pte Ltd'),
 (6, 'ACC-W06', 1,'Gas tank Polygon',          'wa-gas-pol01','GAS_TANK','POL','polygon','PlatformCo Pte Ltd'),
 (7, 'ACC-W07', 1,'Fee collection wallet',     'wa-fee-0012','FEE_REVENUE','USDC','ethereum','PlatformCo Pte Ltd'),
 (8, 'ACC-C01', 2,'Rain card collateral',      'rn-col-0087','COLLATERAL','USDC','ethereum','PlatformCo Pte Ltd'),
 (9, 'ACC-C02', 2,'Rain settlement account',   'rn-set-0087','CORPORATE_OPERATING','USD',NULL,'PlatformCo Pte Ltd'),
 (10,'ACC-C03', 3,'StraitsX card float',       'sx-flt-2210','COLLATERAL','XSGD',NULL,'PlatformCo Pte Ltd'),
 (11,'ACC-C04', 4,'Wasabi card collateral',    'wb-col-0441','COLLATERAL','USDC','ethereum','PlatformCo Pte Ltd'),
 (12,'ACC-B01', 5,'Noah USD operating',        'nh-usd-1001','CORPORATE_OPERATING','USD',NULL,'PlatformCo Pte Ltd'),
 (13,'ACC-B02', 5,'Noah client segregated',    'nh-seg-1002','CLIENT_SAFEGUARDED','USD',NULL,'PlatformCo Pte Ltd'),
 (14,'ACC-B03', 7,'Tazapay SGD collections',   'tz-sgd-3301','CLIENT_SAFEGUARDED','SGD',NULL,'PlatformCo Pte Ltd'),
 (15,'ACC-B04', 7,'Tazapay USD operating',     'tz-usd-3302','CORPORATE_OPERATING','USD',NULL,'PlatformCo Pte Ltd'),
 (16,'ACC-B05', 9,'OpenPayd EUR IBAN',         'op-eur-5510','CLIENT_SAFEGUARDED','EUR',NULL,'PlatformCo Europe BV'),
 (17,'ACC-B06', 9,'OpenPayd GBP IBAN',         'op-gbp-5511','CLIENT_SAFEGUARDED','GBP',NULL,'PlatformCo UK Ltd'),
 (18,'ACC-B07', 9,'OpenPayd corporate reserve','op-usd-5512','CORPORATE_RESERVE','USD',NULL,'PlatformCo Pte Ltd'),
 (19,'ACC-B08',10,'Thunes banking float',      'th-bnk-7701','CORPORATE_OPERATING','USD',NULL,'PlatformCo Pte Ltd'),
 (20,'ACC-P01', 6,'Noah payout prefund',       'nh-pay-2001','PREFUND','USD',NULL,'PlatformCo Pte Ltd'),
 (21,'ACC-P02', 8,'Tazapay payout prefund',    'tz-pay-4401','PREFUND','USD',NULL,'PlatformCo Pte Ltd'),
 (22,'ACC-P03',11,'Thunes payout prefund',     'th-pay-7801','PREFUND','USD',NULL,'PlatformCo Pte Ltd'),
 (23,'ACC-P04',12,'Mesta corridor prefund',    'ms-cor-9901','PREFUND','USD',NULL,'PlatformCo Pte Ltd'),
 (24,'ACC-E01',13,'Kraken trading balance',    'kr-trd-0001','CORPORATE_OPERATING','USD',NULL,'PlatformCo Pte Ltd'),
 (25,'ACC-E02',13,'Kraken USDC balance',       'kr-usdc-001','CORPORATE_OPERATING','USDC','ethereum','PlatformCo Pte Ltd'),
 (26,'ACC-E03',14,'Binance trading balance',   'bn-trd-0001','CORPORATE_OPERATING','USDT','tron','PlatformCo Pte Ltd');
SET IDENTITY_INSERT TreasuryAccount OFF;

-- Reserved is populated only for card collateral (authorisation holds).
-- Wasabi reserved is an ESTIMATE and its position is deliberately stale.
INSERT INTO PositionSnapshot (TreasuryAccountId,AssetCode,NetworkCode,Available,
                              PendingIn,PendingOut,Reserved,ReservedIsEstimate,
                              SourceType,AsOfUtc) VALUES
 (1, 'USDC','ethereum',14200000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T09:12:00'),
 (2, 'USDT','tron',     8100000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T09:12:00'),
 (3, 'USDC','polygon',  4300000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T09:12:00'),
 (4, 'USDC','ethereum', 5400000.00000000,120000,0,     0,0,'PROVIDER_API','2026-08-01T09:12:00'),
 (5, 'ETH', 'ethereum',      48.00000000,     0,0,     0,0,'CHAIN_RPC',   '2026-08-01T09:14:00'),
 (6, 'POL', 'polygon',    12000.00000000,     0,0,     0,0,'CHAIN_RPC',   '2026-08-01T09:14:00'), -- BELOW
 (7, 'USDC','ethereum',  250100.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T09:12:00'),
 (8, 'USDC','ethereum',  185000.00000000,     0,0,612000,0,'PROVIDER_API','2026-08-01T09:12:00'), -- BELOW
 (9, 'USD', NULL,       1050800.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:55:00'),
 (10,'XSGD',NULL,       5000000.00000000,     0,0,180000,0,'PROVIDER_API','2026-08-01T09:02:00'),
 (11,'USDC','ethereum',  420000.00000000,     0,0,180000,1,'PROVIDER_API','2026-07-31T18:40:00'), -- stale + est.
 (12,'USD', NULL,      10400000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:30:00'),
 (13,'USD', NULL,       9800000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:30:00'),
 (14,'SGD', NULL,       8500000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:20:00'),
 (15,'USD', NULL,        499000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:20:00'),
 (16,'EUR', NULL,       3200000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T07:50:00'),
 (17,'GBP', NULL,       1400000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T07:50:00'),
 (18,'USD', NULL,       4000000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T07:50:00'),
 (19,'USD', NULL,       1372000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:44:00'),
 (20,'USD', NULL,       1850000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:44:00'),
 (21,'USD', NULL,        960000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:44:00'),
 (22,'USD', NULL,        620000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T08:44:00'), -- NEAR
 (23,'USD', NULL,       1240000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T05:10:00'),
 (24,'USD', NULL,       2100000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T09:15:00'),
 (25,'USDC','ethereum', 1600000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T09:15:00'),
 (26,'USDT','tron',     1550000.00000000,     0,0,     0,0,'PROVIDER_API','2026-08-01T09:15:00');

-- Accounts 1,2,3,7 deliberately have NO threshold -> HealthStatus 'NONE'.
INSERT INTO AccountThreshold (TreasuryAccountId,AssetCode,CriticalMinimum,
                              MinimumAmount,TargetAmount,MaximumAmount) VALUES
 (4,'USDC', 800000,1500000, 4000000, 9000000),   (5,'ETH',      10,     25,      60,     120),
 (6,'POL',    8000,  15000,   40000,   80000),   (8,'USDC', 150000, 200000,  500000,  900000),
 (9,'USD',  200000, 400000,  900000, 2000000),   (10,'XSGD',1000000,2000000,5000000,10000000),
 (11,'USDC',150000, 250000,  600000, 1200000),   (12,'USD',3000000,5000000,12000000,25000000),
 (13,'USD',      0,      0,       0,       0),   (14,'SGD',      0,      0,       0,       0),
 (15,'USD',  200000, 400000,  800000, 2000000),  (16,'EUR',      0,      0,       0,       0),
 (17,'GBP',      0,      0,       0,       0),   (18,'USD',2000000,3000000, 4000000, 8000000),
 (19,'USD',  400000, 800000, 1500000, 3000000),  (20,'USD', 600000,1000000, 2000000, 4000000),
 (21,'USD',  300000, 500000, 1000000, 2000000),  (22,'USD', 400000, 600000, 1200000, 2400000),
 (23,'USD',  500000, 800000, 1500000, 3000000),  (24,'USD', 500000,1000000, 2500000, 5000000),
 (25,'USDC', 400000, 800000, 2000000, 4000000),  (26,'USDT',400000, 800000, 2000000, 4000000);

20.6 Rules — two deliberately gate-blocked

SET IDENTITY_INSERT LiquidityRule ON;
INSERT INTO LiquidityRule (LiquidityRuleId,RuleCode,Name,SourceAccountId,DestinationAccountId,
  AssetCode,NetworkCode,Strategy,MaxAmountPerRun,MaxDailyValue,MaxExecutionsPerDay,
  ApprovalThresholdBase,SourceMinRemaining,MaxDataAgeSeconds,IsEnabled,
  LastDryRunAtUtc,LastDryRunConfigHash,CreatedByUserId) VALUES
 (1,'rain-collateral-topup',  'Rain card collateral top-up',   4, 8,'USDC','ethereum','TO_TARGET',500000,1500000,4,100000,1500000,900,1,'2026-07-28T10:00:00','a1f4c9',4),
 (2,'gas-eth-topup',          'Ethereum gas tank top-up',      4, 5,'ETH', 'ethereum','TO_TARGET',    20,     60,3,     0,1500000,900,1,'2026-07-20T14:20:00','b2e7d1',4),
 (3,'gas-pol-topup',          'Polygon gas tank top-up',       4, 6,'POL', 'polygon', 'TO_TARGET', 30000,  80000,3,     0,1500000,900,1,'2026-07-20T14:25:00','c3a8e2',4),
 (4,'wasabi-collateral-topup','Wasabi card collateral top-up', 4,11,'USDC','ethereum','TO_TARGET',400000,1000000,3,100000,1500000,900,1,'2026-06-30T09:10:00','d4b9f3',4),
 (5,'mesta-prefund-topup',    'Mesta corridor prefund top-up',12,23,'USD', NULL,      'TO_TARGET',500000,1000000,2,100000,5000000,900,1,'2026-07-15T16:40:00','e5c0a4',4),
 (6,'fee-sweep-monthly',      'Sweep fee wallet to operating', 7, 4,'USDC','ethereum','FULL',     500000, 500000,1,     0,      0,900,0,'2026-07-01T08:00:00','f6d1b5',4);
SET IDENTITY_INSERT LiquidityRule OFF;

-- Executions INCLUDING skips — this is what makes incidents explicable.
INSERT INTO RuleExecution (LiquidityRuleId,TriggeredAtUtc,Outcome,Reason,ComputedAmount,MovementId,DurationMs) VALUES
 (1,'2026-08-01T08:02:00','CREATED',      'Destination below minimum',315000.00,5,412),
 (1,'2026-08-01T08:07:00','NOT_TRIGGERED','A movement is already pending for this account',NULL,NULL,88),
 (4,'2026-08-01T08:05:00','GATE_BLOCKED', 'Feed missing for 2026-07-31.',NULL,NULL,41),
 (4,'2026-08-01T08:10:00','GATE_BLOCKED', 'Feed missing for 2026-07-31.',NULL,NULL,39),
 (5,'2026-08-01T08:05:00','GATE_BLOCKED', 'Feed arrived late. Last certified 2026-07-30.',NULL,NULL,44),
 (3,'2026-08-01T08:05:00','SOURCE_FLOOR', 'Would breach the source minimum of 1,500,000 USDC.',NULL,NULL,96),
 (2,'2026-07-31T11:05:00','CREATED',      'Gas below minimum',18.00,2,210);

20.7 Movements — including the self-approval trap

SET IDENTITY_INSERT Movement ON;
INSERT INTO Movement (MovementId,MovementCode,MovementType,Status,SourceAccountId,
  DestinationAccountId,AssetCode,NetworkCode,Amount,FeeAmount,FeeAssetCode,AmountBase,
  RequiredApprovals,GateStatusAtCreation,IdempotencyKey,InitiatedByUserId,LiquidityRuleId,
  Reference,ExternalRef,FailureReason,CreatedAtUtc,SubmittedAtUtc,SettledAtUtc) VALUES
 (1,'MV-4468','TOP_UP','FAILED',           4,23,'USD', NULL,     400000.00,  NULL,NULL,400000.00,2,'BLOCKED','seed-mv-4468',3,   NULL,'Mesta corridor prefund',NULL,
   'Provider rejected: daily funding cap reached','2026-07-31T16:20:00','2026-07-31T16:41:00',NULL),
 (2,'MV-4469','TOP_UP','SETTLED',          4, 5,'ETH','ethereum',    18.00,0.0021,'ETH', 63000.00,1,'CLEAN','seed-mv-4469',NULL,2,'Gas top-up',
   '0x4a11c7d2e0f9138ba7710c4429e1d5a0b8c3f6e2',NULL,'2026-07-31T11:05:00','2026-07-31T11:06:00','2026-07-31T11:09:00'),
 (3,'MV-4470','SWEEP','SETTLED',           7, 4,'USDC','ethereum',288000.00,12.40,'USDC',288000.00,1,'CLEAN','seed-mv-4470',NULL,6,'Monthly fee sweep',
   '0x77b2ea41c093d5f8210ba64f19cc7d3e05a91b44',NULL,'2026-07-31T08:00:00','2026-07-31T08:01:00','2026-07-31T08:04:00'),
 (4,'MV-4471','TOP_UP','SETTLED',          4, 8,'USDC','ethereum',250000.00,11.80,'USDC',250000.00,2,'CLEAN','seed-mv-4471',NULL,1,'Rain collateral top-up',
   '0x9c1a44b8e7203df51cc0a9b6741e3d0925fa8c17',NULL,'2026-07-30T22:14:00','2026-07-30T22:19:00','2026-07-30T22:23:00'),
 (5,'MV-4472','TOP_UP','PENDING_APPROVAL', 4, 8,'USDC','ethereum',315000.00,11.80,'USDC',315000.00,2,'CLEAN','seed-mv-4472',NULL,1,'Rain collateral top-up',
   NULL,NULL,'2026-08-01T08:02:00',NULL,NULL),
 -- initiated by Anita K (UserId 1): the self-approval trap
 (6,'MV-4473','TOP_UP','PENDING_APPROVAL',12,22,'USD',NULL,     780000.00,  NULL,NULL,780000.00,2,'CLEAN','seed-mv-4473',1,  NULL,'Thunes prefund ahead of month-end payouts',
   NULL,NULL,'2026-08-01T09:05:00',NULL,NULL),
 -- 3 approvals because it touches a connection that is not verified
 (7,'MV-4475','TOP_UP','PENDING_APPROVAL',12,23,'USD',NULL,     400000.00,  NULL,NULL,400000.00,3,'BLOCKED','seed-mv-4475',3,NULL,'Mesta corridor prefund, manual after rule block',
   NULL,NULL,'2026-08-01T09:31:00',NULL,NULL);
SET IDENTITY_INSERT Movement OFF;

-- MV-4472 already has ONE of its two approvals -> the queue shows "1 / 2".
INSERT INTO MovementApproval (MovementId,ApproverUserId,Decision,StepUpVerified,DecidedAtUtc) VALUES
 (4,1,'APPROVED',1,'2026-07-30T22:17:00'),
 (4,2,'APPROVED',1,'2026-07-30T22:18:00'),
 (5,2,'APPROVED',1,'2026-08-01T08:40:00'),
 (7,1,'APPROVED',1,'2026-08-01T09:40:00');

INSERT INTO MovementEvent (MovementId,FromStatus,ToStatus,ActorUserId,ActorType,Reason,OccurredAtUtc) VALUES
 (5,NULL,              'PENDING_APPROVAL',NULL,'RULE',    'Created by rule rain-collateral-topup','2026-08-01T08:02:00'),
 (5,'PENDING_APPROVAL','PENDING_APPROVAL',2,   'USER',    'Approved by Dev P (1 of 2)','2026-08-01T08:40:00'),
 (6,NULL,              'PENDING_APPROVAL',1,   'USER',    'Created by Anita K','2026-08-01T09:05:00'),
 (7,NULL,              'PENDING_APPROVAL',3,   'USER',    'Created by Ravi M — connection not verified, 3 approvals required','2026-08-01T09:31:00'),
 (1,NULL,              'PENDING_APPROVAL',3,   'USER',    'Created by Ravi M','2026-07-31T16:20:00'),
 (1,'PENDING_APPROVAL','SUBMITTED',       NULL,'SYSTEM',  'Approvals complete','2026-07-31T16:41:00'),
 (1,'SUBMITTED',       'FAILED',          NULL,'PROVIDER','Daily funding cap reached','2026-07-31T16:44:00');

20.8 Coverage, alerts and settings

-- Segregated = accounts 1,2,3,13,14,16,17:
--   14,200,000 + 8,100,000 + 4,300,000 + 9,800,000
--   + 8,500,000 SGD @0.74 (=6,290,000) + 3,200,000 EUR @1.08 (=3,456,000)
--   + 1,400,000 GBP @1.27 (=1,778,000)                    = 47,924,000
-- Liabilities 47,262,000 (wallets 26,270,000 + banks 20,992,000) -> 101.4007% -> AMBER
-- Top-up to restore the 102% buffer: 283,240
INSERT INTO CoverageSnapshot (LiabilitiesBase,LiabilitySourceAt,SegregatedBase,
                              RatioPct,Status,ShortfallBase,ComputedAtUtc) VALUES
 (47262000.00,'2026-08-01T02:15:00',47924000.00,101.4007,'INSIDE_BUFFER',NULL,'2026-08-01T09:00:00'),
 (47240000.00,'2026-08-01T01:15:00',47924000.00,101.4479,'INSIDE_BUFFER',NULL,'2026-08-01T08:00:00'),
 (47180000.00,'2026-08-01T00:15:00',47924000.00,101.5769,'INSIDE_BUFFER',NULL,'2026-08-01T07:00:00');

INSERT INTO AlertInstance (AlertType,Severity,DedupKey,Message,TargetRoute,SourceType,
                           RelatedEntityId,Status,RaisedAtUtc) VALUES
 ('THRESHOLD_BREACH','PAGE',    'threshold:8','Rain card collateral is below its minimum — 185,000 of 200,000 USDC','/treasury/accounts/8','THRESHOLD',8,'OPEN','2026-08-01T08:02:00'),
 ('THRESHOLD_BREACH','PAGE',    'threshold:6','Polygon gas tank is below its minimum — 12,000 of 15,000 POL','/treasury/accounts/6','THRESHOLD',6,'OPEN','2026-08-01T06:41:00'),
 ('MOVEMENT_FAILED', 'CRITICAL','movement:1', 'MV-4468 failed — provider rejected, daily funding cap reached','/treasury/movements/MV-4468','MOVEMENT',1,'OPEN','2026-07-31T16:44:00'),
 ('RECON_FEED',      'WARNING', 'recon:4',    'Wasabi · Cards — feed missing for 2026-07-31, automation blocked','/treasury/connections/4','RECON',4,'OPEN','2026-08-01T02:15:00'),
 ('RECON_BREAKS',    'WARNING', 'recon:7',    'Tazapay · Banks — unresolved breaks of 410,000 exceed the 50,000 tolerance','/treasury/connections/7','RECON',7,'OPEN','2026-08-01T02:15:00'),
 ('RECON_FEED',      'WARNING', 'recon:12',   'Mesta · Payments — feed late, last certified 2026-07-30','/treasury/connections/12','RECON',12,'OPEN','2026-08-01T03:22:00'),
 ('COVERAGE_BUFFER', 'WARNING', 'coverage',   'Client coverage is 101.40%, inside the 102% operating buffer','/treasury/coverage',NULL,'OPEN','2026-08-01T09:00:00'),
 ('APPROVALS_WAITING','INFO',   'approvals',  '3 movements are awaiting approval','/treasury/approvals',NULL,'OPEN','2026-08-01T09:31:00');

INSERT INTO SystemSetting (SettingKey,SettingValue) VALUES
 ('automation.killSwitch',        'false'),
 ('approval.thresholdBase',       '100000'),
 ('approval.stepUpThresholdBase', '500000'),
 ('coverage.bufferPct',           '102'),
 ('gate.defaultToleranceBase',    '50000'),
 ('position.stalenessWarnSeconds','900'),
 ('baseCurrency',                 'USD');

20.9 Running order

1. Reference: Module, Provider, Connection, Asset, Network, FxRate
2. Users — map to your existing user table, or insert five test users, one per role
3. ConnectionSyncState, then BalanceCheck
4. TreasuryAccount, then PositionSnapshot, then AccountThreshold
5. Destination, LiquidityRule
6. Movement, MovementApproval, MovementEvent, RuleExecution
7. CoverageSnapshot, AlertInstance, SystemSetting, AuditLog

20.10 Verification queries

Run these after seeding. Values must match §20.2 exactly.

PRINT '--- Expected: 86339500.00 ---';
SELECT CAST(SUM(TotalBase) AS DECIMAL(18,2)) AS TotalHoldingsUsd FROM vw_PositionBase;

PRINT '--- Expected: exactly 2 rows (Polygon gas tank, Rain card collateral) ---';
SELECT AccountName, AssetCode, Available, MinimumAmount, HealthStatus
FROM vw_PositionBase WHERE HealthStatus = 'BELOW';

PRINT '--- Expected: 3 BLOCKED (wasabi-cards, tazapay-banks, mesta-payments) ---';
SELECT ConnectionCode, ProviderName, ModuleName, GateStatus, GateReason
FROM vw_ConnectionGate WHERE GateStatus = 'BLOCKED';

PRINT '--- Expected: 3 pending; MV-4472 shows 1 of 2 ---';
SELECT MovementCode, Amount, AssetCode, RequiredApprovals,
       (SELECT COUNT(*) FROM MovementApproval a
        WHERE a.MovementId = m.MovementId AND a.Decision='APPROVED') AS ApprovalsRecorded
FROM Movement m WHERE Status = 'PENDING_APPROVAL';

PRINT '--- Expected: 32543500 / 6281000 / 37595000 / 4670000 / 5250000 ---';
SELECT ModuleName, CAST(SUM(TotalBase) AS DECIMAL(18,2)) AS TotalUsd, COUNT(*) AS Accounts
FROM vw_PositionBase GROUP BY ModuleName ORDER BY TotalUsd DESC;

PRINT '--- Expected: 47262000.00 / 47924000.00 / 101.4007 / INSIDE_BUFFER ---';
SELECT TOP 1 LiabilitiesBase, SegregatedBase, RatioPct, Status
FROM CoverageSnapshot ORDER BY ComputedAtUtc DESC;

PRINT '--- Expected: 2 rules GATE_BLOCKED (wasabi, mesta) ---';
SELECT r.RuleCode, g.GateStatus, g.GateReason
FROM LiquidityRule r
JOIN TreasuryAccount d ON d.TreasuryAccountId = r.DestinationAccountId
JOIN vw_ConnectionGate g ON g.ConnectionId = d.ConnectionId
WHERE r.IsEnabled = 1 AND g.GateStatus = 'BLOCKED';

Appendix A — Prototype to production map

Appendix B — Reading order for a new engineer

Half a day, and you will understand the system.

The single most expensive mistake on this module is guessing what a number means. Available and total differ by hundreds of thousands of dollars, and picking the wrong one produces a screen that looks correct and is wrong. When in doubt, check §12 or ask.

End of specification. If the code and this document disagree, that is a defect in one of them — raise a change request rather than letting the two drift.