Bilko ADR-017: RLS Multi-Tenancy

Author: ALAI, 2026 # ADR-017: RLS Multi-Tenancy Migration

**Status:** Accepted
**Date:** 2026-04-21
**Author:** ALAI, 2026
**Replaces:** ADR-005 (App-Layer Multi-Tenancy)
**Related:** ADR-015 (Four-Jurisdiction Plugin), ADR-018 (Market vs Locale)

---

## Context

Bilko's current multi-tenancy model (ADR-005) uses **application-layer scoping**: every query manually filters `WHERE organization_id = :current_org`. This works but has scaling and security issues:

**Problems with ADR-005:**

1. **Error-prone:** Forgetting `WHERE organization_id = ...` exposes all tenant data
2. **Performance:** Can't use Postgres partition pruning (requires RLS)
3. **Audit complexity:** Cross-tenant queries are syntactically identical to legitimate queries
4. **No defense-in-depth:** A single SQL injection bypasses all scoping

**Requirement from master plan (Phase 2):**

- Migrate to **PostgreSQL Row-Level Security (RLS)** for tenant isolation
- Non-disruptive 3-phase migration (coexist → validate → retire ADR-005)
- Add **versioned Chart of Accounts** table (regulatory changes = data writes, not schema migrations)
- Partition **audit log** (`logged_action`) by `country_code` (different retention periods per jurisdiction)

---

## Decision

### 1. Add `country_code` Column to All Tenant Tables

**Phase 2A Task 2.1:**

```sql
-- Flyway migration V2_001__add_country_code.sql
ALTER TABLE organizations ADD COLUMN country_code CHAR(2) NOT NULL DEFAULT 'RS';
ALTER TABLE invoices ADD COLUMN country_code CHAR(2);
ALTER TABLE expenses ADD COLUMN country_code CHAR(2);
-- ... repeat for all 15 tenant tables

-- Backfill from organizations.taxJurisdiction → country_code mapping
UPDATE invoices i
SET country_code = SUBSTRING(o.tax_jurisdiction FROM 1 FOR 2)
FROM organizations o
WHERE i.organization_id = o.id;

-- Enforce NOT NULL after backfill
ALTER TABLE invoices ALTER COLUMN country_code SET NOT NULL;
```

**Validation:**

```sql
-- Zero rows should be NULL after backfill
SELECT COUNT(*) FROM invoices WHERE country_code IS NULL;
-- Expect: 0
```

### 2. Enable RLS Policies in PERMISSIVE Mode (Coexistence)

**Phase 2A Task 2.1 (continued):**

```sql
-- Enable RLS on all tenant tables
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE expenses ENABLE ROW LEVEL SECURITY;
-- ... repeat for all tables

-- Create PERMISSIVE policies (allow all during migration)
CREATE POLICY tenant_isolation ON invoices
FOR ALL
USING (country_code = current_setting('app.current_org_country', TRUE)::text);

-- Policy for auditors (cross-tenant READ-ONLY)
CREATE POLICY auditor_country ON invoices
FOR SELECT
USING (
 current_setting('app.user_role', TRUE) = 'auditor'
 AND country_code = ANY(current_setting('app.auditor_countries', TRUE)::text[])
);
```

**Key decisions:**

- Use `current_setting('app.current_org_country')` session variable (set at connection open)
- RLS policies are **PERMISSIVE** during migration — app middleware still enforces scoping
- RLS becomes **redundant safety layer**, not the primary gate

### 3. Versioned Chart of Accounts Table

**Problem:** Pravilnik (regulatory chart of accounts) changes yearly. Example: Serbia changed CoA in 2020 (Sl. glasnik RS br. 89/2020). Existing `chart_of_accounts` table has no versioning — schema migration required for updates.

**Solution:** Time-versioned CoA table per jurisdiction.

**Phase 2A Task 2.1 DDL:**

```sql
CREATE TABLE chart_of_accounts (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 country_code CHAR(2) NOT NULL,
 account_code VARCHAR(10) NOT NULL,
 account_name VARCHAR(255) NOT NULL,
 account_type VARCHAR(50) NOT NULL, -- ASSET, LIABILITY, EQUITY, REVENUE, EXPENSE
 parent_code VARCHAR(10), -- For hierarchical CoA
 valid_from DATE NOT NULL,
 valid_to DATE, -- NULL = currently valid
 version VARCHAR(20) NOT NULL, -- e.g., "RS_2020", "HR_2022"
 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 UNIQUE(country_code, account_code, valid_from)
);

CREATE INDEX idx_coa_country_valid ON chart_of_accounts(country_code, valid_from, valid_to);

-- Seed Serbia 2020 Pravilnik
INSERT INTO chart_of_accounts (country_code, account_code, account_name, account_type, valid_from, version)
VALUES
 ('RS', '100', 'Нематеријална улагања', 'ASSET', '2020-01-01', 'RS_2020'),
 ('RS', '200', 'Некретнине', 'ASSET', '2020-01-01', 'RS_2020'),
 -- ... full CoA insert
;
```

**Query pattern (get current CoA for organization):**

```kotlin
// In application code:
val coa = db.query("""
 SELECT * FROM chart_of_accounts
 WHERE country_code = :country
 AND valid_from <= CURRENT_DATE
 AND (valid_to IS NULL OR valid_to > CURRENT_DATE)
 ORDER BY account_code
""", mapOf("country" to org.countryCode()))
```

**Regulatory update workflow:**

1. Government publishes new Pravilnik (e.g., Serbia 2027)
2. Admin inserts new rows with `valid_from = '2027-01-01'`, `version = 'RS_2027'`
3. Update old rows: `SET valid_to = '2026-12-31' WHERE version = 'RS_2020'`
4. **No schema migration required** — regulatory change is data, not code

### 4. Partition Audit Log by `country_code`

**Problem:** Different retention laws per jurisdiction:

- RS: 10 years
- HR: 11 years
- BA-FED: 10 years
- BA-RS: 11 years

Current `logged_action` table is unpartitioned — retention policy requires full-table scan.

**Solution:** Declarative partitioning by `country_code`.

**Phase 2B Task 2.2 DDL:**

```sql
-- Create partitioned table
CREATE TABLE logged_action_partitioned (
 id BIGSERIAL,
 schema_name TEXT NOT NULL,
 table_name TEXT NOT NULL,
 user_name TEXT,
 action TEXT NOT NULL, -- INSERT, UPDATE, DELETE
 original_data JSONB,
 new_data JSONB,
 query TEXT,
 country_code CHAR(2) NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY LIST (country_code);

-- Create partitions
CREATE TABLE logged_action_rs PARTITION OF logged_action_partitioned FOR VALUES IN ('RS');
CREATE TABLE logged_action_hr PARTITION OF logged_action_partitioned FOR VALUES IN ('HR');
CREATE TABLE logged_action_ba PARTITION OF logged_action_partitioned FOR VALUES IN ('BA');

-- Indexes per partition
CREATE INDEX idx_logged_action_rs_created ON logged_action_rs(created_at);
CREATE INDEX idx_logged_action_hr_created ON logged_action_hr(created_at);
CREATE INDEX idx_logged_action_ba_created ON logged_action_ba(created_at);

-- Retention policy (cron job)
-- RS: DELETE WHERE created_at < NOW() - INTERVAL '10 years'
-- HR: DELETE WHERE created_at < NOW() - INTERVAL '11 years'
```

**Migration from old `logged_action` table:**

1. Create `logged_action_partitioned` (new table)
2. Backfill from `logged_action` WHERE `created_at > NOW() - INTERVAL '1 year'` (recent data only)
3. Rename `logged_action` → `logged_action_legacy` (keep for 90 days)
4. Rename `logged_action_partitioned` → `logged_action`
5. Update audit trigger to insert into new partitioned table

**Zero downtime:** Both tables coexist during backfill.

### 5. Retire ADR-005 App-Layer Scoping

**Phase 2C Task 2.3 (only after validation):**

**Validation gate (Task 4.1):**

```sql
-- Test cross-tenant isolation with rogue role
SET app.current_org_country = 'RS';
SET ROLE rogue_user;
SELECT COUNT(*) FROM invoices WHERE country_code = 'HR';
-- Expect: 0 (RLS blocks cross-tenant read)
```

**Only proceed if validation passes.**

**Retirement steps:**

1. Remove `org-scope.ts` middleware from Ktor request chain
2. Wire `SET app.current_org_country = :country` at Prisma connection open:
 ```kotlin
 val conn = dataSource.connection
 conn.prepareStatement("SET app.current_org_country = ?")
 .apply { setString(1, org.countryCode()) }
 .execute()
 ```
3. Switch RLS policies from PERMISSIVE → RESTRICTIVE:
 ```sql
 DROP POLICY tenant_isolation ON invoices;
 CREATE POLICY tenant_isolation ON invoices
 FOR ALL
 USING (country_code = current_setting('app.current_org_country')::text)
 WITH CHECK (country_code = current_setting('app.current_org_country')::text);
 ```
4. Mark ADR-005 as "Superseded by ADR-017"

---

## Consequences

### Positive

1. **Defense-in-depth:** Even if app logic forgets `WHERE organization_id`, RLS blocks cross-tenant queries
2. **Partition pruning:** Queries scoped to one country → Postgres prunes other partitions → faster
3. **Compliance clarity:** Retention policy is per-jurisdiction, encoded in partition retention jobs
4. **Auditability:** RLS policy violations logged to `pg_stat_activity` — security team can detect anomalies

### Negative

1. **Migration complexity:** 3-phase migration requires careful sequencing (coexist → validate → retire)
2. **Session state:** Every connection must `SET app.current_org_country` — connection pooling requires session reset
3. **Performance overhead:** RLS policies add ~2-5% query overhead (measured in Postgres 16 benchmarks)

### Risks

1. **Connection pooling bugs:** If `app.current_org_country` not reset between connections → cross-tenant leak. **Mitigation:** Wrap all queries in session initializer; unit test connection pool state.
2. **Partition key mismatch:** If `country_code` and `tax_jurisdiction` diverge (e.g., BA split) → partition logic breaks. **Mitigation:** `country_code` derived from `tax_jurisdiction` via DB trigger; enforce consistency at write time.
3. **Backfill failure:** If backfill leaves NULLs in `country_code` → RLS blocks all queries. **Mitigation:** NOT NULL constraint only applied after zero-NULL validation query passes.

---

## Implementation Notes

### Exchange Rate Precision (Corrected per Petter G review)

**Problem:** Current schema uses `NUMERIC(19,4)` for exchange rates. Crypto conversions (BTC/RSD) require 10 decimal places.

**Fix (Phase 2A Task 2.1):**

```sql
ALTER TABLE exchange_rates ALTER COLUMN rate TYPE NUMERIC(20,10);
```

### RLS Policy Testing

**Test suite (Proveo E2E):**

1. Create org in RS jurisdiction
2. Insert invoice with `country_code = 'RS'`
3. Set session `app.current_org_country = 'HR'`
4. Query invoices → expect empty result set
5. Set session `app.current_org_country = 'RS'`
6. Query invoices → expect invoice returned

**Evidence:** `~/system/evidence/bilko-rls-isolation-YYYYMMDD.json`

### Data Quality Audit (Task 2.4)

**Nightly cron:**

```sql
-- Alert if any tenant table has NULL country_code
SELECT table_name, COUNT(*) FROM (
 SELECT 'invoices' AS table_name, COUNT(*) AS ct FROM invoices WHERE country_code IS NULL
 UNION ALL
 SELECT 'expenses', COUNT(*) FROM expenses WHERE country_code IS NULL
) WHERE ct > 0;
```

Slack alert if any row returns count > 0.

---

## References

- **Postgres RLS docs:** https://www.postgresql.org/docs/16/ddl-rowsecurity.html
- **Declarative partitioning:** https://www.postgresql.org/docs/16/ddl-partitioning.html
- **Master plan:** `~/system/specs/bilko-multi-market-architecture-plan.md` (Phase 2 Tasks 2.1–2.3)
- **Related ADRs:**
 - ADR-005: App-Layer Multi-Tenancy (superseded by this ADR)
 - ADR-015: Four-Jurisdiction Plugin Architecture
 - ADR-018: Market vs Locale Separation

---

## Approval

**Approved:** 2026-04-21 by CEO Alem Basic
**Execution:** Phase 2 Tasks 2.1–2.3 (not yet started — blocked on Phase 1 completion)