# HappyX Transactional Schema Slice

Updated: 2026-05-22T20:55:00Z

## Purpose

This is the first implementation slice from the storage and encryption plan. It defines the transactional tables needed to move approval replay safety, run metadata and run output retention away from mutable JSON state.

The current service can keep JSON state until this schema is implemented. Multi-process or horizontally scaled HappyX should not be enabled before the approval-use uniqueness rules below exist in a transactional store.

## SQLite Baseline

Executable artifact: `happyx/storage/schema.sql`. The P15 migration dry-run applies that file in an in-memory SQLite database before importing the current JSON state.

```sql
PRAGMA foreign_keys = ON;

CREATE TABLE schema_migrations (
  version TEXT PRIMARY KEY,
  applied_at TEXT NOT NULL
);

CREATE TABLE events (
  id TEXT PRIMARY KEY,
  time TEXT NOT NULL,
  role TEXT NOT NULL,
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  body_public TEXT NOT NULL,
  session_id TEXT,
  run_id TEXT
);

CREATE TABLE tasks (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  owner TEXT NOT NULL,
  status TEXT NOT NULL,
  priority TEXT NOT NULL,
  acceptance TEXT NOT NULL,
  evidence TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  completed_at TEXT
);

CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  agent TEXT NOT NULL,
  machine_id TEXT NOT NULL,
  status TEXT NOT NULL,
  summary TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE permissions (
  id TEXT PRIMARY KEY,
  session_id TEXT,
  title TEXT NOT NULL,
  scope TEXT NOT NULL,
  status TEXT NOT NULL,
  requested_by TEXT NOT NULL,
  reason TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  approved_at TEXT,
  expires_at TEXT,
  expired_at TEXT,
  revoked_at TEXT,
  approval_use_version INTEGER NOT NULL DEFAULT 0,
  replay_guard TEXT,
  use_count INTEGER NOT NULL DEFAULT 0,
  last_used_at TEXT,
  last_run_id TEXT,
  last_recipe_id TEXT
);

CREATE TABLE approval_uses (
  id TEXT PRIMARY KEY,
  permission_id TEXT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  recipe_id TEXT NOT NULL,
  run_id TEXT,
  use_hash TEXT NOT NULL,
  use_version INTEGER NOT NULL,
  used_at TEXT NOT NULL,
  UNIQUE(permission_id, use_version),
  UNIQUE(use_hash)
);

CREATE TABLE agent_runs (
  id TEXT PRIMARY KEY,
  recipe_id TEXT NOT NULL,
  recipe_title TEXT NOT NULL,
  status TEXT NOT NULL,
  requested_by TEXT NOT NULL,
  machine_id TEXT,
  session_id TEXT,
  started_at TEXT NOT NULL,
  ended_at TEXT,
  timeout_ms INTEGER NOT NULL,
  duration_ms INTEGER,
  exit_code INTEGER,
  signal TEXT,
  truncated INTEGER NOT NULL DEFAULT 0,
  timed_out INTEGER NOT NULL DEFAULT 0,
  stdout_bytes INTEGER NOT NULL DEFAULT 0,
  stderr_bytes INTEGER NOT NULL DEFAULT 0,
  risk_level TEXT NOT NULL,
  approval_required INTEGER NOT NULL DEFAULT 0,
  approval_id TEXT REFERENCES permissions(id),
  approval_replay_guard TEXT,
  runner_mode TEXT NOT NULL,
  worker_boundary TEXT,
  resource_profile TEXT,
  output_pruned INTEGER NOT NULL DEFAULT 0,
  output_pruned_reason TEXT,
  failure_hint TEXT
);

CREATE TABLE agent_run_outputs (
  run_id TEXT PRIMARY KEY REFERENCES agent_runs(id) ON DELETE CASCADE,
  stdout_ciphertext BLOB,
  stderr_ciphertext BLOB,
  stdout_preview_public TEXT,
  stderr_preview_public TEXT,
  retained_until TEXT,
  pruned_at TEXT
);

CREATE INDEX idx_events_time ON events(time DESC);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_permissions_scope_status ON permissions(scope, status);
CREATE INDEX idx_agent_runs_recipe_started ON agent_runs(recipe_id, started_at DESC);
CREATE INDEX idx_agent_runs_status_started ON agent_runs(status, started_at DESC);
```

## Transaction Boundary

High-risk run creation must be one transaction:

1. Load the approval row for update.
2. Reject expired or revoked approval rows.
3. Insert `approval_uses` with the one-time use hash.
4. Increment `permissions.approval_use_version`.
5. Insert the `agent_runs` row.
6. Commit before starting the worker process.

If step 3 violates a unique constraint, reject the request as a stale replay before any worker starts.

## Migration Checks

- JSON import must preserve run ids, event ids and task ids.
- Public exports must still use `redaction.js` projections.
- `npm run verify:public` must pass before and after migration.
- A rollback export must be generated before replacing JSON state in production.