Database — Schema & Models

Bilko Database — Prisma + PostgreSQL

BookStack — Provjeri PRVO

Prije traženja bilo čega — provjeri BookStack (http://localhost:6875). Centralna baza znanja za tools, skills, hooks, agents, rules, projekte, klijente, dokumentaciju. Ako odgovor postoji tamo — NE TRAŽI dalje.

Schema Location

prisma/schema.prisma — 15 models, fully defined

Database Models (15)

Core:

• Organization — Multi-tenant root (baseCurrency, country, language)
• User — RBAC (owner, admin, accountant, viewer)

Chart of Accounts:

• AccountType — Asset, Liability, Equity, Revenue, Expense
• Account — Hierarchical CoA with parent-child relations

Contacts:

• Contact — Customers, vendors, or both (type enum)

Invoicing:

• Invoice — Sales invoices with multi-currency support
• InvoiceItem — Line items with tax rates

Expenses:

• Expense — Purchase tracking with approval workflow

Transactions:

• Transaction — Double-entry ledger (debit + credit accounts)

Banking:

• BankAccount — Bank account metadata
• BankTransaction — Bank statement imports for reconciliation

Multi-Currency:

• Currency — Currency definitions (EUR, RSD, BAM, HRK, etc.)
• ExchangeRate — Historical exchange rates by date

Audit:

• LoggedAction — Immutable audit trail (APPEND-ONLY)
• SchemaVersion — Migration tracking

Key Design Decisions

1. NUMERIC(19,4) for Money

NEVER use float or JavaScript number for currency.

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

2. Double-Entry Bookkeeping

Every financial event creates a Transaction with:

• debitAccountId — Account to debit
• creditAccountId — Account to credit
• amount — MUST be equal for both sides
• Balance = sum(debits) - sum(credits) per account

3. Multi-Currency with Rate Locking

• Invoice.exchangeRate — Locked at invoice date
• Transaction.exchangeRate — Locked at transaction date
• baseAmount — Amount converted to org's baseCurrency
• NEVER recalculate historical transactions with current rates

4. Immutable Audit Trail

LoggedAction table:

• APPEND-ONLY — NEVER delete or update
• Captures: table name, user ID, action (INSERT/UPDATE/DELETE), old/new values
• Used for: compliance, debugging, rollback simulation

5. Transaction Locking

• Transaction.locked — Once true, record is immutable
• Locked transactions cannot be edited or deleted
• Used for: end-of-period close, tax reporting

6. Organization-Scoped Multi-Tenancy

• Every record has organizationId foreign key
• Queries MUST filter by org (enforced in API middleware)
• No cross-org data access

7. UUID Primary Keys

• All IDs: uuid_generate_v4() (PostgreSQL function)
• NEVER use auto-increment for business data
• Portable across systems, no collisions

Migration Rules

1. Never edit existing migrations — Always create new ones
2. Test migrations on copy — Never run on production first
3. Backward compatible — Additive changes only
4. Data migrations separate — Use Prisma seed or custom scripts
5. Rollback plan — Document how to undo breaking changes

Naming Conventions

• DB columns: snake_case (via @map)
• Prisma fields: camelCase
• Indexes: idx_{table}_{column(s)}
• Foreign keys: Auto-generated by Prisma

Indexes

Defined for:

• All foreign keys (automatic)
• Common query patterns (org + date, org + status)
• Unique constraints (org + code, org + invoice number)

Enums

• UserRole: owner, admin, accountant, viewer
• NormalBalance: debit, credit
• ContactType: customer, vendor, both
• InvoiceStatus: draft, sent, viewed, paid, overdue, cancelled
• ExpenseStatus: pending, approved, paid, rejected
• AuditAction: INSERT, UPDATE, DELETE

Development 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

# Open Prisma Studio
npx prisma studio

Critical Rules

1. NUMERIC for money — NEVER float
2. Double-entry enforced — Every transaction has debit + credit
3. Exchange rates locked — At transaction date, NEVER recalculate
4. Audit is append-only — NEVER delete LoggedAction records
5. UUID everywhere — NEVER expose auto-increment IDs