---
title: Operate SQLite-backed profile registry
description: Runbook for operating, inspecting, backing up, and recovering SQLite-backed profile registry storage.
doc_version: 1
last_updated: 2026-07-02
---


# Operate SQLite-backed profile registry

## Goal

Run pinocchio/geppetto apps with durable SQLite profile registry storage, verify read and schema discovery endpoints, and keep safe backup/recovery procedures.

## Before you start

- Confirm your server is wired to profile registry read endpoints (`/api/chat/profiles...`).
- Decide whether you configure by SQLite file path (`--profile-registries ./data/profiles.db`) or explicit DSN source entry (`--profile-registries sqlite-dsn:<dsn>`).
- Ensure only the service account can write the DB file and backup artifacts.

## Step 1: Start with durable profile storage

Preferred file-path form:

```bash
pinocchio web-chat --profile-registries ./data/profiles.db
```

Equivalent DSN form:

```bash
pinocchio web-chat \
  --profile-registries "sqlite-dsn:file:./data/profiles.db?_journal_mode=WAL&_busy_timeout=5000&_foreign_keys=on"
```

## Step 2: Verify the store is live

```bash
curl -s http://localhost:8080/api/chat/profiles | jq .
```

You should see a JSON profile list, not an empty response body or server error.

Verify schema discovery endpoints:

```bash
curl -s http://localhost:8080/api/chat/schemas/middlewares | jq .
curl -s http://localhost:8080/api/chat/schemas/extensions | jq .
```

You should get JSON arrays. If the arrays are empty unexpectedly, check application startup wiring for middleware definitions and extension schema registration.

Quick contract checks:

```bash
curl -s http://localhost:8080/api/chat/schemas/middlewares \
  | jq '.[0] | {name,version,display_name,description}'

curl -s http://localhost:8080/api/chat/schemas/extensions \
  | jq 'map(select(.key | startswith("middleware."))) | .[0].key'
```

Expected:

- middleware schema items expose `name`, `version`, `display_name`, `description`, and `schema`,
- extension schema keys use typed-key format (for example `middleware.agentmode_config@v1`).

## Step 3: Inspect middleware and extension schema shape

Middleware config is described through the schema endpoints, even when the app keeps registries read-only.

Quick middleware schema check:

```bash
curl -s http://localhost:8080/api/chat/schemas/middlewares \
  | jq '.[] | select(.name == "agentmode") | {name,version,schema}'
```

Quick extension schema check:

```bash
curl -s http://localhost:8080/api/chat/schemas/extensions \
  | jq 'map(select(.key | startswith("middleware.")))'
```

Expected:

- middleware schema entries describe the runtime middleware contract,
- extension schema keys use typed-key format such as `middleware.agentmode_config@v1`,
- applications that allow editing can validate payloads against these schemas before persistence.

## Step 4: Back up before risky changes

Filesystem snapshot backup:

```bash
cp ./data/profiles.db ./data/profiles.db.bak.$(date +%Y%m%d-%H%M%S)
```

Optional SQL check:

```bash
sqlite3 ./data/profiles.db "SELECT slug, updated_at_ms FROM profile_registries ORDER BY slug;"
```

## Step 5: Restore procedure

1. Stop the service writing to the DB.
2. Replace the DB with a known-good backup.
3. Start service.
4. Validate profile list and send one profile-selected chat request.

Example:

```bash
cp ./data/profiles.db.bak.20260223-220000 ./data/profiles.db
```

## Step 6: Post-restore validation

- `GET /api/chat/profiles` returns expected slugs/default.
- `GET /api/chat/schemas/middlewares` and `GET /api/chat/schemas/extensions` return expected schema catalogs.
- Chat requests using explicit `profile` still resolve correctly.

## Security and permissions

- Restrict DB file write permission to the app service user.
- Treat DB backups as sensitive artifacts because prompts and runtime metadata can include internal instructions.
- Keep retention bounded and documented.

## Troubleshooting

| Problem | Cause | Solution |
|---|---|---|
| `profile_registries` table missing | DB initialized outside profile store migration path | restart with app-managed SQLite store initialization |
| writes fail with busy/lock errors | concurrent writers and missing timeout/WAL settings | use DSN with WAL + busy timeout and avoid external long-running transactions |
| restored DB loads but profiles seem stale | restored snapshot older than expected | verify backup timestamp and repeat restore with newer snapshot |
| API returns 500 after restore | payload/schema corruption in DB row | inspect `payload_json`, restore cleaner snapshot, then reapply desired changes |
| middleware schemas endpoint unexpectedly empty | app did not register middleware definitions or extension codecs | check runtime bootstrap wiring for schema catalogs |

## See Also

- [Profile Registry in Geppetto](../topics/01-profiles.md)