# 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

```mermaid
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

```mermaid
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:**
- 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

**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:**
```prisma
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 Accounts

### Account Hierarchy & Normal Balances

```mermaid
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:**
```prisma
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:**
```prisma
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:**
```prisma
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:**

```mermaid
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:**
- 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:**
```prisma
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. **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:**
- 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:**
```prisma
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:**
```typescript
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

```bash
# 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
```sql
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
```sql
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**