Database

Engine

Item	Value
Engine	PostgreSQL
Driver	github.com/lib/pq
Metadata DSN	DATABASE_URL
Auth DSN	GEOVAULT_AUTH_DATABASE_URL → CENTRAL_DATABASE_URL → DATABASE_URL
Fallback	If metadata DSN empty: no Meta pool; versions persist to JSON

GeoVault owns nine metadata tables listed below. It does not create Central auth tables, PostGIS Service registry tables, PostGIS extension views, or GeoPackage system tables.

Initialization

On startup (db.OpenMeta):

1. Trim and unquote DATABASE_URL, map sslmode=prefer to disable, and validate common DSN errors
2. sql.Open + Ping
3. Run idempotent migrateSQL
4. Import legacy data/versions.json into versions if the table is empty
5. Run the one-time default-folder data migration, recorded in app_settings

Migration or ping failure aborts startup. Legacy JSON import and default-folder migration failures are logged but do not abort startup. The separate auth pool is mandatory and is opened after metadata initialization.

Connection pooling

Pool	Settings in source
Metadata db.Meta	Default database/sql pool settings (no explicit SetMaxOpenConns in db.go)
Auth store	Default database/sql pool; separate even when its DSN equals DATABASE_URL
Remote user PostGIS (pgconn)	MaxOpenConns=5, MaxIdleConns=2, ConnMaxLifetime=2m
GeoPackage SQLite editor	MaxOpenConns=1

Migrations

internal/db/migrate.go contains one embedded SQL string executed at every metadata startup. It:

1. Creates pg_connections, datasets, and versions.
2. Adds/normalizes dataset visibility and conditionally creates datasets_visibility_check.
3. Adds datasource fields to datasets and versions.
4. Creates GeoServer connection, publication, and job tables plus publication indexes.
5. Adds dataset archive/clone fields, creates dataset_metadata, and adds version rollback provenance.
6. Creates folders, adds folder columns/indexes, drops the old global-name and global-version unique constraints, and creates folder-scoped indexes.
7. Creates app_settings.

The SQL uses IF NOT EXISTS where PostgreSQL supports it, but also unconditionally drops two named legacy constraints. There is no numbered migration history, schema-version table, down migration, transaction wrapper in application code, or migration CLI. The entire SQL string is sent through one Exec; PostgreSQL transaction behavior for that multi-statement execution should not be assumed to replace an explicit migration framework.

GeoVault-owned schema

pg_connections

Column	Type / notes
id	SERIAL PK
name	TEXT UNIQUE NOT NULL
host	TEXT NOT NULL
port	INTEGER default 5432
database_name	TEXT NOT NULL
username	TEXT NOT NULL
password	TEXT NOT NULL (plaintext)
sslmode	TEXT default disable
created_at	TIMESTAMP default NOW()

password is stored as supplied plaintext. No ownership or per-row ACL column exists.

datasets

Column	Type / notes
id	SERIAL PK
name	TEXT NOT NULL; active names are unique per folder via an expression index
project_id	INTEGER NOT NULL default 1
datasource_type	TEXT NOT NULL
connection_id	FK → pg_connections(id) ON DELETE SET NULL
schema_name, table_name, geometry_column	PostGIS pointer fields
visibility	public | restricted (CHECK)
created_by	FK → dashboard_users(id)
created_at	TIMESTAMP default NOW()
datasource_mode, datasource_url	TEXT default empty string
datasource_meta	JSONB
archived_at	soft archive
cloned_from_dataset_id	FK → datasets(id) ON DELETE SET NULL
folder_id	FK → folders(id) ON DELETE SET NULL

The initial table definition declares name UNIQUE, but migration later drops datasets_name_key and replaces it with the active, folder-scoped index.

versions

Column	Type / notes
id	INTEGER PK (application-assigned)
project_id	INTEGER NOT NULL
dataset	TEXT NOT NULL
type	TEXT NOT NULL default empty string
version	INTEGER NOT NULL
file_path, file_type	TEXT NOT NULL default empty string
created_at	TEXT NOT NULL default empty string; application writes RFC3339
created_by	nullable FK → dashboard_users(id)
message	TEXT NOT NULL default empty string
status, operation	nullable TEXT
parent_version	nullable integer version number; no FK
rollback_source_version_id	nullable integer row ID; no FK
datasource_type, datasource_mode, datasource_url	nullable TEXT (mode/url added with empty defaults)
datasource_meta	nullable JSONB
connection_id	nullable FK → pg_connections(id) ON DELETE SET NULL
schema_name, table_name, geometry_column	nullable PostGIS target fields
folder_id	nullable INTEGER; notably no FK to folders

Uniqueness is enforced by index idx_versions_scope_version on (project_id, COALESCE(folder_id,0), LOWER(dataset), version).

The application loads this table into memory and SaveVersions transactionally deletes every row and reinserts the entire slice. IDs are assigned as current maximum plus one in memory; database sequences are not used.

folders

Column	Type / notes
id	SERIAL PK
name	TEXT NOT NULL
description	nullable TEXT
owner_id	nullable FK → dashboard_users(id)
group_name	nullable TEXT; storage exists, but this alone is not an ACL
created_at, updated_at	TIMESTAMP default NOW()
archived_at	nullable TIMESTAMP

Unique active names: idx_folders_name_active on LOWER(name) WHERE archived_at IS NULL.

dataset_metadata

Column	Type / notes
dataset_id	PK and FK → datasets(id) ON DELETE CASCADE
title, description, tags, citation, attribution, keywords, notes	TEXT NOT NULL default empty string
updated_at	TIMESTAMP default NOW()

geoserver_connections

Column	Type / notes
id	SERIAL PK
name	TEXT UNIQUE NOT NULL
url	TEXT NOT NULL
username, password	TEXT NOT NULL default empty string
default_workspace	TEXT default empty string
enabled, verify_ssl	BOOLEAN NOT NULL default true
created_at, updated_at	TIMESTAMP default NOW()

Application writes normally use internal/services/geoserver encryption (enc:); legacy plain:/raw handling exists. Encryption requires configured key material. The schema itself does not enforce ciphertext.

dataset_geoserver_publications

Column	Type / notes
id	SERIAL PK
dataset_id	nullable FK → datasets(id) ON DELETE CASCADE
dataset_name	TEXT NOT NULL denormalized name
connection_id	FK → geoserver_connections(id) ON DELETE CASCADE
workspace	TEXT NOT NULL
store_name	TEXT NOT NULL default empty string
layer_name	TEXT NOT NULL
service_type	TEXT NOT NULL default wms
style_name, wms_url, wfs_url, wmts_url	TEXT default empty string
created_at, updated_at	TIMESTAMP default NOW()

geoserver_jobs

Column	Type / notes
id	SERIAL PK
dataset_name	TEXT NOT NULL default empty string
version_id	nullable integer; no FK
connection_id	nullable FK → geoserver_connections(id) ON DELETE SET NULL
job_type	TEXT NOT NULL
status	TEXT NOT NULL default queued; no CHECK
payload, result	nullable JSONB
error_message, log_text	TEXT default empty string
created_at, updated_at	TIMESTAMP default NOW()

app_settings

Column	Type / notes
key	TEXT PK
value	TEXT NOT NULL default empty string
updated_at	TIMESTAMP default NOW()

Current source uses key default_folder_migration_done to make orphan assignment a one-time operation.

Indexes (explicit)

Index	Table	Definition
idx_dataset_geoserver_pub_dataset	dataset_geoserver_publications	LOWER(dataset_name)
idx_dataset_geoserver_pub_conn	dataset_geoserver_publications	connection_id
idx_folders_name_active	folders	unique lower name where active
idx_datasets_folder_id	datasets	folder_id
idx_datasets_folder_name_active	datasets	unique (COALESCE(folder_id,0), LOWER(name)) where active
idx_versions_folder_dataset	versions	(project_id, COALESCE(folder_id,0), LOWER(dataset))
idx_versions_scope_version	versions	unique scoped version

Relationships

erDiagram
  folders ||--o{ datasets : contains
  pg_connections ||--o{ datasets : registers
  pg_connections ||--o{ versions : optional
  datasets ||--o{ dataset_geoserver_publications : published_as
  geoserver_connections ||--o{ dataset_geoserver_publications : via
  geoserver_connections ||--o{ geoserver_jobs : optional
  datasets ||--o| dataset_metadata : has
  datasets ||--o{ datasets : cloned_from
  dashboard_users ||--o{ datasets : created_by
  dashboard_users ||--o{ versions : created_by
  dashboard_users ||--o{ folders : owns

Additional relation details:

• datasets.cloned_from_dataset_id is a nullable self-reference with ON DELETE SET NULL.
• versions.connection_id references pg_connections; versions.folder_id, parent_version, and rollback_source_version_id are logical links only.
• geoserver_jobs.version_id and geoserver_jobs.dataset_name are logical links only.
• dataset_geoserver_publications keeps both nullable dataset_id and denormalized dataset_name.
• No FK connects a GeoVault dataset to the Central resources ACL row; the logical key is resource type geovault_dataset plus dataset name.

Central tables read by GeoVault (not migrated here)

Queried or mutated by internal/authstore:

• dashboard_users
• dashboard_sessions (read; delete methods also exist)
• auth_groups
• auth_group_members
• resources (read and upsert)
• resource_permissions (read, grant, revoke)

References from GeoVault tables to dashboard_users(id) require those Central tables to exist in the same database when FKs are applied (typical shared acugis_meta/Central DB deployments).

GeoVault does not own or migrate these schemas. Exact columns, indexes, and constraints beyond those referenced by its SQL are controlled by Central and cannot be established from GeoVault source alone.

External PostGIS registry and spatial tables

When PostGIS Service shares DATABASE_URL, GeoVault reads:

• postgis_connections: id, name, host, port, sslmode, admin_username, admin_password
• postgis_databases: id, connection_id, name, status, postgis_enabled
• postgis_jobs: id, status, error_message

These are externally owned; GeoVault neither creates nor indexes them. GeoVault also reads information_schema.tables to detect availability.

In user-selected spatial databases, browse/edit code reads PostgreSQL catalogs, information_schema.columns, information_schema.table_constraints, information_schema.key_column_usage, and PostGIS geometry_columns. GeoPackage editing reads gpkg_contents. None is a GeoVault-owned table.

JSON fallback model

When Meta is nil, internal/store serializes []models.Version to JSON using a temp-file rename. Selection precedence is GEOVAULT_VERSIONS_JSON, then GEOVAULT_DATA_DIR/versions.json, then absolute data/versions.json. Only the version slice has a JSON fallback; dataset registry, folders, metadata, jobs, connections, publications, and settings do not.

Backups

No application-level backup command exists. Operators must back up:

1. PostgreSQL databases used for Meta and Central auth
2. /data/projects (and any alternate trees)
3. GEOVAULT_DATA_DIR / versions.json and data/metadata when used

Backup schedules and tools are not defined in this repository.