# JSON → PostgreSQL Import Runbook

Status: local importer implemented and verified against a temporary local PostgreSQL database; production cutover is **not** approved or performed.

## What this importer covers

`backend/scripts/migrate_json_to_postgres.py` imports the core JSON collections:

- `orgs/` → `organizations`
- `users/` → `users`
- `groups/` + embedded personas → `groups` + `personas`
- `sessions/` + embedded messages → `sessions` + `messages`
- `my_personas/` → deterministic owner-private groups + `personas`

A non-empty `audit/audit.jsonl` is rejected. Do not bypass that rejection: audit migration belongs to the S4.6 PostgreSQL audit-store gate and must remain fail-closed.

## Safety contract

1. Default mode is dry-run. It validates the full reference graph and prints metadata-only counts/checksum.
2. `--apply` requires both an explicit target database URL and a new backup directory.
3. The backup is created before the target transaction begins. `.env`, credential helpers, and unrelated files are not copied.
4. Source JSON is never modified.
5. Existing identical rows are counted as `unchanged`; conflicting rows abort the transaction rather than overwrite data.
6. Cross-tenant references, malformed scalar values, unsafe IDs, oversized JSON files, symlinked source files, invalid timestamps, and duplicate keys fail closed.
7. CLI output contains only mode, checksum, counts, and backup status. Password hashes, latent persona fields, JSON payloads, and connection strings are never printed.

## Dry-run

Run from `backend/` against a read-only copy of the source data. Do not paste a real connection string into documentation or chat.

```bash
cd backend
./.venv/bin/python scripts/migrate_json_to_postgres.py \
  --source /path/to/data-copy
```

Expected output is a single JSON metadata object with `mode: "dry_run"`, a source checksum, and per-table `would_create` counts. A dry-run does not require a target database and does not write anything.

## Apply (operator-gated)

This is an operational action. Obtain explicit deployment/cutover approval first, provision PostgreSQL, apply Alembic migrations, and take an independently retained backup. Supply the target URL through the shell environment; do not record it in logs or chat.

```bash
cd backend
export DATABASE_URL='[REDACTED]'
./.venv/bin/python scripts/migrate_json_to_postgres.py \
  --source /path/to/data-copy \
  --database-url "$DATABASE_URL" \
  --apply \
  --backup-dir /path/to/retained-backup
```

Run the exact command a second time with a **new** backup directory. The second report must show zero `created` rows and only `unchanged` rows. If the source changes or a target row differs, the importer rejects the run; investigate and do not force an overwrite.

## Verification before any cutover

- Run the importer tests: `./.venv/bin/python -m pytest tests/test_json_import.py -q`.
- Run the full backend suite.
- Compare source checksum, per-table counts, and sampled entity hashes against PostgreSQL.
- Verify tenant ownership for every imported group, persona, session, and message.
- Verify signed-export and audit behavior against PostgreSQL/Redis implementations.
- Perform rollback rehearsal while the JSON backup remains read-only.

The importer is not a production approval, a PostgreSQL availability check, or a rollback rehearsal.