Airavata persistence

Everything Airavata's session server (cs serve) and batch server (airavata-server) store. The procedures are in Backups and restore and Upgrades.

The two servers may share a database but not a schema: both define a table named ssh_keys, with different columns.

Server	Connection	Schema	Query layer
cs serve	CS_DATABASE_URL, one connection	Created once in an empty schema; never migrated	Go generated by sqlc from plain SQL
airavata-server	AIRAVATA_DB_DSN, up to 20 connections	GORM AutoMigrate at startup, or versioned files through migrate	GORM

Session tables

Metadata lives in PostgreSQL, credentials in files, and log tails, usage samples and links in memory. CS_DATABASE_URL selects the schema through search_path. The server creates its tables only in an empty schema and refuses a non-empty one without its format marker (cs: unsupported state database format).

subsystems/session/schema.sql, subsystems/ssh/schema.sql

CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  owner TEXT NOT NULL,
  payload TEXT NOT NULL
);

CREATE TABLE runs (
  position BIGINT GENERATED ALWAYS AS IDENTITY,
  session_id TEXT NOT NULL,
  seq BIGINT NOT NULL,
  owner TEXT NOT NULL,
  payload TEXT NOT NULL,
  PRIMARY KEY (session_id, seq)
);

CREATE TABLE ssh_hosts (
  principal TEXT NOT NULL,
  host TEXT NOT NULL,
  payload TEXT NOT NULL,
  PRIMARY KEY (principal, host)
);

-- An alias is unique regardless of case, as OpenSSH matches Host patterns.
CREATE UNIQUE INDEX ssh_hosts_alias ON ssh_hosts (principal, lower(host));

CREATE TABLE ssh_keys (
  principal TEXT NOT NULL,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  PRIMARY KEY (principal, name)
);

internal/db adds schema_meta (key TEXT PRIMARY KEY, value TEXT NOT NULL) holding ('format', 'sql-v1').

Column	Holds
owner, principal	The principal hash: hex of the first 16 bytes of sha256(sub + "\0" + tenant), tenant cilogon
sessions.payload	The session record as JSON, including owner, job ID, node and Dev Tunnel metadata
runs.payload	A finished run as JSON; position orders runs newest first, at most 200 across all callers
ssh_hosts.payload	The parsed SSH host as JSON; the key's path is never stored

A row whose payload identity (id, session_id and seq, or host) disagrees with its key columns is refused, not served. To inspect a record, cast the column:

psql -d cybershuttle -c "SELECT id, payload::json->>'state', payload::json->>'jobId' FROM cs_plane.sessions"

Each subsystem owns its schema.sql and query.sql; go generate ./internal/db runs sqlc over sqlc.yaml and regenerates the committed query.sql.go, sql.go and sql_models.go.

Subsystem	Queries
session	ListSessions, ListRuns (by position), ClearSessions, ClearRuns, InsertSession, InsertRun, GetSession, ListSessionsByOwner, ListRunsByOwner
ssh	ListHosts (by lower(host)), ListHostPrincipals, InsertHost, UpdateHost, DeleteHost, GetKey, ListKeys, InsertKey, DeleteKey

Locked in internal/db serialises every read-modify-write cycle behind a process mutex and a flock on <state dir>/.lock. A session change deletes and reinserts every session and run in one transaction; reads are unlocked. psql takes neither lock, so change rows only while the server is stopped.

Session files

The state directory is ~/.cybershuttle/control, mode 0700. The server refuses a private file that another user owns or that has any group or world permission bit, so restore the directory as the service account with its modes intact. In the paths below, <p> is the principal.

Path	Contents
.lock	The database cycle lock
.session-create-XX.lock	64 lock slots (00 to 3f) serialising start and attach per session ID
.devtunnels-account-<p>.lock	Per-principal Dev Tunnels credential lock
devtunnels-account.key	Key sealing every Dev Tunnels account, created at mode 0600 on first start
credentials/<session id>-<seq>.token	JSON {jupyterToken, linkToken, connectToken?} for one run, mode 0600
hosts/<p>/config	The caller's rendered SSH configuration, mode 0600
hosts/<p>/keys/<id>	Uploaded SSH private keys, mode 0600; .put-* staging and .delete-* tombstones during changes
hosts/<p>/devtunnels-account	The sealed Dev Tunnels account, mode 0600

SSH control sockets live outside it, under $TMPDIR/cs-<uid>/ or /tmp/cs-<uid>/.

Session restarts

A restart is safe while sessions run. At startup the session server, in order:

1. Refuse a non-empty schema without format sql-v1, before creating any credential file.
2. Resolve interrupted SSH key writes and deletions from committed metadata.
3. Re-render every principal's SSH configuration from committed rows.

In-memory state is lost:

State	Effect
Links	Linkspan redials within seconds
Log tails	A live session's tail restarts empty
Usage samples	A live session's samples restart empty
Pending Dev Tunnels authorizations	The user restarts the device flow
Control masters	Interactive masters end; BatchMode masters (ControlPersist=600) may survive up to ten idle minutes. Otherwise the user authenticates again

Batch tables

airavata-server stores every batch resource in PostgreSQL through GORM (internal/db). The schema below is internal/db/migrations/0001_baseline.sql, recorded from AutoMigrate against PostgreSQL 17. Every record ID is a varchar(36) UUID. Columns naming a user (users.user_id, owner_id, other user_id columns) are varchar(255) holding the principal name, such as cilogon:12345 or root; job_id columns hold Slurm job IDs.

Table	Primary key	References (on delete)
users	user_id
user_roles	user_id, role	users (cascade)
groups	group_id	owner user_id → users (restrict)
group_members	group_id, user_id	groups (cascade), users (cascade)
ssh_keys	ssh_key_id	owner_id → users (restrict)
slurm_clusters	slurm_cluster_id
cluster_partitions	partition_id	cluster_id → slurm_clusters (cascade)
slurm_cluster_configs	slurm_cluster_config_id	slurm_clusters, ssh_keys, owner users (all restrict)
slurm_cluster_config_user_sharings	sharing ID	the config, and users (restrict)
slurm_cluster_config_group_sharings	sharing ID	the config, and groups (restrict)
application_templates	template_id
application_template_inputs, _outputs	input_id, output_id	application_templates (cascade); (template_id, input_name) unique
batch_job_configs	batch_job_config_id
batch_application_deployments	deployment_id	slurm_clusters, application_templates, batch_job_configs (restrict)
scp_data_storages	data_id	ssh_keys, owner users (restrict)
scp_data_storage_user_sharings, _group_sharings	sharing ID	the storage, and users or groups (restrict)
data_products	data_id	owner users (restrict); data_storage_id has no foreign key
data_product_user_sharings, _group_sharings	sharing ID	the product, and users or groups (restrict)
processes	process_id	owner users (restrict); last_status_id has no foreign key
process_statuses	process_status_id	processes (restrict)
batch_processes	batch_process_id	parent_process_id → processes (cascade); batch_application_deployments, slurm_cluster_configs, batch_job_configs (restrict)
batch_process_statuses	batch_process_status_id	batch_processes (cascade)
process_template_input_mappings, _output_mappings	mapping ID	batch_processes (cascade), the template input or output (cascade)
data_staging_tasks, job_submission_tasks, job_monitoring_tasks, interactive_command_tasks	task_id	processes (cascade)

ssh_keys.private_key (text) and ssh_keys.passphrase (varchar(255)) are stored unencrypted. Enum columns (status, role, permission, process_type, storage types) are varchar holding the API's upper-case values. created_at and timestamp columns hold epoch milliseconds.

The workflow engine that carries out a launched process keeps its state outside PostgreSQL, in the SQLite file AIRAVATA_WORKFLOW_BACKEND_SQLITE_PATH (default /tmp/airavataorchestrator.sqlite, lost when the host clears /tmp).

Batch migrations

Path	Effect	Records in schema_migrations
AIRAVATA_DB_AUTO_MIGRATE=true (default)	Adds missing tables, columns and indexes at startup; drops nothing	No
airavata-server migrate up	Applies each unapplied NNNN_description.sql in version order, one transaction per file	Yes: version, description, applied_at

Files are embedded in the binary; statements are split on ; followed by a newline. A file that fails part-way is rolled back, DDL included; correct it and run migrate up again. There are no down migrations; a later file corrects a mistake. 0001_baseline.sql is the only file.

The paths do not mix: on a database auto-migration created, 0001_baseline.sql is still pending and migrate up fails on the existing tables.