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:

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

Equivalent DSN form:

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

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:

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:

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:

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

Quick extension schema check:

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:

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

Optional SQL check:

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:

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

See Also

• Profile Registry in Geppetto