Database Schema

Bilko Database Schema

Status: IMPLEMENTED (PrismaLast schemaverified: exists)2026-05-20 Location:Canonical backend: /Users/makinja/ALAI/products/Bilko/packages/database/prisma/schema.prismaapps/api Kotlin/Ktor service Database: PostgreSQL 14+on GCP Cloud SQL for deployed environments ORM:Schema source of truth: PrismaFlyway 5.xSQL Lastmigrations updated:+ 2026-02-20Exposed table mappings

---

Purpose

This document describes the complete database schema forcurrently Bilko.used by the Bilko Kotlin/Ktor API. It replaces the older ORM-era database notes and must be kept aligned with:

• Flyway migrations: apps/api/src/main/resources/db/migration/
• Exposed table mappings: apps/api/src/main/kotlin/no/alai/bilko/models/Tables.kt
• Route/service behaviour: apps/api/src/main/kotlin/no/alai/bilko/routes/ and apps/api/src/main/kotlin/no/alai/bilko/services/
• Environment mapping: infrastructure/gcp/ENV-MATRIX.md

Do not treat generated diagrams, frontend type definitions, or archived deployment notes as database authority.

---

1. Architecture Overview

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

Runtime stack:

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

The schema is IMPLEMENTEDforward-only. inApplied Prismamigrations 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 readypostflight for migration. This doc explains the relationships, constraints, and design decisions.validation.

---

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

Purpose: Multi-tenant root. Every business is one organization.

Column	Type	Constraints	Description
id	UUID	PK, default uuid_generate_v4()	Primary key
name	VARCHAR(255)	NOT NULL	Business name
registrationNumber	VARCHAR(50)	NULL	Company tax ID
vatNumber	VARCHAR(50)	NULL	VAT registration number
baseCurrency	CHAR(3)	NOT NULL, default 'EUR'	ISO 4217 currency code
country	CHAR(2)	NOT NULL	ISO 3166-1 alpha-2 country code
language	CHAR(2)	NOT NULL, default 'sr'	ISO 639-1 language code
fiscalYearStart	DATE	NOT NULL, default '2026-01-01'	Fiscal year start date
createdAt	TIMESTAMP	NOT NULL, default now()	Record creation timestamp
updatedAt	TIMESTAMP	NOT NULL, default now()	Last update timestamp

Indexes:

• Primary key: id

Business rules:

• baseCurrency determines default currency for all transactions
• country determines tax rules (Serbia 20%, BiH 17%, Croatia 25%)
• fiscalYearStart used for annual reports

---

2. User

Source-of-Truth

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

Column	Type	Constraints	Description
id	UUID	PK	Primary key
organizationId	UUID	FK → Organization, NOT NULL, CASCADE	Organization membership
email	VARCHAR(255)	UNIQUE, NOT NULL	Login email
passwordHash	VARCHAR(255)	NOT NULL	bcrypt hash (12 rounds)
fullName	VARCHAR(255)	NOT NULL	Display name
role	ENUM	NOT NULL	owner, admin, accountant, viewer
twoFactorEnabled	BOOLEAN	NOT NULL, default false	2FA status
twoFactorSecret	VARCHAR(255)	NULL	TOTP secret
lastLoginAt	TIMESTAMP	NULL	Last login timestamp
createdAt	TIMESTAMP	NOT NULL	Account creation
updatedAt	TIMESTAMP	NOT NULL	Last update

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

---

Chart of AccountsRules

3. AccountType

Purpose: Defines account categories for double-entry bookkeeping.

Column	Type	Constraints	Description
id	INT	PK, AUTOINCREMENT	Primary key
name	VARCHAR(50)	UNIQUE, NOT NULL	Asset, Liability, Equity, Revenue, Expense
normalBalance	ENUM	NOT NULL	debit or credit
createdAt	TIMESTAMP	NOT NULL	Record creation

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.

Column	Type	Constraints	Description
id	UUID	PK	Primary key
organizationId	UUID	FK → Organization, NOT NULL	Organization scope
code	VARCHAR(10)	NOT NULL	Account code (e.g., "1000", "4000")
name	VARCHAR(255)	NOT NULL	Account name (e.g., "Cash", "Revenue")
accountTypeId	INT	FK → AccountType, NOT NULL	Account category
currencyCode	CHAR(3)	NOT NULL, default 'EUR'	Account currency
parentAccountId	UUID	FK → Account, NULL	Parent account (for sub-accounts)
isActive	BOOLEAN	NOT NULL, default true	Active status
createdAt	TIMESTAMP	NOT NULL	Record creation
updatedAt	TIMESTAMP	NOT NULL	Last update

Indexes:

• Primary key: id
• Unique: (organizationId, code)
• Index: idx_accounts_organization on organizationId
• Index: idx_accounts_type on accountTypeId

Business rules:

• Code MUST be unique within organization
• Cannot delete account with transactions
• Parent-child hierarchy for sub-accounts (e.g., 1000 → 1001, 1002)

---

Contacts (Customers & Vendors)

5. Contact

Purpose: Customers (invoice recipients) and vendors (expense payees).

Column	Type	Constraints	Description
id	UUID	PK	Primary key
organizationId	UUID	FK → Organization, NOT NULL	Organization scope
type	ENUM	NOT NULL	customer, vendor, both
name	VARCHAR(255)	NOT NULL	Contact name
email	VARCHAR(255)	NULL	Email address
phone	VARCHAR(50)	NULL	Phone number
registrationNumber	VARCHAR(50)	NULL	Company registration number
vatNumber	VARCHAR(50)	NULL	VAT number
addressLine1	VARCHAR(255)	NULL	Street address
addressLine2	VARCHAR(255)	NULL	Apt/suite
city	VARCHAR(100)	NULL	City
postalCode	VARCHAR(20)	NULL	Postal/ZIP code
country	CHAR(2)	NULL	ISO 3166-1 alpha-2
currencyCode	CHAR(3)	NOT NULL, default 'EUR'	Preferred currency
paymentTerms	INT	NOT NULL, default 30	Payment terms in days
notes	TEXT	NULL	Free-text notes
isActive	BOOLEAN	NOT NULL, default true	Active status
createdAt	TIMESTAMP	NOT NULL	Record creation
updatedAt	TIMESTAMP	NOT NULL	Last update

Indexes:

• Primary key: id
• Index: idx_contacts_organization on organizationId
• Index: idx_contacts_type on type

Enums:

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

Business rules:

• Soft delete (isActive = false) if has invoices/expenses
• currencyCode determines default invoice/expense currency

---

Invoicing

6. Invoice

Purpose: Sales invoices (outgoing). Revenue recognition.

Column	Type	Constraints	Description
id	UUID	PK	Primary key
organizationId	UUID	FK → Organization, NOT NULL	Organization scope
customerId	UUID	FK → Contact, NOT NULL	Invoice recipient
invoiceNumber	VARCHAR(50)	NOT NULL	Auto-generated (e.g., INV-2026-001)
invoiceDate	DATE	NOT NULL	Invoice issue date
dueDate	DATE	NOT NULL	Payment due date
currencyCode	CHAR(3)	NOT NULL	Invoice currency
exchangeRate	DECIMAL(12,6)	NOT NULL, default 1.0	Exchange rate at invoiceDate
subtotal	DECIMAL(19,4)	NOT NULL	Sum of line totals (before tax)
taxAmount	DECIMAL(19,4)	NOT NULL, default 0	Total VAT/tax
discountAmount	DECIMAL(19,4)	NOT NULL, default 0	Total discount
totalAmount	DECIMAL(19,4)	NOT NULL	subtotal + taxAmount - discountAmount
baseAmount	DECIMAL(19,4)	NOT NULL	Converted to org baseCurrency
status	ENUM	NOT NULL, default 'draft'	Invoice status
sentAt	TIMESTAMP	NULL	When invoice was sent
viewedAt	TIMESTAMP	NULL	When customer viewed (email tracking)
paidAt	TIMESTAMP	NULL	When marked as paid
notes	TEXT	NULL	Internal notes
terms	TEXT	NULL	Payment terms text
pdfUrl	VARCHAR(500)	NULL	Cloudflare R2 URL
createdBy	UUID	FK → User, NULL	Creator user
createdAt	TIMESTAMP	NOT NULL	Record creation
updatedAt	TIMESTAMP	NOT NULL	Last update

Indexes:

• Primary key: id
• Unique: (organizationId, invoiceNumber)
• Index: idx_invoices_organization on organizationId
• Index: idx_invoices_customer on customerId
• Index: idx_invoices_status on status
• Index: idx_invoices_due_date on dueDate
• Composite: idx_invoices_org_status_date on (organizationId, status, invoiceDate)

Enums:

enum InvoiceStatus {
  draft      // Being edited
  sent       // Sent to customer
  viewed     // Customer viewed email
  paid       // Payment received
  overdue    // Past dueDate and unpaid
  cancelled  // Voided
}

Business rules:

• invoiceNumber auto-generated on first save
• exchangeRate locked at invoiceDate (NEVER recalculate)
• baseAmount = totalAmount * exchangeRate
• Cannot edit invoice unless status = draft
• When status changes to 'paid', create Transaction (debit BankAccount, credit AccountsReceivable)

---

7. InvoiceItem

Purpose: Line items on invoices.

Column	Type	Constraints	Description
id	UUID	PK	Primary key
invoiceId	UUID	FK → Invoice, CASCADE, NOT NULL	Parent invoice
lineNumber	INT	NOT NULL	Line order (1, 2, 3...)
description	VARCHAR(500)	NOT NULL	Item description
quantity	DECIMAL(10,2)	NOT NULL	Quantity sold
unitPrice	DECIMAL(19,4)	NOT NULL	Price per unit
taxRate	DECIMAL(5,2)	NOT NULL, default 0	VAT rate (20 = 20%)
lineTotal	DECIMAL(19,4)	NOT NULL	quantity * unitPrice
accountId	UUID	FK → Account, NULL	Revenue account
createdAt	TIMESTAMP	NOT NULL	Record creation

Indexes:

• Primary key: id
• Index: idx_invoice_items_invoice on invoiceId

Business rules:

• lineTotal = quantity * unitPrice (calculated before save)
• Tax amount = lineTotal * (taxRate / 100)
• accountId determines which revenue account is credited

---

Expenses

8. Expense

Purpose: Purchase tracking (incoming). Expense recognition.

Column	Type	Constraints	Description
id	UUID	PK	Primary key
organizationId	UUID	FK → Organization, NOT NULL	Organization scope
vendorId	UUID	FK → Contact, NULL	Vendor (optional)
expenseNumber	VARCHAR(50)	NOT NULL	Auto-generated (e.g., EXP-2026-001)
expenseDate	DATE	NOT NULL	Expense date
currencyCode	CHAR(3)	NOT NULL	Expense currency
exchangeRate	DECIMAL(12,6)	NOT NULL, default 1.0	Exchange rate at expenseDate
amount	DECIMAL(19,4)	NOT NULL	Total expense amount
baseAmount	DECIMAL(19,4)	NOT NULL	Converted to org baseCurrency
taxAmount	DECIMAL(19,4)	NOT NULL, default 0	VAT amount
category	VARCHAR(100)	NOT NULL	Expense category
paymentMethod	VARCHAR(50)	NULL	cash, card, bank_transfer, etc.
accountId	UUID	FK → Account, NULL	Expense account
description	TEXT	NULL	Expense description
receiptUrl	VARCHAR(500)	NULL	Cloudflare R2 URL
status	ENUM	NOT NULL, default 'pending'	Approval status
approvedBy	UUID	FK → User, NULL	Approver user
approvedAt	TIMESTAMP	NULL	Approval timestamp
paidAt	TIMESTAMP	NULL	Payment timestamp
createdBy	UUID	FK → User, NULL	Creator user
createdAt	TIMESTAMP	NOT NULL	Record creation
updatedAt	TIMESTAMP	NOT NULL	Last update

Indexes:

• Primary key: id
• Unique: (organizationId, expenseNumber)
• Index: idx_expenses_organization on organizationId
• Index: idx_expenses_vendor on vendorId
• Index: idx_expenses_category on category
• Index: idx_expenses_date on expenseDate
• Composite: idx_expenses_org_date_category on (organizationId, expenseDate, category)

Enums:

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

Business rules:

• expenseNumber auto-generated
• exchangeRate locked at expenseDate
• baseAmount = amount * exchangeRate
• When status → approved, create Transaction (debit ExpenseAccount, credit AccountsPayable)
• When status → paid, create Transaction (debit AccountsPayable, credit BankAccount)

---

Transactions (Double-Entry Ledger)

9. Transaction

Purpose: General ledger transactions. Every financial event creates a transaction.

Column	Type	Constraints	Description
id	UUID	PK	Primary key
organizationId	UUID	FK → Organization, NOT NULL	Organization scope
transactionDate	DATE	NOT NULL	Transaction date
description	VARCHAR(255)	NOT NULL	Transaction description
debitAccountId	UUID	FK → Account, NOT NULL	Account to debit
creditAccountId	UUID	FK → Account, NOT NULL	Account to credit
amount	DECIMAL(19,4)	NOT NULL	Transaction amount
currencyCode	CHAR(3)	NOT NULL	Transaction currency
exchangeRate	DECIMAL(12,6)	NOT NULL, default 1.0	Exchange rate at transactionDate
baseAmount	DECIMAL(19,4)	NOT NULL	Converted to org baseCurrency
referenceType	VARCHAR(50)	NULL	invoice, expense, payment, manual
referenceId	UUID	NULL	Invoice/Expense ID
locked	BOOLEAN	NOT NULL, default false	Immutable if true
lockedAt	TIMESTAMP	NULL	When locked
reconciled	BOOLEAN	NOT NULL, default false	Matched to bank transaction
reconciledAt	TIMESTAMP	NULL	When reconciled
notes	TEXT	NULL	Free-text notes
createdBy	UUID	FK → User, NULL	Creator user
createdAt	TIMESTAMP	NOT NULL	Record creation

Indexes:

• Primary key: id
• Index: idx_transactions_organization on organizationId
• Index: idx_transactions_date on transactionDate
• Index: idx_transactions_debit on debitAccountId
• Index: idx_transactions_credit on creditAccountId
• Index: idx_transactions_reference on (referenceType, referenceId)
• Composite: idx_transactions_org_date on (organizationId, transactionDate)

Business rules:

• DEBITS = CREDITS — Every transaction has exactly one debit and one credit
• debitAccountId ≠ creditAccountId (enforced in API)
• Cannot edit if locked = true
• Cannot delete if reconciled = true
• exchangeRate locked at transactionDate
• baseAmount = amount * exchangeRate

Common transaction patterns:

1. InvoiceAdd createdschema (draftchanges →via sent):new Flyway migrations only.

   • Debit:Use Accountsthe Receivablenext (Asset)available version under apps/api/src/main/resources/db/migration/.
   • Credit:Never Revenuerewrite (Revenue)an already-applied migration in demo, staging, or production.

2. InvoiceUpdate paid:Exposed mappings in the same change.

   • Debit:Tables.kt Bankshould Accountmatch (Asset)
   the
   • Credit:migrated Accountsdatabase Receivablesurface (Asset)used by routes/services.

3. ExpenseUpdate approved:API and docs together.

   • Debit:If Expensea Accountschema (Expense)
   change
   • Credit:alters Accountsrequest/response Payableshapes, (Liability)update docs/backend/openapi.yaml and relevant backend docs.

4. ExpenseValidate paid:with Flyway before deploy promotion.

   • Debit:A Accountsvalid Payabledeploy (Liability)target must pass Flyway validation before the API is considered healthy.
5. Credit: Bank

   Keep Accountenvironment (Asset)identity explicit.

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

---

Banking3. &Migration ReconciliationInventory

10.

As BankAccount

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.

Purpose:Important Bankmigration account metadata.groups:

Column	Type	Constraints	Description
id	UUID	PK	Primary key
organizationId	UUID	FK → Organization, NOT NULL	Organization scope
accountId	UUID	FK → Account, NOT NULL	GL account (must be Asset)
bankName	VARCHAR(255)	NOT NULL	Bank name
accountNumber	VARCHAR(50)	NULL	Account number
iban	VARCHAR(50)	NULL	IBAN
currencyCode	CHAR(3)	NOT NULL, default 'EUR'	Account currency
currentBalance	DECIMAL(19,4)	NOT NULL, default 0	Current balance
isActive	BOOLEAN	NOT NULL, default true	Active status
createdAt	TIMESTAMP	NOT NULL	Record creation
updatedAt	TIMESTAMP	NOT NULL	Last update

Indexes:

• Primary key: id
• Index: idx_bank_accounts_organization on organizationId

Business rules:

• accountId MUST be Asset type account
• currentBalance updated when transactions created
• Soft delete (isActive = false)

---

11. BankTransaction

Purpose: Bank statement imports. For reconciliation.

Column	Type	Constraints	Description
id	UUID	PK	Primary key
bankAccountId	UUID	FK → BankAccount, CASCADE, NOT NULL	Parent bank account
transactionDate	DATE	NOT NULL	Transaction date
amount	DECIMAL(19,4)	NOT NULL	Positive = credit, negative = debit
description	VARCHAR(500)	NULL	Bank description
reference	VARCHAR(255)	NULL	Reference number
reconciled	BOOLEAN	NOT NULL, default false	Matched to GL transaction
matchedTransactionId	UUID	NULL	GL transaction ID
createdAt	TIMESTAMP	NOT NULL	Record creation

Indexes:

• Primary key: id
• Index: idx_bank_transactions_account on bankAccountId
• Index: idx_bank_transactions_date on transactionDate

Business rules:

• Imported from CSV bank statements
• Matched to GL transactions via reconciliation workflow
• reconciled = true when matched

---

Multi-Currency

12. Currency

Purpose: Supported currencies.

Column	Type	Constraints	Description
code	CHAR(3)	PK	ISO 4217 currency code
name	VARCHAR(100)	NOT NULL	Currency name
symbol	VARCHAR(10)	NULL	Currency symbol
decimalPlaces	SMALLINT	NOT NULL, default 2	Decimal precision
isActive	BOOLEAN	NOT NULL, default true	Active status
createdAt	TIMESTAMP	NOT NULL	Record creation

Seed data:

EUR | Euro           | €    | 2
RSD | Serbian Dinar  | din. | 2
BAM | Bosnian Mark   | KM   | 2
HRK | Croatian Kuna  | kn   | 2
USD | US Dollar      | $    | 2

---

13. ExchangeRate

Purpose: Historical exchange rates.

Column	Type	Constraints	Description
id	UUID	PK	Primary key
baseCurrency	CHAR(3)	FK → Currency, NOT NULL	From currency
targetCurrency	CHAR(3)	FK → Currency, NOT NULL	To currency
rate	DECIMAL(12,6)	NOT NULL	Exchange rate
effectiveDate	DATE	NOT NULL	Rate effective date
source	VARCHAR(50)	NULL	ECB, fixer.io, manual
lastUpdated	TIMESTAMP	NOT NULL	Last update timestamp

Indexes:

• Primary key: id
• Unique: (baseCurrency, targetCurrency, effectiveDate)
• Index: idx_exchange_rates_date on effectiveDate
• Index: idx_exchange_rates_pair on (baseCurrency, targetCurrency)

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: Immutable audit log. Captures all INSERT/UPDATE/DELETE.

Column	Type	Constraints	Description
eventId	BIGINT	PK, AUTOINCREMENT	Event ID
schemaName	TEXT	NOT NULL	Database schema (default: public)
tableName	TEXT	NOT NULL	Table name
userId	UUID	FK → User, NULL	User who performed action
actionTimestamp	TIMESTAMP	NOT NULL, default now()	When action occurred
action	ENUM	NOT NULL	INSERT, UPDATE, DELETE
rowData	JSONB	NULL	Full row data before change
changedFields	JSONB	NULL	Changed fields (UPDATE only)
queryText	TEXT	NULL	SQL query (if available)
clientIp	INET	NULL	Client IP address
applicationName	TEXT	NOT NULL, default 'fiken-clone-api'	Application identifier

Indexes:

• Primary key: eventId
• Index: idx_logged_actions_timestamp on actionTimestamp
• Index: idx_logged_actions_table on tableName
• Index: idx_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: Migration tracking.

Column	Type	Constraints	Description
version	VARCHAR(20)	PK	Version string (e.g., "1.0.0")
appliedAt	TIMESTAMP	NOT NULL, default now()	Migration timestamp
description	TEXT	NULL	Migration description

Business rules:

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

---

Data Types & Precision

NUMERIC(19,4) for ALL Money

CRITICAL: NEVER use float, double, or JavaScript number for currency.

• Precision: 19 digits total, 4 decimal places
• Range: -999,999,999,999,999.9999 to +999,999,999,999,999.9999
• Prisma type: Decimal
• PostgreSQL type: NUMERIC(19,4)

Why:

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

Usage in code:

import { Decimal } from '@prisma/client/runtime'

const amount = new Decimal('125000.0000')
const taxRate = new Decimal('0.20')
const taxAmount = amount.times(taxRate) // 25000.0000

---

Constraints Summary

Primary Keys

All tables use 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:

   • (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

# Generate Prisma Client
npx prisma generate

# Create migration
npx prisma migrate dev --name migration_name

# Apply migrations (production)
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Seed initial data
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

Type	Name format	Example
Add table	add_{table_name}	add_webhook_subscriptions
Add column	add_{column}_to_{table}	add_sefId_to_invoices
Remove column	remove_{column}_from_{table}	remove_legacy_field_from_users
Add index	add_index_{table}_{columns}	add_index_invoices_status_dueDate
Data fix	fix_{description}	fix_applicationName_default

Zero-Downtime Migration Patterns

For production migrations on live data:

Pattern	When to use	Example
Additive only	New nullable columns	Add sefId String? to Invoice
Expand-contract	Rename column	Add new column → backfill → drop old
Shadow table	Large table restructure	Create new table → migrate → swap
Index concurrently	Add index without lock	Manual SQL via prisma db execute

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:

schema,compatibilitytiers,StripeRLSpermissiveAdapterconfiguration,hardening,security-defineradmingrants,bcrypt-prefixnormalization,passwordhash

Table	Index Columns	Type	Purpose
InvoiceV1-V6	(organizationId,Initial status,accounting invoiceDate)	Composite	Listcolumns, invoicesencrypted withidentifiers, statussupplementary filtertables, organization logo, role storage normalization
InvoiceV7-V15	(organizationId,Plan contactId)	Composite	Invoiceswebhook bylogging, customercompliance calendar, recurring invoices, audit enum cleanup, demo/CI seed data, trial fields
InvoiceV16-V24	(organizationId,Country dueDate,constraints, status)	Composite	Overduebaseline, invoiceinvoice/expense cronstatus enum support, demo org fixes, CI viewer, BA jurisdiction split, BA entity charts
InvoiceItemV25-V29	(invoiceId)	FK	Loadplatform-admin linemarker, itemsSerbia chart, logged action width, UAT demo users
ExpenseV30-V35	(organizationId,RLS/session status,auth expenseDate)	Composite	Listauth expenseshelpers, withdemo filter
Expense	(organizationId,UAT categoryAccountId)	Composite	Expense by category
Transaction	(organizationId, transactionDate)	Composite	Date range reports
Transaction	(organizationId, debitAccountId)	Composite	Account balance calc
Transaction	(organizationId, creditAccountId)	Composite	Account balance calc
Transaction	(referenceType, referenceId)	Composite	Lookup by invoice/expense
BankTransaction	(bankAccountId, transactionDate)	Composite	Bank statement view
BankTransaction	(organizationId, reconciled)	Composite	Unreconciled transactions
ExchangeRate	(baseCurrency, targetCurrency, effectiveDate)	Unique	Daily rate lookup
LoggedAction	(organizationId, tableName, createdAt)	Composite	Audit trail queries
LoggedAction	(organizationId, userId)	Composite	User activity queriesreset

Operational note from MC #101509:

• Staging checksum drift was detected for V22, V25, V26, and V28.
• Schema checks proved checksum-only drift before repair.
• Controlled Flyway repair + migrate brought staging 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 by organization_id 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.

Application code must set the correct organization/user context before querying tenant-scoped tables. Any new table containing tenant data should include organization_id unless it is intentionally global reference data.

---

5. Tables

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

Index5.1 Designorganizations

Principles

Tenant root table.

Key fields:

• 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

Notes:

• country is constrained by migration history and used 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

Notes:

• Password and 2FA behaviour is implemented in the Kotlin auth services.
• is_platform_admin was added by V26.

5.3 refresh_tokens

Session refresh-token storage.

Key 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_id.

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

Notes:

• 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

Notes:

• Expense status enum support was added by V19.

5.11 transactions

General ledger transactions.

Key fields:

• 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

5.12 bank_accounts

Bank accounts linked to ledger 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

Audit table populated by database/application audit paths.

Key fields:

• 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 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.22 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 column

1. Org-firstCreate compositea indexes:new EveryFlyway querymigration filters by organizationId first — it must bewith the leftmostnext columnversion.
2. Add or update the corresponding Exposed mapping in all multi-column indexes.Tables.kt.
3. StatusUpdate +services/routes/tests datethat combos:use Listthe endpointsnew combinefield.
status
4. Update filter + date sort, so (orgId, status, date) triples cover both filterOpenAPI and ORDERbackend BY.docs if API shape changes.
5. ForeignRun keyFlyway indexes: Prisma creates FK indexes automatically; verify with \d tableNamevalidation/migration in psql.the intended environment.
6. CoveringCapture indexes:evidence Forfor reportMC/PR queries that fetch only a few columns, consider partial indexes in Phase 2 (e.g., WHERE status = 'paid').review.

QueryChange Performancean Targets

existing applied

Query	Target	Notes
List invoices (1000 rows)	< 50ms	With org + status index
Invoice by ID	< 5ms	PK lookup
Dashboard metrics	< 300ms	7 parallel aggregations
P&L report (1 year)	< 500ms	Transaction table scan with date index
Audit trail query	< 100ms	LoggedAction composite index
VAT report (monthly)	< 200ms	Invoice + Expense aggregation

---

Audit Log Partitioning Strategy

Why Partition LoggedAction?migration

The LoggedAction table is 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 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 doesDo not nativelyedit manageit. PostgreSQL table partitioning. Strategy:Instead:

1. DefineCreate basea tablenew forward migration.
2. Explain the compatibility path in schema.prismathe (noPR/MC partitioning directive)evidence.
3. ApplyValidate partitioning via prisma db execute with raw SQL after initial migration
4. Maintain partition creation ason a yearlynon-production operationstarget taskbefore (or use pg_partman extension)promotion.

ArchiveRepair StrategyFlyway (7-Yearmetadata Retention)drift

Year
Only 1-7:perform Activerepair partitionsafter inall PostgreSQLof (Railwaythese EUare Frankfurt)captured:
Year

7+:
Target Archiveidentity: partitionproject, toinstance, colddatabase, storageenvironment.
(Cloudflare
Flyway R2validate Glacieroutput tier)showing Deleteexact fromdrift.
PostgreSQL
Schema Retainchecks R2proving archivesthe live schema matches expected intent.

Written runbook and abort conditions.

Repair transcript.

Post-repair validate/migrate/info output.

Deployment or smoke evidence if the drift blocked CI/CD.


MC #101509 is the reference example for GDPRthis compliance period

Trigger: Automated yearly job checks MIN(createdAt) in oldest partition. If > 7 years: export to R2, drop partition.

MVP Approach (Phase 1)

For MVP: no partitioning required. Single table with composite index (organizationId, tableName, createdAt) sufficient for < 1M rows. Implement partitioning in Phase 2 before reaching 5M rows.flow.

---

8. Validation Checklist

EndBefore ofmarking Databasedatabase Schemadocumentation 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. 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.