Skip to main content

Database Schema

BilkoDrop Database Schema

Status: IMPLEMENTED Last verified: 2026-05-20 Canonical backend:Source: apps/apisrc/shared/db/schema.ts Kotlin/Ktor(Drizzle serviceORM Database:schema PostgreSQL— on GCP Cloud SQL for deployed environments Schemasingle source of truth:truth Flywayfor SQLall migrations + Exposed table mappingsenvironments)

Overview

ThisDrop documentuses describesPostgreSQL 16 as the database schema currently used by the Bilko Kotlin/Ktor API. It replaces the older ORM-erasole database notesengine in all environments (development, CI, staging, production). Database access is via Drizzle ORM. There is no SQLite dependency and mustno bedual-driver kept aligned with:abstraction.

  • FlywayLocal migrations:dev: PostgreSQL 16 in Docker (apps/api/src/main/resources/db/migration/docker compose up -d), port 5433
  • ExposedCI: tablePostgreSQL mappings:16 apps/api/src/main/kotlin/no/alai/bilko/models/Tables.ktservice container in GitHub Actions
  • Route/serviceProduction: behaviour:PostgreSQL 16 on AWS RDS (apps/api/src/main/kotlin/no/alai/bilko/routes/db.t3.small and apps/api/src/main/kotlin/no/alai/bilko/services/)
  • EnvironmentSchema mapping:definition: infrastructure/gcp/ENV-MATRIX.mdsrc/shared/db/schema.ts (Drizzle schema, TypeScript, PostgreSQL-native)
  • Migrations: managed by drizzle-kit

DoSee notADR-014 treatfor generatedthe diagrams,full frontendrationale.

type

Total definitions,tables: or19 archived(12 deploymentcore notes+ as7 database authority.compliance)

1. Architecture OverviewTables

Bilko is a multi-tenant accounting SaaS. The active backend stores tenant data in PostgreSQL and scopes business tables by organization_id where appropriate.

users

Runtime stack:

  • API: Kotlin/Ktor
  • SQL migration engine: Flyway
  • Kotlin SQL mapping: JetBrains Exposed
  • Database engine: PostgreSQL
  • Deployed DB platform: GCP Cloud SQL
  • Primary migrationuser command path: Cloud Build / backend Gradle Flyway tasks

The schema is forward-only. Applied migrations must not be edited after deployment. If a deployed environment has Flyway metadata drift, repair is handled as a controlled operations procedure with target identity checks, schema checks, transcript, and postflight validation.

Relationship overview

erDiagram
  organizations ||--o{ users : owns
  organizations ||--o{ accounts : owns
  organizations ||--o{ contacts : owns
  organizations ||--o{ invoices : owns
  organizations ||--o{ expenses : owns
  organizations ||--o{ transactions : owns
  organizations ||--o{ bank_accounts : owns
  organizations ||--o{ recurring_invoices : owns
  organizations ||--o{ adapter_config : configures
  organizations ||--o{ stripe_webhook_events : receives

  users ||--o{ refresh_tokens : has
  users ||--o{ invoices : creates
  users ||--o{ expenses : creates
  users ||--o{ transactions : creates
  users ||--o{ logged_actions : actor

  account_types ||--o{ accounts : classifies
  accounts ||--o{ accounts : parent
  accounts ||--o{ invoice_items : revenue_account
  accounts ||--o{ expenses : expense_account
  accounts ||--o{ bank_accounts : ledger_account
  accounts ||--o{ transactions : debit_account
  accounts ||--o{ transactions : credit_account

  contacts ||--o{ invoices : customer
  contacts ||--o{ expenses : vendor
  contacts ||--o{ recurring_invoices : template_customer

  invoices ||--o{ invoice_items : contains
  bank_accounts ||--o{ bank_transactions : imports
  transactions ||--o{ bank_transactions : reconciles
  currencies ||--o{ exchange_rates : base_currency
  currencies ||--o{ exchange_rates : target_currency

2. Source-of-Truth Rules

  1. Add schema changes via new Flyway migrations only.

    • Use the next available version under apps/api/src/main/resources/db/migration/.
    • Never rewrite an already-applied migration in demo, staging, or production.

  2. Update Exposed mappings in the same change.

    • Tables.kt should match the migrated database surface used by routes/services.

  3. Update API and docs together.

    • If a schema change alters request/response shapes, update docs/backend/openapi.yaml and relevant backend docs.

  4. Validate with Flyway before deploy promotion.

    • A valid deploy target must pass Flyway validation before the API is considered healthy.

  5. Keep environment identity explicit.

    • Staging, demo, and production databases are separate targets. Migration or repair work must state which database is being touched.

3. Migration Inventory

As of this verification pass, the repository contains 36 Flyway migration files. The deployed stage repair for MC #101509 validated the current Flyway version as 35 after applying pending migrations.

Important migration groups:accounts.

accountingcompatibility columns, encrypted identifiers, supplementary tables, organization logo, role storage normalizationtiers,logging, compliance calendar, recurring invoices, audit enum cleanup, demo/CI seed data, trial fieldsconstraints,permissive baseline, invoice/expense status enum support, demo org fixes, CI viewer, BA jurisdiction split, BA entity chartsconfiguration,marker, Serbia chart, logged action width, UAT demo usersauthsecurity-definerauthhelpers,demoadmingrants,bcrypt-prefixnormalization,UATpasswordhashreset
Version rangeColumn PurposeTypeConstraintsDefault
V1-V6id InitialTEXT PRIMARY schema,KEY -
V7-V15email PlanTEXT UNIQUE StripeNOT webhookNULL -
V16-V24password_hash CountryTEXT NOT RLSNULL -
V25-V29first_name AdapterTEXT NOT platform-adminNULL -
V30-V35last_name RLS/sessionTEXT NOT hardening,NULL -
phone TEXT - NULL
date_of_birth TEXT -NULL
kyc_statusTEXTCHECK('pending','approved','rejected')'pending'
roleTEXTCHECK('user','merchant')'user'
created_atTEXT-CURRENT_TIMESTAMP

OperationalID noteformat: from MC #101509:

  • Staging checksum drift was detected for V22, V25, V26, and V28.
  • Schema checks proved checksum-only drift before repair.
  • Controlled Flyway repairusr_ + migrate16 broughthex stagingchars to version 35 and flyway validate passed.
  • The authoritative stage Cloud Build trigger then succeeded.

4. Tenant and Security Model

Most business data is scoped(generated by organization_idrandomId("usr") and accessed through authenticated Ktor routes. The schema includes:

  • Organization-level tenant boundary.
  • User roles per organization.
  • Platform-admin marker for controlled platform operations.
  • Refresh-token session storage.
  • Logged action/audit table.
  • Row-level-security related migration work in the V17 and V30+ series.
utils-server.ts:4)

ApplicationPassword codehashing: mustbcrypt setwith the12 correctrounds organization/user context before querying tenant-scoped tables. Any new table containing tenant data should include (organization_idutils-server.ts:8-11 unless it is intentionally global reference data.)

5. Tables

The following table inventory is derived from Tables.kt and active migrations.

Column notationrecipients

TheSaved detailedremittance type/constraintrecipients authorityper remains Flyway SQL plus Tables.kt. This document uses these shorthand types for quick review:user.

primary foreign column;length is migration-definedmoney/rate column JSON deleted_atlifecycle column
NotationColumn MeaningTypeConstraintsDefault
uuid pkid UUIDTEXT PRIMARY keyKEY-
uuid fkuser_id UUIDTEXT NOT keyNULL, FK → users(id)-
text / varchar(n)name StringTEXT NOT exactNULL -
numericcountry DecimalTEXT NOT valueNULL-
date / timestampcurrency Date/timeTEXT NOT NULL-
json/jsonbbank_account StructuredTEXT NOT columnNULL-
boolbank_name BooleanTEXT-NULL
soft-deletecreated_at NullableTEXT - CURRENT_TIMESTAMP

CommonSupported lifecyclecountries: columnsRS, areBA, PL, PK, TR (enforced at API level, not DB level)

Index: created_atidx_recipients_user, on updated_atuser_id, version, and deleted_at where present.

5.1 organizationsmerchants

TenantRegistered rootmerchant table.

profiles

Keyfor fields:

QR
  • id
  • name
  • registration_number
  • vat_number, vat_country, vat_registered, vat_rate
  • firm_type
  • base_currency
  • country, language
  • fiscal_year_start
  • logo_url
  • security_settings
  • subscription/trial fields: plan_tier, quota_invoices_month, quota_contacts, quota_users, stripe_customer_id, stripe_subscription_id, trial_started_at, trial_ends_at
  • lifecycle fields: created_at, updated_at, version, deleted_at

Column summary:payments.

summary uuidname textandtext values; VAT registration is boolean-backedlanguage,currency, fiscal year startsecurity_settings quota,Stripe IDs, trial timestampsversion,soft-delete
Column group Type/constraintType ConstraintsDefault
Identityid idTEXT PRIMARY pk,KEY -
Registration/taxuser_id registrationTEXT NOT VATNULL, columnsFK are→ nullableusers(id) -
Locale/marketbusiness_name country,TEXT NOT baseNULL -
Branding/settingsorg_number logo_url,TEXT UNIQUE json/jsonbNOT NULL-
Subscription/trialaddress plan,TEXT - NULL
Lifecyclebank_account timestamps,TEXT NOT deleted_atNULL -
fee_rateREAL-0.01
statusTEXT-'active'
created_atTEXT-CURRENT_TIMESTAMP

Notes:Index: idx_merchants_org on org_number

  • country

    transactions

    is

    All constrainedfinancial bytransactions migration history(remittances and usedQR by market-specific tax/e-invoice logic.

  • BA jurisdiction support was added in the V22/V23/V24 migration set.

5.2 users

Authenticated users belonging to organizations.

Key fields:

  • id
  • organization_id
  • email
  • password_hash
  • full_name
  • role
  • two_factor_enabled, two_factor_secret, two_factor_backup_codes
  • notification_preferences
  • last_login_at
  • invite fields: invite_token, invite_expires_at
  • status
  • is_platform_admin
  • lifecycle fields: created_at, updated_at, version, deleted_at

Column summary:payments).

summary uuidorganization_id uuid fktext textstatus text, is_platform_admin boolflag,secret, backup-code storagetoken/expiry,login timestampversion,deleted_atsoft-delete
Column group Type/constraintType ConstraintsDefault
Identityid idTEXT PRIMARY pk,KEY -
Loginuser_id emailTEXT NOT unique,NULL, password_hashFK text→ users(id)-
RBACtype roleTEXT NOT NULL, CHECK('remittance','qr_payment') -
2FAstatus booleanTEXT CHECK('processing','completed','failed') 'processing'
Invites/session metadataamount inviteREAL NOT lastNULL -
Lifecyclecurrency timestamps,TEXT - 'NOK'
feeREAL-0
recipient_idTEXTFK → recipients(id)NULL
merchant_idTEXTFK → merchants(id)NULL
send_amountREAL-NULL
send_currencyTEXT-NULL
receive_amountREAL-NULL
receive_currencyTEXT-NULL
exchange_rateREAL-NULL
created_atTEXT-CURRENT_TIMESTAMP
completed_atTEXT-NULL

Indexes: idx_transactions_user on user_id, idx_transactions_merchant on merchant_id

Notes:

  • PasswordRemittances andhave 2FArecipient_id behaviourset, ismerchant_id implementedNULL
    • in
  • QR thepayments Kotlinhave authmerchant_id services.set, recipient_id NULL
  • is_platform_adminsend_* was/ addedreceive_* by/ V26.exchange_rate fields are populated for remittances only

5.3 refresh_tokensexchange_rates

SessionCurrency refresh-tokenexchange storage.

rates

Key(from fields:

  • id
  • user_id
  • jti
  • expires_at
  • created_at
  • version

Notes:

  • Used by auth-lifecycle and logout/revocation flows.
  • Session listing/revocation routes are documented in OpenAPI.

5.4 account_types

Reference data for account classifications.

Key fields:

  • id
  • name
  • normal_balance
  • created_at
  • version

5.5 accounts

Chart of accounts entries.

Key fields:

  • id
  • organization_id
  • code
  • name
  • account_type_id
  • currency_code
  • parent_account_id
  • is_active
  • lifecycle fields: created_at, updated_at, version, deleted_at

Notes:

  • Market-specific chart additions exist for BA and RS.
  • Account hierarchy is represented with parent_account_idNOK).

5.6 contacts

Customers and vendors.

Key fields:

  • id
  • organization_id
  • type
  • name
  • email, phone
  • registration and tax identifiers: registration_number, vat_number, jmbg, jmbg_hash, oib, oib_hash
  • address fields: address_line1, address_line2, city, postal_code, country
  • currency_code
  • payment_terms
  • notes
  • is_active
  • lifecycle fields: created_at, updated_at, version, deleted_at

Notes:

  • Sensitive personal/business identifiers are handled through the Kotlin service layer and migration-provided columns.

5.7 invoices

Sales invoices and e-invoice tracking.

Key fields:

  • id
  • organization_id
  • customer_id
  • invoice_number
  • dates: invoice_date, due_date, sent_at, viewed_at, paid_at
  • money fields: currency_code, exchange_rate, subtotal, tax_amount, discount_amount, total_amount, base_amount
  • status
  • notes, terms, pdf_url
  • e-invoice fields: is_reverse_charge, sef_id, sef_document_id, sef_status, sef_submitted_at, sef_accepted_at
  • created_by
  • lifecycle fields: created_at, updated_at, version, deleted_at

Column summary:

summary uuidorganization_id uuid fk, customer_id uuid fknumber,invoice/due dates, send/view/pay timestampstax,total, base amount as numeric money valuestext/enum-backedmigration history user, timestamps, version, deleted_at soft-delete
Column group Type/constraintType ConstraintsDefault
Identity/scopeid idSERIAL PRIMARY pk,KEY AUTOINCREMENT
Numbering/datesfrom_currency invoiceTEXT - 'NOK'
Amountsto_currency subtotal,TEXT NOT discount,NULL -
Statusrate statusREAL NOT byNULL -
E-invoiceupdated_at reverse-charge flag and SEF IDs/status/timestamps
Ownership/lifecycleTEXT creator- CURRENT_TIMESTAMP

Notes:

Seed
    data
  • Invoice status enum support was added by V18.
  • Serbia SEF integration fields are present on the invoice table.

5.8 invoice_items

Line items for invoices.

Key fields:

  • id
  • invoice_id
  • line_number
  • description
  • quantity
  • unit_price
  • tax_rate
  • vat_exempt
  • line_total
  • account_id
  • created_at
  • version
  • deleted_at

5.9 recurring_invoices

Recurring invoice templates/schedules.

Key fields:

  • id
  • organization_id
  • contact_id
  • frequency
  • next_issue_date
  • day_of_month
  • currency_code
  • notes
  • is_active
  • template_data
  • created_at, updated_at

5.10 expenses

Purchase/expense records.

Key fields:

  • id
  • organization_id
  • vendor_id
  • expense_number
  • expense_date
  • money fields: currency_code, exchange_rate, amount, base_amount, tax_amount
  • category
  • payment_method
  • account_id
  • description
  • receipt_url
  • status
  • approval/payment fields: approved_by, approved_at, paid_at
  • created_by
  • lifecycle fields: created_at, updated_at, version, deleted_at

Column summary:(db.ts:531-545):

Column groupCorridor Type/constraint summaryRate
Identity/scopeNOK → RSD id uuid pk, organization_id uuid fk, vendor_id uuid fk11.7
Numbering/dateNOK → BAM expense number and expense date1.04
AmountsNOK → PLN amount, base amount, tax amount, currency, exchange rate0.41
ClassificationNOK → PKR category, payment method, expense ledger account26.8
Approval/paymentNOK → TRY status, approver, approved timestamp, paid timestamp3.45
Evidence/lifecycleNOK → EUR receipt URL, creator, timestamps, version, deleted_at soft-delete0.089

Notes:

  • Expense status enum support was added by V19.

5.11 transactionsbank_accounts

GeneralLinked ledgerbank transactions.accounts (AISP — Open Banking read in production, mock balances in dev).

KeyPass-through fields:model: Drop NEVER holds customer money. The balance column stores the last AISP-read balance from the user's real bank account — it is a cached read-only value, not a Drop-held balance. In dev/demo mode, mock balances are seeded for testing.

  • id
  • organization_id
  • transaction_date
  • description
  • debit_account_id
  • credit_account_id
  • money fields: amount, currency_code, exchange_rate, base_amount
  • source reference: reference_type, reference_id
  • lock/reconciliation fields: locked, locked_at, reconciled, reconciled_at
  • notes
  • created_by
  • created_at
  • version
  • deleted_at

Column summary:

summary uuidorganization_id uuid fkaccount basecurrency, exchange ratetype/idback to invoices, expenses, or manual entriesandreconciliation flags/timestampscreated_at,version,deleted_atsoft-delete
Column group Type/constraintType ConstraintsDefault
Identity/scopeid idTEXT PRIMARY pk,KEY -
Double-entry legsuser_id debitTEXT NOT FK, credit accountNULL, FK → users(id)-
Amountsbank_name amount,TEXT NOT amount,NULL -
Source referenceaccount_number referenceTEXT NOT linksNULL -
Controlsiban lockTEXT - NULL
Lifecyclebalance creator,REAL - 0
currencyTEXT-'NOK'
is_primaryINTEGER-0
connected_atTEXT-CURRENT_TIMESTAMP

5.12

Index: bank_accountsidx_bank_accounts_user on user_id

cards (FUTURE — feature-flagged)

BankNote: accountsCards linkedare a FUTURE feature, gated behind feature flags (all default to ledgerfalse). accounts.

Key fields:

  • id
  • organization_id
  • account_id
  • bank_name
  • account_number
  • iban
  • currency_code
  • current_balance
  • is_active
  • lifecycle fields: created_at, updated_at, version, deleted_at

5.13 bank_transactions

Imported or entered bank movements.

Key fields:

  • id
  • bank_account_id
  • transaction_date
  • amount
  • description
  • reference
  • reconciled
  • matched_transaction_id
  • created_at
  • version
  • deleted_at

5.14 currencies

Currency reference data.

Key fields:

  • code
  • name
  • symbol
  • decimal_places
  • is_active
  • created_at
  • version

5.15 exchange_rates

Foreign-exchange rates.

Key fields:

  • id
  • base_currency
  • target_currency
  • rate
  • effective_date
  • source
  • last_updated
  • version
  • deleted_at

5.16 logged_actions

AuditThis table populatedexists byin database/applicationthe auditschema paths.but is not actively used until a card issuing partner is integrated.

ColumnVirtual summary:and physical payment cards.

summary primary names ID,timestamp, client IP, application namerowchangedfields,querytext
Column group Type/constraintType ConstraintsDefault
Identityid event_idTEXT PRIMARY identifierKEY-
Targetuser_id schema/tableTEXT NOT NULL, FK → users(id)-
Actor/timetype userTEXT CHECK('virtual','physical') 'virtual'
Change payloadlast_four action,TEXT NOT data,NULL -
token_refTEXT-NULL
expiryTEXTNOT NULL-
statusTEXTCHECK('active','frozen','cancelled')'active'
shipping_addressTEXT-NULL
created_atTEXT-CURRENT_TIMESTAMP
pin_hashTEXT-NULL (added via runtime migration in cards/[id]/pin/route.ts:51-53)

KeyIndex: fields:

idx_cards_user
    on
  • event_id
  • schema_name
  • table_name
  • user_id
  • action_timestamp
  • action
  • row_data
  • changed_fields
  • query
  • client_ip
  • application_name

Notes:

  • V28 widened the action column.
  • V30+ migrations add auth/RLS-related grants and helper functions.

5.17 chat_conversations

AI assistant conversation storage.

Key fields:

  • id
  • user_id
  • organization_id
  • messages
  • updated_at
  • version
  • deleted_at

5.18 beta_interests

Public/beta interest capture.

Key fields:

  • id
  • email
  • company_size
  • use_case
  • source
  • created_at
  • version

5.19 leads

Lead capture records from public/landing flows.

Key fields:

  • id
  • name
  • email
  • company
  • phone
  • country
  • message
  • lead_source
  • ip
  • user_agent
  • status
  • created_at

5.20 stripe_webhook_events

Payment provider webhook idempotency/audit log.

Key fields:

  • id
  • event_type
  • organization_id
  • payload
  • processed_at
  • error

5.21 sef_webhook_events

SEF status webhook idempotency/audit log added by V36. Because SEF does not expose a documented immutable event ID, the API computes a SHA-256 idempotency key from the parsed SEF payload and raw body before calling SefService.handleWebhook().

Key fields:

  • id — SHA-256 idempotency key; primary key
  • sef_invoice_id
  • status
  • status_date
  • payload
  • processing_status — processing, processed, or failed
  • processed_at
  • error
  • created_at

Notes:

  • Duplicate webhook deliveries with the same idempotency key return 200 with duplicate=true and are not reprocessed once processing is in progress or complete.
  • Failed events are marked failed; a later duplicate delivery can retry processing.
  • Signature verification still happens first via X-Sef-Signature / SEF_WEBHOOK_SECRET.

5.22 adapter_config

Per-market integration adapter toggles.

Key fields:

  • id
  • market
  • adapter_type
  • adapter_name
  • enabled
  • reason
  • updated_at
  • updated_by

Notes:

  • Added by V25.
  • Used to control market adapters such as e-invoice integrations.

5.23 schema_version

Legacy/internal schema marker table mapped by Exposed.

Key fields:

  • version
  • applied_at
  • description

Notes:

  • Flyway remains the migration authority. This table is not a replacement for Flyway history.

6. Cross-Cutting Conventions

UUID identifiers

Most business tables use UUID primary keys. Public API paths expose UUID strings for resource identifiers.

Soft deletion

Several tenant/business tables include deleted_at. Application queries should exclude soft-deleted rows unless a route is explicitly designed for archive/audit use.

Optimistic version field

Many tables include a version field. Preserve it when adding update paths and migrations.

Money

Money columns are stored as decimal/numeric values with explicit currency fields. base_amount fields support organization base-currency reporting.

Country and market support

Market-specific support currently includes HR/RS/BA concepts across country constraints, tax rates, chart-of-accounts migrations, SEF fields, and adapter configuration.

7. Operational Procedures

Add a table or columnsessions

    JWT

  1. Createsession a new Flyway migration with the next version.
  2. Add or update the corresponding Exposed mapping in Tables.kt.
  3. Update services/routes/tests that use the new field.
  4. Update OpenAPI and backend docs if API shape changes.
  5. Run Flyway validation/migration in the intended environment.
  6. Capture evidencetracking for MC/PRrevocation review.

Change an existing applied migration

Do not edit it. Instead:

  1. Create a new forward migration.
  2. Explain the compatibility path in the PR/MC evidence.
  3. Validate on a non-production target before promotion.

Repair Flyway metadata drift

Only perform repair after all of these are captured:

  1. Target identity: project, instance, database, environment.
  2. Flyway validate output showing exact drift.
  3. Schema checks proving the live schema matches expected intent.
  4. Written runbook and abort conditions.
  5. Repair transcript.
  6. Post-repair validate/migrate/info output.
  7. Deployment or smoke evidence if the drift blocked CI/CD.

MC #101509 is the reference example for this flow.

8. Validation Checklist

Before marking database documentation current:

  • Tables.kt table inventory reviewed.
  • Flyway migration directory reviewed.
  • No stale deployment assumptions remain in this document.
  • No legacy ORM workflow is presented as active.
  • OpenAPI/API docs updated when endpoint shapes changed.
  • Environment-specific migration claims cite evidence.

9. Index and Performance Strategy

The exact index inventory is migration-defined and should be inspected with psql against the target database when diagnosing query plans. The application design depends on these index principles:support.

accesspatternlookuporganization_id plus status/date/name as applicable+ +ordering+transaction date range; debit/credit account joinsaccountreconciliation status + transaction date filtering and user/time filtering
Query familyColumn RequiredType Constraints Default
Tenant listsid compositeTEXT PRIMARY byKEY -
Invoice listsuser_id organizationTEXT NOT customer/status/dateNULL, orderingFK → users(id)-
Expense liststoken_hash organizationTEXT NOT vendor/status/dateNULL -
Ledger reportscreated_at organizationTEXT - CURRENT_TIMESTAMP
Bank reconciliationexpires_at bankTEXT NOT +NULL -
Authrevoked user email lookup and refresh-token jti/user lookup
AuditINTEGER table/action/time- 0

PerformanceIndexes: targetsidx_sessions_user on user_id, idx_sessions_token on token_hash

Token hash: SHA-256 of the JWT string (auth.ts:59)

notifications

In-app notifications.

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
user_idTEXTNOT NULL, FK → users(id)-
typeTEXTNOT NULL-
titleTEXTNOT NULL-
bodyTEXTNOT NULL-
readINTEGER-0
created_atTEXT-CURRENT_TIMESTAMP

Index: idx_notifications_user on user_id

settings

Per-user preferences.

ColumnTypeConstraintsDefault
user_idTEXTPRIMARY KEY, FK → users(id)-
currencyTEXT-'NOK'
languageTEXT-'nb'
push_enabledINTEGER-1
email_enabledINTEGER-1
updated_atTEXT-CURRENT_TIMESTAMP

spending_limits (FUTURE — feature-flagged)

Note: Tied to the cards feature. Only active when card feature flags are enabled.

Card spending limits.

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
user_idTEXTNOT NULL, FK → users(id)-
card_idTEXTFK → cards(id)NULL
limit_typeTEXTNOT NULL-
amountREALNOT NULL-
currencyTEXT-'NOK'
created_atTEXT-CURRENT_TIMESTAMP

Indexes: idx_spending_limits_user on user_id, idx_spending_limits_card on card_id

Limit types (API-enforced): daily, weekly, monthly, transaction

rate_limits

Persistent rate limiting store.

ColumnTypeConstraintsDefault
keyTEXTPRIMARY KEY-
countINTEGERNOT NULL-
reset_atINTEGERNOT NULL-

Used by middleware.ts:rateLimit() for product-facingIP-based paths:rate limiting. Expired entries are cleaned on each call (middleware.ts:11).

Compliance & GDPR Tables

Added: 2026-02-16 (compliance infrastructure)

These tables support Drop's compliance requirements for Norwegian financial services regulation, GDPR, and AML/KYC requirements per hvitvaskingsloven.

audit_log

User action audit trail for compliance and security monitoring.

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
timestampTEXT-CURRENT_TIMESTAMP
user_idTEXTFK → users(id)NULL
actionTEXTNOT NULL-
resource_typeTEXT-NULL
resource_idTEXT-NULL
detailsTEXT-NULL
ip_addressTEXT-NULL
user_agentTEXT-NULL

Indexes: idx_audit_log_user on user_id, idx_audit_log_timestamp on timestamp, idx_audit_log_action on action

Purpose: Tracks all significant user actions (login, transaction, settings change, etc.) for audit purposes.

aml_alerts

AML (Anti-Money Laundering) transaction monitoring alerts.

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
user_idTEXTNOT NULL, FK → users(id)-
alert_typeTEXTNOT NULL-
severityTEXTNOT NULL, CHECK('low','medium','high','critical')-
transaction_idTEXTFK → transactions(id)NULL
detailsTEXT-NULL
statusTEXTCHECK('open','investigating','resolved','escalated','filed')'open'
reviewed_byTEXT-NULL
reviewed_atTEXT-NULL
created_atTEXT-CURRENT_TIMESTAMP

Indexes: idx_aml_alerts_user on user_id, idx_aml_alerts_status on status

Purpose: Records suspicious transaction patterns flagged by AML monitoring rules (e.g., structuring, velocity, high-risk corridors).

str_reports

STR (Suspicious Transaction Reports) filed with financial authorities.

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
user_idTEXTNOT NULL, FK → users(id)-
alert_idTEXTFK → aml_alerts(id)NULL
report_typeTEXTNOT NULL-
statusTEXTCHECK('draft','submitted','acknowledged')'draft'
filed_atTEXT-NULL
reference_numberTEXT-NULL
detailsTEXT-NULL
created_atTEXT-CURRENT_TIMESTAMP

Purpose: Tracks STRs filed with Økokrim/EFE (Norwegian financial intelligence unit) per hvitvaskingsloven requirements.

screening_results

Results from sanctions/PEP (Politically Exposed Persons) screening.

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
user_idTEXTNOT NULL, FK → users(id)-
screening_typeTEXTNOT NULL, CHECK('pep','sanctions','adverse_media')-
providerTEXT-NULL
resultTEXTNOT NULL, CHECK('clear','match','potential_match','error')-
match_detailsTEXT-NULL
screened_atTEXT-CURRENT_TIMESTAMP

Indexes: idx_screening_user on user_id

Purpose: Stores results from automated screening against PEP lists, sanctions lists (OFAC, UN, EU), and adverse media databases.

consents

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
user_idTEXTNOT NULL, FK → users(id)-
consent_typeTEXTNOT NULL-
grantedINTEGERNOT NULL1
granted_atTEXT-CURRENT_TIMESTAMP
withdrawn_atTEXT-NULL
ip_addressTEXT-NULL

Purpose: Tracks when users grant or withdraw consent for different types of data processing, with IP address as proof of consent action.

data_access_requests

GDPR data access/erasure/rectification requests (Art. 15-17).

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
user_idTEXTNOT NULL, FK → users(id)-
request_typeTEXTNOT NULL, CHECK('export','erasure','rectification','restriction')-
statusTEXTCHECK('pending','processing','completed','rejected')'pending'
requested_atTEXT-CURRENT_TIMESTAMP
completed_atTEXT-NULL
download_urlTEXT-NULL
notesTEXT-NULL

Indexes: idx_data_requests_user on user_id

Purpose: Tracks GDPR data subject access requests. export requests generate full data export, erasure triggers account deletion.

complaints

Customer complaints per Finansavtaleloven §3-53 (15-day response requirement).

ColumnTypeConstraintsDefault
idTEXTPRIMARY KEY-
user_idTEXTNOT NULL, FK → users(id)-
categoryTEXTNOT NULL-
subjectTEXTNOT NULL-
descriptionTEXTNOT NULL-
statusTEXTCHECK('received','investigating','resolved','escalated')'received'
resolutionTEXT-NULL
created_atTEXT-CURRENT_TIMESTAMP
resolved_atTEXT-NULL

Indexes: idx_complaints_user on user_id, idx_complaints_status on status

Categories (API-enforced): transaction, service, fees, privacy, technical, other

Purpose: Formal complaint logging system to ensure compliance with Norwegian financial services law requiring 15 business day response time.

Database Access Layer

Source: db.ts

Data Access Layer

The database access layer is Drizzle ORM (src/shared/db/schema.ts). The old db.ts dual-driver abstraction has been removed (see ADR-014).

Use Drizzle query builder or the sql template tag for raw queries:

import { db } from '@drop/shared/db';
import { users } from '@drop/shared/db/schema';
import { eq } from 'drizzle-orm';

// Type-safe query
const user = await db.select().from(users).where(eq(users.id, userId)).limit(1);

// Raw SQL escape hatch (PostgreSQL syntax, $1 params not needed — Drizzle handles binding)
import { sql } from 'drizzle-orm';
const result = await db.execute(sql`SELECT id FROM users WHERE email = ${email}`);

Migrations are managed by drizzle-kit:

cd src/shared && npx drizzle-kit generate  # Generate migration file
cd src/shared && npx drizzle-kit push      # Push schema to dev database
make db-push                               # Shortcut (from repo root)

Seed Data

When exchange_rates table is empty, seedData() (db.ts:530) populates:

  • Tenant-scoped6 listexchange endpointsrate shouldcorridors avoid(NOK full-table→ scansRSD, acrossBAM, organizations.PLN, PKR, TRY, EUR)
  • Month/quarterDemo reportdata queries(when shouldNODE_ENV be!== bounded"production" byor organizationSEED_DEMO=true): and
      date
    • 1 range.demo user (usr_demo1, [email protected], role: merchant)
    • Background3 reconciliation/exportrecipients jobs(Serbia, mayBosnia, use broader scans, but should be batchable and observable.Turkey)
    • Any1 newmerchant high-cardinality(Ahmetov fieldKebab)
      • used
    • 3 intransactions filters(2 shouldremittances, include1 anQR indexpayment)
      • decision
    • 2 inbank theaccounts migration(DNB PR.primary with 45,230 NOK, SpareBank 1 with 12,800 NOK)

    When adding an index:

    1. Add it in a new Flyway migration.
    2. Explain the route/report it supports.
    3. Verify with EXPLAIN or a representative query when data volume makes the risk material.

    10. Audit Log Scaling and Retention

    logged_actions can grow faster than ordinary tenant tables. Current documentation stance:

    • The active schema keeps audit rows in PostgreSQL and records actor, target table, action, row data, changed fields, query text, client IP, and application name.
    • V28 widened the action field to support current action labels.
    • No partitioning migration is currently documented as applied in Tables.kt/Flyway source of truth.

    Future scaling decision:

    • If audit volume threatens report/API latency or storage budgets, introduce an explicit Flyway migration for partitioning or archival.
    • The migration must include retention policy, query impact, backfill plan, and restore/audit requirements.
    • Until that migration exists, do not describe partitioning as active behaviour.

    11. Known Follow-Ups

    • Keep docs/backend/openapi.yaml aligned with implemented Ktor routes.
    • Keep docs/backend/API-REFERENCE.md aligned with OpenAPI.
    • Keep deployment docs aligned with GCP Cloud Run and Cloud SQL reality.
    • Consider generating a schema snapshot from a migrated Cloud SQL-compatible database for future reviews.
Back to top