Metadata database

The service migrates its metadata PostgreSQL database on every startup with one idempotent SQL batch. There are exactly five service-owned metadata tables.

erDiagram
    postgis_connections ||--o{ postgis_databases : "ON DELETE CASCADE"
    postgis_connections ||--o{ postgis_hba_rules : "ON DELETE CASCADE"
    postgis_databases o|--o{ postgis_databases : "primary clone; ON DELETE SET NULL"
    postgis_databases ||--o{ postgis_role_database_access : "ON DELETE CASCADE"

postgis_jobs has no foreign keys. In particular, created_by and database IDs stored inside JSON payloads are not constrained.

Tables

postgis_connections

Column	Definition
id	SERIAL PRIMARY KEY
name	TEXT NOT NULL UNIQUE
host	TEXT NOT NULL
port	INTEGER NOT NULL DEFAULT 5432
sslmode	TEXT NOT NULL DEFAULT 'disable'
admin_username	TEXT NOT NULL
admin_password	TEXT NOT NULL; encrypted/prefixed by application, not database
created_at, updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
hba_file_path	Added by ALTER TABLE ... IF NOT EXISTS; TEXT NOT NULL DEFAULT ''

postgis_databases

Column	Definition
id	SERIAL PRIMARY KEY
connection_id	Required FK to connections, cascade delete
name	TEXT NOT NULL; unique with connection_id
purpose	TEXT NOT NULL DEFAULT 'platform'
status	TEXT NOT NULL DEFAULT 'active'; application uses at least provisioning, active, failed
size_bytes	Nullable BIGINT
postgis_enabled	BOOLEAN NOT NULL DEFAULT FALSE
database_role	Nullable TEXT; current source reads it but does not write it
primary_database_id	Nullable self-FK, set null when source row is deleted
created_at, updated_at	Timestamps

postgis_jobs

Column	Definition
id	SERIAL PRIMARY KEY
type	TEXT NOT NULL
status	Defaults to queued; application states: queued, running, completed, failed, cancelled
progress	INTEGER NOT NULL DEFAULT 0; no database range check
stage	TEXT NOT NULL DEFAULT ''
payload, result	JSONB NOT NULL DEFAULT '{}'
error_message	TEXT NOT NULL DEFAULT ''
created_by	INTEGER NOT NULL DEFAULT 0; no Central user FK
created_at, started_at, finished_at	Creation required; start/finish nullable

Job types are create_database, delete_database, clone_database, backup_database, restore_database, and import_vector.

postgis_hba_rules

Contains connection FK, unique (connection_id, name), CIDR/host, auth method (default scram-sha-256), optional database/role scopes represented as empty strings, enabled flag, and timestamps.

postgis_role_database_access

Persists a unique (database_id, role_name) access level (readonly, readwrite, or admin) with timestamps. role_name is not a foreign key because the PostgreSQL role lives on a managed server, not in metadata.

Indexes and constraints

Name	Definition
idx_postgis_jobs_status	(status, created_at DESC)
idx_postgis_databases_connection	(connection_id)
idx_postgis_hba_rules_connection	(connection_id)
idx_postgis_role_db_access_database	(database_id)
idx_postgis_role_db_access_role	(role_name)

Primary keys and unique constraints create their normal PostgreSQL indexes. There are no check constraints for ports, statuses, job progress, SSL modes, auth methods, or access levels.

Migration behavior

There is no schema-version table, migration history, rollback, locking protocol, or destructive migration in source. CREATE TABLE/INDEX IF NOT EXISTS and one ADD COLUMN IF NOT EXISTS are executed as a single SQL string. Operators must provide their own backup and migration governance.

Central auth/ACL tables (dashboard_users, dashboard_sessions, auth_groups, auth_group_members, resources, and resource_permissions) are accessed but are not created or migrated by this service. See Authentication.