Skip to content

Database

Connection and initialization

The main service selects the first non-empty URL in this order:

  1. QGIS_AUTH_DATABASE_URL
  2. CENTRAL_DATABASE_URL
  3. DATABASE_URL

It opens PostgreSQL through lib/pq and pings at startup. If no URL exists or the connection fails, the process continues with Central auth and database-backed stores disabled.

After a successful connection, startup runs idempotent CREATE TABLE/INDEX IF NOT EXISTS statements for the service-owned registries below. Each registry initialization logs success/failure independently; a failure does not terminate startup. There is no versioned migration framework, downgrade, transaction spanning all schemas, or startup lock.

Service-owned tables

runtime_datasets

  • id UUID PRIMARY KEY
  • slug TEXT NOT NULL UNIQUE
  • display_name, runtime_db_name, pg_service_name, source_file
  • timestamps imported_at, updated_at
  • optional owner ID/name
  • project_ids TEXT[] DEFAULT '{}'
  • status and optional status message

The unique constraint on slug supplies its index.

runtime_dataset_tables

  • serial primary key and cascading FK to runtime_datasets
  • original/imported table names
  • geometry flag, optional primary key/geometry column
  • source provider defaulting to gpkg
  • unique (runtime_dataset_id, imported_table_name)
  • index idx_runtime_dataset_tables_dataset(runtime_dataset_id)

runtime_dataset_relations

  • serial primary key and cascading FK to runtime_datasets
  • parent/child tables, FK field, optional relation ID/name/type
  • index idx_runtime_dataset_relations_dataset(runtime_dataset_id)

basemaps

  • id TEXT PRIMARY KEY
  • title, thumbnail, source type, URL, cross-origin value, attribution
  • sort order and default flag

When the table is empty, startup seeds Carto Light/Dark, Esri Satellite/Topo, OpenStreetMap, and Atlas definitions. One tracked seed URL contains a credential-like query value; do not reuse it as a production secret, and rotate/replace it. There is no constraint ensuring only one default, but store writes clear other defaults transactionally.

browser_maps

  • UUID primary key
  • owner ID/name, title, description
  • map_config JSONB
  • optional thumbnail path
  • visibility defaulting to restricted
  • created/updated timestamps
  • indexes idx_browser_maps_owner(owner_user_id) and idx_browser_maps_updated(updated_at DESC)

ingest_sessions

  • UUID primary key
  • user ID, status, created/updated timestamps
  • indexes idx_ingest_sessions_user(user_id) and idx_ingest_sessions_updated(updated_at DESC)

ingest_session_sources

  • UUID primary key and cascading FK to ingest_sessions
  • source type/name, metadata JSONB, status, temporary location, timestamps
  • index idx_ingest_session_sources_session(session_id)

project_metadata

  • bigserial primary key and unique textual project_id
  • descriptive, contact, license, status, version, language, lineage, and UUID fields
  • tags JSONB DEFAULT '[]'
  • created/updated timestamps
  • unique index project_metadata_project_id_uidx(project_id)
  • GIN index project_metadata_tags_gin(tags)

project_id intentionally has no SQL FK because projects live on disk.

Central-owned auth tables

The service does not create or migrate these tables. Their exact Central DDL and indexes are outside this repository; only the queried columns/relationships can be verified.

  • dashboard_users: reads id, username, email, password_hash, groups, is_admin, enabled, created_at; count is supported.
  • dashboard_sessions: reads token_hash, username, expires_at; session lookup joins to dashboard_users. The adapter can delete a session or expired sessions, although this service exposes no login/logout route.
  • auth_groups: reads id, name, description, created_at.
  • auth_group_members: joins users to groups through user_id and group_id.
  • resources: reads/upserts id, resource_type, resource_key, display_name, service, created_by, created_at. Upsert requires a Central unique constraint on (resource_type, resource_key).
  • resource_permissions: reads/inserts/deletes id, resource_id, group_id, permission, created_at; joins resources/groups. Upsert requires a unique constraint on (resource_id, group_id, permission).

The service writes only resource catalog/permission rows during normal publishing and reconciliation; user/group creation and modification return a “Central owned” error.

Recommended Central indexes, whose existence cannot be verified here, are on session token/expiry, usernames, group memberships, resource identity, and permission foreign keys.

Runtime PostGIS databases

Operational import creates one database named from the sanitized dataset slug, enables postgis, and imports layers into public. It may create a cluster role and grants:

  • all database privileges;
  • all privileges on public;
  • all current/future table and sequence privileges.

The source records registry metadata in Central but does not centrally migrate imported table schemas. Foreign keys are discovered from information_schema; GDAL creates the imported tables. Backups, retention, role cleanup, database deletion, and failure rollback are operator responsibilities.

Backup and restore

Back up as one consistency set:

  • the Central PostgreSQL database;
  • every runtime PostGIS database and relevant roles;
  • PROJECTS_ROOT;
  • version, runtime dataset, ingest (if needed), thumbnail, and qcarta roots;
  • PG_SERVICE_FILE through a secret-aware backup mechanism.

Project version restore only copies a filesystem snapshot back into the project directory. It does not restore Central rows, runtime databases, browser maps, or cache state.