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: 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: 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 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: 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 } 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: 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: Invoice created (draft → sent): Debit: Accounts Receivable (Asset) Credit: Revenue (Revenue) Invoice paid: Debit: Bank Account (Asset) Credit: Accounts Receivable (Asset) Expense approved: Debit: Expense Account (Expense) Credit: Accounts Payable (Liability) 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: 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 List by organization + filter: (organizationId, status, date) composite index on invoices (organizationId, category, date) composite index on expenses Foreign key lookups: All foreign keys have indexes Date range queries: Dedicated indexes on transactionDate , invoiceDate , expenseDate , dueDate 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