Backend Backend — Target Architecture Bilko API — Express Backend 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. Status: NOT BUILT YET This directory is EMPTY. The CLAUDE.md describes the target architecture. When building, follow docs/backend/API-REFERENCE.md as the implementation contract. Target Tech Stack Framework: Express + TypeScript Database: PostgreSQL 15 via Prisma Auth: JWT (15min access + 7d refresh) + Passport.js Validation: Zod Middleware: helmet, cors, rate-limit, auth-guard, zod-validation, error-handler Route Structure All routes under /api/v1/{resource} : /api/v1/auth — login, register, refresh, logout /api/v1/organizations — org CRUD /api/v1/users — user management /api/v1/accounts — chart of accounts /api/v1/invoices — invoice CRUD /api/v1/expenses — expense CRUD /api/v1/transactions — transaction ledger /api/v1/contacts — customer/vendor contacts /api/v1/banking — bank account integration /api/v1/reports — financial reports Middleware Stack (Order Matters) helmet — Security headers cors — CORS with whitelist express.json() — Body parser rate-limit — 100 req/15min per IP auth-guard — JWT validation (protected routes) zod-validation — Request validation route-handler — Business logic error-handler — Centralized error responses Error Response Format { "error": "Error message", "code": "ERROR_CODE", "details": {} // optional } HTTP Status Codes: 400 — Validation error 401 — Unauthorized (missing/invalid token) 403 — Forbidden (insufficient permissions) 404 — Not found 500 — Internal server error Database Access ORM: Prisma Client from @bilko/database package Connection: Read DATABASE_URL from env Transactions: Use Prisma transactions for multi-step operations NEVER: Raw SQL for business logic (use for migrations only) Authentication Strategy: JWT (access + refresh tokens) Access token: 15min expiry, httpOnly cookie Refresh token: 7d expiry, httpOnly cookie, stored in DB Password: bcrypt hash with salt rounds = 12 2FA: Optional TOTP (stored in User.twoFactorSecret) Validation Rules All requests validated with Zod schemas: Money: Must be string or number, converted to Decimal Currency: 3-letter ISO code (EUR, RSD, BAM, HRK) Dates: ISO 8601 format (YYYY-MM-DD) UUIDs: Valid v4 UUIDs for all IDs Emails: RFC 5322 compliant Double-Entry Rules (CRITICAL) Every financial transaction MUST: Have both debit and credit accounts Equal amounts (debit = credit) Reference the source (invoice ID, expense ID) Lock exchange rate at transaction date Create audit log entry (LoggedAction) Development Rules NEVER hold money — This is an accounting tool, not a payment processor Immutable transactions — Once locked, NEVER modify Audit everything — All mutations logged to LoggedAction Multi-currency always — Even single-currency orgs need exchange rate support Test with real accounting scenarios — Invoice → payment → reconciliation API Reference Full endpoint documentation in docs/backend/API-REFERENCE.md (to be created). This file will be the contract for implementation. 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 Never edit existing migrations — Always create new ones Test migrations on copy — Never run on production first Backward compatible — Additive changes only Data migrations separate — Use Prisma seed or custom scripts 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 NUMERIC for money — NEVER float Double-entry enforced — Every transaction has debit + credit Exchange rates locked — At transaction date, NEVER recalculate Audit is append-only — NEVER delete LoggedAction records UUID everywhere — NEVER expose auto-increment IDs API Reference Bilko API Reference Status: SPECIFICATION (backend not implemented) Base URL: http://localhost:4000/api/v1 (development) Production URL: https://api.bilko.io/api/v1 Last updated: 2026-02-20 Purpose This document is the implementation contract for Bilko's backend. All ~35 endpoints are specified with: HTTP method + path Authentication requirements Request/response TypeScript interfaces Query parameters Error responses Example requests/responses CRITICAL: Backend is NOT BUILT. This is the spec that apps/api/ MUST implement. Table of Contents Authentication (5 endpoints) Organization (2 endpoints) Users (4 endpoints) Contacts (5 endpoints) Invoices (8 endpoints) Expenses (6 endpoints) Bank Accounts (4 endpoints) Reports (7 endpoints) Chart of Accounts (3 endpoints) Transactions (2 endpoints) Settings (2 endpoints) Currencies (2 endpoints) Total: 50 endpoints API Architecture Overview graph LR subgraph CLIENT [Client] FE[Next.js Frontend\nbilko.io:3000] end subgraph API [Express API — api.bilko.io:4000] AUTH_R[/auth/*\nPublic] ORG_R[/organization\nAll roles] USR_R[/users/*\nowner, admin] CON_R[/contacts/*\nAll roles] INV_R[/invoices/*\nAll roles] EXP_R[/expenses/*\nAll roles] BANK_R[/bank-accounts/*\nAll roles] RPT_R[/reports/*\nAll roles] ACC_R[/accounts/*\nAll roles] TXN_R[/transactions/*\nAll roles] SET_R[/settings/*\nowner, admin] CUR_R[/currencies\nAll roles] end FE -->|Bearer token\nin Authorization header| API FE -->|refreshToken\nhttpOnly cookie| AUTH_R style AUTH_R fill:#e2e8f0,color:#000 style FE fill:#00E5A0,color:#000 Global Response Patterns Pagination All list endpoints support pagination: interface PaginatedResponse { data: T[] meta: { total: number // Total records page: number // Current page (1-indexed) perPage: number // Records per page totalPages: number // Total pages } } Query parameters: page (default: 1) perPage (default: 20, max: 100) sort (field name, default varies by endpoint) order ( asc or desc , default: desc ) Error Responses interface ApiError { error: string // Human-readable error message code: string // Machine-readable error code details?: Record // Field-level validation errors } HTTP Status Codes: 400 Bad Request — Invalid request body/params 401 Unauthorized — Missing or invalid auth token 403 Forbidden — User lacks required role 404 Not Found — Resource does not exist 422 Unprocessable Entity — Validation failed 500 Internal Server Error — Server error 1. Authentication POST /api/v1/auth/register Create new organization and owner user. Auth: None Rate limit: 5 req/min Request: interface RegisterRequest { // Organization organizationName: string country: 'RS' | 'BA' | 'HR' // Serbia, BiH, Croatia baseCurrency: 'EUR' | 'RSD' | 'BAM' | 'HRK' language: 'sr' | 'bs' | 'hr' registrationNumber?: string // Company tax ID vatNumber?: string // User email: string // Must be unique password: string // Min 8 chars, 1 upper, 1 lower, 1 number fullName: string } Response (201): interface RegisterResponse { user: { id: string email: string fullName: string role: 'owner' } organization: { id: string name: string country: string baseCurrency: string } tokens: { accessToken: string // JWT, expires in 15 min refreshToken: string // Expires in 7 days } } Errors: 400 — Email already exists 422 — Validation failed (weak password, invalid country, etc.) POST /api/v1/auth/login Authenticate with email + password. Auth: None Rate limit: 5 req/min Request: interface LoginRequest { email: string password: string rememberMe?: boolean // If true, refreshToken expires in 30 days } Response (200): interface LoginResponse { user: { id: string email: string fullName: string role: 'owner' | 'admin' | 'accountant' | 'viewer' organizationId: string organizationName: string } tokens: { accessToken: string // JWT, expires in 15 min refreshToken: string // httpOnly cookie } } Errors: 401 — Invalid credentials 403 — Account disabled or requires 2FA POST /api/v1/auth/refresh Get new access token using refresh token. Auth: Refresh token (httpOnly cookie) Rate limit: 100 req/min Request: None (uses cookie) Response (200): interface RefreshResponse { accessToken: string } Errors: 401 — Invalid or expired refresh token POST /api/v1/auth/logout Invalidate refresh token. Auth: Bearer token Rate limit: 100 req/min Request: None Response (204): No content GET /api/v1/auth/me Get current user info. Auth: Bearer token Rate limit: 100 req/min Response (200): interface CurrentUser { id: string email: string fullName: string role: 'owner' | 'admin' | 'accountant' | 'viewer' twoFactorEnabled: boolean lastLoginAt: string | null organization: { id: string name: string country: string baseCurrency: string language: string } } 2. Organization GET /api/v1/organization Get organization details. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): interface Organization { id: string name: string registrationNumber: string | null vatNumber: string | null baseCurrency: string country: string language: string fiscalYearStart: string // ISO date, e.g., "2026-01-01" createdAt: string updatedAt: string } PUT /api/v1/organization Update organization details. Auth: Bearer token Roles: owner, admin Rate limit: 10 req/min Request: interface UpdateOrganizationRequest { name?: string registrationNumber?: string vatNumber?: string baseCurrency?: 'EUR' | 'RSD' | 'BAM' | 'HRK' language?: 'sr' | 'bs' | 'hr' fiscalYearStart?: string // ISO date } Response (200): Organization object (same as GET) Errors: 422 — Validation failed (invalid currency code, etc.) 3. Users GET /api/v1/users List all users in organization. Auth: Bearer token Roles: owner, admin Rate limit: 100 req/min Query: role (filter by role) Response (200): interface UserListResponse { data: Array<{ id: string email: string fullName: string role: 'owner' | 'admin' | 'accountant' | 'viewer' twoFactorEnabled: boolean lastLoginAt: string | null createdAt: string }> } POST /api/v1/users/invite Invite new user to organization. Auth: Bearer token Roles: owner, admin Rate limit: 10 req/min Request: interface InviteUserRequest { email: string fullName: string role: 'admin' | 'accountant' | 'viewer' // Cannot create 'owner' } Response (201): interface InviteUserResponse { user: { id: string email: string fullName: string role: string } inviteLink: string // One-time setup link, expires in 7 days } Errors: 400 — Email already exists in organization 422 — Invalid role PUT /api/v1/users/:id/role Change user role. Auth: Bearer token Roles: owner Rate limit: 10 req/min Request: interface ChangeRoleRequest { role: 'admin' | 'accountant' | 'viewer' } Response (200): User object Errors: 403 — Cannot change owner role or demote yourself 404 — User not found DELETE /api/v1/users/:id Remove user from organization. Auth: Bearer token Roles: owner Rate limit: 10 req/min Response (204): No content Errors: 403 — Cannot delete owner or yourself 404 — User not found 4. Contacts GET /api/v1/contacts List contacts (customers/vendors). Auth: Bearer token Roles: All Rate limit: 100 req/min Query: type ( customer , vendor , both ) page , perPage , sort , order Response (200): type ContactListResponse = PaginatedResponse interface Contact { id: string type: 'customer' | 'vendor' | 'both' name: string email: string | null phone: string | null registrationNumber: string | null vatNumber: string | null addressLine1: string | null addressLine2: string | null city: string | null postalCode: string | null country: string | null currencyCode: string paymentTerms: number // Days isActive: boolean createdAt: string updatedAt: string } POST /api/v1/contacts Create new contact. Auth: Bearer token Roles: owner, admin, accountant Rate limit: 50 req/min Request: interface CreateContactRequest { type: 'customer' | 'vendor' | 'both' name: string email?: string phone?: string registrationNumber?: string vatNumber?: string addressLine1?: string addressLine2?: string city?: string postalCode?: string country?: string // ISO 3166-1 alpha-2 (e.g., 'RS') currencyCode?: string // ISO 4217 (default: org baseCurrency) paymentTerms?: number // Default: 30 days notes?: string } Response (201): Contact object Errors: 422 — Validation failed (invalid country code, currency code, etc.) GET /api/v1/contacts/:id Get contact details. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): Contact object + notes field PUT /api/v1/contacts/:id Update contact. Auth: Bearer token Roles: owner, admin, accountant Rate limit: 50 req/min Request: Same as CreateContactRequest (all fields optional) Response (200): Contact object DELETE /api/v1/contacts/:id Soft-delete contact (sets isActive = false). Auth: Bearer token Roles: owner, admin Rate limit: 10 req/min Response (204): No content Errors: 400 — Contact has active invoices or expenses 5. Invoices GET /api/v1/invoices List invoices. Auth: Bearer token Roles: All Rate limit: 100 req/min Query: status ( draft , sent , viewed , paid , overdue , cancelled ) customerId (UUID) fromDate , toDate (ISO dates) page , perPage , sort , order Response (200): type InvoiceListResponse = PaginatedResponse interface InvoiceSummary { id: string invoiceNumber: string customerId: string customerName: string invoiceDate: string dueDate: string currencyCode: string totalAmount: string // Decimal as string, e.g., "125000.0000" status: 'draft' | 'sent' | 'viewed' | 'paid' | 'overdue' | 'cancelled' createdAt: string } Invoice Creation — Full Sequence sequenceDiagram participant FE as Frontend participant MW as Middleware Stack\n(auth, roleGuard, validate) participant H as Invoice Handler participant DB as PostgreSQL\n(Prisma) participant EX as Exchange Rate\nService FE->>MW: POST /api/v1/invoices\nAuthorization: Bearer {accessToken} MW->>MW: authGuard: verify JWT\nAttach req.user {id, role, orgId} MW->>MW: roleGuard: check owner/admin/accountant MW->>MW: validate(createInvoiceSchema)\ncustomerId UUID, dates, items[] MW->>H: Validated request H->>DB: Find Contact by customerId\nwhere orgId matches DB-->>H: Contact { email, currencyCode } H->>EX: getExchangeRate(invoiceCurrency, orgBaseCurrency, invoiceDate) EX-->>H: rate (locked at invoiceDate — NEVER changes) H->>H: Calculate:\nlineTotal = qty × unitPrice\ntaxAmount = SUM(lineTotal × taxRate/100)\ntotalAmount = subtotal + taxAmount - discount\nbaseAmount = totalAmount × exchangeRate H->>DB: BEGIN TRANSACTION\nGenerate invoiceNumber INV-YYYY-NNN\nINSERT Invoice { status: draft }\nINSERT InvoiceItems[] DB-->>H: Invoice created H->>DB: INSERT LoggedAction\n{ action: INSERT, tableName: Invoice } DB-->>H: Logged H->>FE: 201 Created\n{ id, invoiceNumber, status: draft, items, totals } POST /api/v1/invoices Create invoice. Auth: Bearer token Roles: owner, admin, accountant Rate limit: 50 req/min Request: interface CreateInvoiceRequest { customerId: string invoiceDate: string // ISO date dueDate: string // ISO date currencyCode?: string // Default: customer's currency items: Array<{ description: string quantity: number // Decimal as number unitPrice: number // Decimal as number taxRate: number // Percentage, e.g., 20 for 20% accountId?: string // Revenue account }> notes?: string terms?: string } Response (201): interface Invoice { id: string invoiceNumber: string // Auto-generated customerId: string customerName: string invoiceDate: string dueDate: string currencyCode: string exchangeRate: string // Decimal as string subtotal: string taxAmount: string discountAmount: string totalAmount: string baseAmount: string // Converted to org baseCurrency status: 'draft' items: Array<{ id: string lineNumber: number description: string quantity: string unitPrice: string taxRate: string lineTotal: string accountId: string | null }> notes: string | null terms: string | null pdfUrl: string | null createdBy: string createdAt: string updatedAt: string } Errors: 404 — Customer not found 422 — Validation failed (invalid date, negative amount, etc.) GET /api/v1/invoices/:id Get invoice details. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): Invoice object (same as POST response) PUT /api/v1/invoices/:id Update invoice (draft only). Auth: Bearer token Roles: owner, admin, accountant Rate limit: 50 req/min Request: Same as CreateInvoiceRequest Response (200): Invoice object Errors: 400 — Invoice is not in draft status Invoice Status Transition — Send Flow sequenceDiagram participant FE as Frontend participant API as Bilko API participant PDF as Puppeteer\nPDF Service participant R2 as Cloudflare R2 participant SG as SendGrid participant DB as PostgreSQL FE->>API: PATCH /invoices/:id/status\n{ action: "send" } API->>DB: Fetch Invoice with items, customer, org DB-->>API: Invoice (must be status=draft) API->>PDF: generateInvoicePDF(invoice data) PDF-->>API: PDF Buffer API->>R2: PUT invoices/{orgId}/INV-2026-001.pdf R2-->>API: pdfUrl stored API->>DB: BEGIN TRANSACTION API->>DB: INSERT Transaction {\n DR: Accounts Receivable (1200)\n CR: Revenue (4000)\n amount: invoice.totalAmount\n referenceType: 'invoice'\n} API->>DB: UPDATE Invoice SET\n status='sent', sentAt=now()\n pdfUrl=url API->>SG: sendEmail({\n to: customer.email,\n subject: "Invoice INV-2026-001 from Org",\n html: template,\n attachment: pdf\n}) SG-->>API: { messageId } DB-->>API: COMMIT API->>FE: 200 { status: sent, sentAt, pdfUrl } Note over API: If SendGrid fails:\nKeep invoice as draft\nAdd note: "Email delivery failed"\nAlert admin via Slack PATCH /api/v1/invoices/:id/status Change invoice status. Auth: Bearer token Roles: owner, admin, accountant Rate limit: 50 req/min Request: interface ChangeInvoiceStatusRequest { action: 'send' | 'mark-paid' | 'cancel' paidAt?: string // Required if action = 'mark-paid' } Response (200): Invoice object Business logic: send : draft → sent (generates PDF, sends email via SendGrid) mark-paid : sent/viewed → paid (creates Transaction: debit BankAccount, credit AccountsReceivable) cancel : any → cancelled (reverses Transaction if paid) Errors: 400 — Invalid status transition GET /api/v1/invoices/:id/pdf Get invoice PDF. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): Content-Type: application/pdf Content-Disposition: attachment; filename="INV-2026-001.pdf" Errors: 404 — Invoice or PDF not found POST /api/v1/invoices/:id/send Send invoice email to customer. Auth: Bearer token Roles: owner, admin, accountant Rate limit: 10 req/min Request: interface SendInvoiceRequest { to?: string // Override customer email cc?: string[] subject?: string // Override default subject message?: string // Custom message } Response (200): interface SendInvoiceResponse { sentAt: string sentTo: string emailId: string // SendGrid message ID } Errors: 400 — Customer has no email 500 — SendGrid error 6. Expenses GET /api/v1/expenses List expenses. Auth: Bearer token Roles: All Rate limit: 100 req/min Query: status ( pending , approved , paid , rejected ) category vendorId fromDate , toDate page , perPage , sort , order Response (200): type ExpenseListResponse = PaginatedResponse interface ExpenseSummary { id: string expenseNumber: string vendorId: string | null vendorName: string | null expenseDate: string category: string amount: string currencyCode: string status: 'pending' | 'approved' | 'paid' | 'rejected' receiptUrl: string | null createdAt: string } POST /api/v1/expenses Create expense. Auth: Bearer token Roles: owner, admin, accountant Rate limit: 50 req/min Request: interface CreateExpenseRequest { vendorId?: string expenseDate: string category: string // Free text or predefined categories amount: number currencyCode?: string // Default: org baseCurrency taxAmount?: number paymentMethod?: string // 'cash', 'card', 'bank_transfer', etc. accountId?: string // Expense account description?: string receiptFile?: File // Multipart form upload (max 10MB) } Response (201): interface Expense { id: string expenseNumber: string // Auto-generated vendorId: string | null vendorName: string | null expenseDate: string category: string currencyCode: string exchangeRate: string amount: string baseAmount: string taxAmount: string paymentMethod: string | null accountId: string | null description: string | null receiptUrl: string | null // Cloudflare R2 URL status: 'pending' createdBy: string createdAt: string updatedAt: string } Errors: 422 — Validation failed (negative amount, invalid date, etc.) 413 — File too large GET /api/v1/expenses/:id Get expense details. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): Expense object PUT /api/v1/expenses/:id Update expense (pending only). Auth: Bearer token Roles: owner, admin, accountant Rate limit: 50 req/min Request: Same as CreateExpenseRequest Response (200): Expense object Errors: 400 — Expense is not pending Expense Approval — Full Sequence sequenceDiagram participant FE as Frontend\n(admin/owner) participant MW as Middleware Stack participant H as Expense Handler participant DB as PostgreSQL FE->>MW: PATCH /api/v1/expenses/:id/approve\nAuthorization: Bearer {accessToken} MW->>MW: authGuard: verify JWT MW->>MW: roleGuard: owner or admin ONLY\n(accountant CANNOT approve) MW->>H: Request passes H->>DB: Find Expense by id\nwhere organizationId = req.orgId DB-->>H: Expense { status: pending, amount, accountId } H->>H: Validate: status must be 'pending'\nIf not → 400 Bad Request H->>DB: BEGIN TRANSACTION H->>DB: Find ExpenseAccount\n(expense.accountId or default 5xxx) H->>DB: Find AccountsPayable account\n(2110 or configured account) H->>DB: INSERT Transaction {\n debitAccountId: expenseAccountId,\n creditAccountId: accountsPayableId,\n amount: expense.amount,\n referenceType: 'expense',\n referenceId: expense.id\n} H->>DB: UPDATE Expense SET\n status='approved',\n approvedBy=req.user.id,\n approvedAt=now() H->>DB: INSERT LoggedAction DB-->>H: COMMIT H->>FE: 200 OK\n{ id, status: approved, approvedBy, approvedAt } PATCH /api/v1/expenses/:id/approve Approve expense. Auth: Bearer token Roles: owner, admin Rate limit: 50 req/min Response (200): Expense object (status = approved) Business logic: Creates Transaction: debit ExpenseAccount, credit AccountsPayable Errors: 400 — Expense already approved/paid/rejected DELETE /api/v1/expenses/:id Delete expense (pending only). Auth: Bearer token Roles: owner, admin Rate limit: 10 req/min Response (204): No content Errors: 400 — Expense is not pending 7. Bank Accounts GET /api/v1/bank-accounts List bank accounts. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): interface BankAccountListResponse { data: Array<{ id: string accountId: string // GL account ID accountCode: string // GL account code bankName: string accountNumber: string | null iban: string | null currencyCode: string currentBalance: string isActive: boolean createdAt: string updatedAt: string }> } POST /api/v1/bank-accounts Create bank account. Auth: Bearer token Roles: owner, admin Rate limit: 10 req/min Request: interface CreateBankAccountRequest { accountId: string // Must be Asset account bankName: string accountNumber?: string iban?: string currencyCode: string currentBalance?: number // Default: 0 } Response (201): BankAccount object Errors: 404 — Account not found 422 — Account is not Asset type GET /api/v1/bank-accounts/:id/transactions Get bank transactions. Auth: Bearer token Roles: All Rate limit: 100 req/min Query: fromDate , toDate reconciled (true/false) page , perPage , sort , order Response (200): type BankTransactionListResponse = PaginatedResponse interface BankTransaction { id: string transactionDate: string amount: string // Positive = credit, negative = debit description: string | null reference: string | null reconciled: boolean matchedTransactionId: string | null createdAt: string } POST /api/v1/bank-accounts/:id/import Import bank statement (CSV). Auth: Bearer token Roles: owner, admin, accountant Rate limit: 10 req/min Request: Multipart form: file (CSV, max 5MB) CSV format: Date,Description,Amount,Reference 2026-02-19,"Payment from customer",3500.00,INV-2026-002 2026-02-18,"AWS Invoice",-850.00, Response (200): interface ImportStatementResponse { imported: number duplicates: number errors: Array<{ line: number error: string }> } Errors: 422 — Invalid CSV format 413 — File too large Bank Reconciliation — Full Sequence sequenceDiagram participant FE as Frontend participant API as Bilko API participant DB as PostgreSQL Note over FE,DB: Step 1 — Import Bank Statement FE->>API: POST /bank-accounts/:id/import\n[multipart: CSV file, max 5MB] API->>API: Parse CSV\nDate, Description, Amount, Reference API->>DB: INSERT BankTransaction[] records\n{ bankAccountId, transactionDate, amount, reference } DB-->>API: Imported count API->>FE: 200 { imported: 45, duplicates: 2, errors: [] } Note over FE,DB: Step 2 — Auto-Match Suggestions FE->>API: GET /bank-accounts/:id/transactions?reconciled=false API->>DB: Fetch unreconciled BankTransactions DB-->>API: BankTransaction[] API->>DB: Fetch unreconciled GL Transactions\nfor same date range DB-->>API: Transaction[] API->>API: calculateMatchScore() for each pair\nAmount match +50\nDate match +30/+20/+10\nReference match +20 API->>FE: 200 { bankTransactions, suggestions[{ bankTxId, glTxId, score }] } Note over FE,DB: Step 3 — Confirm Reconciliation FE->>API: POST /bank-accounts/:id/reconcile\n{ bankTransactionId, transactionId } API->>DB: Find both records, verify same org API->>DB: UPDATE BankTransaction SET\n reconciled=true\n matchedTransactionId=glTxId API->>DB: UPDATE Transaction SET\n reconciled=true\n reconciledAt=now() DB-->>API: Both updated API->>FE: 200 { bankTransaction, transaction, confidence: 95 } POST /api/v1/bank-accounts/:id/reconcile Reconcile bank transactions with GL transactions. Auth: Bearer token Roles: owner, admin, accountant Rate limit: 10 req/min Request: interface ReconcileRequest { bankTransactionId: string transactionId: string // GL transaction ID } Response (200): interface ReconcileResponse { bankTransaction: BankTransaction transaction: Transaction confidence: number // 0-100 match score } Errors: 404 — Bank transaction or GL transaction not found 400 — Already reconciled 8. Reports GET /api/v1/reports/dashboard Get dashboard metrics. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): interface DashboardMetrics { cashBalance: string // Total across all bank accounts (in baseCurrency) revenueMTD: string // Month-to-date revenue unpaidInvoices: string // Total unpaid invoices expensesMTD: string // Month-to-date expenses profitMTD: string // revenueMTD - expensesMTD cashFlowChange: number // Percentage change from last month // Chart data monthlyPL: Array<{ month: string revenue: string expenses: string profit: string }> receivablesAging: { current: string // 0-30 days days30: string // 31-60 days days60: string // 61-90 days days90plus: string // 90+ days } expensesByCategory: Array<{ category: string amount: string currencyCode: string }> } GET /api/v1/reports/profit-loss Profit & Loss statement. Auth: Bearer token Roles: All Rate limit: 50 req/min Query: from (ISO date, required) to (ISO date, required) Response (200): interface ProfitLossReport { period: { from: string to: string } baseCurrency: string revenue: { total: string accounts: Array<{ accountCode: string accountName: string amount: string }> } expenses: { total: string accounts: Array<{ accountCode: string accountName: string amount: string }> } netProfit: string // revenue.total - expenses.total } GET /api/v1/reports/balance-sheet Balance Sheet. Auth: Bearer token Roles: All Rate limit: 50 req/min Query: date (ISO date, default: today) Response (200): interface BalanceSheetReport { asOfDate: string baseCurrency: string assets: { total: string current: { total: string accounts: Array } fixed: { total: string accounts: Array } } liabilities: { total: string current: { total: string accounts: Array } longTerm: { total: string accounts: Array } } equity: { total: string accounts: Array } } interface AccountBalance { accountCode: string accountName: string balance: string } GET /api/v1/reports/cash-flow Cash Flow statement. Auth: Bearer token Roles: All Rate limit: 50 req/min Query: from , to (ISO dates, required) Response (200): interface CashFlowReport { period: { from: string to: string } baseCurrency: string operating: { total: string items: Array<{ description: string amount: string }> } investing: { total: string items: Array<{ description: string amount: string }> } financing: { total: string items: Array<{ description: string amount: string }> } netCashFlow: string openingBalance: string closingBalance: string } GET /api/v1/reports/vat VAT/PDV report. Auth: Bearer token Roles: All Rate limit: 50 req/min Query: from , to (ISO dates, required) Response (200): interface VATReport { period: { from: string to: string } country: string // Organization country outputVAT: { total: string invoices: Array<{ invoiceNumber: string customerName: string invoiceDate: string baseAmount: string vatAmount: string vatRate: string }> } inputVAT: { total: string expenses: Array<{ expenseNumber: string vendorName: string expenseDate: string baseAmount: string vatAmount: string vatRate: string }> } netVAT: string // outputVAT.total - inputVAT.total reconciliationStatus: { allInvoicesPaid: boolean allExpensesApproved: boolean unmatchedTransactions: number } } GET /api/v1/reports/trial-balance Trial Balance. Auth: Bearer token Roles: All Rate limit: 50 req/min Query: date (ISO date, default: today) Response (200): interface TrialBalanceReport { asOfDate: string baseCurrency: string accounts: Array<{ accountCode: string accountName: string accountType: string debitTotal: string creditTotal: string balance: string }> totals: { debit: string credit: string } balanced: boolean // totals.debit === totals.credit } 9. Chart of Accounts GET /api/v1/accounts List chart of accounts. Auth: Bearer token Roles: All Rate limit: 100 req/min Query: accountTypeId (filter by type) isActive (true/false) Response (200): interface AccountListResponse { data: Array<{ id: string code: string // e.g., "1000", "4000" name: string // e.g., "Bank Account EUR", "Revenue" accountTypeId: number accountTypeName: string // Asset, Liability, Equity, Revenue, Expense normalBalance: 'debit' | 'credit' currencyCode: string parentAccountId: string | null parentAccountCode: string | null isActive: boolean currentBalance: string // Calculated from transactions createdAt: string updatedAt: string }> } POST /api/v1/accounts Create account. Auth: Bearer token Roles: owner, admin Rate limit: 10 req/min Request: interface CreateAccountRequest { code: string // Must be unique within organization name: string accountTypeId: number // 1-5 (Asset, Liability, Equity, Revenue, Expense) currencyCode?: string // Default: org baseCurrency parentAccountId?: string // For sub-accounts } Response (201): Account object Errors: 400 — Code already exists 404 — Parent account not found 422 — Invalid account type PUT /api/v1/accounts/:id Update account. Auth: Bearer token Roles: owner, admin Rate limit: 10 req/min Request: interface UpdateAccountRequest { name?: string isActive?: boolean } Response (200): Account object Errors: 400 — Cannot deactivate account with transactions 10. Transactions GET /api/v1/transactions List general ledger transactions. Auth: Bearer token Roles: All Rate limit: 100 req/min Query: fromDate , toDate accountId (show transactions for specific account) referenceType ( invoice , expense , payment , manual ) page , perPage , sort , order Response (200): type TransactionListResponse = PaginatedResponse interface Transaction { id: string transactionDate: string description: string debitAccountId: string debitAccountCode: string debitAccountName: string creditAccountId: string creditAccountCode: string creditAccountName: string amount: string currencyCode: string exchangeRate: string baseAmount: string referenceType: string | null referenceId: string | null locked: boolean reconciled: boolean createdBy: string createdAt: string } POST /api/v1/transactions Create manual journal entry. Auth: Bearer token Roles: owner, admin, accountant Rate limit: 20 req/min Request: interface CreateTransactionRequest { transactionDate: string description: string debitAccountId: string creditAccountId: string amount: number currencyCode?: string // Default: org baseCurrency notes?: string } Response (201): Transaction object Errors: 404 — Account not found 422 — Validation failed (debit = credit account, negative amount, etc.) 11. Settings GET /api/v1/settings/tax-rates Get tax rate configuration. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): interface TaxRatesResponse { country: string defaultVATRate: number // e.g., 20 for Serbia, 17 for BiH rates: Array<{ name: string // "Standard", "Reduced", "Zero" rate: number description: string }> } PUT /api/v1/settings/tax-rates Update tax rate configuration. Auth: Bearer token Roles: owner, admin Rate limit: 10 req/min Request: interface UpdateTaxRatesRequest { defaultVATRate: number rates: Array<{ name: string rate: number description: string }> } Response (200): TaxRatesResponse 12. Currencies GET /api/v1/currencies List supported currencies. Auth: Bearer token Roles: All Rate limit: 100 req/min Response (200): interface CurrencyListResponse { data: Array<{ code: string // ISO 4217 name: string symbol: string | null decimalPlaces: number isActive: boolean }> } GET /api/v1/exchange-rates Get exchange rates. Auth: Bearer token Roles: All Rate limit: 100 req/min Query: base (currency code, required) target (currency code, required) date (ISO date, default: today) Response (200): interface ExchangeRateResponse { baseCurrency: string targetCurrency: string rate: string // Decimal as string effectiveDate: string source: string // "ECB", "fixer.io", "manual" lastUpdated: string } Errors: 404 — No rate found for date (return nearest available) Endpoint Summary Map graph TD subgraph AUTH [Authentication — No auth required] A1[POST /auth/register] A2[POST /auth/login] A3[POST /auth/refresh] A4[POST /auth/logout] A5[GET /auth/me] end subgraph ORG [Organization] O1[GET /organization] O2[PUT /organization] end subgraph USR [Users] U1[GET /users] U2[POST /users/invite] U3[PUT /users/:id/role] U4[DELETE /users/:id] end subgraph CON [Contacts] C1[GET /contacts] C2[POST /contacts] C3[GET /contacts/:id] C4[PUT /contacts/:id] C5[DELETE /contacts/:id] end subgraph INV [Invoices] I1[GET /invoices] I2[POST /invoices] I3[GET /invoices/:id] I4[PUT /invoices/:id] I5[PATCH /invoices/:id/status] I6[GET /invoices/:id/pdf] I7[POST /invoices/:id/send] end subgraph EXP [Expenses] E1[GET /expenses] E2[POST /expenses] E3[GET /expenses/:id] E4[PUT /expenses/:id] E5[PATCH /expenses/:id/approve] E6[DELETE /expenses/:id] end subgraph BANK [Bank Accounts] B1[GET /bank-accounts] B2[POST /bank-accounts] B3[GET /bank-accounts/:id/transactions] B4[POST /bank-accounts/:id/import] B5[POST /bank-accounts/:id/reconcile] end subgraph RPT [Reports] R1[GET /reports/dashboard] R2[GET /reports/profit-loss] R3[GET /reports/balance-sheet] R4[GET /reports/cash-flow] R5[GET /reports/vat] R6[GET /reports/trial-balance] end subgraph MISC [Other] M1[GET /accounts] M2[POST /accounts] M3[PUT /accounts/:id] M4[GET /transactions] M5[POST /transactions] M6[GET /settings/tax-rates] M7[PUT /settings/tax-rates] M8[GET /currencies] M9[GET /exchange-rates] end Implementation Notes Request Validation All requests validated with Zod schemas. Invalid requests return 422 with field-level errors. Database Transactions All write operations wrapped in database transactions. Rollback on error. Audit Logging All INSERT/UPDATE/DELETE captured in LoggedAction table via Prisma middleware. Rate Limiting General: 100 req/min per user Auth: 5 req/min per IP Write ops: 10-50 req/min per user File Uploads Max size: 10MB (receipts), 5MB (CSV) Allowed: PDF, PNG, JPG, CSV Storage: Cloudflare R2 Virus scanning: ClamAV CORS Allowed origins: https://bilko.io , http://localhost:3000 Credentials: true (cookies) Error Logging Sentry for production errors Winston for structured logs Example Requests Create Invoice curl -X POST http://localhost:4000/api/v1/invoices \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{ "customerId": "550e8400-e29b-41d4-a716-446655440000", "invoiceDate": "2026-02-20", "dueDate": "2026-03-20", "items": [ { "description": "Web Development", "quantity": 40, "unitPrice": 100, "taxRate": 20 } ] }' Get Dashboard Metrics curl http://localhost:4000/api/v1/reports/dashboard \ -H "Authorization: Bearer $TOKEN" End of API Reference 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 Authentication & Authorization Bilko Authentication & Authorization Status: SPECIFICATION (backend not implemented) Last updated: 2026-02-20 Purpose This document specifies the authentication and authorization system for Bilko's backend. Covers JWT tokens, password hashing, 2FA, role-based access control (RBAC), and session management. Authentication Flow System Overview graph LR CLIENT[Frontend\nbilko.io] subgraph AUTH [Auth Layer] LOGIN[POST /auth/login] REGISTER[POST /auth/register] REFRESH[POST /auth/refresh] LOGOUT[POST /auth/logout] end subgraph TOKENS [Token Storage] AT[Access Token\nBearer header\n15 min TTL] RT[Refresh Token\nhttpOnly Cookie\n7-30 days TTL] BL[Blacklist\nRevoked JTIs] end subgraph GUARDS [Middleware Guards] AG[authGuard\nVerify JWT] RG[roleGuard\nCheck role] RL[rateLimiter\n5 req/min auth] end CLIENT --> LOGIN CLIENT --> REGISTER CLIENT --> REFRESH CLIENT --> LOGOUT LOGIN --> AT LOGIN --> RT REGISTER --> AT REGISTER --> RT REFRESH --> AT LOGOUT --> BL AT --> AG AG --> RG RG --> HANDLER[Route Handler] style AT fill:#00E5A0,color:#000 style RT fill:#ffd700,color:#000 style BL fill:#f87171,color:#fff 1. Registration Endpoint: POST /api/v1/auth/register sequenceDiagram participant C as Client participant API as Express API participant DB as PostgreSQL participant JWT as JWT Service C->>API: POST /auth/register\n{email, password, orgName, country} API->>API: Validate Zod schema\nCheck password strength API->>DB: Check email uniqueness DB-->>API: Email available API->>API: bcrypt.hash(password, 12) API->>DB: BEGIN TRANSACTION\nCreate Organization\nCreate User (role=owner)\nCreate default Chart of Accounts DB-->>API: Organization + User created API->>JWT: Generate access token (15min)\nGenerate refresh token (7days) JWT-->>API: { accessToken, refreshToken } API->>C: 201 Created\n{ user, organization, tokens }\nSet-Cookie: refreshToken (httpOnly) Steps: Validate request body (email uniqueness, password strength, country/currency codes) Hash password with bcrypt (12 rounds) Create database transaction: Create Organization Create User (role = 'owner') Create default Chart of Accounts (seed accounts based on country) Generate JWT access token (15 min expiry) Generate refresh token (7 days expiry) Set refresh token in httpOnly cookie Return user + organization + tokens Password Requirements: Minimum 8 characters At least 1 uppercase letter At least 1 lowercase letter At least 1 number Optional: 1 special character Password Hashing: import bcrypt from 'bcrypt' const SALT_ROUNDS = 12 async function hashPassword(password: string): Promise { return bcrypt.hash(password, SALT_ROUNDS) } async function verifyPassword(password: string, hash: string): Promise { return bcrypt.compare(password, hash) } Errors: 400 Bad Request — Email already exists 422 Unprocessable Entity — Weak password, invalid country code 2. Login Endpoint: POST /api/v1/auth/login sequenceDiagram participant C as Client participant RL as Rate Limiter\n5 req/min/IP participant API as Express API participant DB as PostgreSQL participant JWT as JWT Service C->>RL: POST /auth/login\n{email, password} RL-->>C: 429 Too Many Requests (if exceeded) RL->>API: Pass through API->>DB: Find user by email DB-->>API: User record API->>API: bcrypt.compare(password, hash) alt Invalid credentials API-->>C: 401 Unauthorized else 2FA required API-->>C: 403 { requiresTwoFactor: true } C->>API: POST /auth/verify-2fa { code } API->>API: speakeasy.totp.verify() end API->>DB: UPDATE users SET lastLoginAt = now() API->>JWT: Generate access token (15min)\nGenerate refresh token\n(7d or 30d if rememberMe) JWT-->>API: Tokens API->>C: 200 OK { user, tokens }\nSet-Cookie: refreshToken (httpOnly, Secure, SameSite=Strict) Steps: Find user by email Verify password with bcrypt.compare() If 2FA enabled, send TOTP challenge (not covered in MVP) Update user.lastLoginAt Generate JWT access token (15 min expiry) Generate refresh token (7 days or 30 days if rememberMe = true) Set refresh token in httpOnly cookie Return user + tokens Rate Limiting: Max 5 login attempts per 1 minute per IP address After 5 failed attempts, return 429 Too Many Requests Lockout duration: 15 minutes Errors: 401 Unauthorized — Invalid email or password 403 Forbidden — Account disabled or requires 2FA 429 Too Many Requests — Rate limit exceeded 3. Token Refresh Endpoint: POST /api/v1/auth/refresh sequenceDiagram participant C as Client participant API as Express API participant BL as Blacklist\n(Redis/PostgreSQL) participant JWT as JWT Service C->>API: POST /auth/refresh\n[Cookie: refreshToken] API->>API: Extract JWT from httpOnly cookie API->>JWT: Verify signature & expiry JWT-->>API: RefreshTokenPayload { sub, jti, exp } API->>BL: Check if jti is blacklisted BL-->>API: Not blacklisted API->>JWT: Generate new access token (15min) JWT-->>API: New accessToken API->>C: 200 OK { accessToken } note over C,API: Access token expires every 15min\nClient must silently refresh via cookie Steps: Extract refresh token from httpOnly cookie Verify refresh token signature Check if token is blacklisted (revoked) Check expiry Generate new access token (15 min expiry) Return new access token Refresh Token Storage: Stored in httpOnly cookie (prevents XSS attacks) Secure flag = true (HTTPS only) SameSite = Strict (prevents CSRF attacks) Path = /api/v1/auth/refresh Token Revocation: On logout, add refresh token to blacklist (Redis or PostgreSQL) Blacklist stores token JTI (JWT ID) + expiry Expired blacklist entries auto-deleted after 30 days Errors: 401 Unauthorized — Invalid or expired refresh token 4. Logout Endpoint: POST /api/v1/auth/logout Steps: Extract refresh token from cookie Add token JTI to blacklist Clear httpOnly cookie Return 204 No Content JWT Tokens Access Token Purpose: Short-lived token for API authentication. Claims: interface AccessTokenPayload { sub: string // User ID (UUID) email: string // User email role: UserRole // owner, admin, accountant, viewer orgId: string // Organization ID (UUID) iat: number // Issued at (Unix timestamp) exp: number // Expires at (Unix timestamp, iat + 15 min) } Expiry: 15 minutes Header: { "alg": "HS256", "typ": "JWT" } Usage: Sent in Authorization: Bearer header Verified on every API request via authGuard middleware If expired, client requests new token via /auth/refresh Refresh Token Purpose: Long-lived token for obtaining new access tokens. Claims: interface RefreshTokenPayload { sub: string // User ID (UUID) jti: string // JWT ID (for revocation) iat: number // Issued at (Unix timestamp) exp: number // Expires at (Unix timestamp, iat + 7 days or 30 days) } Expiry: 7 days (default) or 30 days (if rememberMe = true) Storage: httpOnly cookie (client cannot access via JavaScript) Secure = true (HTTPS only in production) SameSite = Strict (prevents CSRF) Revocation: On logout, JTI added to blacklist On password change, all refresh tokens revoked On user deletion, all refresh tokens revoked JWT Secret Management CRITICAL: JWT secret MUST be stored securely. Environment Variables: # .env JWT_SECRET=<256-bit random string, minimum 32 chars> JWT_REFRESH_SECRET= Generation: # Generate secure random secret openssl rand -base64 32 Best Practices: Use different secrets for access and refresh tokens Rotate secrets every 90 days (requires re-login for all users) Store in environment variables, NEVER in code Use Vaultwarden or similar secret manager in production Two-Factor Authentication (2FA) Status: OPTIONAL in MVP, implement in v2 sequenceDiagram participant U as User participant APP as Frontend participant API as Backend participant DB as PostgreSQL Note over U,DB: 2FA Setup Flow U->>APP: Enable 2FA in settings APP->>API: POST /settings/2fa/enable API->>API: Generate 32-char base32 TOTP secret API->>APP: { secret, qrCodeUrl } APP->>U: Display QR code U->>U: Scan with Google Authenticator / Authy U->>APP: Enter 6-digit code to verify APP->>API: POST /settings/2fa/verify { code } API->>API: speakeasy.totp.verify(secret, code) API->>DB: UPDATE users SET twoFactorEnabled=true\ntwoFactorSecret=encrypted API->>APP: 2FA activated Note over U,DB: Login with 2FA U->>APP: Enter email + password APP->>API: POST /auth/login API->>DB: Find user, verify password API->>APP: 403 { requiresTwoFactor: true } APP->>U: Prompt for 6-digit code U->>APP: Enter TOTP code APP->>API: POST /auth/verify-2fa { code } API->>API: Verify TOTP (30-second window ±1 step) API->>APP: 200 OK { user, tokens } Flow: User enables 2FA in settings Generate TOTP secret (32-char base32 string) Display QR code (Google Authenticator, Authy compatible) User scans QR code User enters 6-digit code to verify Store twoFactorSecret (encrypted) in users table Set twoFactorEnabled = true Login with 2FA: User enters email + password If twoFactorEnabled = true , return 403 with requiresTwoFactor: true Frontend prompts for 6-digit code User submits code via POST /api/v1/auth/verify-2fa Verify TOTP code (30-second window) If valid, issue tokens TOTP Verification: import speakeasy from 'speakeasy' function verifyTOTP(secret: string, token: string): boolean { return speakeasy.totp.verify({ secret, encoding: 'base32', token, window: 1 // Allow 1 time step before/after (30s window) }) } Role-Based Access Control (RBAC) RBAC Model graph TD subgraph ROLES [User Roles — Hierarchy] OW[owner\nFull control] AD[admin\nManage users + all financials] AC[accountant\nCreate financials only] VW[viewer\nRead-only] end subgraph ACTIONS [Protected Actions] direction LR INV_C[Create Invoice] INV_S[Send Invoice] INV_P[Mark Invoice Paid] EXP_C[Create Expense] EXP_AP[Approve Expense] TXN[Create Transaction] USR_I[Invite User] USR_R[Change User Role] USR_D[Delete User] ORG_S[Org Settings] ORG_D[Delete Organization] RPT[View Reports] end OW --> INV_C & INV_S & INV_P OW --> EXP_C & EXP_AP OW --> TXN OW --> USR_I & USR_R & USR_D OW --> ORG_S & ORG_D OW --> RPT AD --> INV_C & INV_S & INV_P AD --> EXP_C & EXP_AP AD --> TXN AD --> USR_I AD --> ORG_S AD --> RPT AC --> INV_C & INV_S & INV_P AC --> EXP_C AC --> TXN AC --> RPT VW --> RPT style OW fill:#00E5A0,color:#000 style AD fill:#60a5fa,color:#000 style AC fill:#fbbf24,color:#000 style VW fill:#94a3b8,color:#000 Roles Role Permissions owner Full access: manage users, delete organization, change settings, all financial operations admin Manage users (except owner), change settings, all financial operations accountant Create/edit invoices, expenses, transactions. View reports. Cannot manage users or settings. viewer Read-only access to all financial data. Cannot create or edit. Permission Matrix Action owner admin accountant viewer Create invoice ✅ ✅ ✅ ❌ Edit invoice (draft) ✅ ✅ ✅ ❌ Send invoice ✅ ✅ ✅ ❌ Mark invoice paid ✅ ✅ ✅ ❌ Create expense ✅ ✅ ✅ ❌ Approve expense ✅ ✅ ❌ ❌ Create manual transaction ✅ ✅ ✅ ❌ View reports ✅ ✅ ✅ ✅ Invite user ✅ ✅ ❌ ❌ Change user role ✅ ❌ ❌ ❌ Delete user ✅ ❌ ❌ ❌ Update org settings ✅ ✅ ❌ ❌ Delete organization ✅ ❌ ❌ ❌ Middleware Implementation Role Guard: import { Request, Response, NextFunction } from 'express' type UserRole = 'owner' | 'admin' | 'accountant' | 'viewer' function roleGuard(allowedRoles: UserRole[]) { return (req: Request, res: Response, next: NextFunction) => { const user = req.user // Attached by authGuard middleware if (!user) { return res.status(401).json({ error: 'Unauthorized', code: 'NO_AUTH' }) } if (!allowedRoles.includes(user.role)) { return res.status(403).json({ error: 'Forbidden', code: 'INSUFFICIENT_PERMISSIONS', details: { required: allowedRoles, current: user.role } }) } next() } } // Usage in routes app.post('/api/v1/invoices', authGuard, roleGuard(['owner', 'admin', 'accountant']), createInvoice ) Session Management Session Storage Option 1: JWT-only (stateless, recommended for MVP): No server-side session storage All state in JWT claims Fast, scales horizontally Cannot revoke access tokens (must wait for expiry) Option 2: Redis sessions (for v2): Store session data in Redis JWT contains only session ID Can revoke immediately Requires Redis infrastructure Recommended for MVP: JWT-only (stateless) Session Invalidation On password change: Hash new password Update users.passwordHash Delete all refresh tokens from blacklist older than 1 hour (force re-login) Return success On account deletion: Soft-delete user (set isActive = false ) Add all user's refresh tokens to blacklist Revoke access immediately Security Best Practices 1. Password Storage NEVER store plain text passwords Use bcrypt with 12 rounds (2^12 iterations) Bcrypt auto-salts (no need to store salt separately) 2. Token Security Access tokens in Authorization header (NOT cookies to avoid CSRF) Refresh tokens in httpOnly cookies (prevent XSS) Use Secure flag (HTTPS only) Use SameSite=Strict (prevent CSRF) 3. Rate Limiting Login: 5 attempts per minute per IP Register: 5 attempts per minute per IP Refresh: 100 attempts per minute per user All other endpoints: 100 requests per minute per user 4. HTTPS Only All traffic over HTTPS in production Redirect HTTP → HTTPS HSTS header: Strict-Transport-Security: max-age=31536000; includeSubDomains 5. CORS Configuration const corsOptions = { origin: ['https://bilko.io', 'http://localhost:3000'], credentials: true, // Allow cookies methods: ['GET', 'POST', 'PUT', 'PATCH', 'DELETE'], allowedHeaders: ['Content-Type', 'Authorization'] } app.use(cors(corsOptions)) 6. Input Validation Validate all inputs with Zod schemas Sanitize SQL inputs (Prisma prevents SQL injection) Escape HTML in user-generated content Example Implementation Auth Middleware import jwt from 'jsonwebtoken' import { Request, Response, NextFunction } from 'express' interface AuthRequest extends Request { user?: { id: string email: string role: UserRole organizationId: string } } async function authGuard(req: AuthRequest, res: Response, next: NextFunction) { const authHeader = req.headers.authorization if (!authHeader || !authHeader.startsWith('Bearer ')) { return res.status(401).json({ error: 'Unauthorized', code: 'NO_TOKEN' }) } const token = authHeader.substring(7) // Remove 'Bearer ' try { const payload = jwt.verify(token, process.env.JWT_SECRET!) as AccessTokenPayload // Attach user to request req.user = { id: payload.sub, email: payload.email, role: payload.role, organizationId: payload.orgId } next() } catch (error) { if (error.name === 'TokenExpiredError') { return res.status(401).json({ error: 'Token expired', code: 'TOKEN_EXPIRED' }) } return res.status(401).json({ error: 'Invalid token', code: 'INVALID_TOKEN' }) } } export { authGuard, roleGuard } Environment Variables # JWT JWT_SECRET=<256-bit secret for access tokens> JWT_REFRESH_SECRET=<256-bit secret for refresh tokens> JWT_ACCESS_EXPIRY=15m JWT_REFRESH_EXPIRY=7d # Rate Limiting RATE_LIMIT_AUTH=5 # Max login attempts per minute RATE_LIMIT_GENERAL=100 # Max requests per minute # Session SESSION_COOKIE_SECURE=true # HTTPS only (production) SESSION_COOKIE_SAMESITE=strict End of Authentication Documentation Business Logic Bilko Business Logic Status: SPECIFICATION (backend not implemented) Last updated: 2026-02-20 Purpose This document defines the accounting domain rules that Bilko's backend MUST enforce. These are non-negotiable business requirements for financial accuracy and compliance. Table of Contents Double-Entry Bookkeeping Invoice Workflow Expense Workflow VAT Calculation Multi-Currency Bank Reconciliation Chart of Accounts Fiscal Year Audit Trail 1. Double-Entry Bookkeeping Core Principle EVERY financial event creates a Transaction with exactly one debit and one credit. The fundamental equation: DEBITS = CREDITS Double-Entry Flow flowchart TD EVENT[Financial Event\ne.g. Invoice sent, Expense approved, Payment received] EVENT --> TXN[Create Transaction\ndebitAccountId + creditAccountId + amount] TXN --> CHK{Validate:\ndebit ≠ credit\namount > 0} CHK -->|FAIL| ERR[422 Validation Error] CHK -->|PASS| DEBIT[Debit Account\nIncrease if Asset/Expense\nDecrease if Liability/Equity/Revenue] DEBIT --> CREDIT[Credit Account\nIncrease if Liability/Equity/Revenue\nDecrease if Asset/Expense] CREDIT --> BAL{Trial Balance\nSum Debits = Sum Credits?} BAL -->|Balanced| LOCK[Lock Transaction\nappend to GL] BAL -->|Unbalanced| ALERT[System Alert\nCritical Error] style EVENT fill:#00E5A0,color:#000 style ERR fill:#f87171,color:#fff style ALERT fill:#f87171,color:#fff style LOCK fill:#60a5fa,color:#000 Common Transaction Patterns flowchart LR subgraph INV_SENT [Invoice Sent] IS_D[Debit: 1200 Accounts Receivable\nAsset ↑] IS_C[Credit: 4000 Revenue\nRevenue ↑] IS_D -. "amount" .- IS_C end subgraph INV_PAID [Invoice Paid] IP_D[Debit: 1000 Bank Account\nAsset ↑] IP_C[Credit: 1200 Accounts Receivable\nAsset ↓] IP_D -. "amount" .- IP_C end subgraph EXP_APR [Expense Approved] EA_D[Debit: 5100 Expense Account\nExpense ↑] EA_C[Credit: 2000 Accounts Payable\nLiability ↑] EA_D -. "amount" .- EA_C end subgraph EXP_PAID [Expense Paid] EP_D[Debit: 2000 Accounts Payable\nLiability ↓] EP_C[Credit: 1000 Bank Account\nAsset ↓] EP_D -. "amount" .- EP_C end style IS_D fill:#4ade80,color:#000 style IP_D fill:#4ade80,color:#000 style EA_D fill:#fb923c,color:#000 style EP_D fill:#4ade80,color:#000 Account types and normal balances: Account Type Normal Balance Increases with Decreases with Asset Debit Debit Credit Liability Credit Credit Debit Equity Credit Credit Debit Revenue Credit Credit Debit Expense Debit Debit Credit Transaction Rules Debit Account ≠ Credit Account A transaction cannot debit and credit the same account Enforced at API validation layer Amount > 0 Transaction amount must be positive Sign is determined by debit/credit, not amount Balanced Entries Debit amount = Credit amount No split transactions in MVP (one debit, one credit only) Locked Transactions Once transaction.locked = true , cannot be edited or deleted Locked at end-of-period close or when reconciled Common Transaction Patterns 1. Invoice Created (draft → sent) Debit: 1200 - Accounts Receivable (Asset) +125,000 RSD Credit: 4000 - Revenue (Revenue) +125,000 RSD Effect: Increases asset (money owed to us), increases revenue. 2. Invoice Paid Debit: 1000 - Bank Account (Asset) +125,000 RSD Credit: 1200 - Accounts Receivable (Asset) -125,000 RSD Effect: Increases cash, decreases receivables (converted to cash). 3. Expense Approved Debit: 5100 - Infrastructure Expense (Expense) +850 EUR Credit: 2000 - Accounts Payable (Liability) +850 EUR Effect: Increases expense, increases liability (we owe money). 4. Expense Paid Debit: 2000 - Accounts Payable (Liability) -850 EUR Credit: 1000 - Bank Account (Asset) -850 EUR Effect: Decreases liability, decreases cash. Balance Calculation Account balance = Sum(debits) - Sum(credits) for debit-normal accounts (Asset, Expense) Account balance = Sum(credits) - Sum(debits) for credit-normal accounts (Liability, Equity, Revenue) Trial Balance: Sum of all debit balances = Sum of all credit balances If unbalanced, there is an error in the ledger 2. Invoice Workflow Status Transitions draft → sent → viewed → paid ↓ ↓ ↓ └─────→ cancelled stateDiagram-v2 [*] --> draft : POST /invoices\n(auto-number: INV-YYYY-NNN) draft --> sent : PATCH /invoices/:id/status\naction=send\n[generates PDF → R2]\n[sends email via SendGrid]\n[creates Transaction:\nDR Receivable / CR Revenue] sent --> viewed : Email tracking pixel loaded\n[updates invoice.viewedAt] viewed --> paid : PATCH status action=mark-paid\n[creates Transaction:\nDR Bank / CR Receivable] sent --> paid : PATCH status action=mark-paid\n[creates Transaction:\nDR Bank / CR Receivable] draft --> cancelled : PATCH status action=cancel sent --> cancelled : PATCH status action=cancel\n[reverses Transaction] viewed --> cancelled : PATCH status action=cancel\n[reverses Transaction] paid --> [*] cancelled --> [*] note right of draft Editable: items, dates, amounts Invoice number locked on first save end note note right of sent LOCKED — cannot edit amounts PDF stored in Cloudflare R2 exchangeRate locked at invoiceDate end note note right of paid 2 GL Transactions created total: 1. draft→sent: DR Receivable / CR Revenue 2. paid: DR Bank / CR Receivable end note Invoice Calculation Flow flowchart TD ITEMS[Invoice Items\nquantity × unitPrice = lineTotal] ITEMS --> SUB[subtotal = SUM all lineTotals] SUB --> TAX[taxAmount = SUM lineTotal × taxRate/100] TAX --> DISC[Apply discountAmount] DISC --> TOTAL[totalAmount = subtotal + taxAmount - discountAmount] TOTAL --> BASE[baseAmount = totalAmount × exchangeRate\nexchangeRate locked at invoiceDate] BASE --> LOCK[Store — NEVER recalculate\nfrom future exchange rates] style LOCK fill:#f87171,color:#fff style BASE fill:#ffd700,color:#000 Status rules: From To Action Transaction Created? draft sent Send email Yes (Debit Receivable, Credit Revenue) sent viewed Email opened No viewed paid Mark paid Yes (Debit Bank, Credit Receivable) sent paid Mark paid Yes (Debit Bank, Credit Receivable) any cancelled Cancel Reverses original transaction Business Rules Rule 1: Invoice Number Auto-Generation Format: INV-YYYY-NNN (e.g., INV-2026-001 ) Generated on first save (when status changes from null → draft) Sequential within organization per year NEVER reuse cancelled invoice numbers Rule 2: Draft-Only Editing Can only edit invoice if status = 'draft' Once sent, cannot change line items or amounts Can still update notes/terms Rule 3: Overdue Detection Invoice becomes overdue if dueDate < today AND status != 'paid' Checked automatically via scheduled job (daily at 00:00 UTC) Rule 4: Subtotal Calculation subtotal = SUM(lineTotal) for all invoice items lineTotal = quantity * unitPrice Rule 5: Tax Calculation taxAmount = SUM(lineTotal * (taxRate / 100)) for all items Rule 6: Total Calculation totalAmount = subtotal + taxAmount - discountAmount Rule 7: Base Amount Conversion baseAmount = totalAmount * exchangeRate exchangeRate locked at invoiceDate NEVER recalculated Rule 8: PDF Generation PDF generated when status → sent Stored in Cloudflare R2 URL saved to invoice.pdfUrl PDF includes: org branding, line items, tax breakdown, payment terms Rule 9: Email Delivery Sent to contact.email Subject: "Invoice [invoiceNumber] from [organizationName]" Attachment: PDF Tracking pixel for viewedAt timestamp 3. Expense Workflow Status Transitions pending → approved → paid ↓ rejected stateDiagram-v2 [*] --> pending : POST /expenses\n(auto-number: EXP-YYYY-NNN)\ncreatedBy: accountant/admin/owner pending --> approved : PATCH /expenses/:id/approve\nRoles: owner, admin ONLY\n[creates Transaction:\nDR Expense / CR Accounts Payable] pending --> rejected : PATCH /expenses/:id/reject\nRoles: owner, admin ONLY\n[no Transaction created] approved --> paid : PATCH /expenses/:id/pay\n[creates Transaction:\nDR Accounts Payable / CR Bank] paid --> [*] rejected --> [*] note right of pending Can be edited before approval Receipt upload optional (max 10MB) PDF/PNG/JPG formats end note note right of approved Cannot edit after approval Stored in Cloudflare R2 receipts/ exchangeRate locked at expenseDate end note Status rules: From To Action Transaction Created? pending approved Approve Yes (Debit Expense, Credit Payable) pending rejected Reject No approved paid Mark paid Yes (Debit Payable, Credit Bank) Business Rules Rule 1: Expense Number Auto-Generation Format: EXP-YYYY-NNN (e.g., EXP-2026-001 ) Generated on creation Sequential within organization per year Rule 2: Approval Required Expenses created with status = 'pending' Only owner or admin can approve accountant can create but cannot approve Once approved, cannot be edited Rule 3: Receipt Upload Optional but recommended Max file size: 10MB Allowed formats: PDF, PNG, JPG Stored in Cloudflare R2 URL saved to expense.receiptUrl Rule 4: Category Tracking Free-text category field Common categories suggested: Infrastructure, Software, Office, Travel, Marketing, Utilities Used for expense reports by category Rule 5: Tax Amount Optional taxAmount field If provided, represents input VAT (can be deducted from output VAT) Used in VAT report Rule 6: Base Amount Conversion baseAmount = amount * exchangeRate exchangeRate locked at expenseDate 4. VAT Calculation VAT Calculation Flow flowchart TD subgraph OUTPUT [Output VAT — Sales] INV[Invoice sent to customer] INV --> OLINE[For each line item:\nlineTotal = qty × unitPrice\nlineTaxAmount = lineTotal × taxRate/100] OLINE --> OTOT[Invoice taxAmount = SUM all lineTaxAmounts] OTOT --> OREC[Recorded as Output VAT\nin VAT Report] end subgraph INPUT [Input VAT — Purchases] EXP[Expense from vendor] EXP --> ETAX[expense.taxAmount field\nUser-entered or calculated] ETAX --> IREC[Recorded as Input VAT\nin VAT Report] end subgraph NET [Net VAT Calculation] OREC --> CALC[netVAT = outputVAT - inputVAT] IREC --> CALC CALC --> POS{netVAT > 0?} POS -->|Yes| OWE[Owe to tax authority\nFile PDV/VAT return] POS -->|No| REF[Tax authority owes refund\nRare for SMBs] end style OWE fill:#f87171,color:#fff style REF fill:#4ade80,color:#000 VAT Rates by Country Country Standard VAT Reduced VAT Zero VAT Serbia (RS) 20% 10% 0% BiH (BA) 17% - 0% Croatia (HR) 25% 13% 0% Business Rules Rule 1: Tax Rate Application Invoice items have taxRate field (percentage) Default to organization's country standard rate User can override per line item Rule 2: Tax Amount Calculation For each invoice item: lineTotal = quantity * unitPrice lineTaxAmount = lineTotal * (taxRate / 100) For invoice: subtotal = SUM(lineTotal) taxAmount = SUM(lineTaxAmount) totalAmount = subtotal + taxAmount - discountAmount Rule 3: Output VAT (Sales) VAT collected on invoices sent to customers Recorded when invoice status → sent Included in VAT report as "Output VAT" Rule 4: Input VAT (Purchases) VAT paid on expenses from vendors Recorded from expense.taxAmount field Included in VAT report as "Input VAT" Rule 5: Net VAT Calculation netVAT = outputVAT - inputVAT If positive: owe VAT to tax authority If negative: tax authority owes refund (rare for small businesses) VAT Report Structure interface VATReport { period: { from: string, to: string } outputVAT: { total: Decimal // Total VAT collected invoices: Array<{ invoiceNumber: string customerName: string invoiceDate: string baseAmount: Decimal // Subtotal vatAmount: Decimal // Tax amount vatRate: Decimal // Tax rate % }> } inputVAT: { total: Decimal // Total VAT paid expenses: Array<{ expenseNumber: string vendorName: string expenseDate: string baseAmount: Decimal vatAmount: Decimal vatRate: Decimal }> } netVAT: Decimal // outputVAT - inputVAT reconciliationStatus: { allInvoicesPaid: boolean // All invoices in period are paid allExpensesApproved: boolean // All expenses in period are approved unmatchedTransactions: number // Unreconciled bank transactions } } 5. Multi-Currency Supported Currencies MVP: EUR (Euro) — default RSD (Serbian Dinar) BAM (Bosnian Mark) HRK (Croatian Kuna) USD (US Dollar) Exchange Rate Locking CRITICAL RULE: Exchange rates are locked at transaction date. Why: Financial reports must be consistent over time Cannot recalculate historical transactions with current rates Accounting standards require rate at transaction date How it works: Invoice created on 2026-02-20: currencyCode = 'RSD' exchangeRate = 117.50 (EUR to RSD rate on 2026-02-20) totalAmount = 125,000 RSD baseAmount = 125,000 / 117.50 = 1,063.83 EUR (locked) Today (2026-03-15), rate is now 120.00: Invoice baseAmount stays 1,063.83 EUR NEVER recalculated to 125,000 / 120.00 = 1,041.67 EUR Exchange Rate Sources Primary: European Central Bank (ECB) API Free Daily updates Reliable Fallback: fixer.io API Freemium (1000 requests/month free) Real-time rates Manual Entry: If API unavailable, user can enter rate manually Stored with source = 'manual' Base Currency Conversion All reports displayed in organization's baseCurrency . Example: Organization baseCurrency = EUR Invoice 1: 125,000 RSD → 1,063.83 EUR (rate 117.50) Invoice 2: 3,500 EUR → 3,500 EUR (rate 1.0) Expense 1: 850 USD → 794.39 EUR (rate 1.07) Total Revenue: 1,063.83 + 3,500 = 4,563.83 EUR 6. Bank Reconciliation Purpose Match bank transactions (from statements) to general ledger transactions (from invoices/expenses). flowchart TD CSV[Bank Statement CSV\nDate, Description, Amount, Reference] CSV --> PARSE[Parse & validate CSV\nCreate BankTransaction records] PARSE --> LINK[Link to BankAccount] LINK --> MATCH[Auto-Match Algorithm\nScore 0-100] subgraph SCORE [Match Score Calculation] S1[+50 pts: Exact amount match] S2[+30 pts: Same date\n+20 pts: ±1 day\n+10 pts: ±3 days] S3[+20 pts: Reference contains\ninvoice/expense number] end MATCH --> SCORE SCORE --> THRESH{Score?} THRESH -->|≥ 90| AUTO[Auto-match\nreconciled = true] THRESH -->|70-89| SUGGEST[Suggest to user\nUser confirms] THRESH -->|< 70| MANUAL[Manual review\nUser links manually] SUGGEST --> CONFIRM{User\nconfirms?} CONFIRM -->|Yes| RECONCILE[Set reconciled = true\nmatchedTransactionId = glTxId] CONFIRM -->|No| MANUAL MANUAL --> RECONCILE AUTO --> RECONCILE RECONCILE --> REPORT[Reconciliation Report\nbalanceDiscrepancy should = 0] style AUTO fill:#4ade80,color:#000 style MANUAL fill:#fb923c,color:#000 style REPORT fill:#60a5fa,color:#000 Process Import bank statement (CSV): Parse CSV file Create BankTransaction records Link to BankAccount Auto-match transactions: Match by amount + date (within ±3 days) Match by reference (invoice number in description) Calculate confidence score (0-100) Manual reconciliation: User links BankTransaction to Transaction Set bankTransaction.reconciled = true Set bankTransaction.matchedTransactionId = transaction.id Unmatched transactions: Flag in reconciliation report User must create manual journal entry or mark as miscellaneous Matching Algorithm Score calculation: function calculateMatchScore( bankTx: BankTransaction, glTx: Transaction ): number { let score = 0 // Exact amount match if (Math.abs(bankTx.amount) === glTx.amount) { score += 50 } // Date within ±3 days const daysDiff = Math.abs( daysBetween(bankTx.transactionDate, glTx.transactionDate) ) if (daysDiff === 0) score += 30 else if (daysDiff <= 1) score += 20 else if (daysDiff <= 3) score += 10 // Reference contains invoice/expense number if (glTx.referenceType === 'invoice' && bankTx.description?.includes(glTx.referenceId)) { score += 20 } return score } Auto-match threshold: Score ≥ 90 → Auto-match Score 70-89 → Suggest match (user confirms) Score < 70 → No match suggested Reconciliation Report interface ReconciliationReport { bankAccount: { id: string name: string currentBalance: Decimal } period: { from: string, to: string } bankTransactions: { total: number reconciled: number unreconciled: number totalAmount: Decimal } glTransactions: { total: number reconciled: number unreconciled: number totalAmount: Decimal } unmatchedBankTransactions: Array unmatchedGLTransactions: Array balanceDiscrepancy: Decimal // Should be 0 when fully reconciled } 7. Chart of Accounts Structure Hierarchical account codes: 1xxx = Assets 2xxx = Liabilities 3xxx = Equity 4xxx = Revenue 5xxx = Expenses Example Serbian Chart of Accounts: 1000 Assets 1100 Current Assets 1110 Cash 1120 Bank Accounts 1121 Intesa RSD Account 1122 Raiffeisen EUR Account 1200 Accounts Receivable 1500 Fixed Assets 1510 Equipment 1520 Vehicles 2000 Liabilities 2100 Current Liabilities 2110 Accounts Payable 2120 VAT Payable 2500 Long-term Liabilities 2510 Loans Payable 3000 Equity 3100 Share Capital 3900 Retained Earnings 4000 Revenue 4100 Service Revenue 4200 Product Sales 5000 Expenses 5100 Operating Expenses 5110 Salaries 5120 Rent 5130 Utilities 5200 Cost of Goods Sold Business Rules Rule 1: Account Hierarchy Parent account codes must exist before creating child accounts Cannot delete parent account if child accounts exist Sub-account balance rolls up to parent Rule 2: Account Deactivation Cannot deactivate account with transactions Deactivated accounts hidden from dropdowns but visible in reports Rule 3: Reserved Accounts System creates default accounts on organization registration Cannot delete: Cash, Bank Account, Accounts Receivable, Accounts Payable, Revenue, Expense 8. Fiscal Year Definition Fiscal year: 12-month period for financial reporting. Default: January 1 - December 31 Configurable: Organization can set custom fiscal year start (e.g., April 1 for UK-style fiscal year) Business Rules Rule 1: Year-End Close At fiscal year end, close Revenue and Expense accounts Transfer net profit/loss to Retained Earnings Lock all transactions for closed fiscal year Cannot edit locked transactions Rule 2: Period-Based Reports Profit & Loss: always for a period (from → to) Balance Sheet: always as of a date (point in time) Cash Flow: always for a period 9. Audit Trail Purpose Immutable log of all data changes for: Compliance (GDPR, financial regulations) Debugging (track down errors) Rollback simulation (undo mistakes) What is Logged ALL INSERT/UPDATE/DELETE operations on: Invoices Expenses Transactions Contacts Users Organization settings Captured data: Table name User ID (who made the change) Timestamp Action (INSERT, UPDATE, DELETE) Old values (before change) New values (after change) Client IP address Implementation Via Prisma Middleware: prisma.$use(async (params, next) => { const result = await next(params) if (['create', 'update', 'delete'].includes(params.action)) { await prisma.loggedAction.create({ data: { tableName: params.model, userId: getCurrentUserId(), action: params.action.toUpperCase(), rowData: params.action === 'delete' ? params.args.where : null, changedFields: params.action === 'update' ? params.args.data : null, clientIp: getClientIp(), applicationName: 'bilko-api' } }) } return result }) Retention Policy Audit logs retained for 7 years (financial compliance requirement) After 7 years, archived to cold storage (AWS S3 Glacier) NEVER deleted Summary of Critical Business Rules Double-entry: Every transaction has one debit and one credit Debits = Credits: Ledger must always balance Exchange rate locking: Rates locked at transaction date, NEVER recalculated Invoice workflow: draft → sent → paid (creates 2 transactions) Expense workflow: pending → approved → paid (creates 2 transactions) VAT calculation: taxAmount = lineTotal * (taxRate / 100) Account hierarchy: Parent-child relationships in Chart of Accounts Audit trail: ALL changes logged immutably Fiscal year close: Lock transactions, transfer P&L to Retained Earnings Reconciliation: Match bank transactions to GL transactions End of Business Logic Documentation Middleware Stack Bilko Middleware Stack Status: SPECIFICATION (backend not implemented) Last updated: 2026-02-20 Purpose This document specifies the Express middleware stack for Bilko's backend. Middleware order is CRITICAL — security, authentication, validation, and error handling must execute in the correct sequence. Middleware Execution Order The order matters. Middleware executes top-to-bottom: Full Middleware Pipeline flowchart TD REQ[Incoming HTTP Request] REQ --> H[1. Helmet\nSecurity Headers\nHSTS, CSP, X-Frame-Options,\nX-Content-Type-Options] H --> CORS[2. CORS\nAllow: bilko.io, localhost:3000\ncredentials: true\nmaxAge: 86400] CORS --> BP[3. Body Parser\nexpress.json limit=10mb\nexpress.urlencoded] BP --> RL{4. Rate Limiter\nAuth: 5 req/min\nGeneral: 100 req/min} RL -->|Exceeded| R429[429 Too Many Requests] RL -->|OK| LOG[5. Morgan Logger\nWinston transport\nCombined format] LOG --> ROUTE[6. Router\n/api/v1/*] ROUTE --> AUTH{authGuard\nVerify Bearer JWT} AUTH -->|No token| R401A[401 NO_TOKEN] AUTH -->|Expired| R401B[401 TOKEN_EXPIRED] AUTH -->|Invalid| R401C[401 INVALID_TOKEN] AUTH -->|Valid| ATTACH[Attach req.user\n{id, email, role, orgId}] ATTACH --> ROLE{roleGuard\nCheck allowed roles} ROLE -->|Insufficient| R403[403 INSUFFICIENT_PERMISSIONS] ROLE -->|Authorized| VALID{validate\nZod schema} VALID -->|Fails| R422[422 VALIDATION_ERROR] VALID -->|Passes| SCOPE[organizationScope\nAttach req.organizationId] SCOPE --> HANDLER[Route Handler\nBusiness Logic + Prisma] HANDLER --> AUDIT[Prisma Middleware\nLoggedAction INSERT] AUDIT --> RESP[200/201/204 Response] HANDLER -->|Error thrown| ERR[7. Error Handler\nMUST be last middleware] ERR --> FMTERR[Format error response\n{error, code, details}] FMTERR --> ERRRESP[4xx/500 Response] style REQ fill:#00E5A0,color:#000 style R429 fill:#f87171,color:#fff style R401A fill:#f87171,color:#fff style R401B fill:#f87171,color:#fff style R401C fill:#f87171,color:#fff style R403 fill:#f87171,color:#fff style R422 fill:#f87171,color:#fff style RESP fill:#4ade80,color:#000 import express from 'express' import helmet from 'helmet' import cors from 'cors' import rateLimit from 'express-rate-limit' import { authGuard, roleGuard } from './middleware/auth' import { validate } from './middleware/validation' import { errorHandler } from './middleware/error-handler' const app = express() // 1. Security headers (helmet) app.use(helmet()) // 2. CORS configuration app.use(cors(corsOptions)) // 3. Body parsing app.use(express.json({ limit: '10mb' })) app.use(express.urlencoded({ extended: true })) // 4. Rate limiting app.use(rateLimiter) // 5. Request logging (Morgan) app.use(morgan('combined')) // 6. Routes (with auth + validation per-route) app.use('/api/v1', routes) // 7. Error handler (MUST be last) app.use(errorHandler) 1. Helmet (Security Headers) Purpose: Sets HTTP security headers to prevent common attacks. Installation: npm install helmet Configuration: import helmet from 'helmet' app.use(helmet({ contentSecurityPolicy: { directives: { defaultSrc: ["'self'"], styleSrc: ["'self'", "'unsafe-inline'"], // Allow inline styles for Next.js scriptSrc: ["'self'"], imgSrc: ["'self'", "data:", "https://r2.bilko.io"], // Cloudflare R2 connectSrc: ["'self'", "https://api.bilko.io"], fontSrc: ["'self'"], objectSrc: ["'none'"], upgradeInsecureRequests: [] } }, hsts: { maxAge: 31536000, // 1 year includeSubDomains: true, preload: true }, frameguard: { action: 'deny' }, // Prevent clickjacking noSniff: true, // Prevent MIME sniffing xssFilter: true // Enable XSS filter })) Headers set: Strict-Transport-Security — Force HTTPS X-Content-Type-Options: nosniff — Prevent MIME sniffing X-Frame-Options: DENY — Prevent clickjacking X-XSS-Protection: 1; mode=block — Enable XSS filter Content-Security-Policy — Restrict resource loading 2. CORS (Cross-Origin Resource Sharing) Purpose: Allow frontend (Next.js) to call backend API from different origin. Installation: npm install cors Configuration: import cors from 'cors' const corsOptions = { origin: (origin, callback) => { const allowedOrigins = [ 'https://bilko.io', 'https://www.bilko.io', 'http://localhost:3000' // Development only ] if (!origin || allowedOrigins.includes(origin)) { callback(null, true) } else { callback(new Error('Not allowed by CORS')) } }, credentials: true, // Allow cookies (refresh tokens) methods: ['GET', 'POST', 'PUT', 'PATCH', 'DELETE'], allowedHeaders: ['Content-Type', 'Authorization'], exposedHeaders: ['X-Total-Count', 'X-Page-Count'], // For pagination maxAge: 86400 // Cache preflight for 24h } app.use(cors(corsOptions)) Why credentials: true? Allows httpOnly cookies (refresh tokens) Frontend must set credentials: 'include' in fetch() 3. Body Parsing Purpose: Parse JSON request bodies. Built-in Express middleware: app.use(express.json({ limit: '10mb', // Max request body size strict: true, // Reject non-arrays/objects type: 'application/json' })) app.use(express.urlencoded({ extended: true, limit: '10mb' })) Limits: 10MB for file uploads (receipts, CSV imports) Reject if Content-Length > 10MB Return 413 Payload Too Large 4. Rate Limiting Purpose: Prevent abuse, brute-force attacks, DDoS. Installation: npm install express-rate-limit Configuration: import rateLimit from 'express-rate-limit' // General API rate limiter const apiLimiter = rateLimit({ windowMs: 60 * 1000, // 1 minute max: 100, // Max 100 requests per minute message: { error: 'Too many requests', code: 'RATE_LIMIT_EXCEEDED', retryAfter: 60 }, standardHeaders: true, // Return RateLimit-* headers legacyHeaders: false, handler: (req, res) => { res.status(429).json({ error: 'Too many requests', code: 'RATE_LIMIT_EXCEEDED', retryAfter: 60 }) } }) // Auth rate limiter (stricter) const authLimiter = rateLimit({ windowMs: 60 * 1000, // 1 minute max: 5, // Max 5 login attempts per minute skipSuccessfulRequests: true, // Don't count successful logins keyGenerator: (req) => { return req.ip // Rate limit by IP } }) // Apply to routes app.use('/api/v1', apiLimiter) app.use('/api/v1/auth/login', authLimiter) app.use('/api/v1/auth/register', authLimiter) Rate limits by endpoint: Endpoint Limit Window Why /api/v1/auth/login 5 1 min Prevent brute-force /api/v1/auth/register 5 1 min Prevent spam registration /api/v1/* (general) 100 1 min General API protection Write ops (POST/PUT/PATCH) 50 1 min Prevent resource exhaustion 5. Request Logging Purpose: Log all HTTP requests for debugging and monitoring. Installation: npm install morgan npm install winston Configuration: import morgan from 'morgan' import winston from 'winston' // Winston logger const logger = winston.createLogger({ level: 'info', format: winston.format.json(), transports: [ new winston.transports.File({ filename: 'error.log', level: 'error' }), new winston.transports.File({ filename: 'combined.log' }) ] }) if (process.env.NODE_ENV !== 'production') { logger.add(new winston.transports.Console({ format: winston.format.simple() })) } // Morgan HTTP logging app.use(morgan('combined', { stream: { write: (message) => logger.info(message.trim()) } })) Log format: :remote-addr - :remote-user [:date[clf]] ":method :url HTTP/:http-version" :status :res[content-length] ":referrer" ":user-agent" Example: 192.168.1.100 - user@example.com [20/Feb/2026:10:30:15 +0000] "POST /api/v1/invoices HTTP/1.1" 201 512 "https://bilko.io" "Mozilla/5.0..." Per-Route Middleware Composition graph LR subgraph PUBLIC [Public Routes — No Auth] P1["POST /auth/register\n[rateLimiter(5/min)] → handler"] P2["POST /auth/login\n[rateLimiter(5/min)] → handler"] P3["POST /auth/refresh\n[rateLimiter(100/min)] → handler"] P4["GET /track/email/:id\n[handler — tracking pixel]"] end subgraph VIEWER [Viewer Routes — All Roles] V1["GET /invoices\n[auth] → [orgScope] → handler"] V2["GET /reports/*\n[auth] → [orgScope] → handler"] V3["GET /contacts\n[auth] → [orgScope] → handler"] end subgraph ACCOUNTANT [Accountant+ Routes] A1["POST /invoices\n[auth] → [role:owner,admin,accountant]\n→ [validate] → [orgScope] → handler"] A2["POST /expenses\n[auth] → [role:owner,admin,accountant]\n→ [validate] → handler"] A3["POST /transactions\n[auth] → [role:owner,admin,accountant]\n→ [validate] → handler"] end subgraph ADMIN [Admin+ Routes] AD1["PATCH /expenses/:id/approve\n[auth] → [role:owner,admin] → handler"] AD2["POST /users/invite\n[auth] → [role:owner,admin] → handler"] AD3["PUT /organization\n[auth] → [role:owner,admin] → handler"] end subgraph OWNER [Owner-Only Routes] O1["DELETE /users/:id\n[auth] → [role:owner] → handler"] O2["PUT /users/:id/role\n[auth] → [role:owner] → handler"] O3["DELETE /organization\n[auth] → [role:owner] → handler"] end style PUBLIC fill:#e2e8f0,color:#000 style VIEWER fill:#dcfce7,color:#000 style ACCOUNTANT fill:#fef9c3,color:#000 style ADMIN fill:#dbeafe,color:#000 style OWNER fill:#fce7f3,color:#000 6. Authentication Middleware Purpose: Verify JWT access token, attach user to request. Implementation: import jwt from 'jsonwebtoken' import { Request, Response, NextFunction } from 'express' interface AuthRequest extends Request { user?: { id: string email: string role: 'owner' | 'admin' | 'accountant' | 'viewer' organizationId: string } } export async function authGuard(req: AuthRequest, res: Response, next: NextFunction) { const authHeader = req.headers.authorization if (!authHeader || !authHeader.startsWith('Bearer ')) { return res.status(401).json({ error: 'Unauthorized', code: 'NO_TOKEN' }) } const token = authHeader.substring(7) // Remove 'Bearer ' try { const payload = jwt.verify(token, process.env.JWT_SECRET!) as { sub: string email: string role: string orgId: string } // Attach user to request req.user = { id: payload.sub, email: payload.email, role: payload.role as any, organizationId: payload.orgId } next() } catch (error) { if (error.name === 'TokenExpiredError') { return res.status(401).json({ error: 'Token expired', code: 'TOKEN_EXPIRED' }) } if (error.name === 'JsonWebTokenError') { return res.status(401).json({ error: 'Invalid token', code: 'INVALID_TOKEN' }) } return res.status(500).json({ error: 'Authentication error', code: 'AUTH_ERROR' }) } } Usage in routes: app.get('/api/v1/invoices', authGuard, getInvoices) 7. Role-Based Access Control (RBAC) Purpose: Restrict endpoints by user role. Implementation: type UserRole = 'owner' | 'admin' | 'accountant' | 'viewer' export function roleGuard(allowedRoles: UserRole[]) { return (req: AuthRequest, res: Response, next: NextFunction) => { if (!req.user) { return res.status(401).json({ error: 'Unauthorized', code: 'NO_AUTH' }) } if (!allowedRoles.includes(req.user.role)) { return res.status(403).json({ error: 'Forbidden', code: 'INSUFFICIENT_PERMISSIONS', details: { required: allowedRoles, current: req.user.role } }) } next() } } Usage in routes: // Only owner and admin can delete users app.delete('/api/v1/users/:id', authGuard, roleGuard(['owner', 'admin']), deleteUser ) // Everyone can view invoices app.get('/api/v1/invoices', authGuard, getInvoices ) // Only owner, admin, accountant can create invoices app.post('/api/v1/invoices', authGuard, roleGuard(['owner', 'admin', 'accountant']), createInvoice ) 8. Request Validation Purpose: Validate request body, query, params with Zod schemas. Installation: npm install zod Implementation: import { z } from 'zod' import { Request, Response, NextFunction } from 'express' type ValidateTarget = 'body' | 'query' | 'params' export function validate(schema: z.ZodSchema, target: ValidateTarget = 'body') { return (req: Request, res: Response, next: NextFunction) => { try { const data = req[target] const validated = schema.parse(data) // Replace with validated data (coerced types) req[target] = validated next() } catch (error) { if (error instanceof z.ZodError) { return res.status(422).json({ error: 'Validation failed', code: 'VALIDATION_ERROR', details: error.flatten().fieldErrors }) } return res.status(500).json({ error: 'Validation error', code: 'VALIDATION_ERROR' }) } } } Usage in routes: import { z } from 'zod' const createInvoiceSchema = z.object({ customerId: z.string().uuid(), invoiceDate: z.string().date(), dueDate: z.string().date(), items: z.array(z.object({ description: z.string().min(1).max(500), quantity: z.number().positive(), unitPrice: z.number().positive(), taxRate: z.number().min(0).max(100) })).min(1) }) app.post('/api/v1/invoices', authGuard, roleGuard(['owner', 'admin', 'accountant']), validate(createInvoiceSchema, 'body'), createInvoice ) Error response: { "error": "Validation failed", "code": "VALIDATION_ERROR", "details": { "customerId": ["Invalid UUID"], "items.0.quantity": ["Must be positive"] } } 9. Organization Scoping Purpose: Automatically filter all queries by organizationId to enforce multi-tenancy. Implementation: export function organizationScope(req: AuthRequest, res: Response, next: NextFunction) { if (!req.user) { return res.status(401).json({ error: 'Unauthorized', code: 'NO_AUTH' }) } // Attach organizationId to request for easy access req.organizationId = req.user.organizationId next() } Usage in Prisma queries: async function getInvoices(req: AuthRequest, res: Response) { const invoices = await prisma.invoice.findMany({ where: { organizationId: req.user!.organizationId // Always filter by org } }) res.json({ data: invoices }) } CRITICAL: NEVER allow cross-organization queries. Always filter by organizationId . 10. Error Handler Purpose: Catch all errors, format consistently, log, return to client. MUST be the last middleware. flowchart TD ERR[Error Thrown by any Middleware or Handler] ERR --> LOG_ERR[Log to Winston:\nmessage, stack, path, method, userId] LOG_ERR --> TYPE{Error Type?} TYPE -->|PrismaClientKnownRequestError| PRISMA{Prisma Code?} PRISMA -->|P2002 Unique violation| R400A[400 DUPLICATE_RESOURCE\n{field: target}] PRISMA -->|P2003 Foreign key| R404A[404 FOREIGN_KEY_ERROR] PRISMA -->|P2025 Record not found| R404B[404 NOT_FOUND] PRISMA -->|Other| R500[500 INTERNAL_ERROR] TYPE -->|ValidationError| R422[422 VALIDATION_ERROR] TYPE -->|JsonWebTokenError| R401A[401 INVALID_TOKEN] TYPE -->|TokenExpiredError| R401B[401 AUTH_ERROR] TYPE -->|Custom AppError| CUSTOM[err.status / err.code] TYPE -->|Unknown| R500 R400A & R404A & R404B & R422 & R401A & R401B & CUSTOM & R500 --> FORMAT[Format Response:\n{ error, code, details? }] FORMAT --> SEND[Send to Client] style ERR fill:#f87171,color:#fff style R500 fill:#dc2626,color:#fff style FORMAT fill:#60a5fa,color:#000 Implementation: import { Request, Response, NextFunction } from 'express' import { Prisma } from '@prisma/client' export function errorHandler(err: any, req: Request, res: Response, next: NextFunction) { // Log error logger.error('Error:', { message: err.message, stack: err.stack, path: req.path, method: req.method, user: req.user?.id }) // Prisma errors if (err instanceof Prisma.PrismaClientKnownRequestError) { // Unique constraint violation if (err.code === 'P2002') { return res.status(400).json({ error: 'Resource already exists', code: 'DUPLICATE_RESOURCE', details: { field: err.meta?.target } }) } // Foreign key constraint violation if (err.code === 'P2003') { return res.status(404).json({ error: 'Related resource not found', code: 'FOREIGN_KEY_ERROR' }) } // Record not found if (err.code === 'P2025') { return res.status(404).json({ error: 'Resource not found', code: 'NOT_FOUND' }) } } // Validation errors (already handled by validate middleware) if (err.name === 'ValidationError') { return res.status(422).json({ error: err.message, code: 'VALIDATION_ERROR' }) } // JWT errors (already handled by authGuard) if (err.name === 'JsonWebTokenError' || err.name === 'TokenExpiredError') { return res.status(401).json({ error: 'Authentication failed', code: 'AUTH_ERROR' }) } // Default error res.status(err.status || 500).json({ error: err.message || 'Internal server error', code: err.code || 'INTERNAL_ERROR' }) } Error response format: { "error": "Human-readable error message", "code": "MACHINE_READABLE_CODE", "details": { "field": "Additional context" } } Complete Middleware Stack Example import express from 'express' import helmet from 'helmet' import cors from 'cors' import morgan from 'morgan' import rateLimit from 'express-rate-limit' import { authGuard, roleGuard, organizationScope } from './middleware/auth' import { validate } from './middleware/validation' import { errorHandler } from './middleware/error-handler' import routes from './routes' const app = express() // 1. Security headers app.use(helmet(helmetConfig)) // 2. CORS app.use(cors(corsOptions)) // 3. Body parsing app.use(express.json({ limit: '10mb' })) app.use(express.urlencoded({ extended: true })) // 4. Rate limiting app.use('/api/v1', apiLimiter) app.use('/api/v1/auth/login', authLimiter) app.use('/api/v1/auth/register', authLimiter) // 5. Request logging app.use(morgan('combined', { stream: logger.stream })) // 6. Routes app.use('/api/v1', routes) // 7. Error handler (MUST be last) app.use(errorHandler) // Start server const PORT = process.env.PORT || 4000 app.listen(PORT, () => { console.log(`Server running on port ${PORT}`) }) Middleware Testing Unit tests for each middleware: import { describe, it, expect } from 'vitest' import request from 'supertest' import app from '../app' describe('Auth Middleware', () => { it('blocks request without token', async () => { const res = await request(app).get('/api/v1/invoices') expect(res.status).toBe(401) expect(res.body.code).toBe('NO_TOKEN') }) it('blocks request with expired token', async () => { const expiredToken = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...' const res = await request(app) .get('/api/v1/invoices') .set('Authorization', `Bearer ${expiredToken}`) expect(res.status).toBe(401) expect(res.body.code).toBe('TOKEN_EXPIRED') }) it('allows request with valid token', async () => { const validToken = generateToken({ sub: 'user-id', role: 'owner', orgId: 'org-id' }) const res = await request(app) .get('/api/v1/invoices') .set('Authorization', `Bearer ${validToken}`) expect(res.status).not.toBe(401) }) }) describe('Role Guard', () => { it('blocks accountant from deleting users', async () => { const token = generateToken({ sub: 'user-id', role: 'accountant', orgId: 'org-id' }) const res = await request(app) .delete('/api/v1/users/other-user-id') .set('Authorization', `Bearer ${token}`) expect(res.status).toBe(403) expect(res.body.code).toBe('INSUFFICIENT_PERMISSIONS') }) }) End of Middleware Documentation External Services Integration Bilko External Services Status: SPECIFICATION (backend not implemented) Last updated: 2026-02-20 Purpose This document specifies the external service integrations for Bilko's backend. Covers email delivery, file storage, exchange rates, and PDF generation. Service Integration Architecture graph TD subgraph BILKO [Bilko Backend — apps/api] API[Express API\nPort 4000] PDF[PDF Service\nPuppeteer] RATE[Exchange Rate Service\nCron: daily 00:00 UTC] AUDIT[Prisma Middleware\nAudit Logger] end subgraph EXTERNAL [External Services] SG[SendGrid\nnoreply@bilko.io\n100 emails/day free] R2[Cloudflare R2\nbilko-files bucket\n10GB free] ECB[ECB API\nexchangerate.host\nFree, daily rates] FIXER[Fixer.io\n100 req/month free\nFallback] CLAM[ClamAV\nVirus Scanner\nlocalhost:3310] end subgraph STORAGE [Database] PG[PostgreSQL 14+\nAll data\nExchangeRate cache] end API -->|Invoice email + PDF| SG API -->|Receipt upload| R2 PDF -->|Store generated PDF| R2 RATE -->|Primary rates fetch| ECB RATE -->|Fallback if ECB fails| FIXER ECB & FIXER -->|Store in DB| PG API -->|Scan uploaded files| CLAM API -->|All reads/writes| PG AUDIT -->|Append-only log| PG style SG fill:#00b0f0,color:#fff style R2 fill:#f6821f,color:#fff style ECB fill:#0070f3,color:#fff style FIXER fill:#6366f1,color:#fff style PG fill:#336791,color:#fff style CLAM fill:#e53e3e,color:#fff Table of Contents SendGrid (Email Delivery) Cloudflare R2 (File Storage) Exchange Rate APIs PDF Generation Error Handling & Fallbacks 1. SendGrid (Email Delivery) Purpose Send invoice emails, payment reminders, user invitations, and password resets. Invoice Email + Tracking Flow sequenceDiagram participant API as Bilko API participant PDF as PDF Service\n(Puppeteer) participant R2 as Cloudflare R2 participant SG as SendGrid participant CUSTOMER as Customer Email Note over API,CUSTOMER: Invoice Send Flow API->>PDF: generateInvoicePDF(invoiceId) PDF->>PDF: Launch headless Chromium\nRender HTML template\nExport A4 PDF PDF-->>API: PDF Buffer API->>R2: PUT invoices/{orgId}/INV-2026-001.pdf R2-->>API: Public URL stored API->>API: Update invoice.pdfUrl API->>SG: sendEmail({ to, subject, html, attachment:pdf }) SG-->>API: { messageId } API->>API: Update invoice.sentAt, status='sent' SG->>CUSTOMER: Deliver email with PDF attachment CUSTOMER->>API: GET /track/email/{invoiceId}\n[1x1 pixel load] API->>API: UPDATE invoice SET status='viewed'\nviewedAt=now() Setup Account: SendGrid (free tier: 100 emails/day) Installation: npm install @sendgrid/mail Environment Variables: SENDGRID_API_KEY=SG.xxxxx SENDGRID_FROM_EMAIL=noreply@bilko.io SENDGRID_FROM_NAME=Bilko Configuration import sgMail from '@sendgrid/mail' sgMail.setApiKey(process.env.SENDGRID_API_KEY!) interface SendEmailOptions { to: string | string[] cc?: string[] bcc?: string[] subject: string text: string html: string attachments?: Array<{ content: string // Base64 encoded filename: string type: string // MIME type disposition: 'attachment' | 'inline' }> } async function sendEmail(options: SendEmailOptions) { const msg = { to: options.to, cc: options.cc, bcc: options.bcc, from: { email: process.env.SENDGRID_FROM_EMAIL!, name: process.env.SENDGRID_FROM_NAME! }, subject: options.subject, text: options.text, html: options.html, attachments: options.attachments } try { const response = await sgMail.send(msg) return { success: true, messageId: response[0].headers['x-message-id'] } } catch (error) { logger.error('SendGrid error:', error) throw new Error('Failed to send email') } } Email Templates 1. Invoice Email Template variables: {{ organizationName }} {{ invoiceNumber }} {{ customerName }} {{ totalAmount }} {{ currencyCode }} {{ dueDate }} {{ viewInvoiceUrl }} HTML template: Invoice {{ invoiceNumber }}

{{ organizationName }}

Dear {{ customerName }},

Your invoice {{ invoiceNumber }} is ready.

Amount:{{ totalAmount }} {{ currencyCode }}
Due Date:{{ dueDate }}

View Invoice

Thank you for your business!

Powered by Bilko

Attachment: Invoice PDF (generated via PDF service) 2. User Invitation Email Template variables: {{ organizationName }} {{ inviterName }} {{ inviteLink }} {{ role }} Subject: {{ inviterName }} invited you to {{ organizationName }} on Bilko 3. Password Reset Email Template variables: {{ resetLink }} {{ expiresIn }} (e.g., "15 minutes") Subject: Reset your Bilko password Email Tracking Purpose: Track when customer views invoice email (for viewedAt timestamp). How it works: Embed 1x1 transparent pixel in email HTML Pixel URL: https://api.bilko.io/track/email/{{ invoiceId }} When customer opens email, browser loads pixel Backend endpoint logs view: app.get('/track/email/:invoiceId', async (req, res) => { const { invoiceId } = req.params await prisma.invoice.update({ where: { id: invoiceId }, data: { status: 'viewed', viewedAt: new Date() } }) // Return 1x1 transparent GIF const pixel = Buffer.from( 'R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7', 'base64' ) res.writeHead(200, { 'Content-Type': 'image/gif', 'Content-Length': pixel.length }) res.end(pixel) }) Rate Limits SendGrid free tier: 100 emails/day 40,000 emails first 30 days After 30 days: $0.00025/email Recommendation for MVP: Free tier sufficient for testing. Upgrade to paid plan at launch. 2. Cloudflare R2 (File Storage) Purpose Store invoice PDFs and expense receipts. Why R2 over S3: S3-compatible API (easy migration) Zero egress fees (S3 charges $0.09/GB) Cheaper storage: $0.015/GB (vs S3 $0.023/GB) Setup Account: Cloudflare (free tier: 10GB storage) Installation: npm install @aws-sdk/client-s3 npm install @aws-sdk/s3-request-presigner Environment Variables: R2_ACCOUNT_ID=your-account-id R2_ACCESS_KEY_ID=your-access-key R2_SECRET_ACCESS_KEY=your-secret-key R2_BUCKET_NAME=bilko-files R2_PUBLIC_URL=https://r2.bilko.io Configuration import { S3Client, PutObjectCommand, GetObjectCommand } from '@aws-sdk/client-s3' import { getSignedUrl } from '@aws-sdk/s3-request-presigner' const s3 = new S3Client({ region: 'auto', endpoint: `https://${process.env.R2_ACCOUNT_ID}.r2.cloudflarestorage.com`, credentials: { accessKeyId: process.env.R2_ACCESS_KEY_ID!, secretAccessKey: process.env.R2_SECRET_ACCESS_KEY! } }) interface UploadFileOptions { key: string // File path (e.g., "invoices/INV-2026-001.pdf") body: Buffer | Uint8Array contentType: string // MIME type metadata?: Record } async function uploadFile(options: UploadFileOptions): Promise { const command = new PutObjectCommand({ Bucket: process.env.R2_BUCKET_NAME!, Key: options.key, Body: options.body, ContentType: options.contentType, Metadata: options.metadata }) await s3.send(command) // Return public URL return `${process.env.R2_PUBLIC_URL}/${options.key}` } async function getSignedDownloadUrl(key: string, expiresIn: number = 3600): Promise { const command = new GetObjectCommand({ Bucket: process.env.R2_BUCKET_NAME!, Key: key }) return getSignedUrl(s3, command, { expiresIn }) } File Organization Bucket structure: bilko-files/ ├── invoices/ │ ├── org-uuid-1/ │ │ ├── INV-2026-001.pdf │ │ └── INV-2026-002.pdf │ └── org-uuid-2/ │ └── INV-2026-001.pdf ├── receipts/ │ ├── org-uuid-1/ │ │ ├── EXP-2026-001.jpg │ │ └── EXP-2026-002.pdf │ └── org-uuid-2/ └── exports/ └── org-uuid-1/ └── report-2026-02-20.xlsx Key format: {category}/{organizationId}/{filename} File Upload Workflow Invoice PDF: async function storeInvoicePDF(invoiceId: string, organizationId: string, pdf: Buffer): Promise { const invoice = await prisma.invoice.findUnique({ where: { id: invoiceId } }) const filename = `${invoice.invoiceNumber}.pdf` const key = `invoices/${organizationId}/${filename}` const url = await uploadFile({ key, body: pdf, contentType: 'application/pdf', metadata: { invoiceId, organizationId, uploadedAt: new Date().toISOString() } }) await prisma.invoice.update({ where: { id: invoiceId }, data: { pdfUrl: url } }) return url } Expense Receipt: async function storeExpenseReceipt(expenseId: string, organizationId: string, file: Express.Multer.File): Promise { const expense = await prisma.expense.findUnique({ where: { id: expenseId } }) const ext = file.mimetype.split('/')[1] // 'pdf', 'jpeg', 'png' const filename = `${expense.expenseNumber}.${ext}` const key = `receipts/${organizationId}/${filename}` const url = await uploadFile({ key, body: file.buffer, contentType: file.mimetype, metadata: { expenseId, organizationId, uploadedAt: new Date().toISOString() } }) await prisma.expense.update({ where: { id: expenseId }, data: { receiptUrl: url } }) return url } Security 1. Signed URLs for private files: Invoice PDFs: public (anyone with URL can view) Expense receipts: private (require signed URL) Signed URLs expire in 1 hour 2. Virus scanning: Use ClamAV to scan uploaded files Reject if virus detected npm install clamscan import NodeClam from 'clamscan' const clam = await new NodeClam().init({ clamdscan: { host: 'localhost', port: 3310 } }) async function scanFile(filePath: string): Promise { const { isInfected } = await clam.scanFile(filePath) return !isInfected } 3. Exchange Rate APIs Purpose Fetch daily exchange rates for multi-currency support. Exchange Rate Fetch & Fallback Flow flowchart TD CRON[Cron Job\nDaily at 00:00 UTC] CRON --> ECB[Fetch from ECB API\nexchangerate.host/latest?base=EUR] ECB --> ECB_OK{Success?} ECB_OK -->|Yes| STORE[Upsert ExchangeRate records\nsource='ECB'] ECB_OK -->|No| FIXER[Fallback: Fixer.io\napi.fixer.io/latest] FIXER --> FIX_OK{Success?} FIX_OK -->|Yes| STORE2[Upsert ExchangeRate records\nsource='fixer.io'] FIX_OK -->|No| PREV[Use yesterday's rates\nWarn in logs] STORE & STORE2 & PREV --> AVAIL[Rates available in DB\nfor transaction date locking] subgraph LOOKUP [Rate Lookup at Transaction Time] L1[getExchangeRate\nbaseCurrency, targetCurrency, date] L2{Same currency?} L3[Return rate = 1.0] L4[Find exact date in DB] L5{Found?} L6[Return rate] L7[Find nearest available date\norderBy effectiveDate DESC] L8[Warn in logs\nReturn nearest rate] L1 --> L2 L2 -->|Yes| L3 L2 -->|No| L4 L4 --> L5 L5 -->|Yes| L6 L5 -->|No| L7 L7 --> L8 end AVAIL --> LOOKUP style PREV fill:#fb923c,color:#000 style L8 fill:#fb923c,color:#000 Primary: European Central Bank (ECB) Endpoint: https://api.exchangerate.host/latest Free: Yes (unlimited) Example: async function fetchECBRates(baseCurrency: string = 'EUR'): Promise> { const url = `https://api.exchangerate.host/latest?base=${baseCurrency}` const response = await fetch(url) const data = await response.json() if (!data.success) { throw new Error('ECB API error') } return data.rates // { RSD: 117.50, BAM: 1.95, HRK: 7.53, USD: 1.07 } } Fallback: Fixer.io Endpoint: https://api.fixer.io/latest Free tier: 100 requests/month Environment Variables: FIXER_API_KEY=your-api-key Example: async function fetchFixerRates(baseCurrency: string = 'EUR'): Promise> { const url = `https://api.fixer.io/latest?base=${baseCurrency}&access_key=${process.env.FIXER_API_KEY}` const response = await fetch(url) const data = await response.json() if (!data.success) { throw new Error('Fixer.io API error') } return data.rates } Exchange Rate Service async function updateExchangeRates(): Promise { try { // Try ECB first const rates = await fetchECBRates('EUR') // Store in database for (const [targetCurrency, rate] of Object.entries(rates)) { await prisma.exchangeRate.upsert({ where: { baseCurrency_targetCurrency_effectiveDate: { baseCurrency: 'EUR', targetCurrency, effectiveDate: new Date() } }, update: { rate, source: 'ECB', lastUpdated: new Date() }, create: { baseCurrency: 'EUR', targetCurrency, rate, effectiveDate: new Date(), source: 'ECB' } }) } logger.info('Exchange rates updated', { source: 'ECB', count: Object.keys(rates).length }) } catch (error) { // Fallback to Fixer.io try { const rates = await fetchFixerRates('EUR') for (const [targetCurrency, rate] of Object.entries(rates)) { await prisma.exchangeRate.upsert({ where: { baseCurrency_targetCurrency_effectiveDate: { baseCurrency: 'EUR', targetCurrency, effectiveDate: new Date() } }, update: { rate, source: 'fixer.io', lastUpdated: new Date() }, create: { baseCurrency: 'EUR', targetCurrency, rate, effectiveDate: new Date(), source: 'fixer.io' } }) } logger.info('Exchange rates updated', { source: 'fixer.io', count: Object.keys(rates).length }) } catch (fallbackError) { logger.error('Failed to update exchange rates', { error: fallbackError }) // Use yesterday's rates (better than nothing) } } } // Schedule: Daily at 00:00 UTC cron.schedule('0 0 * * *', updateExchangeRates) Get Exchange Rate async function getExchangeRate( baseCurrency: string, targetCurrency: string, date: Date = new Date() ): Promise { // If same currency, rate = 1.0 if (baseCurrency === targetCurrency) { return 1.0 } // Find rate for exact date let rate = await prisma.exchangeRate.findUnique({ where: { baseCurrency_targetCurrency_effectiveDate: { baseCurrency, targetCurrency, effectiveDate: date } } }) // If not found, use nearest available rate if (!rate) { rate = await prisma.exchangeRate.findFirst({ where: { baseCurrency, targetCurrency }, orderBy: { effectiveDate: 'desc' } }) } if (!rate) { throw new Error(`No exchange rate found for ${baseCurrency} → ${targetCurrency}`) } return parseFloat(rate.rate.toString()) } 4. PDF Generation Purpose Generate invoice PDFs with organization branding. Option 1: Puppeteer (Server-Side Rendering) Installation: npm install puppeteer Implementation: import puppeteer from 'puppeteer' async function generateInvoicePDF(invoiceId: string): Promise { const invoice = await prisma.invoice.findUnique({ where: { id: invoiceId }, include: { customer: true, organization: true, items: true } }) // Render HTML template const html = renderInvoiceHTML(invoice) // Launch headless browser const browser = await puppeteer.launch({ headless: true, args: ['--no-sandbox', '--disable-setuid-sandbox'] }) const page = await browser.newPage() await page.setContent(html, { waitUntil: 'networkidle0' }) // Generate PDF const pdf = await page.pdf({ format: 'A4', printBackground: true, margin: { top: '1cm', right: '1cm', bottom: '1cm', left: '1cm' } }) await browser.close() return pdf } Option 2: @react-pdf/renderer (React Components) Installation: npm install @react-pdf/renderer Implementation: import { Document, Page, Text, View, StyleSheet, pdf } from '@react-pdf/renderer' const styles = StyleSheet.create({ page: { padding: 30 }, header: { fontSize: 24, marginBottom: 20 }, table: { display: 'table', width: '100%' }, row: { flexDirection: 'row', borderBottomWidth: 1, borderColor: '#ddd' }, cell: { padding: 10 } }) function InvoicePDF({ invoice }) { return ( {invoice.organization.name} Invoice {invoice.invoiceNumber} {invoice.items.map((item) => ( {item.description} {item.quantity} {item.unitPrice} {item.lineTotal} ))} ) } async function generateInvoicePDF(invoiceId: string): Promise { const invoice = await fetchInvoiceData(invoiceId) const doc = const pdfBlob = await pdf(doc).toBlob() return Buffer.from(await pdfBlob.arrayBuffer()) } Recommendation: Puppeteer for MVP (more flexible HTML/CSS), React PDF for v2 (better TypeScript support). 5. Error Handling & Fallbacks Circuit Breaker & Retry Pattern stateDiagram-v2 [*] --> closed : Initial state closed --> open : failures >= threshold (5)\nService marked as unavailable open --> half_open : timeout elapsed (60s)\nAttempt single test call half_open --> closed : Test call succeeded\nReset failure count half_open --> open : Test call failed\nReset timeout note right of closed Normal operation All calls pass through Failures counted end note note right of open All calls REJECTED immediately Error: "Circuit breaker is open" No calls to external service end note note right of half_open Single probe call allowed Determines if service recovered end note Retry Strategy For transient errors (network timeouts, rate limits): async function withRetry( fn: () => Promise, maxRetries: number = 3, delay: number = 1000 ): Promise { for (let i = 0; i < maxRetries; i++) { try { return await fn() } catch (error) { if (i === maxRetries - 1) throw error logger.warn(`Retry ${i + 1}/${maxRetries}`, { error }) await new Promise((resolve) => setTimeout(resolve, delay * (i + 1))) } } throw new Error('Retry limit exceeded') } // Usage const rates = await withRetry(() => fetchECBRates('EUR')) Circuit Breaker For external services that frequently fail: class CircuitBreaker { private failures = 0 private threshold = 5 private timeout = 60000 // 1 minute private state: 'closed' | 'open' | 'half-open' = 'closed' private nextAttempt = 0 async execute(fn: () => Promise): Promise { if (this.state === 'open') { if (Date.now() < this.nextAttempt) { throw new Error('Circuit breaker is open') } this.state = 'half-open' } try { const result = await fn() this.onSuccess() return result } catch (error) { this.onFailure() throw error } } private onSuccess() { this.failures = 0 this.state = 'closed' } private onFailure() { this.failures++ if (this.failures >= this.threshold) { this.state = 'open' this.nextAttempt = Date.now() + this.timeout logger.warn('Circuit breaker opened', { failures: this.failures }) } } } const sendGridBreaker = new CircuitBreaker() async function sendEmailWithBreaker(options: SendEmailOptions) { return sendGridBreaker.execute(() => sendEmail(options)) } Graceful Degradation If external service fails, degrade gracefully: Example: Invoice email delivery async function sendInvoiceEmail(invoiceId: string) { try { await sendEmail({ /* ... */ }) await prisma.invoice.update({ where: { id: invoiceId }, data: { sentAt: new Date(), status: 'sent' } }) } catch (error) { logger.error('Failed to send invoice email', { invoiceId, error }) // Fallback: Mark invoice as sent but flag for manual email await prisma.invoice.update({ where: { id: invoiceId }, data: { status: 'draft', // Keep in draft notes: `Email delivery failed: ${error.message}. Please send manually.` } }) // Alert admin await sendSlackAlert('Invoice email delivery failed', { invoiceId }) } } Environment Variables Summary # SendGrid SENDGRID_API_KEY=SG.xxxxx SENDGRID_FROM_EMAIL=noreply@bilko.io SENDGRID_FROM_NAME=Bilko # Cloudflare R2 R2_ACCOUNT_ID=your-account-id R2_ACCESS_KEY_ID=your-access-key R2_SECRET_ACCESS_KEY=your-secret-key R2_BUCKET_NAME=bilko-files R2_PUBLIC_URL=https://r2.bilko.io # Fixer.io (fallback) FIXER_API_KEY=your-api-key # Feature Flags ENABLE_EMAIL_TRACKING=true ENABLE_VIRUS_SCANNING=false # For MVP End of Services Documentation API Coverage Report API Coverage Report Date: 2026-02-20 Purpose: Map every frontend page to required API endpoints. Verify 100% coverage. Status: Backend NOT implemented (specification only) Coverage Matrix Dashboard Page (/) UI Element Data Required API Endpoint Status Cash Balance metric Total bank account balances in base currency GET /api/v1/reports/dashboard COVERED Revenue MTD metric Month-to-date revenue GET /api/v1/reports/dashboard COVERED Unpaid Invoices metric Total unpaid invoices GET /api/v1/reports/dashboard COVERED Expenses MTD metric Month-to-date expenses GET /api/v1/reports/dashboard COVERED Profit MTD metric Month-to-date profit GET /api/v1/reports/dashboard COVERED Cash Flow Change Percentage change from last month GET /api/v1/reports/dashboard COVERED P&L Bar Chart 6-month monthly P&L data GET /api/v1/reports/dashboard COVERED Receivables Aging Chart Receivables breakdown by age (current, 30d, 60d, 90d+) GET /api/v1/reports/dashboard COVERED Expenses by Category Chart Expenses grouped by category GET /api/v1/reports/dashboard COVERED Recent Transactions table Last 5 transactions GET /api/v1/transactions?perPage=5&sort=transactionDate&order=desc COVERED Invoices List Page (/invoices) UI Element Data Required API Endpoint Status Invoice list (all filters) Paginated invoices with filter/sort/search GET /api/v1/invoices?status=X&search=Y&fromDate=Z&toDate=W&page=P&perPage=20&sort=field&order=desc COVERED Status filter options Invoice list filtered by status GET /api/v1/invoices?status={draft|sent|viewed|paid|overdue|cancelled} COVERED Search (customer/number) Invoice list filtered by search query GET /api/v1/invoices?search={query} COVERED Date range filter Invoice list filtered by date range GET /api/v1/invoices?fromDate=YYYY-MM-DD&toDate=YYYY-MM-DD COVERED Summary totals by status Client-side calculation from filtered list N/A (client-side) COVERED Edit invoice action Get invoice details for editing GET /api/v1/invoices/:id COVERED Delete invoice action Delete invoice DELETE /api/v1/invoices/:id MISSING Send invoice action Send invoice via email POST /api/v1/invoices/:id/send COVERED Download PDF action Get invoice PDF GET /api/v1/invoices/:id/pdf COVERED MISSING ENDPOINT: DELETE /api/v1/invoices/:id — Delete invoice (only draft invoices should be deletable) Method: DELETE Auth: Bearer token Roles: owner, admin, accountant Rate limit: 10 req/min Response: 204 No Content Errors: 400 — Invoice is not in draft status 404 — Invoice not found Invoice Creation Wizard (/invoices/new) Step UI Element Data Required API Endpoint Status 1 Customer dropdown List of customers GET /api/v1/contacts?type=customer COVERED 1 Add customer dialog Create new customer POST /api/v1/contacts COVERED 2 Invoice number Auto-generated invoice number Client-side generation (format: INV-YYYY-NNN) COVERED 2 Currency options List of supported currencies GET /api/v1/currencies COVERED 3 Line items Form input (no API needed) N/A COVERED 3 VAT rate options Tax rate configuration GET /api/v1/settings/tax-rates COVERED 4 Notes/Terms Form input (no API needed) N/A COVERED 5 Preview Client-side rendering of form data N/A COVERED 6 Save as Draft Create invoice with status=draft POST /api/v1/invoices COVERED 6 Send Invoice Create + send invoice POST /api/v1/invoices (then) POST /api/v1/invoices/:id/send COVERED 6 Download PDF Generate PDF GET /api/v1/invoices/:id/pdf COVERED Expenses List Page (/expenses) UI Element Data Required API Endpoint Status Expense list Paginated expenses with filters GET /api/v1/expenses?period=X&category=Y&search=Z&page=P&perPage=20 COVERED Period filter Expenses filtered by date range GET /api/v1/expenses?fromDate=YYYY-MM-DD&toDate=YYYY-MM-DD COVERED Category filter Expenses filtered by category GET /api/v1/expenses?category={category} COVERED Search (description/vendor) Expenses filtered by search query GET /api/v1/expenses (client-side search on fetched data) COVERED Summary stats Client-side calculation from filtered list N/A (client-side) COVERED Create expense Create new expense POST /api/v1/expenses COVERED Upload receipt Upload receipt file POST /api/v1/expenses (multipart with receiptFile) COVERED Edit expense Update expense (pending only) PUT /api/v1/expenses/:id COVERED Approve expense Approve expense PATCH /api/v1/expenses/:id/approve COVERED Delete expense Delete expense (pending only) DELETE /api/v1/expenses/:id COVERED Download receipt Get receipt file GET /api/v1/expenses/:id/receipt MISSING MISSING ENDPOINT: GET /api/v1/expenses/:id/receipt — Download expense receipt Method: GET Auth: Bearer token Roles: All Rate limit: 100 req/min Response: 200 OK Content-Type: image/jpeg, image/png, or application/pdf Content-Disposition: attachment; filename="receipt-{expenseNumber}.{ext}" Errors: 404 — Expense or receipt not found Purchases Page (/purchases) UI Element Data Required API Endpoint Status (Same as /expenses) Same data as expenses page Same as /expenses COVERED Note: This is an alias route to the expenses page. No additional API endpoints needed. Banking Page (/banking) Accounts Tab UI Element Data Required API Endpoint Status Bank account list List of bank accounts GET /api/v1/bank-accounts COVERED Add bank account Create new bank account POST /api/v1/bank-accounts COVERED Account balance Current balance per account GET /api/v1/bank-accounts (included in response) COVERED Reconcile Tab UI Element Data Required API Endpoint Status Account selector List of bank accounts GET /api/v1/bank-accounts COVERED Unreconciled transactions Unreconciled bank transactions for selected account GET /api/v1/bank-accounts/:id/transactions?reconciled=false COVERED Match confidence Client-side calculation based on amount/date/description N/A (client-side) COVERED Approve match Mark transaction as reconciled POST /api/v1/bank-accounts/:id/reconcile COVERED Link to invoice/expense Link bank transaction to existing record POST /api/v1/bank-accounts/:id/reconcile (with transactionId) COVERED Create new transaction Create GL transaction from unmatched bank transaction POST /api/v1/transactions COVERED Transactions Tab UI Element Data Required API Endpoint Status All bank transactions All bank transactions (paginated) GET /api/v1/bank-accounts/:id/transactions COVERED Reconciliation status filter Bank transactions filtered by reconciled status GET /api/v1/bank-accounts/:id/transactions?reconciled={true|false} COVERED Date range filter Bank transactions filtered by date GET /api/v1/bank-accounts/:id/transactions?fromDate=X&toDate=Y COVERED Import Transactions UI Element Data Required API Endpoint Status Import CSV Upload bank statement CSV POST /api/v1/bank-accounts/:id/import COVERED Reports Hub Page (/reports) UI Element Data Required API Endpoint Status P&L Report preview Profit & Loss data for current month GET /api/v1/reports/profit-loss?from=YYYY-MM-01&to=YYYY-MM-DD COVERED Balance Sheet preview Balance sheet data (coming soon) GET /api/v1/reports/balance-sheet?date=YYYY-MM-DD COVERED Cash Flow preview Cash flow data (coming soon) GET /api/v1/reports/cash-flow?from=X&to=Y COVERED VAT Report preview VAT report data (live at /reports/vat) GET /api/v1/reports/vat?from=X&to=Y COVERED Trial Balance preview Trial balance data (coming soon) GET /api/v1/reports/trial-balance?date=YYYY-MM-DD COVERED General Ledger preview Transaction list (coming soon) GET /api/v1/transactions COVERED VAT Report Page (/reports/vat) Step UI Element Data Required API Endpoint Status 1 Reconciliation status check Count of unreconciled bank transactions GET /api/v1/bank-accounts (aggregate unreconciled count client-side) PARTIAL 2 VAT transaction table All invoices and expenses with VAT for period GET /api/v1/reports/vat?from=X&to=Y COVERED 2 Summary boxes (collected/paid/due) Calculated from VAT transaction data GET /api/v1/reports/vat?from=X&to=Y COVERED 3 VAT return boxes Formatted VAT return data GET /api/v1/reports/vat?from=X&to=Y COVERED 3 Export PDF Generate PDF report GET /api/v1/reports/vat/export/pdf?from=X&to=Y MISSING 3 Export XML Generate XML for e-filing GET /api/v1/reports/vat/export/xml?from=X&to=Y MISSING 3 Submit return Submit VAT return (Phase 2) POST /api/v1/reports/vat/submit MISSING MISSING ENDPOINTS: GET /api/v1/reports/vat/export/pdf — Export VAT report as PDF Method: GET Auth: Bearer token Roles: All Query: from (ISO date), to (ISO date) Rate limit: 50 req/min Response: 200 OK Content-Type: application/pdf Content-Disposition: attachment; filename="vat-report-{from}-{to}.pdf" Errors: 422 — Invalid date range GET /api/v1/reports/vat/export/xml — Export VAT report as XML for e-filing Method: GET Auth: Bearer token Roles: All Query: from (ISO date), to (ISO date) Rate limit: 50 req/min Response: 200 OK Content-Type: application/xml Content-Disposition: attachment; filename="vat-return-{from}-{to}.xml" Errors: 422 — Invalid date range POST /api/v1/reports/vat/submit — Submit VAT return to tax authority (Phase 2) Method: POST Auth: Bearer token Roles: owner, admin, accountant Rate limit: 5 req/min Request: interface SubmitVATRequest { period: string // ISO date range, e.g., "2026-02" confirmationEmail: string } Response: 201 Created interface SubmitVATResponse { submissionId: string submittedAt: string status: 'pending' | 'accepted' | 'rejected' confirmationNumber: string | null } Errors: 400 — VAT period already submitted PARTIAL COVERAGE: Reconciliation status check requires aggregating unreconciled count from GET /api/v1/bank-accounts response. Consider dedicated endpoint: GET /api/v1/bank-accounts/unreconciled-count Returns: { total: number, byAccount: Array<{accountId: string, count: number}> } Settings Page (/settings) Company Section UI Element Data Required API Endpoint Status Company profile form Organization data GET /api/v1/organization COVERED Save company profile Update organization PUT /api/v1/organization COVERED Users Section UI Element Data Required API Endpoint Status User list List of users in organization GET /api/v1/users COVERED Invite user Send user invite POST /api/v1/users/invite COVERED Change user role Update user role PUT /api/v1/users/:id/role COVERED Remove user Delete user DELETE /api/v1/users/:id COVERED Tax & Compliance Section UI Element Data Required API Endpoint Status Tax settings form Tax rate configuration GET /api/v1/settings/tax-rates COVERED Save tax settings Update tax rates PUT /api/v1/settings/tax-rates COVERED VAT registration toggle Update organization (vatNumber field) PUT /api/v1/organization COVERED Integrations Section UI Element Data Required API Endpoint Status Connected integrations List of integrations GET /api/v1/integrations MISSING Available integrations List of integrations GET /api/v1/integrations MISSING Connect integration Connect to integration POST /api/v1/integrations/:id/connect MISSING Disconnect integration Disconnect integration DELETE /api/v1/integrations/:id/disconnect MISSING MISSING ENDPOINTS (Integrations): All integration-related endpoints are missing. These should be Phase 2, but placeholders needed: GET /api/v1/integrations — List integrations Method: GET Auth: Bearer token Roles: All Response: 200 OK interface IntegrationsResponse { connected: Array<{ id: string name: string type: string status: 'active' | 'inactive' | 'error' connectedAt: string lastSync: string | null }> available: Array<{ id: string name: string type: string description: string icon: string }> } POST /api/v1/integrations/:id/connect — Connect integration Method: POST Auth: Bearer token Roles: owner, admin Request body varies by integration type Response: 201 Created DELETE /api/v1/integrations/:id/disconnect — Disconnect integration Method: DELETE Auth: Bearer token Roles: owner, admin Response: 204 No Content Notifications Section UI Element Data Required API Endpoint Status Notification preferences User notification settings GET /api/v1/settings/notifications MISSING Save preferences Update notification settings PATCH /api/v1/settings/notifications MISSING MISSING ENDPOINTS (Notifications): GET /api/v1/settings/notifications — Get notification preferences Method: GET Auth: Bearer token Roles: All Response: 200 OK interface NotificationSettings { email: { invoicePaid: boolean invoiceOverdue: boolean expenseApproved: boolean bankAccountSynced: boolean } inApp: { invoiceUpdates: boolean expenseUpdates: boolean reconciliationMatches: boolean } } PATCH /api/v1/settings/notifications — Update notification preferences Method: PATCH Auth: Bearer token Roles: All Request: Same as GET response (partial updates allowed) Response: 200 OK (updated settings) Security Section UI Element Data Required API Endpoint Status Enable 2FA Enable 2FA for user POST /api/v1/auth/2fa/enable MISSING Disable 2FA Disable 2FA for user DELETE /api/v1/auth/2fa/disable MISSING Session timeout User/org settings GET /api/v1/settings/security MISSING Save security settings Update security settings PATCH /api/v1/settings/security MISSING View audit log Audit trail GET /api/v1/security/audit-log MISSING Request data export Export all data POST /api/v1/security/data-export MISSING Delete company Delete organization DELETE /api/v1/organization MISSING MISSING ENDPOINTS (Security): POST /api/v1/auth/2fa/enable — Enable 2FA Response: QR code + backup codes DELETE /api/v1/auth/2fa/disable — Disable 2FA Requires current password confirmation GET /api/v1/settings/security — Get security settings Returns: session timeout, password policy, etc. PATCH /api/v1/settings/security — Update security settings GET /api/v1/security/audit-log — Get audit log (paginated) Query: fromDate, toDate, userId, action, tableName Response: Paginated list of LoggedAction records POST /api/v1/security/data-export — Request GDPR data export Response: Job ID, email sent when ready DELETE /api/v1/organization — Delete organization (danger zone) Requires password confirmation Cascades to all related data Forms → API Mapping Form Submit Action API Endpoint Request Body Status Create Invoice (Step 6) POST POST /api/v1/invoices CreateInvoiceRequest COVERED Send Invoice (Step 6) POST POST /api/v1/invoices/:id/send SendInvoiceRequest COVERED Add Customer (Wizard Step 1) POST POST /api/v1/contacts CreateContactRequest COVERED Create Expense (Dialog) POST POST /api/v1/expenses CreateExpenseRequest (multipart) COVERED Upload Receipt (Expense Dialog) POST POST /api/v1/expenses (multipart receiptFile) File upload COVERED Update Company Profile PUT PUT /api/v1/organization UpdateOrganizationRequest COVERED Update Tax Settings PUT PUT /api/v1/settings/tax-rates UpdateTaxRatesRequest COVERED Invite User POST POST /api/v1/users/invite InviteUserRequest COVERED Change User Role PUT PUT /api/v1/users/:id/role ChangeRoleRequest COVERED Import Bank Statement POST POST /api/v1/bank-accounts/:id/import CSV file upload COVERED Create Manual Transaction POST POST /api/v1/transactions CreateTransactionRequest COVERED Update Notification Preferences PATCH PATCH /api/v1/settings/notifications NotificationSettings MISSING Update Security Settings PATCH PATCH /api/v1/settings/security SecuritySettings MISSING Connect Integration POST POST /api/v1/integrations/:id/connect Integration-specific MISSING Submit VAT Return POST POST /api/v1/reports/vat/submit SubmitVATRequest MISSING Coverage Summary Page Endpoints Required Endpoints Documented Coverage Dashboard 2 2 100% Invoices List 6 5 83% (missing DELETE) Invoice Wizard 6 6 100% Expenses 7 6 86% (missing receipt download) Purchases 7 6 86% (same as expenses) Banking 7 7 100% Reports Hub 6 6 100% VAT Report 7 4 57% (missing PDF/XML export, submit) Settings - Company 2 2 100% Settings - Users 4 4 100% Settings - Tax 2 2 100% Settings - Integrations 3 0 0% (Phase 2) Settings - Notifications 2 0 0% (missing) Settings - Security 6 0 0% (missing) TOTAL 67 50 75% Missing Endpoints High Priority (Core Features) DELETE /api/v1/invoices/:id — Delete invoice (draft only) Reason: Invoice list has delete action GET /api/v1/expenses/:id/receipt — Download expense receipt Reason: Expense list shows receipt indicator, needs download link GET /api/v1/reports/vat/export/pdf — VAT report PDF export Reason: VAT report has export button (placeholder currently) GET /api/v1/reports/vat/export/xml — VAT report XML export (e-filing) Reason: VAT report has export button (placeholder currently) Medium Priority (Settings) GET /api/v1/settings/notifications — Get notification preferences Reason: Settings page has notification section with checkboxes PATCH /api/v1/settings/notifications — Update notification preferences Reason: Settings page has save button for notification preferences GET /api/v1/settings/security — Get security settings Reason: Settings page has security section (session timeout, password policy) PATCH /api/v1/settings/security — Update security settings Reason: Settings page has save button for security settings GET /api/v1/security/audit-log — Audit log Reason: Settings security section has "View Audit Log" button POST /api/v1/security/data-export — GDPR data export Reason: Settings security section has "Request Data Export" button DELETE /api/v1/organization — Delete company Reason: Settings security section has "Delete Company" button (danger zone) POST /api/v1/auth/2fa/enable — Enable 2FA Reason: Settings security section has "Enable 2FA" button DELETE /api/v1/auth/2fa/disable — Disable 2FA Reason: Settings security section needs disable option if 2FA enabled Low Priority (Phase 2 Features) GET /api/v1/integrations — List integrations Reason: Settings integrations section (Phase 2) POST /api/v1/integrations/:id/connect — Connect integration Reason: Settings integrations section (Phase 2) DELETE /api/v1/integrations/:id/disconnect — Disconnect integration Reason: Settings integrations section (Phase 2) POST /api/v1/reports/vat/submit — Submit VAT return Reason: VAT report has submit button (explicitly marked "Coming in Phase 2") GET /api/v1/bank-accounts/unreconciled-count — Unreconciled transaction count Reason: VAT report Step 1 checks reconciliation status (currently client-side aggregation, could be dedicated endpoint) Redundant Endpoints None identified. All endpoints in API-REFERENCE.md are consumed by at least one frontend page. Recommendations 1. Add Missing Core Endpoints Priority: HIGH Scope: Endpoints 1-4 from Missing Endpoints list These are referenced directly in existing UI actions. Without them, users will encounter broken functionality: Delete invoice button will not work Receipt download links will not work VAT export buttons will not work Implementation Order: DELETE /api/v1/invoices/:id (simplest, no business logic) GET /api/v1/expenses/:id/receipt (file download) GET /api/v1/reports/vat/export/pdf (report generation + PDF library) GET /api/v1/reports/vat/export/xml (report generation + XML serialization) 2. Implement Settings Endpoints Priority: MEDIUM Scope: Endpoints 5-13 from Missing Endpoints list Settings page is fully implemented with save buttons, but no backend to persist data. Current behavior: console.log() only. Recommendation: Implement all settings endpoints together in one feature branch. They share similar patterns (GET/PATCH pairs, org-scoped data). 3. Phase 2 Features as Stubs Priority: LOW Scope: Endpoints 14-18 from Missing Endpoints list These are explicitly marked as "Phase 2" or "Coming Soon" in the UI. Consider implementing stub endpoints that return: 501 Not Implemented Or minimal placeholder data This allows frontend to gracefully handle the "not yet implemented" state without errors. 4. Add Endpoint: GET /api/v1/bank-accounts/unreconciled-count Priority: LOW Scope: New endpoint not in API-REFERENCE.md VAT report Step 1 checks for unreconciled transactions. Currently, frontend must: Fetch GET /api/v1/bank-accounts (all accounts) For each account, fetch GET /api/v1/bank-accounts/:id/transactions?reconciled=false Aggregate counts Recommendation: Add dedicated endpoint to avoid N+1 query pattern. // Proposed endpoint GET /api/v1/bank-accounts/unreconciled-count Response: { total: number byAccount: Array<{ accountId: string accountName: string count: number }> } 5. Verify TypeScript Interface Consistency Priority: MEDIUM API-REFERENCE.md defines TypeScript interfaces for request/response bodies. Frontend uses these types in forms and state management. Action Items: Create shared types package ( packages/types/ ) Export all API interfaces from API-REFERENCE.md Import in both apps/api/ (backend validation) and apps/web/ (frontend forms) Use Zod schemas for runtime validation + TypeScript type generation Example: // packages/types/src/invoice.ts import { z } from 'zod' export const CreateInvoiceRequestSchema = z.object({ customerId: z.string().uuid(), invoiceDate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/), dueDate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/), currencyCode: z.enum(['EUR', 'RSD', 'BAM', 'HRK']).optional(), items: z.array(z.object({ description: z.string().min(1), quantity: z.number().positive(), unitPrice: z.number().nonnegative(), taxRate: z.number().nonnegative(), accountId: z.string().uuid().optional() })), notes: z.string().optional(), terms: z.string().optional() }) export type CreateInvoiceRequest = z.infer Backend uses schema for validation: // apps/api/src/routes/invoices.ts import { CreateInvoiceRequestSchema } from '@bilko/types' router.post('/invoices', async (req, res) => { const data = CreateInvoiceRequestSchema.parse(req.body) // Throws if invalid // ... }) Frontend uses type for forms: // apps/web/app/(dashboard)/invoices/new/page.tsx import { CreateInvoiceRequest } from '@bilko/types' const [formData, setFormData] = useState({...}) 6. Authentication Endpoints Missing from Coverage Priority: HIGH Frontend has no login/register pages yet, but API-REFERENCE.md defines 5 auth endpoints: POST /api/v1/auth/register POST /api/v1/auth/login POST /api/v1/auth/refresh POST /api/v1/auth/logout GET /api/v1/auth/me Recommendation: Create auth pages in Phase 2: /login page /register page /logout action (server action) Auth middleware to protect all /dashboard routes 7. Error Handling Pattern Priority: MEDIUM API-REFERENCE.md defines error response format: interface ApiError { error: string code: string details?: Record } Recommendation: Create frontend error handling utility: // lib/api-error.ts export function handleApiError(error: Response) { const apiError: ApiError = await error.json() if (apiError.details) { // Field-level validation errors return Object.entries(apiError.details).map(([field, errors]) => ({ field, message: errors.join(', ') })) } // Generic error return { message: apiError.error, code: apiError.code } } Use in forms: try { await fetch('/api/v1/invoices', { method: 'POST', body: JSON.stringify(data) }) } catch (error) { const errors = handleApiError(error) // Display errors in form } Implementation Priority Matrix Endpoint Priority Reason Est. Effort DELETE /api/v1/invoices/:id HIGH Invoice list delete button 2h GET /api/v1/expenses/:id/receipt HIGH Receipt download links 3h GET /api/v1/reports/vat/export/pdf HIGH VAT export button 8h GET /api/v1/reports/vat/export/xml HIGH VAT e-filing 6h GET /api/v1/settings/notifications MEDIUM Settings page persistence 3h PATCH /api/v1/settings/notifications MEDIUM Settings page persistence 2h GET /api/v1/settings/security MEDIUM Settings page persistence 4h PATCH /api/v1/settings/security MEDIUM Settings page persistence 3h GET /api/v1/security/audit-log MEDIUM Audit log viewer 5h POST /api/v1/security/data-export MEDIUM GDPR compliance 8h DELETE /api/v1/organization MEDIUM Delete company action 4h POST /api/v1/auth/2fa/enable MEDIUM 2FA setup 8h DELETE /api/v1/auth/2fa/disable MEDIUM 2FA removal 2h GET /api/v1/integrations LOW Phase 2 feature 6h POST /api/v1/integrations/:id/connect LOW Phase 2 feature 12h DELETE /api/v1/integrations/:id/disconnect LOW Phase 2 feature 3h POST /api/v1/reports/vat/submit LOW Phase 2 feature 16h GET /api/v1/bank-accounts/unreconciled-count LOW Performance optimization 3h Total Estimated Effort: 98 hours (~12-15 working days for 1 developer) Suggested Implementation Phases: Phase 2a (Core Features) — 19h DELETE /api/v1/invoices/:id GET /api/v1/expenses/:id/receipt GET /api/v1/reports/vat/export/pdf GET /api/v1/reports/vat/export/xml Phase 2b (Settings Persistence) — 31h All settings endpoints (notifications, security, audit log, data export, org delete, 2FA) Phase 2c (Integrations + VAT Submit) — 37h Integrations endpoints (stub or full implementation) VAT submit endpoint (requires external API integration) Phase 2d (Polish) — 11h Unreconciled count endpoint Shared types package Error handling utilities Auth pages Conclusion Overall Coverage: 75% (50 out of 67 required endpoints documented) API-REFERENCE.md is 75% complete. The 50 documented endpoints cover all core business logic: Invoice CRUD (except delete) Expense CRUD (except receipt download) Banking & reconciliation Reporting (except export formats) User management Organization settings Chart of accounts Transactions Missing 25%: 4 high-priority endpoints (delete invoice, receipt download, VAT exports) 9 medium-priority settings endpoints 4 low-priority Phase 2 endpoints No redundant endpoints. Every endpoint in API-REFERENCE.md is consumed by at least one frontend page. Recommendation: Implement Phase 2a (core features, 19h) before beta launch. Settings persistence can follow in Phase 2b after user feedback. Next Steps: Review this coverage report with team Prioritize missing endpoints based on business needs Update API-REFERENCE.md with missing endpoint specs Create implementation tickets in Mission Control Build apps/api/ following API-REFERENCE.md contract Bilko Authentication -- Entra External ID (CIAM) Overview Bilko uses Microsoft Entra External ID (CIAM) as its sole identity provider. Entra authenticates users; Bilko authorises them. Roles and permissions live exclusively in the Bilko database — no role claims are read from Entra tokens. Decision anchor (ADR): "Entra authenticates, Bilko authorises; single-role v1; multi-org deferred (MC #103089)." Stage status: Live on stage (branch stack WP1–WP4, feat/rbac-wp4-retire-legacy-auth commit 3ac1388). Production cutover pending consolidated PR. Tenant Configuration Field Value Tenant ID 20bb17de-9be5-4143-a7e5-8c1ddae6a064 Display name Bilko CIAM Domain bilkociam.onmicrosoft.com Type Entra External ID (CIAM), EU data residency, Norway Billing MAU — free tier from 2026-06-07 Authority (MSAL) https://bilkociam.ciamlogin.com/20bb17de-9be5-4143-a7e5-8c1ddae6a064 Issuer (exact, from OIDC discovery) https://20bb17de-9be5-4143-a7e5-8c1ddae6a064.ciamlogin.com/20bb17de-9be5-4143-a7e5-8c1ddae6a064/v2.0 JWKS URI https://bilkociam.ciamlogin.com/20bb17de-9be5-4143-a7e5-8c1ddae6a064/discovery/v2.0/keys OIDC discovery https://bilkociam.ciamlogin.com/20bb17de-9be5-4143-a7e5-8c1ddae6a064/v2.0/.well-known/openid-configuration Issuer note: OIDC discovery returns the issuer with the tenant-ID subdomain ( 20bb17de-...ciamlogin.com ), NOT the named subdomain ( bilkociam.ciamlogin.com ). The Kotlin backend ENTRA_EXTERNAL_ID_ISSUER env var MUST match the discovery value exactly. See evidence: /tmp/evidence-103076/phase0-config.md . App Registrations App Client ID Flow Notes Bilko API (resource) fe39e0f5-513e-40af-93f0-c3ee624df56c Exposes scope Scope: access_as_user ; full scope string: api://fe39e0f5-513e-40af-93f0-c3ee624df56c/access_as_user ; audience for token validation = client ID; no client secret (resource app) Bilko Web SPA c2902239-ea63-41bd-8619-6cf096d7d45a PKCE auth code (SPA) Redirects: localhost:3000, stage Cloud Run URL, bilko-demo.alai.no, app.bilko.cloud, app.bilko.io, app.bilko.company (+ /auth/callback variants); no client secret Bilko Mobile (native) 916bb9f3-658d-4729-b5a0-64b1f157c8c2 PKCE auth code (native/public) Redirect: com.alai.bilko://auth , msauth.com.alai.bilko://auth ; isFallbackPublicClient=true ; Expo Go workaround documented in Phase 0 config Secrets (none exist — all public clients). Non-secret configuration is stored in GCP Secret Manager ( bilko-entra-issuer , bilko-entra-audience , bilko-entra-jwks-url ) and Bitwarden ("Bilko CIAM Tenant Config"). Token Claims oid — object ID, immutable cross-app anchor; mandatory identity key (built-in in CIAM, always present) sub — pairwise pseudonymous per app; NOT used as identity anchor (changes on app re-registration). The backend logs a warning when sub != oid (expected in CIAM) and uses oid exclusively. Confirmed live: E2E test showed sub=053nt0lk vs oid=3b53a25a . email / preferred_username — informational only; mutable; NOT used for identity resolution in JWT claims issued by Bilko name , family_name , given_name — optional claims Bilko JWTs issued after exchange use the internal Bilko user UUID as the subject claim — email is not the identity anchor in issued tokens. Authentication Flow — Web (MSAL Direct Bearer) Browser opens login page → MSAL browser ( @azure/msal-browser + @azure/msal-react ) initiates PKCE auth code flow Redirect to bilkociam.ciamlogin.com → user authenticates (email/password or social) → Entra issues auth code MSAL exchanges code for tokens (PKCE, in memory — NOT localStorage) MSAL acquires access token for scope api://fe39e0f5.../access_as_user Web sends Authorization: Bearer to Kotlin API Kotlin EntraExternalIdService.verifyIdToken() validates: RS256 signature via live JWKS, issuer exact match, audience = fe39e0f5... , oid claim present, JWKS URL domain-pinned to ciamlogin.com / microsoftonline.com JIT provisioning or email-match link (see JIT section below) → Bilko session returned Session cookie ( SameSite=Lax , httpOnly) established; subsequent requests use Bilko refresh token Sign-out calls Entra logout endpoint to invalidate Entra session + clears local cookie Authentication Flow — Mobile (Token Exchange) Expo native app initiates PKCE via expo-auth-session / useEntraAuthRequest Redirect to Entra → auth code returned to com.alai.bilko://auth MSAL exchanges code; id_token (not access_token) sent to POST /auth/entra/session Kotlin backend verifies id_token, runs JIT provisioning or link, returns { accessToken, refreshToken } Tokens stored in SecureStore; TTL aligns with Bilko 7-day refresh token window JIT Provisioning + Identity Linking Implemented in AuthService.createSessionFromEntraIdToken() (SERIALIZABLE transaction — martin-kleppmann race-prevention mandate): Lookup entra_external_identities by issuer + oid . If found: return session. If not found: email-match lookup in users (case-normalised, lowercase both sides). If unique match and email_verified : insert entra_external_identities row, log audit event entra_jit_link , return session. If no match: call UserProvisioningService.provisionNewUserForEntra() — creates a new org + user with role viewer + inserts entra_external_identities row. New user must be promoted by an admin. Design dissent on record (martin-kleppmann + bruce-momjian): email-match JIT is risky if email is mutable or duplicate. Pre-provision by OID (via admin invite or MS Graph export script) is the safer path. JIT email-match is constrained with a serializable transaction as a partial mitigation. The pre-provision script path (D8 in MC #103075) is the recommended path for production migration. JWKS Cache + Key Rotation EntraExternalIdService maintains a time-bound JWKS key cache: TTL: 12 hours per key (stored as Pair ; evicted on read if age > 12h) On kid miss: force re-fetch regardless of other cached keys JWKS URL domain-pinned: must match ^https://([a-z0-9-]+\.)*ciamlogin\.com/ or ^https://login\.microsoftonline\.com/ Startup fail-closed: if ENTRA_EXTERNAL_ID_ISSUER set but any config absent or URL fails domain assertion → IllegalStateException at Ktor module init (not lazy 503) JWKS verification: live E2E test confirmed 6 RSA keys, all kty=RSA , TLS valid 2026-11-22 Refresh Token Revocation (Known Limitation) Bilko refresh tokens are 7-day HMAC validated locally. A disabled Entra account remains valid in Bilko for up to 7 days. Open CEO/Securion decision (OC#4 from MC #103075): Option A (not yet implemented): revalidate Entra account status on every refresh (~50ms latency) Option B (current default): 7-day window; immediate revocation requires an admin to also disable the user in the Bilko DB. Documented as a risk-acceptance decision in AuthService.kt (code comment references MC #103075). Legacy Email/Password — RETIRED (410 Gone) As of branch feat/rbac-wp4-retire-legacy-auth (commit 3ac1388), the following endpoints return HTTP 410 Gone with body {"code":"ENDPOINT_RETIRED"} : POST /auth/register POST /auth/login POST /auth/forgot-password GET /auth/reset-password POST /auth/reset-password Kept active: POST /auth/entra/session , POST /auth/refresh , POST /auth/mobile/refresh , POST /auth/2fa/challenge . Web login page: email/password form removed; Entra primary CTA only. Self-serve register page removed; shows "contact your admin" message. Forgot/reset password redirects to Entra SSPR portal. Break-glass (first-admin bootstrap): run ./gradlew :apps:api:bootStrapAdmin with BOOTSTRAP_ADMIN_EMAIL + BOOTSTRAP_ADMIN_PASSWORD env vars. Calls AuthService.register() directly; no HTTP endpoint exposed. Phase 0–4 Deployment Facts Phase / WP Scope Branch Status Phase 0 (MC #103076) CIAM tenant provisioning, 3 app registrations, JWKS verification FlowForge standalone DONE — stage live WP1 (MC #103141) RBAC permissions catalog V67, PermissionService, BilkoPrincipal, requirePermission, 204 matrix tests feat/rbac-wp1-permissions-catalog DONE — Proveo PARTIAL (integration test fix applied post-verification) WP2 (MC #103142) JIT provisioning V68, UserProvisioningService, admin/invite API, role-assign endpoint feat/rbac-wp2-user-provisioning DONE — Proveo PASS WP3 (MC #103143) Web: Entra primary CTA, register retired, forgot/reset SSPR, RBAC admin UI feat/rbac-wp3-web-entra-ui DONE — Proveo PASS WP4 (MC #103144) Retire legacy endpoints (410), web login Entra-only, break-glass documented feat/rbac-wp4-retire-legacy-auth DONE — Proveo PASS WP5 (MC #103145) E2E: live CIAM token, OID anchor, JIT provision, RBAC enforcement, invalid token rejection feat/rbac-wp3-web-entra-ui DONE — PASS (browser MSAL flow deferred to Proveo pre-prod) Evidence bundles: /tmp/evidence-103141 through /tmp/evidence-103145 , /tmp/evidence-103076/phase0-config.md . Bilko RBAC -- Users / Roles / Permissions Overview Bilko uses a flat RBAC model : users have one role per organisation; roles map to a permission catalog via a DB seed table. Permission resolution is live from the database on every request (no JWT role claim for authorisation). The system was built in WP1 (MC #103141, branch feat/rbac-wp1-permissions-catalog ). Roles Role Level Scope owner 3 All permissions including billing, account deletion, user management admin 2 All permissions except billing and account deletion; can manage users and roles accountant 1 Create and manage financial records; cannot delete; cannot manage users viewer 0 Read-only access; default for newly JIT-provisioned Entra users Roles are stored in users.role (VARCHAR 50) with a CHECK constraint added in V67 limiting values to these four. Single role per user per organisation (multi-role/multi-org deferred, MC #103089). Permissions Catalog (V67 — 52 keys) Source: apps/api/src/main/resources/db/migration/V67__rbac_permissions_catalog.sql (commit 66629bd). The catalog is stored in the permissions table; all application code references permission keys as string constants. Format: : enforced by a DB CHECK constraint ( permission_key_format ). Example keys by resource group: Resource group Example keys Invoices invoice:read , invoice:create , invoice:update , invoice:delete , invoice:submit Expenses expense:read , expense:create , expense:update , expense:delete Contacts contact:read , contact:create , contact:update , contact:delete Transactions transaction:read , transaction:create , transaction:reconcile Reports report:read , report:export Settings / billing settings:read , settings:update , billing:read , billing:update Users users:read , users:manage , users:invite Account admin account:delete Documents document:read , document:upload , document:delete Articles / products article:read , article:create , article:update , article:delete Full 52-key baseline stored in: apps/api/src/main/resources/rbac/requireRole-baseline-v67.tsv (commit 0bf18fd, 51 data rows). Role-to-Permission Seed (Strategy A — Flat Inheritance) Source: role_permissions table seeded in V67. Each row: (role, permission_key) . No runtime inheritance logic — the seed embeds the full flattened set for each role. Role Permissions count Principle viewer 13 Read-only: all :read + :export keys accountant 40 viewer permissions + create/update on financial resources; no delete, no user management admin 49 accountant permissions + delete + user management; no billing:update, no account:delete owner 52 All 52 permissions (complete set) The seed exactly reproduces the behaviour of the legacy requireRole() numeric hierarchy — verified by 204 RbacMatrixTest cases (0 failures). No behaviour regression. PermissionService — Live DB Resolution Source: apps/api/src/main/kotlin/no/alai/bilko/services/PermissionService.kt (commit dee4fb1) Interface method: fun resolve(role: String): Set (2 implementations: interface + DbPermissionService ) Live DB query against role_permissions on every resolve call Fail-closed: if role is unknown or DB returns empty set, resolves to emptySet() — no permissions granted CEO OCD O1 decision: global per-role cache (4 known values); result cached per role string key. Per CEO spec, cache keyed userId+role-version was the ideal; current implementation uses global per-role cache (simpler, advisory gap noted in Proveo verdict) BilkoPrincipal + requirePermission Source: apps/api/src/main/kotlin/no/alai/bilko/auth/BilkoPrincipal.kt and RbacHelper.kt (commit dee4fb1) BilkoPrincipal carries permissions: Set — resolved at authentication time via PermissionService RoutingContext.requirePermission(permissionKey: String) — Kotlin extension function; throws ForbiddenException (HTTP 403 BILKO-AUTH-003 ) if key not in principal's permission set; calls AuthzAuditLogger All 51 formerly- requireRole() call sites migrated to requirePermission() (17 route files, 0 residual requireRole in routes — verified by grep) requireRole() is kept as a thin compatibility shim (RbacHelper.kt) Role-to-Permission Matrix Permission key viewer accountant admin owner invoice:read Y Y Y Y invoice:create - Y Y Y invoice:update - Y Y Y invoice:delete - - Y Y invoice:submit - Y Y Y expense:read Y Y Y Y expense:create - Y Y Y expense:delete - - Y Y users:read Y Y Y Y users:manage - - Y Y users:invite - - Y Y billing:read - - - Y billing:update - - - Y account:delete - - - Y settings:read Y Y Y Y settings:update - - Y Y report:read Y Y Y Y report:export Y Y Y Y ... (52 total) Full catalog in V67 seed Full read-only matrix visible to admins/owners in the web admin UI at /admin/users (component: lib/permissions.ts ROLE_PERMISSION_MATRIX ). Authorization Audit Log Source: apps/api/src/main/kotlin/no/alai/bilko/auth/AuthzAuditLogger.kt (commit dee4fb1) Every requirePermission() call logs an authz_decision event (SLF4J structured log) Log fields: userId , orgId , permissionKey , granted (boolean), route RbacHelper.kt references AuthzAuditLogger at 4 call sites (verified) V67/V68 Migration Summary Migration Contents V67 ( V67__rbac_permissions_catalog.sql ) Creates permissions table (52 keys, format CHECK); role_permissions table with full 4-role seed; adds users.role CHECK constraint; GRANT SELECT to bilko_app; no RLS (global catalog) V68 ( V68__rbac_user_provisioning.sql ) Adds users:manage and users:invite permission keys; SECURITY DEFINER function bilko_auth.provision_user_with_org(issuer, oid, email, fullName) returning new user UUID; seeds new permissions to admin + owner roles Test Coverage 204 RbacMatrixTest cases (all 51 call sites x 4 roles): 0 failures — feat/rbac-wp1-permissions-catalog 8 UserProvisioningWp2Test cases (T1–T8: JIT, admin CRUD, role guards, self-escalation block): PASS Total test suite: 2534 tests (1070 unit + 1283 integration + 181 web), 0 failures — WP5 E2E evidence /tmp/evidence-103145 Out of Scope (v1) Multi-role per user (single role per org; MC #103089) Multi-org membership (single org per user; MC #103089) ABAC / conditional permissions (e.g. "delete only own drafts") Accountant Portal multi-tier permissions (Collaborator/Approver roles from ACCOUNTANT-PORTAL-SPEC.md §2.2) Bilko Auth Migration Runbook + Admin Guide Scope This runbook covers: (1) how an operator bootstraps the first admin after legacy auth is retired, (2) how an admin creates and invites users, (3) how to assign and change roles, (4) the full user lifecycle via Entra, (5) migration notes from the WP1–WP4 branch stack. For architecture detail see Bilko Authentication — Entra External ID (CIAM) . 1. Bootstrapping the First Admin (Break-Glass) After legacy /auth/register is retired (HTTP 410), there is no HTTP endpoint for creating the first user. Use the Gradle break-glass task: # Set environment variables (never commit these): export BOOTSTRAP_ADMIN_EMAIL="admin@yourorg.com" export BOOTSTRAP_ADMIN_PASSWORD="" # Run from the api project root: cd apps/api ./gradlew :apps:api:bootStrapAdmin What this does: calls AuthService.register() directly (bypasses HTTP routing), creates an organisation + owner user. No HTTP endpoint is exposed — zero backdoor surface. The temporary password should be rotated immediately via Entra SSPR after the admin first signs in. Full runbook file: apps/api/docs/runbooks/BREAK-GLASS-BOOTSTRAP.md (on branch feat/rbac-wp4-retire-legacy-auth ). 2. Creating / Inviting Users (Admin Flow) User creation is now admin-gated. Self-serve registration is retired. Via API POST /api/v1/admin/users Authorization: Bearer { "email": "newuser@example.com", "fullName": "Full Name", "role": "viewer" // viewer | accountant | admin | owner } Response: HTTP 201 Created — returns the new user object including their UUID. The user receives an invite; they sign in via Entra (JIT provisioning links their Entra identity on first sign-in). Permission required users:manage — held by admin and owner roles. Via Web Admin UI Sign in as admin or owner Navigate to Settings > Users ( /admin/users ) Click Invite User Enter email, full name, and select role Submit — user receives invite email (Entra CIAM invitation flow) Viewers and accountants see a redirect to the dashboard if they navigate to /admin/users . 3. Assigning / Changing Roles Via API PUT /api/v1/users/:id/role Authorization: Bearer { "role": "accountant" // viewer | accountant | admin | owner } Constraints enforced: Caller must have users:manage permission (admin+) A user cannot change their own role (self-escalation blocked — HTTP 403) The owner role cannot be changed via this endpoint (owner is protected in SettingsService.changeUserRole() ) Invalid role values return HTTP 400 Via Web Admin UI Navigate to Settings > Users Find the user row Click the role dropdown (visible to admin/owner only) Select the new role — saved immediately via PUT /users/:id/role 4. User Lifecycle Admin creates user via POST /admin/users (role = viewer by default, or specified role) User receives Entra invite (email from bilkociam.onmicrosoft.com ) First sign-in : user clicks Entra sign-in on Bilko web login → authenticates in Entra CIAM → Bilko backend calls createSessionFromEntraIdToken() : Looks up entra_external_identities by oid → not found (first login) Email-match lookup → finds pre-created user → inserts entra_external_identities row (JIT link) → audit event entra_jit_link Bilko session returned; user is logged in as viewer Admin promotes role if needed via PUT /users/:id/role Subsequent logins : Entra → backend finds entra_external_identities by oid → direct session, no email-match step Sign-out : MSAL calls Entra logout endpoint → Entra session invalidated → Bilko session cookie cleared → next visit redirects to Entra login 5. What Changed — Migration Notes (WP1–WP4) Area Before (pre-WP1) After (WP1–WP4) Backend auth enforcement requireRole("admin") inline in 51+ route handlers requirePermission("invoice:create") via extension fn; 0 residual requireRole in routes Permission data No tables; hardcoded numeric hierarchy in RbacHelper V67: permissions table (52 keys), role_permissions seed; V68: provisioning function User provisioning Self-serve POST /auth/register Admin invite ( POST /admin/users ) + JIT Entra link on first sign-in; UserProvisioningService Web login Email/password form + "Sign in with Microsoft" coexisting Entra-only CTA; no email/password form; register page shows "contact your admin" Legacy endpoints Active: /auth/login, /auth/register, /auth/forgot-password, /auth/reset-password HTTP 410 Gone + ENDPOINT_RETIRED body Password reset Email-based reset-password flow (V57 table) Redirect to Entra SSPR portal (self-service via Microsoft account) RBAC admin UI No UI; role changes required direct DB query Web: Settings > Users page with role dropdown (admin/owner only) 6. Branch Stack (WP1–WP4 Stacked PRs) WP Branch Latest commit Key files WP1 — RBAC catalog feat/rbac-wp1-permissions-catalog 890168d (last route commit) V67 migration, PermissionService, BilkoPrincipal, RbacHelper, 17 route files migrated WP2 — Provisioning feat/rbac-wp2-user-provisioning a9fa67c V68 migration, UserProvisioningService, UserManagementRoutes WP3 — Web UI feat/rbac-wp3-web-entra-ui 3c1c019 login/page.tsx (Entra CTA), register/page.tsx (retired), admin/users/page.tsx, lib/permissions.ts WP4 — Retire legacy feat/rbac-wp4-retire-legacy-auth 3ac1388 AuthRoutes.kt (5 x 410), api.ts (removed methods), auth-store.ts, 13 new web tests These branches are stacked and NOT yet merged to main. Production cutover requires a consolidated merge PR after CEO/Securion sign-off. 7. Rollback Procedure If the consolidated deploy to main needs to be rolled back: Feature flag path (if FEATURE_ENTRA_AUTH_ENABLED env var is present): set to false to re-enable the password auth provider path (AuthProvider interface, D5 in MC #103075) Hard rollback : revert to the pre-WP1 commit; Flyway handles down-migration if reversible V67/V68 down scripts were authored (check migration files) password_hash : column was made nullable in V66 but existing rows retain their hash values — password-based login can be re-enabled without data loss during the rollback window Password reset tokens table (V57) : NOT dropped. Must not be dropped until the rollback window closes (minimum 30 days post-production cutover). See D8 plan in MC #103075 Entra disable : if Entra must be disabled urgently, also disable users in Bilko DB to enforce immediate revocation (7-day refresh window caveat — OC#4) 8. Database Schema Reference Table Key columns Notes users id , organization_id , email , password_hash (nullable), role (CHECK owner|admin|accountant|viewer), two_factor_* V66: password_hash nullable; V67: role CHECK added entra_external_identities issuer , subject (= oid), user_id , last_login_at V64: created; UNIQUE(issuer,subject), UNIQUE(user_id,issuer); RLS: V66 permissions permission_key (PK, CHECK format) V67: 52 keys; global catalog, no RLS, GRANT SELECT bilko_app role_permissions role , permission_key V67: exhaustive flat seed; V68: users:manage + users:invite added Evidence Files WP1 verification: /tmp/evidence-103141/wp1-verification.md , proveo-verdict.json WP2 verification: /tmp/evidence-103142/wp2-verification.md , proveo-wp2-verdict.json WP3 Proveo: /tmp/evidence-103143/proveo-wp3-validation.md WP4 verification: /tmp/evidence-103144/wp4-verification.md WP5 E2E: /tmp/evidence-103145/wp5-e2e.md , verification.json Phase 0 config: /tmp/evidence-103076/phase0-config.md ADR-037 -- Entra Authenticates, Bilko Authorises; Single-Role v1; Multi-Org Deferred ADR-037 — Entra Authenticates, Bilko Authorises; Single-Role v1; Multi-Org Deferred Field Value ADR number ADR-037 Date 2026-06-08 Status Accepted Author John (AI Director, ALAI Holding AS) CEO decision Alem Basic — confirmed 2026-06-07 (CEO resolution addendum, MC #103075) Related MCs MC #103075 (Entra migration plan), MC #103141–103146 (WP1–WP6 execution), MC #103089 (multi-org, parked) Supersedes Existing inline requireRole() pattern (pre-WP1) Context Bilko had a custom email/password authentication system and a simple numeric role hierarchy ( requireRole() inline in route handlers). No permission catalog, no RBAC tables, no admin UI for user management. The CEO decision (June 2026) was to: Replace email/password authentication with Microsoft Entra External ID (CIAM) — hard REPLACE, not phased coexist Build a real permission-catalog RBAC system with a DB-backed role-to-permission mapping Multiple design forks were evaluated by a multi-agent panel (Parisa Tabriz, Martin Kleppmann, Petter Graff, Bruce Momjian, Devils Advocate — MC #103075 forged prompt). Key unresolved tensions: web direct-bearer vs exchange, email-match JIT vs pre-provision-by-oid, roles-in-Entra-claims vs roles-in-Bilko-DB. Decision D1 — Identity Provider Boundary Entra External ID (CIAM) authenticates. Bilko authorises. Entra issues tokens; Bilko backend validates JWKS RS256 signature, issuer, audience Bilko reads oid from the Entra token as the sole identity anchor ( sub is pairwise-pseudonymous per app and must NOT be used) Bilko issues its own access + refresh tokens after Entra token exchange; downstream services consume Bilko tokens, not Entra tokens directly Role and permission data live in users.role + role_permissions (Bilko DB). No role or permission claims are read from Entra tokens D2 — Single Role per User per Organisation (v1) One role per user per org: owner | admin | accountant | viewer . The role is stored in users.role (single column). Multi-role per user and multi-org membership are explicitly deferred to a separate epic (MC #103089). Rationale: zero live clients; single-org Entra tenant; keep scope tightly bounded; multi-org requires a organization_members join table and CIAM tenant model decisions that are not yet resolved. D3 — Permission Catalog in DB; Flat Inheritance Seed A permissions catalog table (52 keys, resource:verb format enforced by CHECK) and a role_permissions mapping table (V67) replace the inline requireRole() calls. Seed strategy: flat exhaustive rows per role (Strategy A) — no runtime hierarchy derivation. The seed exactly reproduces existing behaviour (no regression — verified by 204 RbacMatrixTest cases). D4 — Live DB Permission Resolution; Fail-Closed PermissionService.resolve(role) queries role_permissions at request time. Unknown role resolves to emptySet() (no permissions). BilkoPrincipal carries the resolved permission set. All route-level checks use requirePermission("resource:verb") . D5 — Multi-Org Deferred Entra CIAM is provisioned as a single tenant. JIT provisioning assigns a new Entra user to one Bilko organisation. Multi-org (one user in multiple orgs) requires: a organization_members join table, per-org permission resolution, and CIAM tenant model decisions. All deferred to MC #103089. Consequences Positive Authentication complexity moved to Microsoft (password policies, MFA, SSPR, account lifecycle) Bilko no longer stores password hashes for new users ( password_hash is nullable) Permission model is auditable and admin-configurable without code changes (role-to-permission seed is data) Authz decisions are logged ( AuthzAuditLogger ) for incident investigation Admin UI for user + role management (no more raw SQL for role changes) Negative / Trade-offs Entra CIAM has MAU-based pricing; cost gate was raised (OC#1, MC #103075) — free tier starts June 2026 7-day refresh token revocation window: a disabled Entra account remains valid in Bilko for up to 7 days (documented risk OC#4; mitigation: admin disables user in Bilko DB) Email-match JIT carries race risk if email is mutable or duplicated (martin-kleppmann + bruce-momjian dissent on record); serializable transaction is a partial mitigation; pre-provision-by-OID is the recommended production path Single-role v1 limits fine-grained delegation scenarios (e.g. "viewer + approve-only on specific documents") — documented as out of scope Alternatives Considered Alternative Rejected reason Roles in Entra claims (Entra app roles) Couples authorisation to IdP; role changes require Entra admin action not Bilko admin action; prevents clean multi-IdP future. Rejected per petter-graff + parisa-tabriz panel consensus. Phased coexist (email/password + Entra in parallel for 2+ weeks) CEO confirmed hard REPLACE. Panel devils-advocate raised phased coexist as safer; CEO re-confirmed hard REPLACE given zero live users. AuthProvider interface (D5 MC #103075) technically enables a revert if needed. Denormalised entra_oid on users table (bruce-momjian alternative) Separate-table V64 model kept; enables multi-IdP future; join cost is negligible at current scale. Fork preserved but not resolved — separate-table remains. ABAC / policy engine (v1) Premature for current scale and requirements; adds complexity; deferred as explicit out-of-scope with comment in plan. Open Decisions Not Resolved by This ADR OC#4 — Refresh revalidation vs risk acceptance: Option A (revalidate Entra account status on refresh, ~50ms) vs Option B (7-day window, documented risk). Requires CEO/Securion explicit decision. Code stub for Option A is in AuthService.kt referencing MC #103075. OC#2 — Hard REPLACE confirmed but AuthProvider interface (D5, MC #103075) enables reversion if needed. Web direct-bearer vs mobile exchange (parisa-tabriz dissent LIVE): Web: MSAL acquires Entra access token, sends as Bearer to API. Mobile: id_token exchange at POST /auth/entra/session . Web direct-bearer is implemented; exchange path preserved as commented stub per spec. Document Links Bilko Authentication — Entra External ID (CIAM) Bilko RBAC — Users / Roles / Permissions Bilko Auth Migration Runbook + Admin Guide Source plan: /Users/makinja/system/specs/bilko-web-entra-cutover-and-rbac-plan-2026-06-08.md Forged prompt (panel dissent log): /Users/makinja/system/prompts/forged/103075.md Phase 0 config: /tmp/evidence-103076/phase0-config.md Bilko Self-Serve Trial — CIAM Architecture and Auth Pattern (MC #103232) Bilko Self-Serve Trial — CIAM Architecture & Auth Pattern MC: #103232 | Status: LIVE — Proveo 11/11 PASS | Last updated: 2026-06-09 | Securion verdict: LAUNCH WITH CONDITIONS 1. Overview A prospect navigates to app.bilko.cloud (or bilko-demo.alai.no ), clicks "Sign in or create a free account with your email" , and completes a Microsoft CIAM Email-OTP sign-up. On first login, the backend JIT-provisions an empty Bilko organisation with a 7-day trial directly on the real production database ( bilko-demo-db ). There is no separate demo build, no invite-only flow, and no org Microsoft account required — any personal email address works. The deployment target is the standard bilko-main-deploy semver-tag trigger. Stage and demo share the same Kotlin/Ktor binary and the same database instance (multi-tenant via RLS). The CIAM tenant ( bilkociam ) is a dedicated Microsoft Entra External ID tenant, completely separate from the Bilko staff Entra tenant. 1.1 Flow diagram Prospect → app.bilko.cloud/login → "Sign in with Microsoft" (MSAL redirect) → bilkociam.ciamlogin.com [BilkoSignUpSignIn user flow] → Email OTP verification (8-digit code, ~6s delivery) → Consent pages (2 pages on first login only) → Redirect to app.bilko.cloud/auth/callback → MSAL: LOGIN_SUCCESS fires, payload.idToken available → POST /auth/entra/session { idToken } [B1 exchange fix] → bilko-api: JWKS RS256 verify → OID lookup → JIT provision → Response: Bilko HMAC JWT + org { trialEndsAt } → setAuthFromRegistration() [B1.2 session fix] → checkAuth() in-memory JWT fast-path [B1.3 session fix] → /dashboard — empty org, trial active ("Probno: 6 dana preostalo") 2. CIAM Tenant Configuration Property Value Tenant name bilkociam Tenant ID 20bb17de-9be5-4143-a7e5-8c1ddae6a064 Tenant type CIAM (Entra External ID) SPA app name Bilko Web (SPA) SPA client ID c2902239-ea63-41bd-8619-6cf096d7d45a API resource app ID fe39e0f5-513e-40af-93f0-c3ee624df56c Authority URL https://20bb17de-9be5-4143-a7e5-8c1ddae6a064.ciamlogin.com/20bb17de-9be5-4143-a7e5-8c1ddae6a064/v2.0 OIDC issuer same as authority URL (confirmed via discovery endpoint) 2.1 User flow: BilkoSignUpSignIn Property Value Flow ID aa86084b-01dc-453f-9e10-679dfefdd824 Type externalUsersSelfServiceSignUpEventsFlow Display name BilkoSignUpSignIn Identity provider EmailOtpSignup-OAUTH (Email One Time Passcode) isSignUpAllowed true userTypeToCreate member (not guest) Attributes collected email (auto-filled by OTP verification) Linked app c2902239-ea63-41bd-8619-6cf096d7d45a (Bilko Web SPA) Authority URL note: Unlike Azure AD B2C, Entra External ID CIAM does not require a user flow policy name suffix in the authority URL. The BilkoSignUpSignIn flow is applied automatically at the tenant level when the SPA app is linked to it. The deployed authority URL requires no changes. 2.2 Registered SPA redirect URIs https://app.bilko.cloud/auth/callback and https://app.bilko.cloud https://app.bilko.company/auth/callback and https://app.bilko.company https://app.bilko.io/auth/callback and https://app.bilko.io https://bilko-demo.alai.no/auth/callback and https://bilko-demo.alai.no https://bilko-web-stage-dh4m46blja-lz.a.run.app/auth/callback and .a.run.app http://localhost:3000/auth/callback and http://localhost:3000 2.3 Adding identity providers or attributes To add social identity providers (Google, Apple) or additional signup attributes (e.g. display name, company name): Microsoft Entra admin centre → External Identities → User flows → BilkoSignUpSignIn → Identity providers / Attributes. No code changes or redeploys are required for attribute-only changes. Adding a social provider requires app registration on the provider side and linking in the CIAM tenant. 3. Auth Flow — The Hard-Won Pattern This section documents three bugs that were discovered and fixed during Proveo E2E validation (MC #103232 WS-V). The fixes are canonical — do not revert them. B1 — Token exchange (commit 660f410, tag v0.2.45) Problem: MSAL's LOGIN_SUCCESS event fires with an Entra access_token (RS256, Microsoft-issued). The original code set this directly as the API Bearer header. The Bilko API validates HMAC256 JWTs only — all calls returned 401. Fix: After MSAL fires, pass payload.idToken (not payload.accessToken ) to a POST /auth/entra/session { idToken } call. The backend verifies the CIAM RS256 idToken via JWKS, looks up or JIT-provisions the user, and returns a Bilko HMAC JWT. // apps/web/lib/msal/msal-provider.tsx — corrected token selection const idToken = payload.idToken ?? payload.accessToken if (idToken) { handleEntraLogin(idToken) } // apps/web/lib/msal/use-entra-auth.ts — exchange call const sessionResult = await api.auth.entraSession(idToken) const bilkoJwt = sessionResult?.tokens?.accessToken setAccessToken(bilkoJwt) B1.2 — Session persistence via setAuthFromRegistration (commit e1e31c5, tag v0.2.46) Problem: After the B1 exchange, checkAuth() was called to hydrate the store. checkAuth() internally calls POST /auth/refresh using the httpOnly refresh-token cookie. The CIAM exchange path does not set a cookie — so /auth/refresh returned 401, which cleared the Bilko JWT and redirected back to /login . Fix: Replace the checkAuth() call in handleEntraLogin with setAuthFromRegistration() , which hydrates the Zustand auth store directly from the /auth/entra/session response body. No cookie round-trip needed. // apps/web/lib/msal/use-entra-auth.ts — hydrate from session response const { setAuthFromRegistration } = useAuthStore.getState() setAuthFromRegistration({ user: sessionResult.user, organization: sessionResult.organization, tokens: { accessToken: bilkoJwt }, }) // Navigate to /dashboard — Bilko JWT is in-memory Bearer B1.3 — checkAuth in-memory JWT fast-path (commit 30a8c85, tag v0.2.47) Problem: Even with B1.2, setAuthFromRegistration() in handleEntraLogin correctly set isAuthenticated=true . However, AuthProvider mounts on every protected route and calls checkAuth() . That call hit /auth/refresh (cookie path) → 401 → store reset to unauthenticated → redirect to /login on every page navigation. Fix: Added an in-memory JWT fast-path at the top of checkAuth() in auth-store.ts . If a Bilko JWT is already in memory (set via the CIAM exchange), checkAuth() uses GET /auth/me with that Bearer token instead of falling through to the cookie-refresh path. // apps/web/lib/stores/auth-store.ts — in-memory fast-path checkAuth: async () => { const inMemoryToken = getAccessToken() if (inMemoryToken) { try { const me = await api.auth.me() set({ isAuthenticated: true, isLoading: false, user: { ...me, name: me.fullName }, organization: me?.organization ?? null }) return true } catch { set({ isAuthenticated: false, isLoading: false, user: null, organization: null }) return false } } // Original cookie-refresh fallback (unchanged — non-CIAM sessions) ... } ENTRA_EXTERNAL_ID_AUDIENCE — critical build var (fixed in v0.2.47 trigger update) Problem: The bilko-main-deploy Cloud Build trigger had _ENTRA_EXTERNAL_ID_AUDIENCE set to fe39e0f5 (the API resource app ID). This is wrong — the CIAM idToken audience is the SPA client ID ( c2902239 ), because MSAL requests id_tokens scoped to the requesting app. Every new deploy reverted the Cloud Run env to the wrong value, requiring a manual patch. Fix: The trigger substitution was updated: # infrastructure/gcp/cloudbuild.yaml — correct value _ENTRA_EXTERNAL_ID_AUDIENCE: c2902239-ea63-41bd-8619-6cf096d7d45a # SPA client ID # NOT: fe39e0f5-513e-40af-93f0-c3ee624df56c (that is the API resource app — wrong for idToken aud) This is now stable in the trigger — it will not revert on future deploys. 4. Backend JIT Provisioning 4.1 Database migrations (Flyway V66–V69) Migration Purpose V66__entra_rls_and_password_nullable.sql Makes password_hash nullable (Entra-only users have no password). Adds RLS policy on entra_external_identities (FORCE + fail-closed). Adds CHECK constraint: issuer must not end with trailing slash. V67__rbac_permissions_catalog.sql RBAC permissions catalog seeding. V68__rbac_user_provisioning.sql SECURITY DEFINER function bilko_auth.provision_user_with_org() : creates org (7-day trial, trial_starts_at , trial_ends_at = now() + 7 days ), creates user ( role='viewer' , password_hash=NULL ), inserts entra_external_identities row (issuer, OID, user_id). Default: country='BA' , currency='BAM' . V69__fix_provision_rls.sql RLS fix: calls set_config('app.current_org_id', v_org_id, true) before the users INSERT so that RLS policies on the users table pass during JIT provisioning. 4.2 JIT provisioning call flow POST /auth/entra/session { idToken } → EntraExternalIdService.verifyIdToken() [RS256, JWKS, issuer+audience+exp] → AuthUserRepository.findByEntraIdentity(issuer, oid) → null (new user) → AuthUserRepository.findByEmail(email) → null (no existing Bilko account) → UserProvisioningService.provisionNewUserForEntra() → bilko_auth.provision_user_with_org() [SECURITY DEFINER, SERIALIZABLE] → INSERT organizations (trial 7 days) → INSERT users (role=viewer, password=null) → INSERT entra_external_identities (issuer, oid) → jwtService.signAccessToken(userId, email, role='viewer', orgId) → Response: { user, organization { trialEndsAt }, tokens { accessToken, refreshToken } } Idempotency: Re-login with the same OID returns the existing user and org; trial end date is not reset. The entra_external_identities table has a UNIQUE constraint on (issuer, subject) . 4.3 trialEndsAt in /auth/me The GET /auth/me response includes organization.trialEndsAt (ISO 8601). The frontend auth store exposes this on the Organization interface. The trial expiry is enforced server-side by TrialGatePlugin which queries the DB on every gated request — the JWT does not embed expiry. 4.4 RLS isolation All JIT-provisioned tenants are isolated via PostgreSQL Row Level Security. The app.current_org_id session variable is set by OrgScopePlugin from the BilkoPrincipal (JWT-derived, not from any HTTP header). orgTransaction() uses SET LOCAL scoped to the transaction — connection pool does not carry state between requests. Cross-tenant isolation is verified by RlsOrgIsolationV46IntegrationTest . 5. Deploy Property Value Deploy trigger bilko-main-deploy (europe-north1, project tribal-sign-487920-k0) Trigger type semver tag on main: git tag vX.Y.Z && git push origin vX.Y.Z Config infrastructure/gcp/cloudbuild.yaml Current live tag v0.2.47 (commit 30a8c85) Web revision bilko-web-demo-00080-tq5 API revision bilko-api-demo-00155-524 CIAM env vars in trigger NEXT_PUBLIC_ENTRA_CLIENT_ID, NEXT_PUBLIC_ENTRA_AUTHORITY, NEXT_PUBLIC_ENTRA_SCOPE, ENTRA_EXTERNAL_ID_ISSUER, ENTRA_EXTERNAL_ID_AUDIENCE (= c2902239), ENTRA_EXTERNAL_ID_JWKS_URL ZAKON PI2: Do not run cloudbuild.yaml manually. Use git tag + git push origin only. The stage pipeline ( bilko-stage-auto-deploy ) fires on every push to main and is unrelated to the demo deploy. 6. Known Follow-Ups ID Priority Description H1 HIGH — must-fix before scale launch Abuse gate (MC #103245): JIT provisioning has no server-side rate gate on tenant creation. An attacker with many email inboxes can script CIAM sign-ups (each requires a real OTP but automation services exist). Fix: add a platform-level provision rate gate in UserProvisioningService.provisionNewUserForEntra() (max N JIT orgs per hour) + CIAM tenant configuration to block disposable email domains. B3 MEDIUM Migadu email OTP blocking: Migadu (one.com), used for @alai.no , blocks Microsoft Azure CIAM OTP emails. Prospects with Gmail or Outlook receive OTP in ~6 seconds. Alai staff using @alai.no addresses cannot sign up. Fix: whitelist accountprotection.microsoft.com sender in Migadu SPF settings, or configure a custom CIAM email sender domain. UX-1 LOW Org display name: JIT-provisioned orgs are named "unknown's Organization" (no display name collected at signup). The user flow only collects email. Fix: add displayName to the BilkoSignUpSignIn attribute collection (Azure config, no code change), or collect it on first post-login screen. UX-2 LOW Default country/currency: JIT-provisioned org defaults to country='BA' , currency='BAM' . Prospects outside Bosnia must update via Settings. A country selection step at signup would improve the onboarding experience (follow-on, not a blocker). M1 MEDIUM INGRESS_TRAFFIC_ALL (MC #99924): Direct *.run.app access bypasses GCLB, which degrades IP-based rate limiting to per-GFE-region keying. Pre-existing risk, not introduced by CIAM. Fix: lock ingress to internal-only when load balancer is provisioned. M3 MEDIUM No alert on rapid tenant creation: Add a GCP Cloud Monitoring alert triggering when more than N organisations are JIT-provisioned per hour. 7. Validation Evidence Proveo — 11/11 PASS (v0.2.47, 2026-06-09T02:55Z) Real Gmail sign-up (alembasic@gmail.com) end-to-end on bilko-demo.alai.no : Step Result Details 1 PASS Self-serve copy present; "Contact your administrator" absent 2 PASS "Sign in with Microsoft" → ciamlogin.com (tenant 20bb17de) redirect 3 PASS Email entered on CIAM; OTP sent immediately 4 PASS Returning user — OTP sent directly (no create-account needed) 5 PASS 8-digit OTP (17717965) received via Gmail UID:75644 in 7 seconds 6 PASS Redirect back to bilko-demo.alai.no/dashboard 7 PASS POST /auth/entra/session → 200, Bilko HMAC JWT, org 4e96b6ff confirmed 8 PASS /dashboard with trial UI ("Probno: 6 dana preostalo"), /auth/me → 200 + trialEndsAt 2026-06-15 9 PASS /invoices via SPA nav — empty org (0 invoices), session alive 10 PASS /invoices/new — invoice form visible, trial tenant usable 11 PASS Regression clear — admin wall absent, self-serve copy confirmed Zero /auth/refresh calls during SPA navigation after B1.3 fix (confirmed by network capture count=0). Cross-tenant RLS: org 4e96b6ff shows 0 invoices and 0 BAM balances (no data from other tenants). Securion — LAUNCH WITH CONDITIONS CRITICAL: None found. HIGH: H1 (JIT provisioning rate gate) — must fix before scale launch (MC #103245). PASS areas: RS256 JWKS verification, issuer/audience pinning, OID as identity anchor, alg:none bypass blocked, org_id derived from DB (not Entra token), RLS fail-closed, FORCE RLS on all 9 tenant tables, role=viewer hardcoded (no self-escalation), trial re-signup blocked, refresh token rotation (jti-based single-use), legacy auth endpoints retired (HTTP 410). 8. DEPLOY-MAP Reference The CIAM substitutions live in infrastructure/gcp/cloudbuild.yaml under the bilko-main-deploy trigger. The Cloudflare Turnstile entries in DEPLOY-MAP.md cover the marketing landing forms and are unrelated to the CIAM auth flow. No DEPLOY-MAP.md changes are required for the CIAM self-serve trial feature — the trigger substitutions are already updated. Do not add CIAM secrets to the DEPLOY-MAP secrets table — these are build-time substitutions injected directly from the trigger, not GCP Secret Manager secrets. 9. Environment Variables Reference Variable Service Correct value note NEXT_PUBLIC_ENTRA_CLIENT_ID bilko-web-demo (build-time) c2902239-ea63-41bd-8619-6cf096d7d45a (SPA app) NEXT_PUBLIC_ENTRA_AUTHORITY bilko-web-demo (build-time) https://[tenant-id].ciamlogin.com/[tenant-id]/v2.0 — no user flow suffix needed NEXT_PUBLIC_ENTRA_SCOPE bilko-web-demo (build-time) api://fe39e0f5.../access_as_user ENTRA_EXTERNAL_ID_ISSUER bilko-api-demo https://[tenant-id].ciamlogin.com/[tenant-id]/v2.0 ENTRA_EXTERNAL_ID_AUDIENCE bilko-api-demo c2902239-ea63-41bd-8619-6cf096d7d45a (SPA client ID — NOT the API resource ID) ENTRA_EXTERNAL_ID_JWKS_URL bilko-api-demo https://[tenant-id].ciamlogin.com/[tenant-id]/discovery/v2.0/keys Critical: ENTRA_EXTERNAL_ID_AUDIENCE must be the SPA client ID ( c2902239 ), not the API resource app ID. MSAL requests id_tokens with the SPA client as audience. If set to the API app ID, the backend rejects every CIAM idToken with audience mismatch. Page created by Skillforge (MC #103232 WS-D, 2026-06-09). Source evidence: /tmp/evidence-103232/. Validation: Proveo 11/11 PASS + Securion LAUNCH WITH CONDITIONS. Bilko Self-Serve Trial — CIAM Architecture and Auth Pattern (MC #103232) Bilko Self-Serve Trial — CIAM Architecture & Auth Pattern MC: #103232 | Status: LIVE — Proveo 11/11 PASS | Last updated: 2026-06-09 | Securion verdict: LAUNCH WITH CONDITIONS 1. Overview A prospect navigates to app.bilko.cloud (or bilko-demo.alai.no ), clicks "Sign in or create a free account with your email" , and completes a Microsoft CIAM Email-OTP sign-up. On first login, the backend JIT-provisions an empty Bilko organisation with a 7-day trial directly on the real production database ( bilko-demo-db ). There is no separate demo build, no invite-only flow, and no org Microsoft account required — any personal email address works. The deployment target is the standard bilko-main-deploy semver-tag trigger. Stage and demo share the same Kotlin/Ktor binary and the same database instance (multi-tenant via RLS). The CIAM tenant ( bilkociam ) is a dedicated Microsoft Entra External ID tenant, completely separate from the Bilko staff Entra tenant. 1.1 Flow diagram Prospect → app.bilko.cloud/login → "Sign in with Microsoft" (MSAL redirect) → bilkociam.ciamlogin.com [BilkoSignUpSignIn user flow] → Email OTP verification (8-digit code, ~6s delivery) → Consent pages (2 pages on first login only) → Redirect to app.bilko.cloud/auth/callback → MSAL: LOGIN_SUCCESS fires, payload.idToken available → POST /auth/entra/session { idToken } [B1 exchange fix] → bilko-api: JWKS RS256 verify → OID lookup → JIT provision → Response: Bilko HMAC JWT + org { trialEndsAt } → setAuthFromRegistration() [B1.2 session fix] → checkAuth() in-memory JWT fast-path [B1.3 session fix] → /dashboard — empty org, trial active ("Probno: 6 dana preostalo") 2. CIAM Tenant Configuration Property Value Tenant name bilkociam Tenant ID 20bb17de-9be5-4143-a7e5-8c1ddae6a064 Tenant type CIAM (Entra External ID) SPA app name Bilko Web (SPA) SPA client ID c2902239-ea63-41bd-8619-6cf096d7d45a API resource app ID fe39e0f5-513e-40af-93f0-c3ee624df56c Authority URL https://20bb17de-9be5-4143-a7e5-8c1ddae6a064.ciamlogin.com/20bb17de-9be5-4143-a7e5-8c1ddae6a064/v2.0 OIDC issuer same as authority URL (confirmed via discovery endpoint) 2.1 User flow: BilkoSignUpSignIn Property Value Flow ID aa86084b-01dc-453f-9e10-679dfefdd824 Type externalUsersSelfServiceSignUpEventsFlow Display name BilkoSignUpSignIn Identity provider EmailOtpSignup-OAUTH (Email One Time Passcode) isSignUpAllowed true userTypeToCreate member (not guest) Attributes collected email (auto-filled by OTP verification) Linked app c2902239-ea63-41bd-8619-6cf096d7d45a (Bilko Web SPA) Authority URL note: Unlike Azure AD B2C, Entra External ID CIAM does not require a user flow policy name suffix in the authority URL. The BilkoSignUpSignIn flow is applied automatically at the tenant level when the SPA app is linked to it. The deployed authority URL requires no changes. 2.2 Registered SPA redirect URIs https://app.bilko.cloud/auth/callback and https://app.bilko.cloud https://app.bilko.company/auth/callback and https://app.bilko.company https://app.bilko.io/auth/callback and https://app.bilko.io https://bilko-demo.alai.no/auth/callback and https://bilko-demo.alai.no https://bilko-web-stage-dh4m46blja-lz.a.run.app/auth/callback and .a.run.app http://localhost:3000/auth/callback and http://localhost:3000 2.3 Adding identity providers or attributes To add social identity providers (Google, Apple) or additional signup attributes (e.g. display name, company name): Microsoft Entra admin centre → External Identities → User flows → BilkoSignUpSignIn → Identity providers / Attributes. No code changes or redeploys are required for attribute-only changes. Adding a social provider requires app registration on the provider side and linking in the CIAM tenant. 3. Auth Flow — The Hard-Won Pattern This section documents three bugs that were discovered and fixed during Proveo E2E validation (MC #103232 WS-V). The fixes are canonical — do not revert them. B1 — Token exchange (commit 660f410, tag v0.2.45) Problem: MSAL's LOGIN_SUCCESS event fires with an Entra access_token (RS256, Microsoft-issued). The original code set this directly as the API Bearer header. The Bilko API validates HMAC256 JWTs only — all calls returned 401. Fix: After MSAL fires, pass payload.idToken (not payload.accessToken ) to a POST /auth/entra/session { idToken } call. The backend verifies the CIAM RS256 idToken via JWKS, looks up or JIT-provisions the user, and returns a Bilko HMAC JWT. // apps/web/lib/msal/msal-provider.tsx — corrected token selection const idToken = payload.idToken ?? payload.accessToken if (idToken) { handleEntraLogin(idToken) } // apps/web/lib/msal/use-entra-auth.ts — exchange call const sessionResult = await api.auth.entraSession(idToken) const bilkoJwt = sessionResult?.tokens?.accessToken setAccessToken(bilkoJwt) B1.2 — Session persistence via setAuthFromRegistration (commit e1e31c5, tag v0.2.46) Problem: After the B1 exchange, checkAuth() was called to hydrate the store. checkAuth() internally calls POST /auth/refresh using the httpOnly refresh-token cookie. The CIAM exchange path does not set a cookie — so /auth/refresh returned 401, which cleared the Bilko JWT and redirected back to /login . Fix: Replace the checkAuth() call in handleEntraLogin with setAuthFromRegistration() , which hydrates the Zustand auth store directly from the /auth/entra/session response body. No cookie round-trip needed. // apps/web/lib/msal/use-entra-auth.ts — hydrate from session response const { setAuthFromRegistration } = useAuthStore.getState() setAuthFromRegistration({ user: sessionResult.user, organization: sessionResult.organization, tokens: { accessToken: bilkoJwt }, }) // Navigate to /dashboard — Bilko JWT is in-memory Bearer B1.3 — checkAuth in-memory JWT fast-path (commit 30a8c85, tag v0.2.47) Problem: Even with B1.2, setAuthFromRegistration() in handleEntraLogin correctly set isAuthenticated=true . However, AuthProvider mounts on every protected route and calls checkAuth() . That call hit /auth/refresh (cookie path) → 401 → store reset to unauthenticated → redirect to /login on every page navigation. Fix: Added an in-memory JWT fast-path at the top of checkAuth() in auth-store.ts . If a Bilko JWT is already in memory (set via the CIAM exchange), checkAuth() uses GET /auth/me with that Bearer token instead of falling through to the cookie-refresh path. // apps/web/lib/stores/auth-store.ts — in-memory fast-path checkAuth: async () => { const inMemoryToken = getAccessToken() if (inMemoryToken) { try { const me = await api.auth.me() set({ isAuthenticated: true, isLoading: false, user: { ...me, name: me.fullName }, organization: me?.organization ?? null }) return true } catch { set({ isAuthenticated: false, isLoading: false, user: null, organization: null }) return false } } // Original cookie-refresh fallback (unchanged — non-CIAM sessions) ... } ENTRA_EXTERNAL_ID_AUDIENCE — critical build var (fixed in v0.2.47 trigger update) Problem: The bilko-main-deploy Cloud Build trigger had _ENTRA_EXTERNAL_ID_AUDIENCE set to fe39e0f5 (the API resource app ID). This is wrong — the CIAM idToken audience is the SPA client ID ( c2902239 ), because MSAL requests id_tokens scoped to the requesting app. Every new deploy reverted the Cloud Run env to the wrong value, requiring a manual patch. Fix: The trigger substitution was updated: # infrastructure/gcp/cloudbuild.yaml — correct value _ENTRA_EXTERNAL_ID_AUDIENCE: c2902239-ea63-41bd-8619-6cf096d7d45a # SPA client ID # NOT: fe39e0f5-513e-40af-93f0-c3ee624df56c (that is the API resource app — wrong for idToken aud) This is now stable in the trigger — it will not revert on future deploys. 4. Backend JIT Provisioning 4.1 Database migrations (Flyway V66–V69) Migration Purpose V66__entra_rls_and_password_nullable.sql Makes password_hash nullable (Entra-only users have no password). Adds RLS policy on entra_external_identities (FORCE + fail-closed). Adds CHECK constraint: issuer must not end with trailing slash. V67__rbac_permissions_catalog.sql RBAC permissions catalog seeding. V68__rbac_user_provisioning.sql SECURITY DEFINER function bilko_auth.provision_user_with_org() : creates org (7-day trial, trial_starts_at , trial_ends_at = now() + 7 days ), creates user ( role='viewer' , password_hash=NULL ), inserts entra_external_identities row (issuer, OID, user_id). Default: country='BA' , currency='BAM' . V69__fix_provision_rls.sql RLS fix: calls set_config('app.current_org_id', v_org_id, true) before the users INSERT so that RLS policies on the users table pass during JIT provisioning. 4.2 JIT provisioning call flow POST /auth/entra/session { idToken } → EntraExternalIdService.verifyIdToken() [RS256, JWKS, issuer+audience+exp] → AuthUserRepository.findByEntraIdentity(issuer, oid) → null (new user) → AuthUserRepository.findByEmail(email) → null (no existing Bilko account) → UserProvisioningService.provisionNewUserForEntra() → bilko_auth.provision_user_with_org() [SECURITY DEFINER, SERIALIZABLE] → INSERT organizations (trial 7 days) → INSERT users (role=viewer, password=null) → INSERT entra_external_identities (issuer, oid) → jwtService.signAccessToken(userId, email, role='viewer', orgId) → Response: { user, organization { trialEndsAt }, tokens { accessToken, refreshToken } } Idempotency: Re-login with the same OID returns the existing user and org; trial end date is not reset. The entra_external_identities table has a UNIQUE constraint on (issuer, subject) . 4.3 trialEndsAt in /auth/me The GET /auth/me response includes organization.trialEndsAt (ISO 8601). The frontend auth store exposes this on the Organization interface. The trial expiry is enforced server-side by TrialGatePlugin which queries the DB on every gated request — the JWT does not embed expiry. 4.4 RLS isolation All JIT-provisioned tenants are isolated via PostgreSQL Row Level Security. The app.current_org_id session variable is set by OrgScopePlugin from the BilkoPrincipal (JWT-derived, not from any HTTP header). orgTransaction() uses SET LOCAL scoped to the transaction — connection pool does not carry state between requests. Cross-tenant isolation is verified by RlsOrgIsolationV46IntegrationTest . 5. Deploy Property Value Deploy trigger bilko-main-deploy (europe-north1, project tribal-sign-487920-k0) Trigger type semver tag on main: git tag vX.Y.Z && git push origin vX.Y.Z Config infrastructure/gcp/cloudbuild.yaml Current live tag v0.2.47 (commit 30a8c85) Web revision bilko-web-demo-00080-tq5 API revision bilko-api-demo-00155-524 CIAM env vars in trigger NEXT_PUBLIC_ENTRA_CLIENT_ID, NEXT_PUBLIC_ENTRA_AUTHORITY, NEXT_PUBLIC_ENTRA_SCOPE, ENTRA_EXTERNAL_ID_ISSUER, ENTRA_EXTERNAL_ID_AUDIENCE (= c2902239), ENTRA_EXTERNAL_ID_JWKS_URL ZAKON PI2: Do not run cloudbuild.yaml manually. Use git tag + git push origin only. The stage pipeline ( bilko-stage-auto-deploy ) fires on every push to main and is unrelated to the demo deploy. 6. Known Follow-Ups ID Priority Description H1 HIGH — must-fix before scale launch Abuse gate (MC #103245): JIT provisioning has no server-side rate gate on tenant creation. An attacker with many email inboxes can script CIAM sign-ups (each requires a real OTP but automation services exist). Fix: add a platform-level provision rate gate in UserProvisioningService.provisionNewUserForEntra() (max N JIT orgs per hour) + CIAM tenant configuration to block disposable email domains. B3 MEDIUM Migadu email OTP blocking: Migadu (one.com), used for @alai.no , blocks Microsoft Azure CIAM OTP emails. Prospects with Gmail or Outlook receive OTP in ~6 seconds. Alai staff using @alai.no addresses cannot sign up. Fix: whitelist accountprotection.microsoft.com sender in Migadu SPF settings, or configure a custom CIAM email sender domain. UX-1 LOW Org display name: JIT-provisioned orgs are named "unknown's Organization" (no display name collected at signup). The user flow only collects email. Fix: add displayName to the BilkoSignUpSignIn attribute collection (Azure config, no code change), or collect it on first post-login screen. UX-2 LOW Default country/currency: JIT-provisioned org defaults to country='BA' , currency='BAM' . Prospects outside Bosnia must update via Settings. A country selection step at signup would improve the onboarding experience (follow-on, not a blocker). M1 MEDIUM INGRESS_TRAFFIC_ALL (MC #99924): Direct *.run.app access bypasses GCLB, which degrades IP-based rate limiting to per-GFE-region keying. Pre-existing risk, not introduced by CIAM. Fix: lock ingress to internal-only when load balancer is provisioned. M3 MEDIUM No alert on rapid tenant creation: Add a GCP Cloud Monitoring alert triggering when more than N organisations are JIT-provisioned per hour. 7. Validation Evidence Proveo — 11/11 PASS (v0.2.47, 2026-06-09T02:55Z) Real Gmail sign-up (alembasic@gmail.com) end-to-end on bilko-demo.alai.no : Step Result Details 1 PASS Self-serve copy present; "Contact your administrator" absent 2 PASS "Sign in with Microsoft" → ciamlogin.com (tenant 20bb17de) redirect 3 PASS Email entered on CIAM; OTP sent immediately 4 PASS Returning user — OTP sent directly (no create-account needed) 5 PASS 8-digit OTP (17717965) received via Gmail UID:75644 in 7 seconds 6 PASS Redirect back to bilko-demo.alai.no/dashboard 7 PASS POST /auth/entra/session → 200, Bilko HMAC JWT, org 4e96b6ff confirmed 8 PASS /dashboard with trial UI ("Probno: 6 dana preostalo"), /auth/me → 200 + trialEndsAt 2026-06-15 9 PASS /invoices via SPA nav — empty org (0 invoices), session alive 10 PASS /invoices/new — invoice form visible, trial tenant usable 11 PASS Regression clear — admin wall absent, self-serve copy confirmed Zero /auth/refresh calls during SPA navigation after B1.3 fix (confirmed by network capture count=0). Cross-tenant RLS: org 4e96b6ff shows 0 invoices and 0 BAM balances (no data from other tenants). Securion — LAUNCH WITH CONDITIONS CRITICAL: None found. HIGH: H1 (JIT provisioning rate gate) — must fix before scale launch (MC #103245). PASS areas: RS256 JWKS verification, issuer/audience pinning, OID as identity anchor, alg:none bypass blocked, org_id derived from DB (not Entra token), RLS fail-closed, FORCE RLS on all 9 tenant tables, role=viewer hardcoded (no self-escalation), trial re-signup blocked, refresh token rotation (jti-based single-use), legacy auth endpoints retired (HTTP 410). 8. DEPLOY-MAP Reference The CIAM substitutions live in infrastructure/gcp/cloudbuild.yaml under the bilko-main-deploy trigger. The Cloudflare Turnstile entries in DEPLOY-MAP.md cover the marketing landing forms and are unrelated to the CIAM auth flow. No DEPLOY-MAP.md changes are required for the CIAM self-serve trial feature — the trigger substitutions are already updated. Do not add CIAM secrets to the DEPLOY-MAP secrets table — these are build-time substitutions injected directly from the trigger, not GCP Secret Manager secrets. 9. Environment Variables Reference Variable Service Correct value note NEXT_PUBLIC_ENTRA_CLIENT_ID bilko-web-demo (build-time) c2902239-ea63-41bd-8619-6cf096d7d45a (SPA app) NEXT_PUBLIC_ENTRA_AUTHORITY bilko-web-demo (build-time) https://[tenant-id].ciamlogin.com/[tenant-id]/v2.0 — no user flow suffix needed NEXT_PUBLIC_ENTRA_SCOPE bilko-web-demo (build-time) api://fe39e0f5.../access_as_user ENTRA_EXTERNAL_ID_ISSUER bilko-api-demo https://[tenant-id].ciamlogin.com/[tenant-id]/v2.0 ENTRA_EXTERNAL_ID_AUDIENCE bilko-api-demo c2902239-ea63-41bd-8619-6cf096d7d45a (SPA client ID — NOT the API resource ID) ENTRA_EXTERNAL_ID_JWKS_URL bilko-api-demo https://[tenant-id].ciamlogin.com/[tenant-id]/discovery/v2.0/keys Critical: ENTRA_EXTERNAL_ID_AUDIENCE must be the SPA client ID ( c2902239 ), not the API resource app ID. MSAL requests id_tokens with the SPA client as audience. If set to the API app ID, the backend rejects every CIAM idToken with audience mismatch. Page created by Skillforge (MC #103232 WS-D, 2026-06-09). Source evidence: /tmp/evidence-103232/. Validation: Proveo 11/11 PASS + Securion LAUNCH WITH CONDITIONS.