Skip to main content

PostgreSQL Backend

Self-Hosted Kubernetes

By default, AIControls stores audit events, policies, HITL requests, and budgets in an embedded SQLite database on a PVC. SQLite is fine for testing. Switch to PostgreSQL for production when you need more than one replica.

When to use PostgreSQL​

The default SQLite backend is a single file on a ReadWriteOnce PVC — it only supports replicaCount: 1. Two SQLite-backed replicas writing to the same PVC will corrupt state.

Use PostgreSQL when you need:

  • High availability — run replicaCount > 1 behind the Gateway/Ingress so a pod restart or node drain doesn't cause a gap in enforcement.
  • Larger audit volumes — PostgreSQL handles concurrent writers natively; AIControls does not serialize writes behind an in-process lock the way it does for SQLite.

If you're running a single replica for evaluation or a small team, SQLite is simpler and requires no external dependency — there's no need to switch.

Prerequisites​

  • A reachable PostgreSQL 12+ database (any managed offering — RDS, Cloud SQL, Azure Database for PostgreSQL — or a self-managed instance). AIControls does not bundle or manage a Postgres instance itself.
  • A Kubernetes Secret containing the connection string, created in the same namespace as the AIControls release.

Configure the backend​

  1. Create the DSN Secret. The chart never renders connection strings into manifests — create the Secret yourself, referencing your database:

    kubectl create secret generic aicontrols-db-dsn \
      -n aicontrols \
      --from-literal=dsn="postgres://USER:PASSWORD@HOST:5432/aicontrols?sslmode=require"

  2. Point values.yaml at the Secret:

    storage:
      # No PVC needed — state lives in PostgreSQL.
      persistentVolume:
        enabled: false
      database:
        driver: postgres
        # Name of the env var the proxy reads the DSN from.
        dsnEnv: AICONTROLS_DB_DSN
        dsnSecretName: aicontrols-db-dsn
        dsnSecretKey: dsn

    replicaCount: 2

  3. Install or upgrade:

    For an existing database, check Incompatible workload schema upgrades before running this command. Those transitions require downtime rather than a mixed-version rolling upgrade.

    helm upgrade --install aicontrols \
      oci://ghcr.io/nirmata/charts/aicontrols \
      --version $VERSION \
      -n aicontrols \
      -f values.yaml

  4. Verify. The schema is created automatically on first connect — there is no separate migration step to run for a new database. When upgrading an existing database, review the compatibility guidance below and Pre-build session indexes before upgrading first. Confirm all replicas run the target version and are serving traffic:

    kubectl get pods -n aicontrols
    kubectl logs -n aicontrols deploy/aicontrols --tail=50

Incompatible workload schema upgrades​

The workload schema transition from sensitivity to data_classification is incompatible with binaries that still read sensitivity. Retaining other legacy workload tables does not make a mixed-version rollout safe. The migration runs during proxy startup; its database locks protect the transition, not continued operation of old binaries afterward.

When crossing this transition:

  1. Schedule downtime and drain proxy traffic. Stop every old proxy replica connected to this database, including replicas in other releases or clusters. Prevent autoscalers or deployment automation from restarting them.
  2. Take a database backup and verify the restore procedure before starting any new replica.
  3. Upgrade to the target version and start one new replica. Verify that startup migrations complete successfully before restoring the desired replica count, autoscaling, and traffic. Do not restart old replicas against the migrated database.
  4. If startup fails, keep traffic drained and inspect the migration error. Do not assume helm rollback repairs the schema: it changes the deployment, not committed PostgreSQL changes. Use a schema-compatible application version, or stop all writers and restore the pre-upgrade database backup together with its matching application version. Restoring a backup discards writes made after that backup.

New installations do not need this shutdown sequence. Index pre-building alone does not address an incompatible column rename.

Pre-build session indexes before upgrading​

AIControls creates its indexes at startup with a plain CREATE INDEX IF NOT EXISTS. On PostgreSQL, a plain index build blocks writes to the table until it finishes. The session indexes on audit_events take about 30 seconds each on a table with around a million rows. If you upgrade without pre-building them, audit-event writes pause while the new pod builds them. During a rolling upgrade, requests that write audit events on the replicas still running can queue or time out.

To avoid the pause, build the indexes online with CREATE INDEX CONCURRENTLY before you run helm upgrade. The startup statements then find the indexes already in place and do nothing. Do this whenever you upgrade an existing database from a release that doesn't have the _v2 session indexes. A new, empty database doesn't need it.

  1. Connect and check that the database can be pre-built. Connect with psql as a user that owns audit_events. Run every statement in this procedure in the same session, with the same search_path as the DSN that AIControls uses. If the DSN sets search_path (for example ?search_path=aicontrols), start with the matching SET, so the indexes land on the AIControls audit_events table and not on a table of the same name in another schema:

    SET search_path TO aicontrols;  -- only if your DSN sets search_path; use the same value

    The indexes read the session_owner and attribution_key columns. Check that audit_events has both:

    SELECT attname FROM pg_attribute
    WHERE attrelid = 'audit_events'::regclass AND NOT attisdropped
      AND attname IN ('session_owner', 'attribution_key');

    If the query returns both columns, continue with the next step. If either column is missing, the database comes from an older release, and this procedure doesn't apply. Startup adds those columns, together with other columns and indexes that the pre-build doesn't cover, so audit-event writes pause during the upgrade whatever you pre-build. Upgrade during a maintenance window instead, and give the pod enough time to finish its migrations; see Startup migrations on upgrade.

  2. Build the indexes online. Run each statement on its own, outside a transaction block, because CONCURRENTLY can't run inside a transaction. Each build waits for the transactions already in progress to finish, then runs alongside normal traffic.

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_session_owner_bridge_v2 ON audit_events((CASE WHEN session_owner!='' THEN session_owner WHEN attribution_key LIKE 'k8s-cluster://%' THEN attribution_key ELSE agent_id END), cc_session_id, session_id) WHERE session_id!='' AND request_type!='sys';

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_session_owner_ts_v2 ON audit_events((CASE WHEN session_owner!='' THEN session_owner WHEN attribution_key LIKE 'k8s-cluster://%' THEN attribution_key ELSE agent_id END), ts);

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_session_owner_session_v2 ON audit_events((CASE WHEN session_owner!='' THEN session_owner WHEN attribution_key LIKE 'k8s-cluster://%' THEN attribution_key ELSE agent_id END), session_id);

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_cc_session_owner_v2 ON audit_events(cc_session_id, session_id, (CASE WHEN session_owner!='' THEN session_owner WHEN attribution_key LIKE 'k8s-cluster://%' THEN attribution_key ELSE agent_id END));

    Copy the expressions exactly. PostgreSQL uses an expression index only for a query with the identical expression, so an index built on a different expression doesn't help.

  3. Check that every index is valid. A concurrent build that fails or is cancelled leaves an invalid index behind. Startup skips an index that already exists, even an invalid one, and queries never use an invalid index.

    SELECT c.relname, i.indisvalid
    FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
    WHERE i.indrelid = 'audit_events'::regclass
      AND c.relname IN ('idx_audit_session_owner_bridge_v2', 'idx_audit_session_owner_ts_v2',
                        'idx_audit_session_owner_session_v2', 'idx_audit_cc_session_owner_v2');

    All four rows must show indisvalid = t. For any index that shows f or is missing, drop it and run its CREATE INDEX CONCURRENTLY statement again:

    DROP INDEX CONCURRENTLY IF EXISTS idx_audit_session_owner_ts_v2;  -- the invalid index's name

  4. Optional: drop the indexes the _v2 indexes replace. Startup drops them too, but dropping them now avoids the short exclusive lock at startup:

    DROP INDEX CONCURRENTLY IF EXISTS idx_audit_session_owner_bridge;
    DROP INDEX CONCURRENTLY IF EXISTS idx_audit_session_owner_ts;

  5. Upgrade. Run helm upgrade as usual. The new pods start without building the indexes.

Pre-build the evidence qualification indexes before upgrading​

This release stores whether the evidence of each audit and Content Safety finding can be read back, so that the severity cap in the finding list matches the evidence view. It adds four nullable columns to audit_events and to session_content_safety_findings, and four partial indexes. Two of the columns only serve replicas still on an earlier build of this feature. Adding the columns only changes the table definitions. The indexes are built at startup with a plain CREATE INDEX IF NOT EXISTS, which blocks writes on PostgreSQL while it scans the table. On large tables, pre-build them online before you run helm upgrade, so that the startup statements do nothing. The indexes are partial. Two hold only findings that are not yet qualified, which at first is every existing finding and shrinks to none as the background job qualifies them. The third holds only readable LLM findings whose prompt snippet is gone, normally none. The fourth finds legacy-invalidated audit findings that still have a generation marker, normally none; a bounded repair clears their generation before ordinary backfill reclassifies them. Startup also installs a row-level invalidation trigger so an older retention writer cannot leave a stale generation trusted. Replicas predating evidence qualification ignore these columns; v1 replicas use the legacy timestamps while v2 replicas require generation ownership.

  1. Add the columns. Connect with psql as a user that owns audit_events and session_content_safety_findings, in a session with the same search_path as the DSN that AIControls uses. Both statements are quick and do not rewrite the table.

    ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS evidence_unreadable BIGINT;
    ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS evidence_qualification_gen BIGINT;

    ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS evidence_qualified_at BIGINT;

    ALTER TABLE audit_events ADD COLUMN IF NOT EXISTS evidence_qualification_version BIGINT;

    ALTER TABLE session_content_safety_findings ADD COLUMN IF NOT EXISTS evidence_unreadable BIGINT;

    ALTER TABLE session_content_safety_findings ADD COLUMN IF NOT EXISTS evidence_qualification_gen BIGINT;

    ALTER TABLE session_content_safety_findings ADD COLUMN IF NOT EXISTS evidence_qualified_at BIGINT;

    ALTER TABLE session_content_safety_findings ADD COLUMN IF NOT EXISTS evidence_qualification_version BIGINT;

  2. Build the indexes online. Run each statement on its own, outside a transaction block, because CONCURRENTLY can't run inside a transaction. Copy the predicates exactly: PostgreSQL uses a partial index only for a query whose condition implies the index condition.

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_evidence_unqualified_v2 ON audit_events(id) WHERE ((decision IN ('deny', 'require_approval', 'response_blocked', 'exception_requested', 'exception_granted', 'exception_denied', 'exception_revoked', 'exception_expired', 'hitl_approved', 'hitl_denied')
      OR (decision = 'audit' AND policy IN (
        'intent-tier-boundary', 'intent-declared-tool-scope', 'intent-registration-required',
        'intent-conformance-review', 'intent-conformance-high',
        'intent-undeclared-server-low-risk', 'intent-undeclared-server-high-risk')))) AND (evidence_qualification_gen IS NULL OR evidence_qualification_gen <> ts);

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_prompt_cleared_v2 ON audit_events(id) WHERE evidence_qualification_gen = ts AND evidence_unreadable = 0 AND request_type = 'llm' AND COALESCE(prompt_snippet, '') = '';

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_cs_evidence_unqualified_v2 ON session_content_safety_findings(id) WHERE evidence_qualification_gen IS NULL OR evidence_qualification_gen <> created_at;

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_evidence_legacy_invalidated_v2 ON audit_events(id) WHERE ((decision IN ('deny', 'require_approval', 'response_blocked', 'exception_requested', 'exception_granted', 'exception_denied', 'exception_revoked', 'exception_expired', 'hitl_approved', 'hitl_denied')
      OR (decision = 'audit' AND policy IN (
        'intent-tier-boundary', 'intent-declared-tool-scope', 'intent-registration-required',
        'intent-conformance-review', 'intent-conformance-high',
        'intent-undeclared-server-low-risk', 'intent-undeclared-server-high-risk')))) AND (evidence_qualification_gen IS NOT NULL AND (evidence_qualified_at IS NULL OR evidence_qualified_at <> ts));

  3. Check that all four indexes are valid. A concurrent build that fails or is cancelled leaves an invalid index behind, which startup skips and queries never use.

    SELECT c.relname, i.indisvalid
    FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
    WHERE c.relname IN ('idx_audit_evidence_unqualified_v2', 'idx_audit_prompt_cleared_v2', 'idx_cs_evidence_unqualified_v2', 'idx_audit_evidence_legacy_invalidated_v2');

    All four rows must show indisvalid = t. For any index that shows f or is missing, drop it and run its CREATE INDEX CONCURRENTLY statement again:

    DROP INDEX CONCURRENTLY IF EXISTS idx_audit_prompt_cleared_v2;  -- the invalid index's name

  4. Upgrade. Run helm upgrade as usual. Until a background job qualifies findings recorded before the upgrade, their severity is capped at medium. The job works through a bounded number of findings every few minutes, so a large history takes longer.

Retain legacy evidence indexes during mixed-version operation​

Keep idx_audit_evidence_unqualified_v1, idx_audit_prompt_cleared_v1, and idx_cs_evidence_unqualified_v1 while any older replica or supported rollback build may use them. SQLite's older backfill explicitly names its index. Retain the new invalidation trigger through that overlap as well: older retention invalidates its legacy timestamp, and the trigger clears generation ownership atomically; an older backfill cannot restore it. Current retention also protects legacy LLM readers before the new backfill runs.

Only after every replica is on the repaired build and the operator has explicitly ended the old-build rollback window may legacy indexes be removed. Confirm the actual deployed versions and rollback inventory first; a green migration or an empty backfill is insufficient. On PostgreSQL, use DROP INDEX CONCURRENTLY IF EXISTS for each legacy index separately, outside a transaction and during a controlled maintenance step. Never drop a valid index still needed by live or rollback binaries. This runbook does not authorize immediate cleanup or deployment.

Before and after eventual cleanup, inspect pg_index.indisvalid for all retained indexes and use non-executing EXPLAIN on bounded repair updates and EXPLAIN (ANALYZE, BUFFERS) only on their read-only bounded candidate selections to verify index selection and resource limits with representative data. EXPLAIN ANALYZE executes its statement; do not run it on repair updates as a diagnostic. Retain all v2 indexes and the invalidation trigger; legacy-index retirement does not retire them. The native regression checks index applicability with sequential scans disabled on its small PostgreSQL fixture; it does not establish production cost estimates or scale qualification.

High availability with multiple replicas​

With replicaCount > 1, every pod connects to the same PostgreSQL database and shares audit history, policies, budgets, and HITL state. A small set of background jobs (risk scoring, PAT/credential cleanup, cost aggregation, digest scheduling) must only run on one replica at a time — AIControls coordinates this automatically using a lease table in the database, so no extra configuration is needed.

SQLite stays single-replica

The lease-based coordination only applies to the PostgreSQL backend. If you're on SQLite, keep replicaCount: 1.

Connection pool sizing​

Each replica opens its own PostgreSQL connection pool — the settings below are per pod, not cluster-wide. Leave them at 0 to use the built-in defaults (16 open connections, 4 idle, 30-minute max connection lifetime), which work for most deployments.

storage:
  database:
    maxOpenConns: 0            # 0 = built-in default (16)
    maxIdleConns: 0            # 0 = built-in default (4)
    connMaxLifetimeMinutes: 0  # 0 = built-in default (30)

Size maxOpenConns against your PostgreSQL server's max_connections budget: with replicaCount: 4 and maxOpenConns: 16, that's up to 64 connections from AIControls alone, before counting any other workload sharing the database. If you raise replicaCount, lower maxOpenConns (or raise max_connections on the server) to keep the product from exceeding what the database allows — a pool exhausted at the server side surfaces as connection errors across every replica, not just the one that tipped it over.

Reporting timezone across replicas​

Date-bucketed dashboards (cost aggregation, adoption trends — anything that groups data by calendar day) group rows by the PostgreSQL session's timezone setting. Left unset, each replica infers its own local system timezone independently. On a single replica this just matches the local-time bucketing AIControls uses on SQLite, but across multiple replicas it's a correctness hazard: if replicas run on nodes or container images with different system timezones, the same event can be bucketed into different calendar days depending on which replica handled the write or read, so aggregates silently disagree.

When running more than one replica, set reportingTimezone explicitly to a timezone value PostgreSQL accepts (an IANA zone name, or a numeric UTC offset):

storage:
  database:
    reportingTimezone: "UTC"  # or e.g. "America/New_York"; unset = each replica infers its own local zone

"UTC" is the simplest choice since it needs no coordination with the environment; pick a named zone instead if you want dashboards to bucket by a specific region's calendar day.

Global RPM rate-limit accuracy across replicas​

The Requests per minute / Tokens per minute global defaults and per-group/per-identity overrides under Settings → Advanced → Rate Limits (see Rate-limit MCP tools) are enforced per replica, in memory — there's no shared counter for these, unlike the audit/policy/budget state above. With more than one replica, each pod independently allows up to the configured limit, so the fleet-wide aggregate can scale with replica count unless you tell AIControls how many replicas you're running.

Set rateLimit.expectedReplicas so each replica divides its share of the configured limit instead:

rateLimit:
  expectedReplicas: 4   # match your replicaCount, or autoscaling.maxReplicas under HPA

Or via the raw environment variable, if you're injecting config outside of values.yaml (extraEnv, a base ConfigMap you manage yourself, etc.):

env:
  - name: AICONTROLS_RATELIMIT_EXPECTEDREPLICAS
    value: "4"

The Helm chart sets this automatically from replicaCount (or autoscaling.maxReplicas when autoscaling.enabled is true) — you only need to set it yourself if you're not using the chart's replicaCount/autoscaling values to manage scale, or you're overriding the chart's computed value for some other reason.

Keep this value close to your real replica count. Set too low, the fleet-wide limit is admitted well above what you configured; set too high, individual replicas reject requests before the fleet-wide budget is actually used. This is a static, install-time value — it does not track HPA scaling events in real time by itself, only what's configured at the last install/upgrade. The global default RPM tier additionally gets a periodic (~5 second) fleet-wide correction on top of this static split; per-group and per-identity overrides do not — see Rate-limit MCP tools for the accuracy breakdown.

MCP session affinity​

Unlike the audit/policy/budget state above, MCP sessions (/mcp, /mcp/admin) are not shared across replicas — they're held in memory by the pod that created them, keyed by Mcp-Session-Id. A follow-up request for an existing session must land back on that same pod. The chart's Service sets sessionAffinity: ClientIP for this reason, so this works automatically behind the Service — no extra configuration needed for the common case.

If you put an Ingress, Gateway, or an external load balancer in front of the Service, make sure it also pins a client to one backend pod for the duration of an MCP session (session-cookie affinity, IP-hash, or equivalent) — sessionAffinity: ClientIP only affects load balancing by the Service, not decisions made further upstream. ClientIP affinity itself is imprecise behind a shared NAT or corporate proxy, where many distinct clients present the same source IP — this is a known limitation of IP-based affinity, not specific to AIControls.

Session/JWT signing secret​

Every replica must sign JWTs and session cookies with the same secret — otherwise a request handled by a different pod than the one that issued the session invalidates it. The chart handles this automatically: it generates one secret on first install and stores it in a <release>-jwt Secret, reused by every replica and stable across helm upgrade (the same idempotent lookup pattern used for the bootstrap admin password). No configuration is required for the default case.

To use an externally managed secret instead, set:

auth:
  jwtSecretName: my-jwt-secret   # Secret must contain a key named "secret" (or set jwtSecretKey)

The referenced key's value must be base64 text that decodes to at least 16 bytes — e.g. openssl rand -base64 32. Kubernetes env vars can't reliably carry arbitrary binary (notably embedded NUL bytes), so raw/unencoded bytes are not a safe value here even though the proxy falls back to treating a non-base64 value as raw bytes.

Rotation

Rotating the JWT secret (e.g. deleting the chart-managed <release>-jwt Secret) invalidates every active session and signed token across all replicas at once.

Virtual-key encryption secret​

Secrets saved through the UI (for example, an LLM upstream API key added under Settings → Providers) are encrypted at rest with an AES-256 key before being written to PostgreSQL. Every replica must use the same key — otherwise a secret saved via one pod fails to decrypt on every other pod. The chart handles this automatically: it generates one 32-byte key on first install and stores it in a <fullname>-encryption Secret (e.g. <release>-aicontrols-encryption by default), reused by every replica and stable across helm upgrade (the same idempotent lookup pattern used for the bootstrap admin password).

To use an externally managed secret instead, set:

auth:
  encryptionKeySecretName: my-encryption-secret   # Secret must contain a key named "key" (or set encryptionKeySecretKey)
  # The referenced key's value must be base64 TEXT decoding to exactly 32 raw
  # bytes (stricter than the JWT/session secret above, which tolerates raw
  # bytes or any length >= 16) — e.g. generate one with: openssl rand -base64 32
Rotation causes permanent data loss

Unlike the JWT/session-signing secret above (rotation only invalidates sessions), rotating or deleting the encryption secret (the chart-managed <fullname>-encryption Secret, or whatever auth.encryptionKeySecretName points at) is not recoverable: every value previously encrypted with the old key — including saved LLM upstream API keys — becomes permanently undecryptable. There is no "log in again" recovery path. Do not delete or regenerate this secret unless you have already re-saved every encrypted value under the new key.

Upgrading from a chart version without this fix

If your install predates this shared secret, each replica previously generated its own local key under /data/encryption.key. Any secret saved before upgrading was encrypted with a pod-local key that no other replica shares — after upgrading, re-save those secrets (e.g. re-enter LLM upstream API keys under Settings → Providers) so they are encrypted under the new shared key instead.

Last updated on