6.2 30-Day Soak Metric Thresholds

These metrics are measured against the stage Cloud SQL instance from the day V17 applies until Phase 2C is authorized. All thresholds must be met for a continuous 30-day window.

6.3 Fail-Closed Test Scenarios (Securion Checklist)

These test scenarios MUST be executed and pass beforefor Phase 2C is```sql authorized. Securion (Parisa Tabriz) owns this checklist and provides a signed evidence file.

Core Isolation Tests

•  Rogue-role SELECT: SET ROLE bilko_app; SET LOCAL app.current_org_id = '<org_a_uuid>'; SELECT COUNT(*) FROM invoices WHERE organization_id = '<org_b_uuid>'; Expected: 0 rows (even though the WHERE clause explicitly asks for org B).

•  Rogue-role UPDATE: SET ROLE bilko_app; SET LOCAL app.current_org_id = '<org_a_uuid>'; UPDATE invoices SET status = 'CANCELLED' WHERE organization_id = '<org_b_uuid>'; Expected: 0 rows updated (not an error — UPDATE silently affects 0 rows, which is correct fail-closed behavior).

•  Rogue-role INSERT: SET ROLE bilko_app; SET LOCAL app.current_org_id = '<org_a_uuid>'; INSERT INTO invoices (id, organization_id, ...) VALUES (gen_random_uuid(), '<org_b_uuid>', ...); Expected: WITH CHECK violation / 0 rows inserted (RESTRICTIVE WITH CHECK clause must deny cross-org inserts).

•  Missing context SELECT: SET ROLE bilko_app; SELECT COUNT(*) FROM invoices; (no SET LOCAL app.current_org_id) Expected: 0 rows (null/empty context returns false in USING clause).

•  Empty string context SELECT: SET ROLE bilko_app; SET LOCAL app.current_org_id = ''; SELECT COUNT(*) FROM invoices; Expected: 0 rows.

•  Malformed UUID context: SET ROLE bilko_app; SET LOCAL app.current_org_id = 'not-a-uuid'; SELECT COUNT(*) FROM invoices; Expected: 0 rows (length check in USING clause returns false before UUID cast).

invoice_items Subquery Isolation

•  Cross-org invoice_items via subquery: Org A attempts to read invoice_items for an invoice owned by Org B. SET ROLE bilko_app; SET LOCAL app.current_org_id = '<org_a_uuid>'; SELECT COUNT(*) FROM invoice_items WHERE invoice_id = '<org_b_invoice_uuid>'; Expected: 0 rows (invoice_items USING clause subquery on invoices returns empty set for wrong org).

bank_transactions Subquery Isolation

•  Cross-org bank_transactions via subquery: Same pattern as invoice_items. Expected: 0 rows.

bilko_admin Bypass Verification

•  bilko_admin can see all rows: SET ROLE bilko_admin; SELECT COUNT(*) FROM invoices; Expected: returns actual total row count (superuser bypass confirmed — this is CORRECT behavior for admin).

•  bilko_admin FORCE RLS is NOT set on organizations: SELECT relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname = 'organizations'; Expected: relrowsecurity = true, relforcerowsecurity = false (organizations is RLS-enabled but NOT FORCE — bilko_admin can see all orgs for onboarding/support).

Application Integration Tests (Proveo, not Securion)

•  PUT /api/v1/invoices/{id} with cross-tenant JWT: HTTP 404 (not 500, not 200)
•  GET /api/v1/invoices/{id}/pdf with cross-tenant JWT: HTTP 404
•  GET /api/v1/invoices/{id} with own-tenant JWT: HTTP 200 with correct data
•  Create invoice as HR org → invoice visible to HR org, invisible to RS org querying same ID

6.4 RESTRICTIVE Policy DDL

When all §6.1 gate criteria are met, the flip is executed as Flyway migration V23 (or the next available version at execution time).

-- V23__rls_restrictive.V19__rls_restrictive.sql (Phase 2C — post-post Securion audit + 30d soak + CEO sign)audit)
-- ZAKON:Convert CEOPERMISSIVE Phase→ 2CRESTRICTIVE signon requiredall before applying.tables
-- This migrationis retiresthe ADR-005point application-of no return: application layer WHERE org_id clauses.is --retired Afterafter this migration, app code may remove redundant WHERE org_id = :principalOrgId clauses
-- from service methods. Do NOT remove them before this migration runs on production.
--
-- DOWN: revert to V17 PERMISSIVE state
--   DROP POLICY org_isolation ON invoices; ... (repeat for all tables)
--   CREATE POLICY org_isolation ON invoices AS PERMISSIVE ... (restore V17 text)

-- ─── invoices ────────────────────────────────────────────────────────────────────────────────this.

DROP POLICY org_isolation ON invoices;
CREATE POLICY org_isolation ON invoices
    AS RESTRICTIVE
    FOR ALL
    TO bilko_app
    USING (org_id CASE
            WHEN= current_setting('app.current_org_id')::uuid)
    WITH CHECK (org_id = current_setting('app.current_org_id')::uuid);

-- Same pattern for expenses, transactions, bank_transactions, bank_accounts,
-- accounts, contacts, invoice_items (repeat for each table).
```

### 2.3 Versioned Chart of Accounts Table

The `chart_of_accounts` table stores jurisdiction-specific CoA entries with time-ranged
validity. This supports:

- Pravilnik revisions without code changes (ADR-bilko-003 §Layer 3, lines 122–143)
- Historical invoice accuracy (rate in force at transaction date, not current rate)
- `CountryPlugin.getChartOfAccountsDefaults()` seeding on org creation (ADR-015 §2.2)

```sql
-- Part of Flyway V17 or separate V17b (Phase 2A / 1H parallel)

CREATE TABLE chart_of_accounts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    true)jurisdiction    VARCHAR(8) NOT NULL,    -- TaxJurisdiction enum value: 'HR', 'RS', 'BA_FED', 'BA_RS'
    code            VARCHAR(16) NOT NULL,   -- e.g. '1300' (HR Kontni Plan), '204' (RS Pravilnik)
    name            VARCHAR(256) NOT NULL,
    account_type    VARCHAR(16) NOT NULL    -- ASSET, LIABILITY, EQUITY, INCOME, EXPENSE
                    CHECK (account_type IN ('ASSET', 'LIABILITY', 'EQUITY', 'INCOME', 'EXPENSE')),
    vat_treatment   VARCHAR(64),            -- e.g. 'STANDARD_RATE', 'EXEMPT', null for non-VAT accounts
    valid_from      DATE NOT NULL,
    valid_to        DATE,                   -- NULL = currently valid
    version         INT NOT NULL DEFAULT 1, -- increments per Pravilnik revision
    notes           TEXT,                   -- statutory reference e.g. "NN 78/2015, čl. 5"
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (jurisdiction, code, valid_from)
);

CREATE INDEX idx_coa_jurisdiction_date
    ON chart_of_accounts (jurisdiction, valid_from, valid_to);

-- Query pattern: entries valid on a given transaction date
-- SELECT * FROM chart_of_accounts
-- WHERE jurisdiction = $1
--   AND valid_from <= $2
--   AND (valid_to IS NULL OR valid_to > $2)
-- ORDER BY code;

-- When Croatia raises PDV from 25% to 27% on 2027-01-01:
-- INSERT INTO chart_of_accounts (jurisdiction, code, name, account_type, vat_treatment, valid_from, version)
-- VALUES ('HR', '2400', 'PDV po stopi 27%', 'LIABILITY', 'STANDARD_RATE', '2027-01-01', 2);
-- UPDATE chart_of_accounts SET valid_to = '2026-12-31'
-- WHERE jurisdiction = 'HR' AND code = '2400' AND valid_to IS NULL AND version = 1;
-- No code change required.
```

**Seeding:** `CountryPlugin.getChartOfAccountsDefaults()` returns the list of entries
that Flyway data migrations insert into `chart_of_accounts` for each jurisdiction.
Flyway V18 (Phase 1H — separate from V17 RLS) seeds HR Kontni Plan entries.

### 2.4 Exchange Rate Precision Upgrade

**Current precision (CLAUDE.md database rules):** `NUMERIC(19,4)` for ALL monetary amounts.

**Upgrade required for FX rate columns specifically:**

Exchange rates require higher precision than invoice monetary amounts. Using `NUMERIC(19,4)`
for an exchange rate means EUR/RSD at 117.2350 is representable, but EUR/BAM at
1.95583 is stored as 1.9558 — a systematic rounding error that compounds across large
invoice volumes and cross-currency reconciliation.

**Decision:** FX rate columns upgrade to `NUMERIC(20,10)`. Monetary amount columns
(invoice totals, line amounts, tax amounts) remain `NUMERIC(19,4)`.

```sql
-- V17c__exchange_rate_precision.sql (Phase 2A parallel)

ALTER TABLE exchange_rates
    ALTER COLUMN rate TYPE NUMERIC(20,10);  -- was NUMERIC(19,4)

-- If an exchange_rate_history or similar snapshot table exists:
-- ALTER TABLE exchange_rate_history
--     ALTER COLUMN rate TYPE NUMERIC(20,10);

-- NEVER change invoice_items.unit_price, invoice_items.line_total,
-- transactions.amount, etc. — those remain NUMERIC(19,4).
-- Only rate/exchange_rate columns receive this upgrade.
```

**Invariant:** All monetary arithmetic (invoice totals, tax calculations, double-entry
postings) remains at `NUMERIC(19,4)`. The precision upgrade is scoped to the FX
rate storage layer only. Rounding when applying FX rates to amounts: round half-even
(banker's rounding) to 4 decimal places after multiplication.

---

## 3. Connection Middleware — Setting `app.current_org_id`

The RLS policies use `current_setting('app.current_org_id')::uuid`. This session
variable must be set on every database connection before any query executes.

**Pattern (Kotlin / Exposed / HikariCP):**

```kotlin
// apps/api/src/main/kotlin/no/alai/bilko/db/OrgContextInterceptor.kt (Phase 2A NEW)

/**
 * Sets the PostgreSQL session variable `app.current_org_id` to the authenticated
 * org's UUID before any database access.
 *
 * Called from the Ktor routing pipeline after JWT validation, before the
 * database transaction opens.
 *
 * Must reset after the request completes — use try/finally or Ktor plugin lifecycle.
 */
fun setOrgContext(orgId: UUID) {
    transaction {
        exec("SET LOCAL app.current_org_id = '${orgId}'")
    }
}

fun clearOrgContext() {
    transaction {
        exec("RESET app.current_org_id")
        // or: exec("SET LOCAL app.current_org_id = ''")
    }
}
```

**Failure mode:** If `app.current_org_id` is not set, `current_setting('app.current_org_id')`
throws an error in PostgreSQL (by default). To make it return NULL instead (for Flyway
admin connections that do not set the variable):

```sql
-- In V17 migration, set default:
ALTER DATABASE bilko_demo SET app.current_org_id = '';
```

And in the policy, guard against empty string:

```sql
USING (
    CASE WHEN current_setting('app.current_org_id', true) = ''
         OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false  -- deny if not set
         ELSE organization_idorg_id = current_setting('app.current_org_id', true)::uuid
    END
)
WITH CHECK (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    );

-- ─── expenses ────────────────────────────────────────────────────────────────────────────────
DROP POLICY org_isolation ON expenses;
CREATE POLICY org_isolation ON expenses
    AS RESTRICTIVE FOR ALL TO bilko_app
    USING (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    )
    WITH CHECK (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    );

-- ─── transactions ─────────────────────────────────────────────────────────────────────────────
DROP POLICY org_isolation ON transactions;
CREATE POLICY org_isolation ON transactions
    AS RESTRICTIVE FOR ALL TO bilko_app
    USING (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    )
    WITH CHECK (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    );

-- ─── bank_accounts ────────────────────────────────────────────────────────────────────────────
DROP POLICY org_isolation ON bank_accounts;
CREATE POLICY org_isolation ON bank_accounts
    AS RESTRICTIVE FOR ALL TO bilko_app
    USING (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    )
    WITH CHECK (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    );

-- ─── bank_transactions ────────────────────────────────────────────────────────────────────────
DROP POLICY org_isolation ON bank_transactions;
CREATE POLICY org_isolation ON bank_transactions
    AS RESTRICTIVE FOR ALL TO bilko_app
    USING (
        bank_account_id IN (
            SELECT id FROM bank_accounts
            WHERE organization_id = current_setting('app.current_org_id', true)::uuid
              AND current_setting('app.current_org_id', true) IS NOT NULL
              AND current_setting('app.current_org_id', true) <> ''
              AND length(current_setting('app.current_org_id', true)) = 36
        )
    )
    WITH CHECK (
        bank_account_id IN (
            SELECT id FROM bank_accounts
            WHERE organization_id = current_setting('app.current_org_id', true)::uuid
              AND current_setting('app.current_org_id', true) IS NOT NULL
              AND current_setting('app.current_org_id', true) <> ''
              AND length(current_setting('app.current_org_id', true)) = 36
        )
    );

-- ─── accounts ─────────────────────────────────────────────────────────────────────────────────
DROP POLICY org_isolation ON accounts;
CREATE POLICY org_isolation ON accounts
    AS RESTRICTIVE FOR ALL TO bilko_app
    USING (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    )
    WITH CHECK (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    );

-- ─── contacts ─────────────────────────────────────────────────────────────────────────────────
DROP POLICY org_isolation ON contacts;
CREATE POLICY org_isolation ON contacts
    AS RESTRICTIVE FOR ALL TO bilko_app
    USING (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    )
    WITH CHECK (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE organization_id = current_setting('app.current_org_id', true)::uuid
        END
    );

-- ─── invoice_items ────────────────────────────────────────────────────────────────────────────
DROP POLICY org_isolation ON invoice_items;
CREATE POLICY org_isolation ON invoice_items
    AS RESTRICTIVE FOR ALL TO bilko_app
    USING (
        invoice_id IN (
            SELECT id FROM invoices
            WHERE organization_id = current_setting('app.current_org_id', true)::uuid
              AND current_setting('app.current_org_id', true) IS NOT NULL
              AND current_setting('app.current_org_id', true) <> ''
              AND length(current_setting('app.current_org_id', true)) = 36
        )
    )
    WITH CHECK (
        invoice_id IN (
            SELECT id FROM invoices
            WHERE organization_id = current_setting('app.current_org_id', true)::uuid
              AND current_setting('app.current_org_id', true) IS NOT NULL
              AND current_setting('app.current_org_id', true) <> ''
              AND length(current_setting('app.current_org_id', true)) = 36
        )
    );

-- ─── organizations ────────────────────────────────────────────────────────────────────────────
DROP POLICY org_isolation ON organizations;
CREATE POLICY org_isolation ON organizations
    AS RESTRICTIVE FOR ALL TO bilko_app
    USING (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE id = current_setting('app.current_org_id', true)::uuid
        END
    )
    WITH CHECK (
        CASE
            WHEN current_setting('app.current_org_id', true) IS NULL
              OR current_setting('app.current_org_id', true) = ''
              OR length(current_setting('app.current_org_id', true)) <> 36
            THEN false
            ELSE id = current_setting('app.current_org_id', true)::uuid
        END
    );

6.5 Application-Layer Cleanup After Phase 2C

After V23 applies on production, ADR-005 is formally superseded. CodeCraft may begin removing redundant WHERE org_id = :principalOrgId clauses from service methods. This is NOT required immediately — removing them is a cleanup task, not a correctness task.``` The dual-layer`true` was intentional during the migration period.

Do NOT remove application-layer WHERE clauses from:

• Test code (integration tests should still verify org scoping explicitly)
• Admin / superuser code paths (these bypass RLS and need the WHERE clause for correctness)
• Any query that explicitly needs cross-org access (job enumeration phase)

7. Rollback Procedure (Phase 2C)

If RESTRICTIVE policies cause production traffic degradation after Phase 2C applies:

7.1 Immediate Mitigation (< 5 minutes)

Revert RLSparameter to PERMISSIVE`current_setting()` withoutmakes removingit policiesreturn (fastest path — no Flyway involved):

-- Run as bilko_admin via Cloud SQL console or break-glass psql session:
-- Revert all RESTRICTIVE → PERMISSIVE without dropping policies

DO $$
DECLARE
    t text;
    tables text[] := ARRAY['invoices','expenses','transactions','bank_accounts',
                            'bank_transactions','accounts','contacts',
                            'invoice_items','organizations'];
BEGIN
    FOREACH t IN ARRAY tables LOOP
        EXECUTE format('DROP POLICY org_isolation ON %I', t);
    END LOOP;
END $$;

-- Restore PERMISSIVE policies from V17 DDL.
-- The V17 PERMISSIVE policy text is the source of truth.
-- Copy-paste V17 CREATE POLICY blocks here.
-- NOTE: Do NOT drop and recreate ENABLE ROW LEVEL SECURITY — leave RLS enabled.
-- NOTE: Do NOT drop FORCE ROW LEVEL SECURITY — leave force enabled.

After this is applied: RLS is back in PERMISSIVE mode. Application-layer WHERE clauses have NOT been removed yet (per §6.5 timing). The system is back to the Phase 2A dual-layer state. Services are unaffected.

7.2 Root Cause Analysis Gate

Before re-attempting Phase 2C:

•  Identify the query or code path that failed (application logs, Cloud SQL slow query log)
•  Determine if the failure was: (a) missing orgTransaction() call, (b) incorrect USING clause, (c) background job without bilko_job role, (d) monitoring query touching tenant table
•  Fix the root cause — do NOT re-attempt Phase 2C until the failure is understood and the §6.3 fail-closed test for that scenario passes
•  Run the full §6.3 checklist again from scratch
•  Reset the 30-day soak clock

7.3 Full Rollback (Flyway-based, if immediate mitigation insufficient)

If the PERMISSIVE revert does not stabilize the system (e.g., the issue is in FORCE RLS behaviorNULL rather than PERMISSIVE/RESTRICTIVEthrow distinction):

-- Full rollback — disable RLS entirely (emergency only, CEO approval required)
ALTER TABLE invoices            DISABLE ROW LEVEL SECURITY;
ALTER TABLE invoice_items       DISABLE ROW LEVEL SECURITY;
ALTER TABLE expenses            DISABLE ROW LEVEL SECURITY;
ALTER TABLE transactions        DISABLE ROW LEVEL SECURITY;
ALTER TABLE bank_transactions   DISABLE ROW LEVEL SECURITY;
ALTER TABLE bank_accounts       DISABLE ROW LEVEL SECURITY;
ALTER TABLE accounts            DISABLE ROW LEVEL SECURITY;
ALTER TABLE contacts            DISABLE ROW LEVEL SECURITY;
ALTER TABLE organizations       DISABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS org_isolation ON invoices;
DROP POLICY IF EXISTS org_isolation ON invoice_items;
DROP POLICY IF EXISTS org_isolation ON expenses;
DROP POLICY IF EXISTS org_isolation ON transactions;
DROP POLICY IF EXISTS org_isolation ON bank_transactions;
DROP POLICY IF EXISTS org_isolation ON bank_accounts;
DROP POLICY IF EXISTS org_isolation ON accounts;
DROP POLICY IF EXISTS org_isolation ON contacts;
DROP POLICY IF EXISTS org_isolation ON organizations;

ZAKON: Full rollback (disabling RLS entirely) requires CEO approval. It returns the system to a state where application-layer WHERE clauses are the sole isolation mechanism — the Phase 2A state is permanently lost. A new Flyway migration (re-applying V17) is required to restore it.

After full rollback, treat the situation as a new Phase 2A and restart the soak clock.

8. Alternatives Considered

Option A: Single RESTRICTIVE flip with no PERMISSIVE phase

Apply RESTRICTIVE policies immediately without Phase 2A (PERMISSIVE). Remove ADR-005 application-layer clauses at the same time.

Rejected: Any missing orgTransaction() call would result in a user seeing 0 rows (silent data loss), which is harder to debug than a cross-tenant leak. The PERMISSIVE phase ensures that missing context causes empty results that are caught in integration tests while the application layer maintains correct behavior.

Option B: FORCE ROW LEVEL SECURITY on all tables including organizations

Apply FORCE on organizations as well, so bilko_admin cannot bypass RLS on that table.

Rejected: Flyway migrations and admin tooling legitimately need cross-org access to organizations for onboarding, support, and migration operations. FORCE on organizations would require all admin queries to set app.current_org_id — creating a coupling between admin tooling and the RLS session variable mechanism. V17 explicitly omits FORCE on organizations for this reason.

Option C: Use connection-level SET instead of SET LOCAL

Set app.current_org_id at connection checkout (once per connection from the pool) rather than per-transaction via SET LOCAL.

Rejected: HikariCP connection pools reuse connections across requests. A connection-level SET would persist across requests, meaning a subsequent request on the same connection would inherit the previous request's org context ifwhen the variable is not reset.set. SET--- LOCAL## scopes4. automaticallyMigration Schedule | Phase | Flyway Version | Target | Blocking | | ---------- | --------------------------------------------------- | --------------------------- | ------------------------------------------- | | Phase 1H.1 | V16: `organizations.country` NOT NULL + CHECK | HR enum expansion (ADR-015) | ADR-015 accepted | | Phase 2A | V17: PERMISSIVE RLS + CoA table + FX rate precision | Stage only | CEO sign (this ADR) | | Phase 2A | V17 seed: HR Kontni Plan data | Stage only | V17 + PluginHR.getChartOfAccountsDefaults() | | Phase 2B | V18: audit log partitioning | Post-HR GA | Securion review | | Phase 2C | V19: RESTRICTIVE + retire ADR-005 app scoping | Post-Securion audit | Securion audit pass + CEO sign | All migrations use Flyway's expand/contract pattern. No migration modifies data in a way that cannot be reversed by a subsequent compensating migration. Backward compatibility is required across all rolling deployments. --- ## 5. Consequences ### 5.1 Positive 1. **Defence in depth.** Even if a developer introduces a missing `WHERE org_id` in a new route, RLS at the database layer prevents cross-tenant data exposure. 2. **GDPR jurisdiction extraction.** With `country_code` on `logged_actions` (Phase 2B), a request from Croatian DPA for "all data held on Croatian entities" is a single partition query, not a full-table scan with a filter. 3. **Audit surface.** Securion can review one set of RLS policies rather than auditing every application route for correct scoping. 4. **Option D readiness.** If ADR-023 §6 triggers (e.g., first paying HR customer), the same RLS DDL applies to the transactionper-country boundary,databases whichwithout alignschange. naturallyMigration path is not blocked by this ADR. ### 5.2 Negative 1. **Connection middleware requirement.** Every DB connection must set `app.current_org_id` before any query. Forgetting this in a new service or background job will cause all queries to return 0 rows (PERMISSIVE) or error (RESTRICTIVE). Mitigated by integration tests that verify the context middleware fires. 2. **Flyway admin bypass.** Flyway and admin tooling must run as a role that bypasses RLS (`bilko_admin` with Exposed'sBYPASSRLS). transactionThis {}role blockmust andbe eliminateskept tightly restricted — it is a privilege escalation path. 3. **Phase 2A adds overhead.** Each query now evaluates an additional predicate. At current scale (0 paying customers) the raceoverhead conditionis entirely.

9.p95 Consequences

9.1latency Positive

1. Phase 2C can proceed independently of HR GA timeline — all gate criteria are measurable during theafter Phase 2A soakmigration period.
on
2. Thestage. bilko_app### grant5.3 matrixRisks removes1. **GDPR data residency.** Croatian entity data in Cloud SQL europe-north1 (Finland) is legally compliant (EU/EEA). If a future HR DPA contract specifies Frankfurt, a regional migration is required. This ADR does not block that migration. 2. **RLS policy gap.** An incorrect USING clause (e.g., JOIN condition that broadens the ambiguityscope) aroundcould whichexpose cross-tenant data. **Mitigation:** Securion audit before Phase 2C (RESTRICTIVE), automated rogue-role hastest whichin privileges.CI. The3. immutability**Migration enforcement on transactions/bank_transactions adds a defence-in-depth layer against ledger tampering.
3. The service-account separation (bilko_admin/bilko_job/bilko_app/bilko_monitor) follows least-privilege.synchronization.** A compromisedFlyway applicationmigration processfailure cannotmid-run runleaves all markets degraded. All V17+ migrations must be backward-compatible and use expand/contract pattern. If V17 fails, rollback is: `DROP POLICY` + `ALTER TABLE ... DISABLE ROW LEVEL SECURITY`. --- ## 6. References | Reference | Path | Lines | | ----------------------------------------------------------- | ------------------------------------------------------------------------------------------------ | ------- | | ADR-bilko-001 (ancestor draft, absorbed by this ADR) | `~/system/specs/bilko-multi-market-architecture-plan/ADR-bilko-001-multi-tenant-architecture.md` | 1–162 | | ADR-bilko-003 §Layer 3 (versioned CoA model) | `~/system/specs/bilko-multi-market-architecture-plan/ADR-bilko-003-market-abstraction-layers.md` | 122–143 | | ADR-023 §6 (single-DB migration triggers — not fired) | `docs/architecture/ADR-023-TRANSITIONAL-MULTI-MARKET-ROUTING.md` | 166–176 | | Plan v3 §4a (Option D not triggered — evidence) | `~/system/specs/bilko-multi-market-architecture-plan-v3-2026-05-11.md` | 100–108 | | Plan v3 §4c (RLS timing — PERMISSIVE before Phase 1H) | `~/system/specs/bilko-multi-market-architecture-plan-v3-2026-05-11.md` | 135–145 | | Plan v3 §4d (EU data residency does not block HR GA) | `~/system/specs/bilko-multi-market-architecture-plan-v3-2026-05-11.md` | 179–183 | | ADR-015 §2.1 (TaxJurisdiction enum — `country_code` values) | `docs/architecture/ADR-015-FOUR-JURISDICTION-PLUGIN.md` | §2.1 | | Test drift memo (cross-tenant 500 leaks, Round 12.1/12.5) | `~/.claude/projects/-Users-makinja/memory/project_bilko_test_strategy_drift_2026-05-10.md` | — | --- ## 7. Approval **Architecture status:** Accepted (Phase 0' ADR consolidation) **CEO sign status:** SIGNED 2026-05-11 — Phase 2A V17 Flyway migrations.
PERMISSIVE
4. Themigration fail-closedauthorized checklistfor gives Securion a deterministic audit target.

9.2 Negative

1. The bilko_job role with BYPASSRLS requires careful access control. If bilko_job credentials leak, an attacker can enumerate all org IDs.
2. Thestage. Phase 2C RESTRICTIVE flip requiresremains re-runninggated on Securion audit + 30-day soak per §4 schedule. This ADR records the §6.3architectural checklistdecision. ifThe PhaseCEO 2Csignature below is rolled back — this means a minimum of 30 additional days of soak before re-attempting.
3. The transactions and bank_transactions UPDATE/DELETE revoke (§2.2) will cause unexpected permission errors if any future feature requires amending a posted transaction. Correction must be via compensating journal entries.

10. Approval

Architecture status: Accepted Phase 2C CEO sign status: REQUIRED SEPARATELY — Phase 2C flipthe gate requiresfor explicitexecution CEO sign at the time the §6.1 criteria are met. The ADR-017-RLS-MULTITENANCY.md sign (2026-05-11) coversof Phase 2A only.

11.8. Document History