> ## Documentation Index
> Fetch the complete documentation index at: https://docs.reconlayer.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Database Architecture
> The Prisma schema behind ReconLayer's canonical model: tables, enums, relations, and the design rules baked into them.
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):
1. `raw_records.payload` is `jsonb`, never edited, never deleted.
2. `match_links.match_reason` is human-readable text, not a code.
3. `reconciliation_cases` keeps expected, actual, fee, FX, rounding, and `unexplained_delta` as
**separate columns** — nothing is collapsed into one number.
4. Every state change is logged in `audit_events`. Append-only.
5. Amounts use `Decimal(38, 18)` — wide enough for 18-decimal stablecoin values without losing
fractional cents.
## Entity relationship overview
```mermaid theme={null}
erDiagram
PaymentIntent ||--o| ReconciliationCase : "1:1"
PaymentIntent ||--o{ FlowLeg : "legs"
PaymentIntent ||--o{ RawRecord : "rawRecords"
PaymentIntent ||--o{ PaymentIntentReference : "intentReferences"
PaymentIntent ||--o{ AuditEvent : "auditEvents"
RawRecord ||--o{ FlowLeg : "LegFromRawRecord"
RawRecord ||--o{ MatchLink : "matchLinks"
FlowLeg ||--o{ FlowLegReference : "legReferences"
FlowLeg ||--o{ MatchLink : "matchLinks"
ReconciliationCase ||--o{ MatchLink : "matchLinks"
ReconciliationCase ||--o{ AuditEvent : "auditEvents"
ImportProfile ||--o{ ImportBatch : "importBatches"
ImportBatch ||--o{ RawRecord : "rawRecords"
ImportBatch ||--o{ AuditEvent : "auditEvents"
OutboundWebhookEndpoint ||--o{ OutboundWebhookDelivery : "deliveries"
OutboundWebhookEvent ||--o{ OutboundWebhookDelivery : "deliveries"
OrganizationRole ||--o{ OrganizationMemberAccess : "memberAccesses"
```
## `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
```text theme={null}
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
`upsert`s on this key). Indexed on `organizationId`, `caseId`, `legId`.
## `reconciliation_rules` (`ReconciliationRule`)
Per-organization matching tolerances and behavior — see
[Reconciliation Rules](/architecture/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:` |
| `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
```bash theme={null}
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.
What each table represents in the reconciliation lifecycle.
Field-by-field mapping targets for import profiles and adapters.