# Database — Schema & Models

# Bilko Database — Prisma + PostgreSQL

## BookStack — Provjeri PRVO

Prije traženja bilo čega — provjeri BookStack (https://docs.basicconsulting.no). 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

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

# 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