Database Model

This document explains where and in what shape Studio stores its data. Refer to it when scoping a backup or querying directly.

There are two stores

PostgreSQL holds metadata, and the file system (DATA_ROOT) holds app source and build artifacts. You need both to recover — with only the DB you have no app code, and with only the files you have no record of who created what.

Store	Holds
PostgreSQL	Users · app metadata · conversations · audit · watchers · skills
File system (DATA_ROOT)	Workspaces (app source) · build artifacts · state

PostgreSQL runs in one of two modes — bundled container (default) or a platform-shared PG. See the environment variable reference.

---

Tables — 11 total

Group	Table	Holds
Users	studio_users	Accounts · roles (admin/builder/viewer) · password hashes
	studio_tokens	Issued tokens
Conversations	ask_conversations	Chat threads
	ask_history	Exchanged messages and elapsed time
Watchers	watchers	Periodic monitoring definitions and notification settings
	notification_seen	Notification acknowledgment status
Skills	skills	Registered field know-how
Deployments	deploy_log	Deployment history
Audit/usage	audit_log	Who did what
	usage_log	AI token usage
Schema	schema_migrations	Applied migration names and timestamps

watchers used to be called flows

The name was unified on 2026-07-13 (ALTER TABLE flows RENAME TO watchers, data retention). New installs also follow the same sequence — created as flows and then renamed — because migration history is a historical record and isn't rewritten. If you're writing direct queries, use watchers.

---

Migrations manage schema changes

On startup, the server applies the .sql files in src/infra/db/migrations/ in numeric order, and records the applied file names in schema_migrations. Anything already applied is not re-run.

-- 지금 어디까지 적용됐는지
SELECT name, applied_at FROM schema_migrations ORDER BY name;

Don't hand-edit the schema

If schema_migrations and the actual schema drift apart, the next upgrade's migration will fail while "trying to create something that already exists." This blocks the app from starting entirely.

Take a backup before upgrading → Backup and Recovery.

---

File system — DATA_ROOT

The default is /var/lib/pp-studio. Everything below lives under this path.

Contents	If missing
App workspaces (generated source)	The app can't be opened — the DB only has metadata
Build artifacts	Just rebuild
Sessions/state	In-progress work is lost
Bundled PG data	On installs using COMPOSE_PROFILES=bundled-pg, the DB itself lives here

If you use bundled PG, DATA_ROOT IS your DB backup

In bundled mode, the PostgreSQL data directory is also under DATA_ROOT. That means missing this one path means losing both the DB and the app source at once.

---

Easy things to miss in a backup

What to capture	If you skip it
PostgreSQL dump	Accounts, watchers, conversations, and audit records are lost
DATA_ROOT	App source is lost, and the app can't be opened
/etc/kopens/plantpulse-studio.env	Connection info and keys must be re-entered

Try a recovery at least once

The riskiest state is having backups but never having tested a recovery. The procedure is at Backup and Recovery.

---

Related documents

• Backup and Recovery
• Environment Variable Reference — DB mode and DATA_ROOT
• Changing Passwords · API Keys — PG_PASSWORD rotation
• Changing the Initial Password — bootstrap admin account