Database Schema Reference 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 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 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: 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); 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: Table Index Columns Type Purpose Invoice (organizationId, status, invoiceDate) Composite List invoices with status filter Invoice (organizationId, contactId) Composite Invoices by customer Invoice (organizationId, dueDate, status) Composite Overdue invoice cron InvoiceItem (invoiceId) FK Load line items Expense (organizationId, status, expenseDate) Composite List expenses with filter Expense (organizationId, 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 queries Index Design Principles Org-first composite indexes : Every query filters by organizationId first — it must be the leftmost column in all multi-column indexes. Status + date combos : List endpoints combine status filter + date sort, so (orgId, status, date) triples cover both filter and ORDER BY. Foreign key indexes : Prisma creates FK indexes automatically; verify with \d tableName in psql. Covering indexes : For report queries that fetch only a few columns, consider partial indexes in Phase 2 (e.g., WHERE status = 'paid' ). Query Performance Targets 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? 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 does not natively manage PostgreSQL table partitioning. Strategy: Define base table in schema.prisma (no partitioning directive) Apply partitioning via prisma db execute with raw SQL after initial migration Maintain partition creation as a yearly operations task (or use pg_partman extension) Archive Strategy (7-Year Retention) Year 1-7: Active partitions in PostgreSQL (Railway EU Frankfurt) Year 7+: Archive partition to cold storage (Cloudflare R2 Glacier tier) Delete from PostgreSQL Retain R2 archives for GDPR 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. End of Database Schema