INTERNAL CONFIDENTIAL -- AZ1.AI Inc. -- Authorized Personnel Only This document contains proprietary security architecture details. Unauthorized distribution, reproduction, or disclosure is strictly prohibited.

CFS-DB-003: Security & Multi-Tenancy Model

Classification: INTERNAL CONFIDENTIAL Owner: Hal Casteel, CEO/CTO, AZ1.AI Inc. Document Code: CFS-DB-003 Last Updated: 2026-02-20 Component: CODITECT Financial Suite -- General Ledger Engine Database: PostgreSQL 16+

Multi-Tenancy Architecture
Row-Level Security Policies
Application Role and Permissions
Tenant Context Injection
Audit Trail Immutability
Data Protection Patterns
Security Headers (Application Layer)
Compliance Alignment
Security Considerations and Recommendations
Related Documents

1. Multi-Tenancy Architecture

1.1 Approach: Shared-Schema, Shared-Database with Row-Level Security

The GL Engine employs a shared-schema, shared-database multi-tenancy model enforced entirely by PostgreSQL Row-Level Security (RLS). All tenants reside in a single PostgreSQL database and a single schema. Tenant data is segregated at the row level, not at the database or schema level.

This architecture was selected over database-per-tenant and schema-per-tenant alternatives after evaluating operational complexity, isolation guarantees, and scaling characteristics at the anticipated tenant volume (hundreds to thousands of accounting firm clients).

Alternative	Isolation Level	Operational Overhead	Scalability	Selected
Database per tenant	Strongest	Extreme (migrations x N databases)	Poor beyond ~50 tenants	No
Schema per tenant	Strong	High (search_path management)	Moderate	No
Shared schema + RLS	Strong (hardware-enforced)	Low (single migration path)	Excellent	Yes

1.2 The Mandatory Tenant Column

Every GL table carries tenant_id UUID NOT NULL as the first non-primary-key column. This column is:

Not nullable -- enforced at the DDL level with NOT NULL
Not defaulted -- the application must supply it explicitly; no implicit tenant assignment
Indexed on every table -- the first column in every composite index, enabling efficient tenant-scoped queries
Validated by RLS -- PostgreSQL enforces that every SELECT, INSERT, UPDATE, and DELETE operates only on rows belonging to the current tenant context

The eight GL tables and their tenant column declarations are:

Table	Column	Type	Constraint
`gl_accounts`	`tenant_id`	`UUID`	`NOT NULL`
`gl_fiscal_periods`	`tenant_id`	`UUID`	`NOT NULL`
`gl_journal_entries`	`tenant_id`	`UUID`	`NOT NULL`
`gl_journal_lines`	`tenant_id`	`UUID`	`NOT NULL`
`gl_account_balances`	`tenant_id`	`UUID`	`NOT NULL`
`gl_exchange_rates`	`tenant_id`	`UUID`	`NOT NULL`
`gl_audit_log`	`tenant_id`	`UUID`	`NOT NULL`
`gl_sped_exports`	`tenant_id`	`UUID`	`NOT NULL`

1.3 Tenant Context Mechanism

The application signals which tenant is active for a given transaction using a PostgreSQL session-local configuration variable:

SET LOCAL app.current_tenant = '{tenant-uuid}';

SET LOCAL restricts the setting to the current transaction. When the transaction ends via COMMIT or ROLLBACK, the setting is automatically cleared. This property is foundational to security: a connection returned to the pool cannot carry a stale tenant context to the next consumer.

RLS policies read this variable via current_setting('app.current_tenant')::uuid and filter all table access accordingly. This evaluation occurs at the PostgreSQL executor level -- inside the database engine itself -- not in application code. Even a compromised application that omitted tenant filtering in its SQL would still be blocked at the database layer.

1.4 Architecture Benefits

Benefit	Description
Single migration path	Schema changes are applied once and take effect for all tenants simultaneously. No per-tenant migration coordination required.
Simple deployment	One database, one schema, one connection pool. No routing logic to determine which database to connect to per tenant.
Hardware-level isolation guarantee	PostgreSQL RLS is enforced by the query executor before any data is read or written. It cannot be bypassed by application-level bugs.
Connection pool efficiency	All tenants share a single pool. Pool connections are not partitioned per tenant, improving utilization.
Consistent compliance	Audit logging, encryption, and compliance controls apply uniformly. No risk that a per-tenant database was provisioned without a required control.

2. Row-Level Security Policies

2.1 RLS Activation

RLS is enabled and forced on all eight GL tables. The FORCE ROW LEVEL SECURITY clause is critical: it ensures that even the table owner (the gl_admin role that runs migrations) is subject to RLS when connected without the superuser privilege. This prevents accidental or unauthorized cross-tenant data access through privileged database roles.

-- Enable RLS on all GL tables
ALTER TABLE gl_accounts          ENABLE ROW LEVEL SECURITY;
ALTER TABLE gl_fiscal_periods    ENABLE ROW LEVEL SECURITY;
ALTER TABLE gl_journal_entries   ENABLE ROW LEVEL SECURITY;
ALTER TABLE gl_journal_lines     ENABLE ROW LEVEL SECURITY;
ALTER TABLE gl_account_balances  ENABLE ROW LEVEL SECURITY;
ALTER TABLE gl_exchange_rates    ENABLE ROW LEVEL SECURITY;
ALTER TABLE gl_audit_log         ENABLE ROW LEVEL SECURITY;
ALTER TABLE gl_sped_exports      ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owners (critical for security)
ALTER TABLE gl_accounts          FORCE ROW LEVEL SECURITY;
ALTER TABLE gl_fiscal_periods    FORCE ROW LEVEL SECURITY;
ALTER TABLE gl_journal_entries   FORCE ROW LEVEL SECURITY;
ALTER TABLE gl_journal_lines     FORCE ROW LEVEL SECURITY;
ALTER TABLE gl_account_balances  FORCE ROW LEVEL SECURITY;
ALTER TABLE gl_exchange_rates    FORCE ROW LEVEL SECURITY;
ALTER TABLE gl_audit_log         FORCE ROW LEVEL SECURITY;
ALTER TABLE gl_sped_exports      FORCE ROW LEVEL SECURITY;

2.2 Standard Policy Pattern (Seven Tables)

Seven of the eight tables use an identical bidirectional isolation policy. The USING clause filters all read operations (SELECT, UPDATE, DELETE). The WITH CHECK clause filters all write operations (INSERT, UPDATE). Both must match the current tenant context, creating a complete isolation boundary.

Policy template:

CREATE POLICY tenant_isolation_{table} ON {table}
  USING     (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

Individual policy declarations:

-- gl_accounts: Chart of accounts
CREATE POLICY tenant_isolation_accounts ON gl_accounts
  USING     (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- gl_fiscal_periods: Fiscal year and period management
CREATE POLICY tenant_isolation_periods ON gl_fiscal_periods
  USING     (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- gl_journal_entries: Journal entry headers
CREATE POLICY tenant_isolation_entries ON gl_journal_entries
  USING     (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- gl_journal_lines: Journal entry detail lines
CREATE POLICY tenant_isolation_lines ON gl_journal_lines
  USING     (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- gl_account_balances: Materialized balance cache
CREATE POLICY tenant_isolation_balances ON gl_account_balances
  USING     (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- gl_exchange_rates: Currency exchange rates
CREATE POLICY tenant_isolation_rates ON gl_exchange_rates
  USING     (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- gl_sped_exports: Brazilian SPED regulatory export tracking
CREATE POLICY tenant_isolation_sped ON gl_sped_exports
  USING     (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

2.3 Special Case: gl_audit_log (Read Filter Only)

The audit log table uses a USING-only policy, deliberately omitting WITH CHECK. This design reflects the table's operational contract: the application layer never writes directly to gl_audit_log. All insertions occur exclusively through the fn_audit_log() trigger function, which is defined with SECURITY DEFINER semantics and operates with the function owner's privileges.

Omitting WITH CHECK is not a security weakness -- it is the correct implementation. Adding WITH CHECK would break the trigger-based insert mechanism without providing any additional isolation guarantee, since direct application writes to gl_audit_log are not granted by the gl_app role permissions.

-- gl_audit_log: Immutable audit trail (append-only via triggers)
CREATE POLICY tenant_isolation_audit ON gl_audit_log
  USING (tenant_id = current_setting('app.current_tenant')::uuid);
-- No WITH CHECK: inserts are performed exclusively by the fn_audit_log() trigger,
-- not by direct application writes. Direct INSERT is not granted to gl_app.

2.4 Policy Summary

Table	Policy Name	USING	WITH CHECK	Reason
`gl_accounts`	`tenant_isolation_accounts`	Yes	Yes	Full read/write isolation
`gl_fiscal_periods`	`tenant_isolation_periods`	Yes	Yes	Full read/write isolation
`gl_journal_entries`	`tenant_isolation_entries`	Yes	Yes	Full read/write isolation
`gl_journal_lines`	`tenant_isolation_lines`	Yes	Yes	Full read/write isolation
`gl_account_balances`	`tenant_isolation_balances`	Yes	Yes	Full read/write isolation
`gl_exchange_rates`	`tenant_isolation_rates`	Yes	Yes	Full read/write isolation
`gl_sped_exports`	`tenant_isolation_sped`	Yes	Yes	Full read/write isolation
`gl_audit_log`	`tenant_isolation_audit`	Yes	No	Insert-only via trigger; app has no direct INSERT

2.5 What Happens Without a Tenant Context

If the application executes a query without first setting app.current_tenant, PostgreSQL evaluates current_setting('app.current_tenant') and raises an error if the variable has never been set in the session. The current_setting() function has an optional second argument missing_ok; the RLS policies use the single-argument form which throws:

ERROR:  unrecognized configuration parameter "app.current_tenant"

This is a fail-secure design: a missing tenant context causes a hard error, not silent data leakage. All data is invisible by default; the tenant context must be explicitly asserted.

3. Application Role and Permissions

3.1 Role Definition

A dedicated, non-superuser database role handles all application-originated database access:

-- Create application role (non-superuser — RLS is enforced for this role)
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'gl_app') THEN
    CREATE ROLE gl_app LOGIN PASSWORD 'gl_app_password';
  END IF;
END
$$;

-- Schema access
GRANT USAGE ON SCHEMA public TO gl_app;

-- Table permissions: SELECT, INSERT, UPDATE only
-- DELETE is intentionally omitted — no physical deletion of financial records
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO gl_app;

-- Sequence access for UUID generation and serial columns
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO gl_app;

-- Function execution for business logic functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO gl_app;

3.2 Permission Rationale

Permission	Granted	Rationale
`SELECT`	Yes	Required for all query operations
`INSERT`	Yes	Required for creating records
`UPDATE`	Yes	Required for status transitions and corrections
`DELETE`	No	Financial records are never physically deleted; soft deletion via status fields only
`TRUNCATE`	No	Catastrophic operation; not required by any application workflow
`REFERENCES`	No	Foreign key creation; not required at runtime
`TRIGGER`	No	Trigger management is an administrative operation
Superuser	No	Superusers bypass RLS; `gl_app` must never be superuser

3.3 Role Separation

Two roles govern database operations:

Role	Purpose	RLS Enforced	Used For
`gl_app`	Runtime application access	Yes (non-superuser)	All API-driven queries, tenant data access
`gl_admin`	Schema migrations and administration	Yes (FORCE RLS applies)	Running migrations, schema changes, operational maintenance

The gl_admin role connects with the admin user credentials defined in environment variables GL_DB_ADMIN_USER and GL_DB_ADMIN_PASSWORD. It is never used by the application connection pool at runtime. Migration tooling (runMigrations()) instantiates a separate pg.Pool using admin credentials and terminates it immediately after migrations complete.

4. Tenant Context Injection

4.1 Implementation

The application layer injects tenant context as a transaction-scoped PostgreSQL setting before every database operation. This is implemented in /src/services/db.ts:

// ============================================================================
// Tenant Context Injection
// PostgreSQL session-local variables, scoped to the current transaction.
// SET LOCAL automatically clears on COMMIT or ROLLBACK.
// ============================================================================

async function setTenantContext(
  client: pg.PoolClient,
  ctx: QueryContext
): Promise<void> {
  // Primary isolation variable — required for all RLS policies
  await client.query(`SET LOCAL app.current_tenant = '${ctx.tenantId}'`);

  // Optional: user identity for audit log attribution
  if (ctx.userId) {
    await client.query(`SET LOCAL app.current_user_id = '${ctx.userId}'`);
  }

  // Optional: module source for audit log classification
  if (ctx.moduleSource) {
    await client.query(`SET LOCAL app.module_source = '${ctx.moduleSource}'`);
  }
}

4.2 Transaction Wrapper

Every database interaction is wrapped in a transaction that sets the tenant context immediately after BEGIN:

// Single query with tenant context
export async function query<T extends pg.QueryResultRow = pg.QueryResultRow>(
  ctx: QueryContext,
  sql: string,
  params?: unknown[]
): Promise<pg.QueryResult<T>> {
  const client = await getPool().connect();
  try {
    await client.query('BEGIN');
    await setTenantContext(client, ctx);       // RLS context established
    const result = await client.query<T>(sql, params);
    await client.query('COMMIT');              // Context cleared automatically
    return result;
  } catch (err) {
    await client.query('ROLLBACK');            // Context cleared on rollback too
    throw err;
  } finally {
    client.release();                          // Connection returned to pool clean
  }
}

// Multi-statement transaction with tenant context
export async function transaction<T>(
  ctx: QueryContext,
  fn: (client: pg.PoolClient) => Promise<T>
): Promise<T> {
  const client = await getPool().connect();
  try {
    await client.query('BEGIN');
    await setTenantContext(client, ctx);       // RLS context established for all statements
    const result = await fn(client);
    await client.query('COMMIT');              // Context cleared automatically
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

4.3 QueryContext Interface

export interface QueryContext {
  tenantId: string;      // UUID — required — maps to app.current_tenant
  userId?: string;       // UUID — optional — maps to app.current_user_id
  moduleSource?: string; // Enum string — optional — maps to app.module_source
}

4.4 Connection Pool Configuration

const DEFAULT_CONFIG: DBConfig = {
  host:               process.env['GL_DB_HOST'] ?? 'localhost',
  port:               parseInt(process.env['GL_DB_PORT'] ?? '5432', 10),
  database:           process.env['GL_DB_NAME'] ?? 'gl_engine',
  user:               process.env['GL_DB_USER'] ?? 'gl_app',
  password:           process.env['GL_DB_PASSWORD'],  // Required from environment
  max:                20,      // Maximum concurrent connections
  idleTimeoutMillis:  30000,   // 30 seconds idle before connection release
};

4.5 Security Properties of This Approach

Property	Mechanism	Security Effect
Transaction-scoped context	`SET LOCAL` instead of `SET`	Context cannot persist across transaction boundaries; pool connections are always clean
Fail-secure on missing context	`current_setting()` throws without `missing_ok`	Queries without tenant context fail immediately; no silent data access
Immutable during transaction	`SET LOCAL` cannot be changed mid-transaction by a different thread	RLS context set at transaction open cannot be overridden by subsequent statements in the same transaction
Audit variable propagation	`app.current_user_id` and `app.module_source` set alongside tenant	Audit log entries are fully attributed without requiring application-layer audit writes

5. Audit Trail Immutability

5.1 Design Principle

The audit log is the evidentiary foundation for regulatory compliance, fraud investigation, and incident response. Its value depends entirely on its trustworthiness. A mutable audit log provides no assurance -- entries could be modified to conceal unauthorized activity.

The GL Engine enforces immutability at the database trigger level, making it impossible for any application operation to modify or delete an audit record, regardless of the calling role's permissions.

5.2 Immutability Trigger

-- ============================================================================
-- TRIGGER: trg_prevent_audit_mutation
-- Blocks ALL UPDATE and DELETE operations on gl_audit_log.
-- The audit log is INSERT-only via the fn_audit_log() trigger.
-- ============================================================================

CREATE OR REPLACE FUNCTION fn_prevent_audit_mutation()
RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION
    'GL_060: Audit log is immutable. UPDATE and DELETE operations are prohibited.';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_audit_mutation
  BEFORE UPDATE OR DELETE ON gl_audit_log
  FOR EACH ROW
  EXECUTE FUNCTION fn_prevent_audit_mutation();

Any attempt to execute UPDATE gl_audit_log ... or DELETE FROM gl_audit_log ... -- by any role including gl_admin -- raises error GL_060 and aborts the transaction.

5.3 Audit Log Record Structure

CREATE TABLE gl_audit_log (
  id              UUID        NOT NULL DEFAULT uuid_generate_v4(),
  tenant_id       UUID        NOT NULL,    -- RLS-enforced tenant isolation
  table_name      TEXT        NOT NULL,    -- Name of the audited table
  record_id       UUID        NOT NULL,    -- UUID of the affected record
  action          gl_audit_action NOT NULL, -- INSERT | UPDATE | STATUS_CHANGE
  old_values      JSONB,                   -- Complete before-image (UPDATE only)
  new_values      JSONB,                   -- Complete after-image (INSERT/UPDATE)
  user_id         UUID,                    -- From app.current_user_id session variable
  session_id      UUID,                    -- Application session identifier
  ip_address      INET,                    -- Client IP address
  module_source   TEXT,                    -- From app.module_source session variable
  sped_export_id  UUID,                    -- Links audit entries to SPED exports
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

5.4 Audit Trigger Coverage

The fn_audit_log() trigger is applied to all mutable GL tables:

-- Applied to all tables that can be modified by application operations
CREATE TRIGGER trg_audit_accounts
  AFTER INSERT OR UPDATE ON gl_accounts
  FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_periods
  AFTER INSERT OR UPDATE ON gl_fiscal_periods
  FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_entries
  AFTER INSERT OR UPDATE ON gl_journal_entries
  FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_lines
  AFTER INSERT ON gl_journal_lines
  FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_balances
  AFTER INSERT OR UPDATE ON gl_account_balances
  FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

CREATE TRIGGER trg_audit_rates
  AFTER INSERT OR UPDATE ON gl_exchange_rates
  FOR EACH ROW EXECUTE FUNCTION fn_audit_log();

Note that gl_journal_lines captures INSERT only (journal lines are never modified after creation). gl_audit_log itself and gl_sped_exports do not have audit triggers on them -- the audit log is self-evidencing, and SPED exports have their own integrity mechanism (SHA-256 file hash).

5.5 STATUS_CHANGE Classification

The audit trigger distinguishes routine field updates from state transitions, which are the highest-significance events in financial workflows:

-- Within fn_audit_log():
IF TG_TABLE_NAME IN ('gl_journal_entries', 'gl_fiscal_periods') THEN
  IF (v_old ->> 'status') IS DISTINCT FROM (v_new ->> 'status')
     OR (v_old ->> 'state') IS DISTINCT FROM (v_new ->> 'state') THEN
    v_action := 'STATUS_CHANGE';  -- Differentiated audit action
  ELSE
    v_action := 'UPDATE';
  END IF;
END IF;

This enables compliance queries that specifically retrieve status transition events (e.g., all period closings, all entry postings, all reversals) without scanning the full audit log.

5.6 Partitioning for Retention Management

gl_audit_log is partitioned by half-year ranges. This serves two purposes:

Performance -- queries with a date range predicate on created_at are executed against only the relevant partition(s), avoiding full table scans.
Retention management -- regulatory retention periods can be enforced by dropping individual partitions after the required retention window expires, without disrupting active partitions.

-- Partitions pre-created for 2025 through 2027
CREATE TABLE gl_audit_log_2025_h1 PARTITION OF gl_audit_log
  FOR VALUES FROM ('2025-01-01') TO ('2025-07-01');
CREATE TABLE gl_audit_log_2025_h2 PARTITION OF gl_audit_log
  FOR VALUES FROM ('2025-07-01') TO ('2026-01-01');
CREATE TABLE gl_audit_log_2026_h1 PARTITION OF gl_audit_log
  FOR VALUES FROM ('2026-01-01') TO ('2026-07-01');
CREATE TABLE gl_audit_log_2026_h2 PARTITION OF gl_audit_log
  FOR VALUES FROM ('2026-07-01') TO ('2027-01-01');
CREATE TABLE gl_audit_log_2027_h1 PARTITION OF gl_audit_log
  FOR VALUES FROM ('2027-01-01') TO ('2027-07-01');
CREATE TABLE gl_audit_log_2027_h2 PARTITION OF gl_audit_log
  FOR VALUES FROM ('2027-07-01') TO ('2028-01-01');

New partitions must be created before each half-year boundary. This is an operational responsibility tracked in the infrastructure runbook.

6. Data Protection Patterns

6.1 No Physical Deletion

The GL Engine contains no DELETE SQL anywhere in the application layer. The gl_app role is not granted the DELETE privilege. Financial data is never physically removed from the database. Instead, records transition through defined status states:

Record Type	Terminal State	Transition Mechanism
Accounts	`INACTIVE` or `BLOCKED`	Status field update via account service
Journal entries	`REVERSED`	Reversal creates a new offsetting entry
Fiscal periods	`LOCKED`	One-way state machine: `FUTURE -> OPEN -> CLOSED -> LOCKED`

This pattern ensures complete data lineage for the lifetime of each tenant's data.

6.2 Journal Entry Immutability

Once a journal entry transitions to POSTED status, the database trigger trg_prevent_posted_modification enforces that no fields other than status can be changed, and the only permitted status transition is to REVERSED:

CREATE OR REPLACE FUNCTION fn_prevent_posted_modification()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.status = 'POSTED' THEN
    IF NEW.status = 'REVERSED' THEN
      -- Verify no other fields were changed alongside the status
      IF NEW.reference_number != OLD.reference_number
         OR NEW.entry_date != OLD.entry_date
         OR NEW.description != OLD.description
         OR NEW.module_source != OLD.module_source
         OR NEW.period_id != OLD.period_id THEN
        RAISE EXCEPTION
          'GL_030: Cannot modify fields of a POSTED journal entry (id: %). '
          'Only status change to REVERSED is allowed.', OLD.id;
      END IF;
      RETURN NEW;
    ELSE
      RAISE EXCEPTION
        'GL_030: Cannot modify POSTED journal entry (id: %). '
        'Only reversal is permitted.', OLD.id;
    END IF;
  END IF;

  IF OLD.status = 'REVERSED' THEN
    RAISE EXCEPTION
      'GL_031: Cannot modify REVERSED journal entry (id: %).', OLD.id;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

6.3 Period Locking

Fiscal periods progress through a one-way state machine enforced by trg_period_state_machine. The LOCKED state is terminal: no journal entries can be posted to a locked period, and the period state cannot be reversed.

-- Valid transitions only: FUTURE -> OPEN -> CLOSED -> LOCKED
-- Any other transition raises GL_013
CREATE OR REPLACE FUNCTION fn_period_state_machine()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.state = 'FUTURE' AND NEW.state = 'OPEN'   THEN RETURN NEW; END IF;
  IF OLD.state = 'OPEN'   AND NEW.state = 'CLOSED' THEN
    NEW.closed_at := NOW();
    RETURN NEW;
  END IF;
  IF OLD.state = 'CLOSED' AND NEW.state = 'LOCKED' THEN
    NEW.locked_at := NOW();
    RETURN NEW;
  END IF;
  RAISE EXCEPTION
    'GL_013: Invalid period state transition from % to %. '
    'Valid transitions: FUTURE->OPEN->CLOSED->LOCKED', OLD.state, NEW.state;
END;
$$ LANGUAGE plpgsql;

6.4 SPED Export Integrity

Brazilian SPED regulatory exports (ECD, ECF, EFD) are tracked with a SHA-256 file hash stored in gl_sped_exports.file_hash. The hash is computed over the generated file content before storage and submission, providing:

Integrity verification -- the submitted file can be re-hashed and compared against the stored hash to confirm it has not been altered
Audit linkage -- gl_audit_log.sped_export_id links individual audit entries to the SPED export that included them, enabling reconstruction of what data was reported and when

Column	Purpose
`file_hash`	SHA-256 hash of the generated SPED file content
`file_size_bytes`	File size for consistency verification
`record_count`	Count of SPED registros for reconciliation
`validation_result`	JSON-encoded output of SPED validation rules

6.5 JSONB Snapshots in Audit Log

The old_values and new_values columns in gl_audit_log capture complete row snapshots using PostgreSQL's to_jsonb() function. This means:

Every field value present at the time of the operation is preserved, including fields added by future schema migrations (as they were valued at audit time)
Forensic investigators can reconstruct the exact state of any record at any point in its history by replaying the audit log sequence
The snapshots are self-contained; they do not depend on the current schema definition to be interpretable

7. Security Headers (Application Layer)

7.1 Tenant Identification Headers

The API layer receives tenant identity through HTTP headers that are validated before any database operation is initiated:

Header	Purpose	Validation
`X-Tenant-Id`	Identifies the active tenant (UUID)	Format-validated as UUID; verified against authenticated session
`X-User-Id`	Identifies the authenticated user (UUID)	Bound to JWT claims; must match authenticated principal

These headers are extracted from the HTTP request context and translated into the QueryContext object passed to all database operations. The mapping is:

X-Tenant-Id  -->  ctx.tenantId  -->  SET LOCAL app.current_tenant
X-User-Id    -->  ctx.userId    -->  SET LOCAL app.current_user_id

7.2 Header Validation Requirements

The X-Tenant-Id value must be present and must be a valid UUID v4 format
The authenticated JWT must include the same tenant claim as X-Tenant-Id; a mismatch rejects the request before any database access occurs
Requests without X-Tenant-Id are rejected at the API gateway with HTTP 400
Requests where the JWT tenant claim does not match X-Tenant-Id are rejected with HTTP 403

7.3 Defense in Depth

The header-based tenant validation is the application-layer defense. PostgreSQL RLS is the database-layer defense. Both must be satisfied for a request to access data. A bypass of the application-layer check would still be blocked by RLS. A bypass of RLS would require a superuser-level database credential, which is not accessible to the application runtime.

8. Compliance Alignment

8.1 Sarbanes-Oxley (SOX) -- Section 404

SOX Requirement	Implementation
Complete and accurate financial records	Double-entry enforcement via `trg_enforce_double_entry`
Immutable audit trail	`gl_audit_log` with `trg_prevent_audit_mutation`
Access controls over financial data	RBAC in application layer; RLS at database layer
Period locking	`gl_period_state_machine` with one-way `LOCKED` terminal state
Segregation of duties	`gl_app` role has no DDL or schema modification privileges

8.2 Brazilian SPED ECD/ECF Compliance

SPED Requirement	Implementation
CNPJ validation	Application-layer validation before tenant provisioning
Registro generation	`gl_sped_exports` with full generation metadata
File integrity	SHA-256 hash in `file_hash` column
Audit linkage	`gl_audit_log.sped_export_id` foreign key
Account mapping	`sped_natura`, `sped_cod_agl`, `sped_referencial_code` on `gl_accounts`
Period-based reporting	`gl_fiscal_periods` with fiscal year and period number

Privacy Requirement	Implementation
Data isolation between organizations	RLS prevents any cross-tenant data access
Audit of data access	All reads within a transaction that modifies data are captured in audit log
Data retention control	Partitioned `gl_audit_log` supports partition-level deletion at retention expiry
Right to access	Tenant-scoped data is fully queryable; all records are attributable to a tenant
Lawful basis for processing	Module source classification (`gl_module_source`) identifies processing purpose

8.4 Compliance Control Summary

Control	Standard	Mechanism	Layer
Tenant data isolation	LGPD/GDPR, SOC2 CC6	RLS policies	Database
Audit trail completeness	SOX 404, SOC2 CC7	Trigger-based audit capture	Database
Audit trail integrity	SOX 404, SOC2 CC7	`trg_prevent_audit_mutation`	Database
Access control enforcement	SOC2 CC6, HIPAA	Role-based grants, RLS	Database
Least privilege	SOC2 CC6	No DELETE or DDL granted to `gl_app`	Database
Financial record immutability	SOX 404	`trg_prevent_posted_modification`	Database
Period closure enforcement	SOX 404	`trg_period_state_machine`	Database
Regulatory export integrity	SPED ECD	SHA-256 file hash	Application + Database

9. Security Considerations and Recommendations

9.1 SQL Injection in Tenant Context Setting (Current Limitation)

Severity: Medium Status: Known -- remediation recommended before production deployment

The current setTenantContext() implementation uses string interpolation when setting the tenant context variables:

// Current implementation -- string interpolation
await client.query(`SET LOCAL app.current_tenant = '${ctx.tenantId}'`);

While ctx.tenantId is validated as a UUID at the API layer (and UUID format makes SQL injection practically infeasible), this pattern is technically a violation of parameterized query standards.

Recommended remediation:

// Recommended -- parameterized approach via format function
await client.query('SELECT set_config($1, $2, true)', [
  'app.current_tenant',
  ctx.tenantId
]);

The set_config(name, value, is_local) PostgreSQL function accepts parameters and provides the same SET LOCAL behavior while fully parameterizing the call. The third argument true makes the setting transaction-local, equivalent to SET LOCAL.

9.2 Password Management

Severity: High (for production) Status: Prototype default; must be resolved before production

The current rls/001_rls_policies.sql migration contains a hardcoded password:

CREATE ROLE gl_app LOGIN PASSWORD 'gl_app_password';

Required for production:

The gl_app password must be generated as a cryptographically random 32-character string at provisioning time
The password must be stored in a secrets manager (GCP Secret Manager, AWS Secrets Manager, or HashiCorp Vault)
The application must retrieve the password at startup from the secrets manager, not from environment variables or configuration files
Rotation policy: 90-day maximum credential lifetime with automated rotation
The migration script must read the password from an environment variable at migration time, not hardcode it

9.3 Network Isolation

Severity: High (for production)

The PostgreSQL instance must not be publicly accessible. Required network controls:

Control	Requirement
VPC placement	PostgreSQL in private subnet, no public IP
Firewall rules	Inbound port 5432 allowed only from application service CIDR
SSL/TLS	`ssl = on` in `postgresql.conf`; `sslmode=require` in connection strings
Client certificate auth	Recommended for service-to-service connections (`sslmode=verify-full`)

9.4 Encryption at Rest

Severity: Medium Status: Infrastructure-level decision pending

Financial data at rest should be encrypted. Options in preference order:

Cloud-managed encryption (recommended) -- GCP Cloud SQL or AWS RDS provides transparent encryption at rest using cloud KMS. Zero operational overhead; keys are managed by the cloud provider with HSM backing.
PostgreSQL Transparent Data Encryption (TDE) -- Available via extensions or commercial distributions (e.g., EDB). More complex to operate.
File system encryption -- dm-crypt/LUKS at the block device level if self-hosting. Key management responsibility falls on operations team.

9.5 Connection Encryption

All connections from the application connection pool to PostgreSQL must use TLS. The connection configuration must specify:

// Required for production pool configuration
ssl: {
  rejectUnauthorized: true,   // Verify the server certificate
  ca: fs.readFileSync('/path/to/ca-cert.pem'),  // CA that signed the server cert
}

9.6 Superuser Access Controls

PostgreSQL superuser access bypasses all RLS policies. Superuser credentials must be:

Never granted to the gl_app application role
Never granted to the gl_admin migration role
Restricted to the database owner account used only for provisioning
Protected by IAM-gated bastion host access in production
All superuser sessions logged via pgaudit extension

9.7 pg_audit Extension

For production deployments targeting SOC2 Type II, the pgaudit extension should be installed and configured to capture:

All DDL statements (for change management verification)
All superuser connections (for access monitoring)
Failed authentication attempts (for intrusion detection)

This supplements the application-level gl_audit_log with database-level infrastructure audit events.

9.8 Row-Level Security Performance

RLS policies add a predicate to every query. This predicate (tenant_id = current_setting('app.current_tenant')::uuid) is evaluated against the tenant_id column, which is the leading column of every table's primary index. The performance overhead is therefore negligible for queries that already filter by tenant_id (which all well-formed application queries should).

Monitor the following under load:

EXPLAIN ANALYZE output to confirm index scans on tenant_id predicates
Policy evaluation time via pg_stat_user_tables combined with query timing
Ensure enable_row_security is confirmed on via SHOW row_security

Document	Title	Location
CFS-DB-001	Database Architecture Overview	`docs/database/CFS-DB-001-DATABASE-ARCHITECTURE.md`
CFS-DB-002	Core Schema Reference	`docs/database/CFS-DB-002-CORE-SCHEMA-REFERENCE.md`
CFS-DB-004	Business Logic Layer	`docs/database/CFS-DB-004-BUSINESS-LOGIC-LAYER.md`
CFS-004	Technical Architecture	`docs/architecture/CFS-004-TECHNICAL-ARCHITECTURE.md`
CFS-010	Security & Privacy Architecture	`docs/internal-confidential/CFS-010-SECURITY-AND-PRIVACY.md`
CFS-008	Regulatory Compliance Matrix	`docs/internal-confidential/CFS-008-REGULATORY-COMPLIANCE-MATRIX.md`

Source Files Referenced

File	Path in Repository
RLS policies (SQL)	`GL-PROTOTYPE/src/schema/rls/001_rls_policies.sql`
Database triggers (SQL)	`GL-PROTOTYPE/src/schema/triggers/001_triggers.sql`
Core schema migration (SQL)	`GL-PROTOTYPE/src/schema/migrations/001_core_schema.sql`
Database connection layer (TypeScript)	`GL-PROTOTYPE/src/services/db.ts`

Appendix A: RLS Policy Verification Queries

The following queries can be used to verify that RLS is correctly configured in a target PostgreSQL instance:

-- Verify RLS is enabled and forced on all GL tables
SELECT
  tablename,
  rowsecurity      AS rls_enabled,
  forcerowsecurity AS rls_forced
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename LIKE 'gl_%'
ORDER BY tablename;

-- Verify all expected policies exist
SELECT
  schemaname,
  tablename,
  policyname,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE schemaname = 'public'
  AND tablename LIKE 'gl_%'
ORDER BY tablename, policyname;

-- Verify gl_app role permissions (no DELETE)
SELECT
  grantee,
  table_name,
  privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'gl_app'
  AND table_schema = 'public'
ORDER BY table_name, privilege_type;

-- Verify gl_app is not a superuser
SELECT
  rolname,
  rolsuper,
  rolcreatedb,
  rolcreaterole
FROM pg_roles
WHERE rolname = 'gl_app';

Appendix B: Tenant Context Test Vectors

The following SQL sequences can be used to verify isolation in a test environment:

-- Test 1: Verify data is invisible without tenant context
-- Expected: ERROR - unrecognized configuration parameter "app.current_tenant"
BEGIN;
SELECT COUNT(*) FROM gl_accounts;
ROLLBACK;

-- Test 2: Verify tenant A cannot see tenant B data
-- Setup: tenant_a_id and tenant_b_id are UUIDs of two distinct tenants with data
BEGIN;
SET LOCAL app.current_tenant = '{tenant_a_uuid}';
SELECT COUNT(*) FROM gl_accounts;  -- Should return only Tenant A's accounts
ROLLBACK;

BEGIN;
SET LOCAL app.current_tenant = '{tenant_b_uuid}';
SELECT COUNT(*) FROM gl_accounts;  -- Should return only Tenant B's accounts
ROLLBACK;

-- Test 3: Verify cross-tenant insert is blocked
-- Expected: ERROR - new row violates row-level security policy
BEGIN;
SET LOCAL app.current_tenant = '{tenant_a_uuid}';
INSERT INTO gl_accounts (tenant_id, ...) VALUES ('{tenant_b_uuid}', ...);
ROLLBACK;

-- Test 4: Verify audit log is immutable
-- Expected: ERROR - GL_060: Audit log is immutable.
BEGIN;
SET LOCAL app.current_tenant = '{tenant_a_uuid}';
UPDATE gl_audit_log SET action = 'INSERT' WHERE id = (SELECT id FROM gl_audit_log LIMIT 1);
ROLLBACK;

Document Control

Field	Value
Document ID	CFS-DB-003
Version	1.0.0
Status	Final
Classification	INTERNAL CONFIDENTIAL
Created	2026-02-20
Author	Hal Casteel, CEO/CTO, AZ1.AI Inc.
Reviewed By	Pending
Next Review	2026-08-20

INTERNAL CONFIDENTIAL -- AZ1.AI Inc. This document contains proprietary security architecture details for the CODITECT Financial Suite GL Engine. Authorized recipients are limited to engineering, security, and compliance personnel with a business need to know. Do not distribute externally without written authorization from the document owner.

Table of Contents​

1. Multi-Tenancy Architecture​

1.1 Approach: Shared-Schema, Shared-Database with Row-Level Security​

1.2 The Mandatory Tenant Column​

1.3 Tenant Context Mechanism​

1.4 Architecture Benefits​

2. Row-Level Security Policies​

2.1 RLS Activation​

2.2 Standard Policy Pattern (Seven Tables)​

2.3 Special Case: gl_audit_log (Read Filter Only)​

2.4 Policy Summary​

2.5 What Happens Without a Tenant Context​

3. Application Role and Permissions​

3.1 Role Definition​

3.2 Permission Rationale​

3.3 Role Separation​

4. Tenant Context Injection​

4.1 Implementation​

4.2 Transaction Wrapper​

4.3 QueryContext Interface​

4.4 Connection Pool Configuration​

4.5 Security Properties of This Approach​

5. Audit Trail Immutability​

5.1 Design Principle​

5.2 Immutability Trigger​

5.3 Audit Log Record Structure​

5.4 Audit Trigger Coverage​

5.5 STATUS_CHANGE Classification​

5.6 Partitioning for Retention Management​

6. Data Protection Patterns​

6.1 No Physical Deletion​

6.2 Journal Entry Immutability​

6.3 Period Locking​

6.4 SPED Export Integrity​

6.5 JSONB Snapshots in Audit Log​

7. Security Headers (Application Layer)​

7.1 Tenant Identification Headers​

7.2 Header Validation Requirements​

7.3 Defense in Depth​

8. Compliance Alignment​

8.1 Sarbanes-Oxley (SOX) -- Section 404​

8.2 Brazilian SPED ECD/ECF Compliance​

8.3 LGPD (Lei Geral de Proteção de Dados) / GDPR​

8.4 Compliance Control Summary​

9. Security Considerations and Recommendations​

9.1 SQL Injection in Tenant Context Setting (Current Limitation)​

9.2 Password Management​

9.3 Network Isolation​

9.4 Encryption at Rest​

9.5 Connection Encryption​

9.6 Superuser Access Controls​

9.7 pg_audit Extension​

9.8 Row-Level Security Performance​

10. Related Documents​

Source Files Referenced​

Appendix A: RLS Policy Verification Queries​

Appendix B: Tenant Context Test Vectors​

Table of Contents