> ## Documentation Index
> Fetch the complete documentation index at: https://docs.manthan.systems/llms.txt
> Use this file to discover all available pages before exploring further.

# Audit Schema

> PostgreSQL table schemas for the Parmana audit database

## Overview

The `@parmanasystems/audit-db` package manages five PostgreSQL tables. Schema migrations run automatically when the server connects.

***

## audit\_decisions

One row per executed authority verification outcome.

| Column                  | Type            | Description                                                 |
| ----------------------- | --------------- | ----------------------------------------------------------- |
| `id`                    | `bigserial`     | Auto-increment primary key                                  |
| `execution_id`          | `text NOT NULL` | The `executionId` from the execute request                  |
| `execution_fingerprint` | `text NOT NULL` | SHA-256 of canonical `{ policyId, policyVersion, signals }` |
| `policy_id`             | `text NOT NULL` | Policy identifier                                           |
| `policy_version`        | `text NOT NULL` | Policy version                                              |
| `schema_version`        | `text`          | Policy schema version                                       |
| `runtime_version`       | `text`          | Runtime semver                                              |
| `runtime_hash`          | `text`          | Runtime binary hash                                         |
| `decision`              | `text`          | Decision action: `"approve"`, `"reject"`, or custom         |
| `execution_state`       | `text`          | `"completed"` or `"pending_override"`                       |
| `signals_hash`          | `text`          | SHA-256 of canonical signals                                |
| `bundle_hash`           | `text`          | Policy bundle content hash                                  |
| `signature`             | `text`          | Ed25519 signature                                           |
| `attestation`           | `jsonb`         | Full `ExecutionAttestation` JSON                            |
| `executed_at`           | `timestamptz`   | When the execution occurred                                 |
| `recorded_at`           | `timestamptz`   | When the record was inserted                                |
| `verification_valid`    | `boolean`       | `true` if verified, `false` if not, `null` if unverified    |
| `signature_verified`    | `text`          | `"verified"` \| `"failed"` \| `"unknown"`                   |
| `runtime_verified`      | `text`          | `"verified"` \| `"failed"` \| `"unknown"`                   |
| `schema_compatible`     | `text`          | `"verified"` \| `"failed"` \| `"unknown"`                   |
| `verified_at`           | `timestamptz`   | When verification was last run                              |

**Indexes:** `execution_id` (unique), `execution_fingerprint`, `policy_id`, `executed_at`

***

## audit\_overrides

One row per override record (pending, approved, or rejected).

| Column                  | Type            | Description                                           |
| ----------------------- | --------------- | ----------------------------------------------------- |
| `id`                    | `bigserial`     | Auto-increment primary key                            |
| `execution_id`          | `text NOT NULL` | The `executionId` from the original execute request   |
| `execution_fingerprint` | `text NOT NULL` | Links to the semantic execution                       |
| `policy_id`             | `text`          | Policy identifier                                     |
| `policy_version`        | `text`          | Policy version                                        |
| `schema_version`        | `text`          | Policy schema version                                 |
| `decision`              | `jsonb`         | The original decision object                          |
| `approved_by`           | `text`          | Reviewer identifier (from `POST /override`)           |
| `approver_role`         | `text`          | Reviewer role                                         |
| `reason`                | `text`          | Override reason                                       |
| `resolution_status`     | `text`          | `"pending"` → `"approved"` or `"rejected"`            |
| `override_signature`    | `text`          | Ed25519 signature of the override authorization       |
| `signature_type`        | `text`          | Signing algorithm used                                |
| `pending_context`       | `jsonb`         | Stored execution context for re-execution on approval |
| `created_at`            | `timestamptz`   | When the override was initiated                       |
| `resolved_at`           | `timestamptz`   | When the override was resolved                        |

**Indexes:** `execution_id`, `execution_fingerprint`, `resolution_status`

***

## audit\_verifications

One row per verification call.

| Column                  | Type            | Description                                                        |
| ----------------------- | --------------- | ------------------------------------------------------------------ |
| `id`                    | `bigserial`     | Auto-increment primary key                                         |
| `execution_fingerprint` | `text NOT NULL` | Links to the decision                                              |
| `execution_id`          | `text`          | The `executionId` from the attestation                             |
| `valid`                 | `boolean`       | Overall verification result                                        |
| `signature_verified`    | `text`          | Ed25519 signature check: `"verified"` \| `"failed"` \| `"unknown"` |
| `runtime_verified`      | `text`          | Runtime identity check: `"verified"` \| `"failed"` \| `"unknown"`  |
| `schema_compatible`     | `text`          | Schema version check: `"verified"` \| `"failed"` \| `"unknown"`    |
| `verified_at`           | `timestamptz`   | When verification occurred                                         |

**Indexes:** `execution_fingerprint`, `execution_id`, `verified_at`

***

## audit\_security\_events

Security-relevant anomalies.

| Column             | Type            | Description                                                                                                     |
| ------------------ | --------------- | --------------------------------------------------------------------------------------------------------------- |
| `id`               | `bigserial`     | Auto-increment primary key                                                                                      |
| `event_type`       | `text NOT NULL` | `"replay_attempt"`, `"signature_failure"`, `"invalid_policy"`, `"rate_limit_exceeded"`, `"unauthorized_access"` |
| `severity`         | `text`          | `"critical"`, `"high"`, `"medium"`, `"low"`                                                                     |
| `event_count`      | `bigint`        | Number of occurrences                                                                                           |
| `first_occurrence` | `timestamptz`   | Earliest occurrence                                                                                             |
| `last_occurrence`  | `timestamptz`   | Most recent occurrence                                                                                          |
| `metadata`         | `jsonb`         | Additional event context (execution ID, policy ID, etc.)                                                        |

**Indexes:** `event_type`, `last_occurrence`

***

## audit\_api\_calls

API call log — every request to every endpoint.

| Column         | Type          | Description                      |
| -------------- | ------------- | -------------------------------- |
| `id`           | `bigserial`   | Auto-increment primary key       |
| `endpoint`     | `text`        | Request path and method          |
| `status_code`  | `integer`     | HTTP response status             |
| `execution_id` | `text`        | The `executionId` if applicable  |
| `policy_id`    | `text`        | The `policyId` if applicable     |
| `recorded_at`  | `timestamptz` | When the call occurred           |
| `duration_ms`  | `integer`     | Request duration in milliseconds |

**Indexes:** `recorded_at`, `endpoint`, `execution_id`

***

## TypeScript interfaces (from @parmanasystems/audit-db)

```typescript theme={null}
interface DecisionRow {
  execution_id: string;
  execution_fingerprint: string;
  policy_id: string;
  policy_version: string;
  decision: string;
  execution_state: string;
  runtimeVersion: string;
  runtimeHash: string;
  executed_at: string;
  recorded_at: string;
  verification_valid: boolean | null;
  signature_verified: "verified" | "failed" | "unknown";
  runtime_verified: "verified" | "failed" | "unknown";
  schema_compatible: "verified" | "failed" | "unknown";
  verified_at: string | null;
}

interface DecisionDetail extends DecisionRow {
  id: number;
  schema_version: string | null;
  signals_hash: string | null;
  bundle_hash: string | null;
  signature: string | null;
  attestation: ExecutionAttestation | null;
}

interface AuditStats {
  total_decisions: string;
  decisions_today: string;
  total_verifications: string;
  valid_verifications: string;
  invalid_verifications: string;
  total_security_events: string;
  total_api_calls: string;
}

interface SecurityEventRow {
  event_type: string;
  severity: string;
  event_count: string;
  last_occurrence: string;
  first_occurrence: string;
}
```