Database Schema

DropBilko Database Schema

Source:Status: src/shared/db/schema.tsIMPLEMENTED (Drizzle ORMPrisma schema —exists) singleLocation: source/Users/makinja/ALAI/products/Bilko/packages/database/prisma/schema.prisma ofDatabase: truthPostgreSQL for14+ allORM: environments)Prisma 5.x Last updated: 2026-02-20

Purpose

This document describes the complete database schema for Bilko. The schema is IMPLEMENTED in Prisma and ready for migration. This doc explains the relationships, constraints, and design decisions.

Entity Relationship Overview

Organization (1) ──┬── (N) User
                   ├── (N) Account
                   ├── (N) Contact
                   ├── (N) Invoice
                   ├── (N) Expense
                   ├── (N) Transaction
                   └── (N) BankAccount

Contact (1) ────┬── (N) Invoice
                └── (N) Expense

Invoice (1) ──── (N) InvoiceItem

Account (1) ───┬── (N) InvoiceItem
               ├── (N) Expense
               ├── (N) BankAccount
               ├── (N) Transaction (debit)
               ├── (N) Transaction (credit)
               └── (N) Account (parent-child hierarchy)

BankAccount (1) ── (N) BankTransaction

Currency (1) ───┬── (N) ExchangeRate (base)
                └── (N) ExchangeRate (target)

User (1) ───┬── (N) Invoice (creator)
            ├── (N) Expense (creator)
            ├── (N) Expense (approver)
            ├── (N) Transaction (creator)
            └── (N) LoggedAction

Core Tables

1. Organization

Drop uses PostgreSQL 16Purpose: asMulti-tenant theroot. soleEvery database engine in all environments (development, CI, staging, production). Database accessbusiness is viaone organization.

Drizzle ORMIndexes:. There is no SQLite dependency and no dual-driver abstraction.

• LocalPrimary dev: PostgreSQL 16 in Docker (docker compose up -d), port 5433
• CI: PostgreSQL 16 service container in GitHub Actions
• Production: PostgreSQL 16 on AWS RDS (db.t3.small)
• Schema definition:key: src/shared/db/schema.ts (Drizzle schema, TypeScript, PostgreSQL-native)
• Migrations: managed by drizzle-kitid

SeeBusiness ADR-014rules:

• baseCurrency determines default currency for theall fulltransactions
rationale.
• country determines tax rules (Serbia 20%, BiH 17%, Croatia 25%)
• fiscalYearStart used for annual reports

2. User

Total tables:Purpose: 19Users within an organization. Role-based access control.

Indexes:

• Primary key: id
• Unique: email
• Foreign key: organizationId → Organization(id)
• Index: idx_users_organization on organizationId
• Index: idx_users_email on email

Enums:

enum UserRole {
  owner       // Full access, can delete org
  admin       // Can manage users and settings
  accountant  // Can create invoices/expenses
  viewer      // Read-only access
}

Business rules:

• One owner per organization (enforced in API)
• Cannot delete owner
• Password must be bcrypt hashed, NEVER plain text

TablesChart of Accounts

users3. AccountType

Purpose: Defines account categories for double-entry bookkeeping.

Enums:

enum NormalBalance {
  debit   // Asset, Expense accounts increase with debits
  credit  // Liability, Equity, Revenue accounts increase with credits
}

Seed data:

1 | Asset      | debit
2 | Liability  | credit
3 | Equity     | credit
4 | Revenue    | credit
5 | Expense    | debit

4. Account

Purpose: Chart of Accounts. Hierarchical GL accounts.

ID format: usr_ + 16 hex charscode (generatede.g., by"1000", randomId("usr"4000") in utils-server.ts:4)

Password hashing: bcrypt with 12 rounds (utils-server.ts:8-11)

recipients

Saved remittance recipients per user.

Supported countries:Indexes: RS, BA, PL, PK, TR (enforced at API level, not DB level)

Index:

• Primary key: idx_recipients_userid
• Unique: (organizationId, code)
• Index: idx_accounts_organization on user_id
  organizationId

---

merchants

Registered merchant profiles for QR payments.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
business_name	TEXT	NOT NULL	-
org_number	TEXT	UNIQUE NOT NULL	-
address	TEXT	-	NULL
bank_account	TEXT	NOT NULL	-
fee_rate	REAL	-	0.01
status	TEXT	-	'active'
created_at	TEXT	-	CURRENT_TIMESTAMP

• Index: idx_merchants_orgidx_accounts_type on org_numberaccountTypeId

enum ContactType {
  customer  // Invoice recipient
  vendor    // Expense payee
  both      // Can be both customer and vendor
}

enum 

enum ExpenseStatus {
  pending   // Awaiting approval
  approved  // Approved, ready to pay
  paid      // Payment made
  rejected  // Approval denied
}

daily,
• Imported weekly,from monthly,CSV transaction
  bank statements
• Matched to GL transactions via reconciliation workflow
• reconciled = true when matched

Multi-Currency

rate_limits12. Currency

PersistentPurpose: rateSupported limiting store.currencies.

UsedSeed bydata:

middleware.ts:rateLimit()EUR | Euro           | €    | 2
RSD | Serbian Dinar  | din. | 2
BAM | Bosnian Mark   | KM   | 2
HRK | Croatian Kuna  | kn   | 2
USD | US Dollar      | $    | 2
 for IP-based rate limiting. Expired entries are cleaned on each call (middleware.ts:11).

Compliance

13. & 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_logExchangeRate

UserPurpose: actionHistorical auditexchange trail for compliance and security monitoring.rates.

Indexes:

• Primary key: idx_audit_log_userid
• Unique: (baseCurrency, targetCurrency, effectiveDate)
• Index: idx_exchange_rates_date on user_id,effectiveDate
• Index: idx_audit_log_timestampidx_exchange_rates_pair on timestamp,(baseCurrency, idx_audit_log_actiontargetCurrency)
on

Business rules:

• Rates fetched daily from ECB or fixer.io API
• When creating transaction, rate is locked at transaction date
• If no rate for exact date, use nearest available (warn in logs)

Audit Trail

14. LoggedAction

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

aml_alerts

AML (Anti-Money Laundering) transaction monitoring alerts.INSERT/UPDATE/DELETE.

Indexes:

• Primary key: idx_aml_alerts_usereventId
• Index: idx_logged_actions_timestamp on user_id,actionTimestamp
• Index: idx_aml_alerts_statusidx_logged_actions_table on tableName
• Index: statusidx_logged_actions_user on userId

Enums:

enum AuditAction {
  INSERT
  UPDATE
  DELETE
}

Business rules:

• APPEND-ONLY — NEVER delete or update records
• Triggered via Prisma middleware (automatic)
• Used for: compliance, debugging, rollback simulation

Schema Version

15. SchemaVersion

Purpose: RecordsMigration 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.tracking.

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

screening_results

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

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

GDPR consent tracking for user data processing.

Indexes: idx_consents_user on user_id

Consent Types (API-enforced): terms, privacy, marketing, cookies_analytics, cookies_marketing

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).

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).

Indexes:Business rules:

• Updated by Prisma migrations
• Used to verify schema version matches application version

Data Types & Precision

NUMERIC(19,4) for ALL Money

CRITICAL: idx_complaints_userNEVER onuse user_idfloat, idx_complaints_statusdouble, or JavaScript number onfor currency.

• Precision: 19 digits total, 4 decimal places
• Range: -999,999,999,999,999.9999 to +999,999,999,999,999.9999
• Prisma type: statusDecimal

• PostgreSQL type: NUMERIC(19,4)

CategoriesWhy:

• JavaScript number has 53-bit precision (API-enforced):safe transaction,up service,to fees,2^53 privacy,- technical,1 other
  = 9,007,199,254,740,991)
• Financial calculations require exact decimal precision
• Example: 0.1 + 0.2 = 0.30000000000000004 (float error)

Purpose:Usage in code: 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 { dbDecimal } from '@drop/shared/db';@prisma/client/runtime'

importconst {amount users= }new fromDecimal('125000.0000')
const taxRate = new Decimal('@drop/shared/db/schema';0.20')
importconst {taxAmount eq= } from 'drizzle-orm';amount.times(taxRate) // 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}`);25000.0000

Constraints Summary

Primary Keys

MigrationsAll aretables manageduse UUID primary keys (except AccountType uses INT auto-increment).

Foreign Keys

All foreign keys have onDelete: Cascade (deleting organization deletes all data).

Unique Constraints

• User.email
• Account.(organizationId, code)
• Invoice.(organizationId, invoiceNumber)
• Expense.(organizationId, expenseNumber)
• ExchangeRate.(baseCurrency, targetCurrency, effectiveDate)

Check Constraints

(Enforced in API layer, not database):

• amount > 0 for all financial amounts
• dueDate >= invoiceDate for invoices
• debitAccountId ≠ creditAccountId for transactions

Indexes Strategy

Query Patterns Optimized

1. List by organization + filter:

   • drizzle-kit(organizationId, status, date): composite index on invoices
   • (organizationId, category, date) composite index on expenses

2. Foreign key lookups:

   • All foreign keys have indexes

3. Date range queries:

   • Dedicated indexes on transactionDate, invoiceDate, expenseDate, dueDate

4. Reconciliation:

   • Index on (referenceType, referenceId) for transaction lookups

Migration Commands

cd# src/sharedGenerate &&Prisma Client
npx drizzle-kitprisma generate

# GenerateCreate migration
filenpx cdprisma src/sharedmigrate &&dev --name migration_name

# Apply migrations (production)
npx drizzle-kitprisma pushmigrate deploy

# Push schema to devReset database make(dev db-pushonly)
npx prisma migrate reset

# ShortcutSeed (frominitial repodata
root)npx prisma db seed

Seed Data

AccountType

INSERT INTO account_types (id, name, normal_balance) VALUES
(1, 'Asset', 'debit'),
(2, 'Liability', 'credit'),
(3, 'Equity', 'credit'),
(4, 'Revenue', 'credit'),
(5, 'Expense', 'debit');

Currency

INSERT INTO currencies (code, name, symbol, decimal_places) VALUES
('EUR', 'Euro', '€', 2),
('RSD', 'Serbian Dinar', 'din.', 2),
('BAM', 'Bosnian Mark', 'KM', 2),
('HRK', 'Croatian Kuna', 'kn', 2),
('USD', 'US Dollar', '$', 2);

Entity Relationship Diagram

erDiagram
    ORGANIZATION ||--o{ USER : "members"
    ORGANIZATION ||--o{ CONTACT : "contacts"
    ORGANIZATION ||--o{ INVOICE : "invoices"
    ORGANIZATION ||--o{ EXPENSE : "expenses"
    ORGANIZATION ||--o{ TRANSACTION : "transactions"
    ORGANIZATION ||--o{ BANK_ACCOUNT : "bank accounts"
    ORGANIZATION ||--o{ ACCOUNT_TYPE : "account types"
    ORGANIZATION ||--o{ ACCOUNT : "chart of accounts"
    ORGANIZATION ||--o{ CURRENCY : "currencies"
    ORGANIZATION ||--o{ LOGGED_ACTION : "audit log"

    INVOICE ||--o{ INVOICE_ITEM : "line items"
    INVOICE }o--|| CONTACT : "billed to"
    INVOICE }o--|| USER : "created by"

    EXPENSE }o--o| CONTACT : "vendor"
    EXPENSE }o--|| ACCOUNT : "category account"

    ACCOUNT_TYPE ||--o{ ACCOUNT : "classifies"
    ACCOUNT ||--o{ TRANSACTION : "debited in"
    ACCOUNT ||--o{ TRANSACTION : "credited in"
    ACCOUNT |o--o| BANK_ACCOUNT : "linked GL"

    BANK_ACCOUNT ||--o{ BANK_TRANSACTION : "transactions"
    BANK_TRANSACTION }o--o| TRANSACTION : "reconciles"

    CURRENCY ||--o{ EXCHANGE_RATE : "base rates"

    LOGGED_ACTION }o--o| USER : "performed by"

Domain groupings:

• Identity: Organization, User
• Financial Core: Account, AccountType, Transaction
• Invoicing: Invoice, InvoiceItem, Contact
• Expenses: Expense
• Banking: BankAccount, BankTransaction
• Compliance: LoggedAction, SchemaVersion
• Currency: Currency, ExchangeRate

Migration Strategy

Prisma Migrate Workflow

Bilko uses Prisma Migrate for all schema changes. No manual SQL migrations.

Development workflow

# 1. Edit packages/database/prisma/schema.prisma
# 2. Generate and apply migration
npx prisma migrate dev --name describe_your_change

# 3. Regenerate Prisma Client
npx prisma generate

# 4. Seed if new lookup data needed
npx prisma db seed

Production deployment workflow

# Applied automatically during Railway deploy via package.json postinstall:
# "postinstall": "prisma migrate deploy && prisma generate"

# Manual production apply:
npx prisma migrate deploy

# Verify migration status:
npx prisma migrate status

Migration naming conventions

Zero-Downtime Migration Patterns

For production migrations on live data:

Rule: Never drop columns in the same migration that removes their usage from code. Deploy code first (ignoring old column), then migrate.

Pre-Deploy Migration Checklist

Before deploying any migration to production:

•  prisma migrate status shows no drift from dev
•  Migration tested on a staging DB with production data volume
•  Rollback plan documented (additive migrations are safe; destructive need manual rollback SQL)
•  LoggedAction.applicationName default changed from "fiken-clone-api" to "bilko-api" (pending — fix before first production deploy)
•  Backup taken before running destructive migrations

Known Pre-Deploy Fix Required

-- LoggedAction.applicationName has legacy default value in current schema
-- Run via: npx prisma db execute --file fix_applicationName.sql

ALTER TABLE "LoggedAction"
  ALTER COLUMN "applicationName" SET DEFAULT 'bilko-api';

UPDATE "LoggedAction"
  SET "applicationName" = 'bilko-api'
  WHERE "applicationName" = 'fiken-clone-api';

Enhanced Index Design

Index Inventory

All indexes defined in packages/database/prisma/schema.prisma and verified against query patterns:

Index Design Principles

1. Org-first composite indexes: Every query filters by organizationId first — it must be the leftmost column in all multi-column indexes.
2. Status + date combos: List endpoints combine status filter + date sort, so (orgId, status, date) triples cover both filter and ORDER BY.
3. Foreign key indexes: Prisma creates FK indexes automatically; verify with \d tableName in psql.
4. Covering indexes: For report queries that fetch only a few columns, consider partial indexes in Phase 2 (e.g., WHERE status = 'paid').

Query Performance Targets

Audit Log Partitioning Strategy

Why Partition LoggedAction?

The LoggedAction table is empty,append-only and retains data for 7 years (financial compliance requirement). At 10 requests/minute per organization, a 100-org instance generates ~500K audit rows/month. After 3 years: ~18M rows.

Without partitioning: sequential scans on seedData(LoggedAction become slow. With partitioning: queries can prune partitions by year.

Partitioning Approach: Range by Year

-- Convert LoggedAction to range-partitioned table (PostgreSQL 11+)
-- Execute ONCE during Phase 2 setup, before data volume grows

CREATE TABLE "LoggedAction_partitioned"
  PARTITION OF "LoggedAction" (
    -- same columns
  ) PARTITION BY RANGE (EXTRACT(YEAR FROM "createdAt"));

-- Annual partitions
CREATE TABLE "LoggedAction_2026" PARTITION OF "LoggedAction_partitioned"
  FOR VALUES FROM (2026) TO (2027);

CREATE TABLE "LoggedAction_2027" PARTITION OF "LoggedAction_partitioned"
  FOR VALUES FROM (2027) TO (2028);
-- etc.

Prisma Compatibility

Prisma does not natively manage PostgreSQL table partitioning. Strategy:

1. Define base table in schema.prisma (db.ts:530)no populates:
   partitioning
   • 6 exchange rate corridors (NOK → RSD, BAM, PLN, PKR, TRY, EUR)directive)
   • DemoApply datapartitioning (whenvia NODE_ENVprisma !==db "production"execute orwith SEED_DEMO=true):raw
     SQL
     • 1after demoinitial user (usr_demo1, [email protected], role: merchant)migration
     • 3Maintain recipientspartition creation as a yearly operations task (Serbia,or Bosnia,use Turkey)pg_partman extension)
     • 1

Archive Strategy (Ahmetov7-Year Kebab)Retention)

Year transactions1-7:    Active partitions in PostgreSQL (2Railway remittances,EU 1Frankfurt)
QRYear payment)

End of Database Schema