Audit RLS coverage for all tables and add migration-time enforcement

[harry-rhesis]

Summary

Audit all database tables to verify Row-Level Security (RLS) is correctly enabled, and add an automated mechanism to ensure new tables from future migrations are properly covered.

Background / Context

The refresh_token table was recently added without organization_id (intentionally — it's pre-auth, like token and user). This highlighted that the blanket RLS migration (fcac5b8b5eb0) only ran once at creation time. Any tables added since then may not have RLS enabled, and there is no mechanism to catch this during development or CI.

Goals

• Confirm every table that has organization_id has RLS enabled with a tenant_isolation policy.
• Confirm tables without organization_id (auth-layer: user, token, refresh_token) are explicitly excluded.
• Prevent future regressions by adding an automated check.

Deliverables

• Audit script or query that reports RLS status for every table.
• A pytest test (or Alembic post-migration hook) that asserts:
  • All tables with organization_id have RLS enabled + tenant_isolation policy.
  • All tables in the explicit exclude list do NOT have RLS or have it disabled.
  • No table is left uncategorised.
• Fix any tables found to be misconfigured.

Steps / Action Plan

1. Query pg_class / pg_policies to list RLS status of every table.
2. Cross-reference with information_schema.columns to find tables with/without organization_id.
3. Identify gaps (tables with organization_id but no RLS, or vice versa).
4. Fix any misconfigured tables via a new Alembic migration.
5. Add a pytest test that runs the audit query and fails if a table is miscategorised.
6. Document the RLS exclude list (user, token, refresh_token, alembic_version, organization) in a comment or constant.

Acceptance Criteria

• All tables with organization_id have RLS enabled with tenant_isolation policy
• Auth-layer tables (user, token, refresh_token) are explicitly excluded from RLS
• A test exists that fails if a new migration adds a table with organization_id but no RLS
• A test exists that fails if a new migration enables RLS on an auth-layer table