Database Schema

Bilko Database Schema

Status: IMPLEMENTED (Prisma schema exists) Location: /Users/makinja/ALAI/products/Bilko/packages/database/prisma/schema.prisma Database: PostgreSQL 14+ ORM: 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

Full ER Diagram

erDiagram
    Organization {
        UUID id PK
        VARCHAR name
        VARCHAR registrationNumber
        VARCHAR vatNumber
        CHAR baseCurrency
        CHAR country
        CHAR language
        DATE fiscalYearStart
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }

    User {
        UUID id PK
        UUID organizationId FK
        VARCHAR email
        VARCHAR passwordHash
        VARCHAR fullName
        ENUM role
        BOOLEAN twoFactorEnabled
        VARCHAR twoFactorSecret
        TIMESTAMP lastLoginAt
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }

    AccountType {
        INT id PK
        VARCHAR name
        ENUM normalBalance
        TIMESTAMP createdAt
    }

    Account {
        UUID id PK
        UUID organizationId FK
        VARCHAR code
        VARCHAR name
        INT accountTypeId FK
        CHAR currencyCode
        UUID parentAccountId FK
        BOOLEAN isActive
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }

    Contact {
        UUID id PK
        UUID organizationId FK
        ENUM type
        VARCHAR name
        VARCHAR email
        VARCHAR phone
        VARCHAR vatNumber
        VARCHAR addressLine1
        VARCHAR city
        CHAR country
        CHAR currencyCode
        INT paymentTerms
        BOOLEAN isActive
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }

    Invoice {
        UUID id PK
        UUID organizationId FK
        UUID customerId FK
        VARCHAR invoiceNumber
        DATE invoiceDate
        DATE dueDate
        CHAR currencyCode
        DECIMAL exchangeRate
        DECIMAL subtotal
        DECIMAL taxAmount
        DECIMAL discountAmount
        DECIMAL totalAmount
        DECIMAL baseAmount
        ENUM status
        TIMESTAMP sentAt
        TIMESTAMP paidAt
        VARCHAR pdfUrl
        UUID createdBy FK
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }

    InvoiceItem {
        UUID id PK
        UUID invoiceId FK
        INT lineNumber
        VARCHAR description
        DECIMAL quantity
        DECIMAL unitPrice
        DECIMAL taxRate
        DECIMAL lineTotal
        UUID accountId FK
        TIMESTAMP createdAt
    }

    Expense {
        UUID id PK
        UUID organizationId FK
        UUID vendorId FK
        VARCHAR expenseNumber
        DATE expenseDate
        CHAR currencyCode
        DECIMAL exchangeRate
        DECIMAL amount
        DECIMAL baseAmount
        DECIMAL taxAmount
        VARCHAR category
        VARCHAR paymentMethod
        UUID accountId FK
        ENUM status
        UUID approvedBy FK
        TIMESTAMP approvedAt
        UUID createdBy FK
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }

    Transaction {
        UUID id PK
        UUID organizationId FK
        DATE transactionDate
        VARCHAR description
        UUID debitAccountId FK
        UUID creditAccountId FK
        DECIMAL amount
        CHAR currencyCode
        DECIMAL exchangeRate
        DECIMAL baseAmount
        VARCHAR referenceType
        UUID referenceId
        BOOLEAN locked
        BOOLEAN reconciled
        UUID createdBy FK
        TIMESTAMP createdAt
    }

    BankAccount {
        UUID id PK
        UUID organizationId FK
        UUID accountId FK
        VARCHAR bankName
        VARCHAR accountNumber
        VARCHAR iban
        CHAR currencyCode
        DECIMAL currentBalance
        BOOLEAN isActive
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }

    BankTransaction {
        UUID id PK
        UUID bankAccountId FK
        DATE transactionDate
        DECIMAL amount
        VARCHAR description
        VARCHAR reference
        BOOLEAN reconciled
        UUID matchedTransactionId
        TIMESTAMP createdAt
    }

    Currency {
        CHAR code PK
        VARCHAR name
        VARCHAR symbol
        SMALLINT decimalPlaces
        BOOLEAN isActive
        TIMESTAMP createdAt
    }

    ExchangeRate {
        UUID id PK
        CHAR baseCurrency FK
        CHAR targetCurrency FK
        DECIMAL rate
        DATE effectiveDate
        VARCHAR source
        TIMESTAMP lastUpdated
    }

    LoggedAction {
        BIGINT eventId PK
        TEXT schemaName
        TEXT tableName
        UUID userId FK
        TIMESTAMP actionTimestamp
        ENUM action
        JSONB rowData
        JSONB changedFields
        INET clientIp
    }

    SchemaVersion {
        VARCHAR version PK
        TIMESTAMP appliedAt
        TEXT description
    }

    Organization ||--o{ User : "has"
    Organization ||--o{ Account : "owns"
    Organization ||--o{ Contact : "manages"
    Organization ||--o{ Invoice : "issues"
    Organization ||--o{ Expense : "tracks"
    Organization ||--o{ Transaction : "records"
    Organization ||--o{ BankAccount : "holds"

    User ||--o{ Invoice : "createdBy"
    User ||--o{ Expense : "createdBy"
    User ||--o{ Expense : "approvedBy"
    User ||--o{ Transaction : "createdBy"
    User ||--o{ LoggedAction : "performed"

    AccountType ||--o{ Account : "categorizes"
    Account ||--o{ Account : "parentOf"
    Account ||--o{ InvoiceItem : "revenueAccount"
    Account ||--o{ Expense : "expenseAccount"
    Account ||--o| BankAccount : "glAccount"
    Account ||--o{ Transaction : "debitAccount"
    Account ||--o{ Transaction : "creditAccount"

    Contact ||--o{ Invoice : "customer"
    Contact ||--o{ Expense : "vendor"

    Invoice ||--o{ InvoiceItem : "contains"

    BankAccount ||--o{ BankTransaction : "has"

    Currency ||--o{ ExchangeRate : "base"
    Currency ||--o{ ExchangeRate : "target"

Multi-Tenant Scoping

graph LR
    ORG[Organization\nMulti-tenant Root]

    ORG --> U[Users\nowner/admin/accountant/viewer]
    ORG --> COA[Chart of Accounts\nHierarchical GL]
    ORG --> C[Contacts\nCustomers & Vendors]
    ORG --> INV[Invoices\nOutgoing]
    ORG --> EXP[Expenses\nIncoming]
    ORG --> TXN[Transactions\nDouble-Entry Ledger]
    ORG --> BANK[BankAccounts\nReconciliation]

    INV --> ITEM[InvoiceItems\nLine Items]
    BANK --> BTXN[BankTransactions\nStatement Import]
    TXN --> LOG[LoggedAction\nAudit Trail]

    style ORG fill:#00E5A0,color:#000
    style TXN fill:#ffd700,color:#000

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:

Business rules:

2. User

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:

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:

Chart of Accounts

Account Hierarchy & Normal Balances

graph TD
    COA[Chart of Accounts]

    COA --> A[1xxx Assets\nnormalBalance: debit]
    COA --> L[2xxx Liabilities\nnormalBalance: credit]
    COA --> E[3xxx Equity\nnormalBalance: credit]
    COA --> R[4xxx Revenue\nnormalBalance: credit]
    COA --> EX[5xxx Expenses\nnormalBalance: debit]

    A --> CA[1100 Current Assets]
    A --> FA[1500 Fixed Assets]
    CA --> Cash[1110 Cash]
    CA --> Bank[1120 Bank Accounts]
    CA --> AR[1200 Accounts Receivable]
    Bank --> B1[1121 Intesa RSD]
    Bank --> B2[1122 Raiffeisen EUR]

    L --> CL[2100 Current Liabilities]
    L --> LL[2500 Long-term]
    CL --> AP[2110 Accounts Payable]
    CL --> VAT[2120 VAT Payable]

    E --> SC[3100 Share Capital]
    E --> RE[3900 Retained Earnings]

    R --> SR[4100 Service Revenue]
    R --> PR[4200 Product Sales]

    EX --> OE[5100 Operating Expenses]
    OE --> SAL[5110 Salaries]
    OE --> RENT[5120 Rent]

    style A fill:#4ade80,color:#000
    style L fill:#f87171,color:#000
    style E fill:#60a5fa,color:#000
    style R fill:#a78bfa,color:#000
    style EX fill:#fb923c,color:#000

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:

Business rules:

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:

Enums:

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

Business rules:

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:

Enums:

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

Invoice Status Transitions:

stateDiagram-v2
    [*] --> draft : Created
    draft --> sent : Send email\n(creates GL Transaction)
    sent --> viewed : Tracking pixel loaded
    viewed --> paid : Mark as paid\n(creates GL Transaction)
    sent --> paid : Mark as paid\n(creates GL Transaction)
    draft --> cancelled : Cancel
    sent --> cancelled : Cancel\n(reverses Transaction)
    viewed --> cancelled : Cancel\n(reverses Transaction)
    paid --> [*]
    cancelled --> [*]

    note right of draft
        Can edit line items,\ndates, amounts
    end note
    note right of sent
        Locked — no edits\nPDF generated & stored in R2
    end note
    note right of overdue
        Automated check: dueDate < today\nAND status != paid
    end note

Business rules:

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:

Business rules:

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:

Enums:

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

Business rules:

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:

Business rules:

Common transaction patterns:

  1. Invoice created (draft → sent):

    • Debit: Accounts Receivable (Asset)
    • Credit: Revenue (Revenue)

  2. Invoice paid:

    • Debit: Bank Account (Asset)
    • Credit: Accounts Receivable (Asset)

  3. Expense approved:

    • Debit: Expense Account (Expense)
    • Credit: Accounts Payable (Liability)

  4. Expense paid:

    • Debit: Accounts Payable (Liability)
    • Credit: Bank Account (Asset)

Banking & Reconciliation

10. BankAccount

Purpose: Bank account metadata.

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:

Business rules:

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:

Business rules:

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:

Business rules:

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:

Enums:

enum AuditAction {
  INSERT
  UPDATE
  DELETE
}

Business rules:

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:

Data Types & Precision

NUMERIC(19,4) for ALL Money

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

Why:

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

Check Constraints

(Enforced in API layer, not database):

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

End of Database Schema

Revision #3
Created 2026-02-23 10:47:52 UTC by John
Updated 2026-05-31 20:02:35 UTC by John