v3.5.0 7 February 2026 Breaking

Row Level Security

Summary

Database-enforced tenant isolation via PostgreSQL Row Level Security (RLS). Every tenant query now passes through a transaction-scoped session variable (app.current_org_id), and PostgreSQL itself rejects any row that doesn’t belong to the requesting organisation. This is defence in depth — even if application code omits a WHERE organisation_id = ? filter, the database returns zero rows.

What Changed

RLS Transaction Middleware (rls-transaction.ts)

New Fastify plugin wrapping every tenant-scoped request in a database transaction:

  • onRequest: checks out a PoolClient from the pool, opens BEGIN, attaches to request.dbClient
  • preHandler: calls set_config('app.current_org_id', orgId, true) after auth and tenant middleware have resolved the organisation
  • onResponse: COMMIT and release
  • onError: ROLLBACK and release

Registered on the tenantApi scope only — auth, registration, and public routes are unaffected.

Route Migration (7 files)

All tenant route handlers migrated from fastify.db.query() / pool.query() to request.dbClient.query():

  • quotes.ts — 4 transaction blocks converted to savepoints (sp_quote_create, sp_quote_update, sp_quote_convert, sp_quote_delete)
  • organisation.ts — settings update converted to savepoint (sp_org_settings), removed redundant SET search_path
  • categorisation-rules.ts — reorder converted to savepoint (sp_reorder_rules)
  • transactions.ts — create transaction converted to savepoint (sp_txn_create)
  • invoice-email.ts — pool references replaced with request.dbClient
  • invoice-service.ts — refactored with Queryable interface and withTransaction() helper that uses savepoints when called within the middleware transaction, or BEGIN/COMMIT when called at registration time

Savepoint Pattern

Routes that previously checked out a second connection (pool.connect() → BEGIN/COMMIT/ROLLBACK → client.release()) now use savepoints within the middleware’s transaction. The pattern: get request.dbClient, issue SAVEPOINT sp_name, do the work, then RELEASE SAVEPOINT sp_name on success or ROLLBACK TO SAVEPOINT sp_name on error. No finally block, no client.release() — the middleware handles that.

This keeps all queries on the RLS-scoped connection. No second connection, no leaked org context.

RLS Policies (25 tables)

Enabled ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY on all tenant-scoped tables:

accounts, api_keys, api_request_log, attachments, audit_log, bank_accounts, bank_imports, bank_statement_rows, bug_reports, categorisation_rules, contacts, entities, financial_periods, idempotency_keys, invoice_lines, invoices, recurring_transaction_log, recurring_transactions, settings, transaction_lines, transactions, vat_returns, webhook_deliveries, webhook_endpoints, year_end_log

Each table has four policies (SELECT, INSERT, UPDATE, DELETE) matching on organisation_id = current_setting('app.current_org_id', true)::uuid.

Exemptions

  • user_organisations — excluded from RLS. This join table is queried during auth to resolve which org a user belongs to, before app.current_org_id can be set. No tenant data in this table.
  • users — no organisation_id column, not a tenant table.

Database Role

Created speybooks_admin role with BYPASSRLS for admin panel queries and future migration scripts.

Technical Notes

  • SET LOCAL doesn’t support parameterised queries in pg. Used set_config('app.current_org_id', $1, true) instead — the true parameter makes it transaction-local, equivalent to SET LOCAL.
  • Fastify plugin hooks run before hooks added directly to the encapsulated instance. The set_config call is registered as a preHandler on tenantApi (not inside the plugin) to ensure it runs after apiKeyAuth and tenantMiddleware.
  • The fastify.audit.log() helper in server.ts uses the pool directly and doesn’t include organisation_id. Auth route audit logs (e.g. LOGIN_SUCCESS) silently fail under RLS. This is a known issue — will be addressed in v3.5.1.
  • Existing WHERE organisation_id = $N filters in route handlers remain in place. RLS is a safety net, not a replacement for correct application logic.

Migration

Run in order after deploying the updated code:

  1. Build and restart (middleware must be live before enabling RLS): pnpm build then sudo systemctl restart speybooks
  2. Enable RLS on all 25 tables: sudo -u postgres psql speytech_accounting -f db/migrations/rls-enable.sql
  3. Verify with SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'accounting' AND c.relkind = 'r' AND c.relrowsecurity = true ORDER BY relname

What This Means

Before v3.5.0, tenant isolation relied entirely on application code — every query had to include WHERE organisation_id = ?. Miss one, and tenant A could see tenant B’s data.

After v3.5.0, PostgreSQL enforces the boundary at the database layer. The application filters are still there (belt), and RLS is the braces. For an accounting platform handling financial data, this is the standard you’d expect.