Database Architecture

ReconLayer’s persistence layer (packages/db/prisma/schema.prisma) is PostgreSQL via Prisma. packages/db owns the schema exclusively — HTTP handlers (apps/api) and the pure matching logic (packages/reconciliation-engine) never execute SQL directly. This page documents the actual tables, enums, and relations as defined in schema.prisma.

Five design rules are baked into this schema (from the schema header comments):

raw_records.payload is jsonb, never edited, never deleted.
match_links.match_reason is human-readable text, not a code.
reconciliation_cases keeps expected, actual, fee, FX, rounding, and unexplained_delta as separate columns — nothing is collapsed into one number.
Every state change is logged in audit_events. Append-only.
Amounts use Decimal(38, 18) — wide enough for 18-decimal stablecoin values without losing fractional cents.

Entity relationship overview

`payment_intents` (`PaymentIntent`)

What the customer asks ReconLayer to reconcile.

Column	Type	Notes
`id`	`String @id @default(cuid())`
`organizationId`	`String`	Tenant scope.
`externalReference`	`String?`	Client’s business reference.
`sourceCurrency` / `sourceAmount`	`String` / `Decimal(38,18)`
`destinationCurrency` / `destinationAmount`	`String` / `Decimal(38,18)`
`paymentType`	`PaymentType @default(stablecoin)`
`paymentSubtype`	`String?`
`direction`	`PaymentDirection @default(debit)`
`effectiveDate`	`DateTime?`
`stablecoin` / `chain`	`String?`
`beneficiaryAccount` / `beneficiaryName`	`String?`
`status`	`PaymentIntentStatus @default(initiated)`
`references` / `metadata`	`Json?`
`createdAt` / `updatedAt`	`DateTime`

Constraints & indexes: @@unique([organizationId, externalReference]) — the idempotency key for the intake endpoint (nullable externalReference values don’t conflict with each other). Indexed on organizationId, (organizationId, status), externalReference, and createdAt. Relations: legs: FlowLeg[], reconciliationCase: ReconciliationCase? (1:1), rawRecords: RawRecord[], auditEvents: AuditEvent[], intentReferences: PaymentIntentReference[].

`payment_intent_references` (`PaymentIntentReference`)

Typed identifiers attached to an expectation, used by the matcher to find candidates by provider_transfer_id or tx_hash.

Column	Type
`id`, `organizationId`, `paymentIntentId`	`String`
`type`, `value`	`String`
`createdAt`	`DateTime`

Unique on (organizationId, type, value) and (paymentIntentId, type, value). Indexed on organizationId, paymentIntentId, and (organizationId, type, value).

`raw_records` (`RawRecord`)

Every payload ingested, untouched, forever.

Column	Type	Notes
`id`, `organizationId`	`String`
`paymentIntentId` / `importBatchId`	`String?`	Set once normalized/matched.
`source`	`RawSource`	`file \| api \| webhook \| manual`
`sourceType`	`ImportSourceType?`
`provider` / `integrationKey`	`String?`
`sourceRef`	`String`	provider transfer id, tx hash, csv row id, etc.
`payload`	`Json`	raw blob, never edited
`rowNumber`	`Int?`
`normalizedType` / `normalizedId`	`String?`
`validationStatus`	`RecordValidationStatus?`	`pending \| valid \| warning \| failed`
`validationErrors`	`Json?`
`signature` / `signatureValid`	`String?` / `Boolean?`	HMAC and verification result
`receivedAt`	`DateTime @default(now())`
`r2Url` / `archivedAt`	`String?` / `DateTime?`	R2 archive pointer, set by a background job

Constraints & indexes: @@unique([organizationId, source, sourceRef]) — tenant-scoped idempotent ingestion. Indexed on organizationId, paymentIntentId, importBatchId, receivedAt, (source, receivedAt), sourceType, (organizationId, provider), (organizationId, integrationKey). A GIN index on payload is added via migration SQL for ad-hoc JSONB querying. Relations: paymentIntent: PaymentIntent?, importBatch: ImportBatch?, flowLegs: FlowLeg[] (relation name LegFromRawRecord), matchLinks: MatchLink[], auditEvents: AuditEvent[].

`flow_legs` (`FlowLeg`)

Normalized legs where value actually moved.

Column	Type	Notes
`id`, `organizationId`	`String`
`paymentIntentId` / `rawRecordId` / `routeGroupId`	`String?`
`type`	`LegType`	`provider_transfer \| onchain_transfer \| bank_transfer`
`phase`	`LegPhase?`	`source \| intermediary_in \| transfer \| intermediary_out \| destination`
`status`	`LegStatus @default(pending)`	`pending \| confirmed \| failed \| reversed \| missing`
`sequence`	`Int?`
`reconciliationScope`	`LegReconciliationScope @default(required)`	`required \| optional \| ignored`
`providerTransferId` / `txHash`	`String?`	First-class — commonly queried
`chainId`	`Int?`
`fromAddress` / `toAddress` / `tokenAddress`	`String?`
`provider` / `integrationKey`	`String?`
`currency` / `amount`	`String?` / `Decimal(38,18)?`
`occurredAt`	`DateTime?`
`receivedAt`	`DateTime @default(now())`
`metadata`	`Json?`

Indexes: organizationId, paymentIntentId, routeGroupId, providerTransferId, txHash, (paymentIntentId, sequence), (organizationId, provider), (organizationId, integrationKey), (type, status), receivedAt. Relations: paymentIntent: PaymentIntent?, rawRecord: RawRecord? (relation LegFromRawRecord), matchLinks: MatchLink[], legReferences: FlowLegReference[].

`flow_leg_references` (`FlowLegReference`)

Column	Type
`id`, `organizationId`, `flowLegId`	`String`
`type`, `value`	`String`
`createdAt`	`DateTime`

Unique on (flowLegId, type, value). Indexed on organizationId, flowLegId, and (organizationId, type, value).

`reconciliation_cases` (`ReconciliationCase`)

One per PaymentIntent — the truth row.

Column	Type	Notes
`id`, `organizationId`	`String`
`paymentIntentId`	`String? @unique`	Enforces 1:1 with `PaymentIntent`.
`status`	`CaseStatus @default(open)`	`open \| resolved \| archived`
`reconciliationStatus`	`ReconciliationStatus @default(unreconciled)`	`unreconciled \| tentatively_reconciled \| reconciled`
`expectedAmount` / `actualAmount`	`Decimal(38,18)?`	Kept separate, never collapsed
`providerFee`, `networkFee`, `developerFee`, `fxSpread`, `roundingDelta`	`Decimal(38,18)?`	Individual delta components
`unexplainedDelta`	`Decimal(38,18)?`	The column ops cares about — indexed
`exceptionType`	`ExceptionType?`
`notes`	`String?`
`reconciledAt` / `lastRunAt`	`DateTime?`
`createdAt` / `updatedAt`	`DateTime`

Indexes: organizationId, (organizationId, status), (organizationId, reconciliationStatus), (organizationId, exceptionType), unexplainedDelta. Relations: paymentIntent: PaymentIntent?, matchLinks: MatchLink[], auditEvents: AuditEvent[].

`ExceptionType` enum

missing_expected_record   missing_evidence       missing_callback
asset_mismatch             amount_mismatch         fee_variance
fx_variance                unexplained_variance    state_mismatch
timing_delay               settlement_reversed     invalid_signature
duplicate_record           chain_reorg

`match_links` (`MatchLink`)

Explainable links between raw records, flow legs, and cases.

Column	Type	Notes
`id`, `organizationId`, `caseId`, `legId`, `rawRecordId`	`String`
`matchType`	`MatchType`	`provider_id \| tx_hash \| reference_exact \| amount_and_time_window \| manual_override`
`matchReason`	`String`	Human-readable, e.g. “Bridge transfer ID X matched on-chain tx Y via destination_tx_hash”
`confidence`	`MatchConfidence @default(deterministic)`	`deterministic \| heuristic \| manual`
`matchedAt`	`DateTime @default(now())`

Constraints: @@unique([caseId, legId, rawRecordId]) — idempotent linking (the matcher upserts on this key). Indexed on organizationId, caseId, legId.

`reconciliation_rules` (`ReconciliationRule`)

Per-organization matching tolerances and behavior — see Reconciliation Rules for how each field is used.

Column	Type
`id`, `organizationId`, `name`	`String`
`paymentType`	`PaymentType?`
`sourceType`	`ImportSourceType?`
`isActive`	`Boolean @default(true)`
`amountTolerance`	`Decimal(38,18)?`
`timeWindowMinutes`, `expectedCompletionMinutes`, `delayedSettlementThresholdMinutes`	`Int?`
`sourceSlaMinutes`, `intermediaryInSlaMinutes`, `transferSlaMinutes`, `intermediaryOutSlaMinutes`, `destinationSlaMinutes`	`Int?`
`allowProviderIdMatch`, `allowTxHashMatch`, `allowReferenceExactMatch`, `allowAmountAndTimeWindowMatch`	`Boolean @default(true)`
`requireAllRequiredLegs`	`Boolean @default(true)`
`metadata`	`Json?`
`createdAt` / `updatedAt`	`DateTime`

Indexed on organizationId, (organizationId, isActive), (organizationId, paymentType), (organizationId, sourceType).

`audit_events` (`AuditEvent`)

Append-only log of everything the system did.

Column	Type
`id`, `organizationId`	`String`
`paymentIntentId`, `caseId`, `rawRecordId`, `importBatchId`	`String?`
`eventType`	`String` — e.g. `ingest.bridge.received`, `match.deterministic.linked`, `case.resolved`
`actor`	`String` — e.g. `system:bridge_adapter`, `system:matcher`, `user:<id>`
`payload`	`Json?`
`message`	`String?`
`occurredAt`	`DateTime @default(now())`

Indexed on organizationId, paymentIntentId, caseId, rawRecordId, importBatchId, eventType, occurredAt. Relations to PaymentIntent?, ReconciliationCase?, RawRecord?, ImportBatch?.

Import pipeline tables

`import_profiles` (`ImportProfile`)

Saved mapping/parsing rule sets for file onboarding.

Column	Type
`id`, `organizationId`, `name`	`String`
`sourceType`	`ImportSourceType`
`provider` / `integrationKey`	`String?`
`fieldMappings`	`Json`
`valueMappings` / `parsingRules`	`Json?`
`isActive`	`Boolean @default(true)`
`createdAt` / `updatedAt`	`DateTime`

Indexed on organizationId, (organizationId, sourceType), (organizationId, provider), (organizationId, integrationKey). Relation: importBatches: ImportBatch[].

`import_batches` (`ImportBatch`)

One uploaded file or import job.

Column	Type
`id`, `organizationId`	`String`
`importProfileId`	`String?`
`sourceType`	`ImportSourceType`
`provider` / `integrationKey`	`String?`
`originalFileName` / `fileStorageKey` / `fileHash`	`String`
`status`	`ImportBatchStatus @default(uploaded)`
`totalRows`, `validRows`, `warningRows`, `failedRows`	`Int @default(0)`
`createdByUserId`	`String?` (Clerk user id)
`createdAt`	`DateTime`
`completedAt`	`DateTime?`

@@unique([organizationId, fileHash]). Indexed on organizationId, (organizationId, status), importProfileId, createdByUserId, (organizationId, provider), (organizationId, integrationKey). Relations: importProfile: ImportProfile?, rawRecords: RawRecord[], auditEvents: AuditEvent[]. ImportBatchStatus: uploaded, mapping_required, parsing, validating, processing, completed, failed. ImportSourceType (also used as RawRecord.sourceType and ReconciliationRule.sourceType): client_transfer_report, client_internal_ledger, bank_statement, onchain_report, psp_report, manual.

Operational and platform tables

These tables support API access, integrations, outbound webhooks, org settings, and UI preferences. They are not part of the reconciliation data model but share the same database.

Table	Model	Purpose
`api_keys`	`ApiKey`	Machine credentials for headless org integrations. Unique `prefix` and `hashedKey`.
`integrations`	`Integration`	Configured provider/bank/chain-indexer/system-of-record connections. `kind`: `provider \| bank \| chain_indexer \| system_of_record`. `status`: `disabled \| active \| deleted`.
`outbound_webhook_endpoints`	`OutboundWebhookEndpoint`	Customer-configured webhook destinations with `subscribedEvents[]` and a `signingSecret`.
`outbound_webhook_events`	`OutboundWebhookEvent`	Queued events (`pending \| delivered \| failed \| skipped`), unique on `(organizationId, sourceKey)`.
`outbound_webhook_deliveries`	`OutboundWebhookDelivery`	Per-endpoint delivery attempts with retry state (`pending \| retry_scheduled \| delivered \| failed`).
`organization_settings`	`OrganizationSettings`	Per-workspace defaults: `name`, `region`, `currency`, `slaProfile`, `retention`.
`organization_roles` / `organization_member_access`	`OrganizationRole` / `OrganizationMemberAccess`	RBAC roles and member invitations, keyed to Clerk users.
`user_preferences`	`UserPreference`	Per-user UI/accessibility defaults (`fontSize`, `density`, `reducedMotion`, `highContrast`).
`idempotency_requests`	`IdempotencyRequest`	Request-level idempotency for client retries, unique on `(organizationId, operation, idempotencyKey)`.

Schema migrations

pnpm db:generate  # Generate the Prisma Client
pnpm db:migrate   # Apply pending migrations

Because core-model (canonical shape types and Zod schemas) is separate from db (Prisma persistence types), the reconciliation engine and most service-layer logic can be tested without a database.

Core Concepts

What each table represents in the reconciliation lifecycle.

Canonical Field Registry

Field-by-field mapping targets for import profiles and adapters.

​Entity relationship overview

​payment_intents (PaymentIntent)

​payment_intent_references (PaymentIntentReference)

​raw_records (RawRecord)

​flow_legs (FlowLeg)

​flow_leg_references (FlowLegReference)

​reconciliation_cases (ReconciliationCase)

​ExceptionType enum

​match_links (MatchLink)

​reconciliation_rules (ReconciliationRule)

​audit_events (AuditEvent)

​Import pipeline tables

​import_profiles (ImportProfile)

​import_batches (ImportBatch)

​Operational and platform tables

​Schema migrations