Database

Database engine

Item	Value
Engine	PostgreSQL
Client	github.com/jackc/pgx/v5 pgxpool
ORM	None
Named schema	Not created; objects use the connection search path
SQLite/MySQL/Redis	Not supported

DSN sources: CENTRAL_DATABASE_URL, then DATABASE_URL.

Connection pooling

pgxpool.New(ctx, databaseURL) followed by Ping. Central does not set explicit max/min connections, idle timeouts, or lifetimes; those use pgxpool defaults and any DSN/URL parameters you supply.

Startup uses a 10-second context for connect + EnsureSchema. Request handlers use r.Context() without additional DB deadlines. The pool is closed on process exit.

Database initialization

On startup when a DSN is present:

1. Open and ping the pool
2. Run PostgresStore.EnsureSchema (unversioned SQL statements)
3. Wire auth provider / session service
4. EnsurePlatformServiceResources for platform service ACL rows
5. Bootstrap a local admin if dashboard_users count is zero
6. During empty-admin bootstrap, delete expired sessions once

There is no versioned migration framework, migration table, or rollback set. deploy/sql/portal_cards.sql creates only portal_cards and is not a complete schema substitute.

Schema statements execute one-by-one (non-transactional). A mid-init failure can leave a partial schema; a restart may repair additive portions.

Schemas / tables

Central declares eight runtime tables.

ER overview

erDiagram
  dashboard_users ||--o{ dashboard_sessions : username
  dashboard_users ||--o{ personal_access_tokens : user_id
  dashboard_users ||--o{ auth_group_members : user_id
  auth_groups ||--o{ auth_group_members : group_id
  auth_groups ||--o{ resource_permissions : group_id
  resources ||--o{ resource_permissions : resource_id
  dashboard_users ||--o{ resources : created_by
  portal_cards }o..o{ resources : "service+resource_key logical"

portal_cards

Column	Type	Notes
id	SERIAL PK
title	TEXT NOT NULL
description	TEXT	nullable
card_type	TEXT NOT NULL	Go-validated: map, dashboard, report, dataset, external, admin
url	TEXT NOT NULL	absolute http(s) or root-relative
icon	TEXT
image_url	TEXT
category	TEXT
sort_order	INTEGER DEFAULT 0
is_public	BOOLEAN DEFAULT FALSE	used for manual cards
source_service	TEXT	logical link to resources.service
source_resource_id	TEXT	logical link to resources.resource_key
is_imported	BOOLEAN DEFAULT FALSE
created_at	TIMESTAMPTZ DEFAULT NOW()

Indexes:

• portal_cards_visible_idx (is_public, sort_order, title)
• portal_cards_category_idx (category)
• portal_cards_source_idx (source_service, source_resource_id)

There is no FK to resources and no uniqueness constraint on imported source pairs.

dashboard_users

Column	Type	Notes
id	SERIAL UNIQUE NOT NULL	also unique index dashboard_users_id_key
username	TEXT PK	login key
email	TEXT NOT NULL DEFAULT ''	not used as login identity
password_hash	TEXT NOT NULL	PBKDF2-SHA256 encoded string
groups	JSONB NOT NULL DEFAULT '[]'	legacy mirror of memberships
is_admin	BOOLEAN NOT NULL DEFAULT FALSE	legacy/admin mirror
enabled	BOOLEAN NOT NULL DEFAULT TRUE
created_at	TIMESTAMPTZ DEFAULT NOW()

dashboard_sessions

Column	Type	Notes
token_hash	TEXT PK	SHA-256 hex of cookie value
username	TEXT NOT NULL	FK → dashboard_users(username) ON DELETE CASCADE
expires_at	TIMESTAMPTZ NOT NULL
created_at	TIMESTAMPTZ DEFAULT NOW()

Index: idx_dashboard_sessions_expires_at (expires_at).

personal_access_tokens

Column	Type	Notes
id	SERIAL PK
user_id	INTEGER NOT NULL	FK → dashboard_users(id) ON DELETE CASCADE
name	TEXT NOT NULL
token_hash	TEXT NOT NULL UNIQUE	SHA-256 hex of agp_...
token_prefix	TEXT NOT NULL	first 12 chars for UI
scopes	JSONB NOT NULL DEFAULT '[]'	currently empty on create
expires_at	TIMESTAMPTZ	nullable
last_used_at	TIMESTAMPTZ	throttled updates (~5 minutes)
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()
revoked_at	TIMESTAMPTZ	soft revoke

Indexes: idx_personal_access_tokens_user_id, idx_personal_access_tokens_token_hash (in addition to UNIQUE).

auth_groups

Column	Type	Notes
id	SERIAL PK
name	VARCHAR(100) UNIQUE NOT NULL	case-sensitive uniqueness
description	TEXT
created_at	TIMESTAMP DEFAULT NOW()	no time zone

Seeded names: Administrators, Editors, Analysts, Customers, Public. Application code protects renaming/deleting exact-case Administrators and Public.

auth_group_members

Column	Type	Notes
id	SERIAL PK
group_id	INTEGER	FK → auth_groups(id) ON DELETE CASCADE (nullable at DDL level)
user_id	INTEGER	FK → dashboard_users(id) ON DELETE CASCADE
created_at	TIMESTAMP DEFAULT NOW()

Unique: (group_id, user_id).

resources

Column	Type	Notes
id	SERIAL PK
resource_type	VARCHAR(100) NOT NULL	e.g. service, dataset types from other apps
resource_key	VARCHAR(255) NOT NULL
display_name	VARCHAR(255)
service	VARCHAR(100)
created_by	INTEGER	FK → dashboard_users(id) (no ON DELETE action)
created_at	TIMESTAMP DEFAULT NOW()

Unique: (resource_type, resource_key).

Startup upserts platform service resources (resource_type=service, service=platform):

Key	Display name
qgis_publish	QGIS Publish Service
geovault	GeoVault
jasper_reports	Jasper Reports
dashboard_service	Dashboard Service
tileserver_admin	TileServer Admin
postgis_service	PostGIS Service

Newly inserted rows receive Administrators admin permission.

resource_permissions

Column	Type	Notes
id	SERIAL PK
resource_id	INTEGER	FK → resources(id) ON DELETE CASCADE
group_id	INTEGER	FK → auth_groups(id) ON DELETE CASCADE
permission	VARCHAR(50) NOT NULL	runtime: view, edit, admin
created_at	TIMESTAMP DEFAULT NOW()

Unique: (resource_id, group_id, permission).

Permission implication in queries: admin satisfies all; edit satisfies view.

Relationships used by portal visibility

• Administrators see every card.
• Manual cards (source_service or source_resource_id empty) require is_public=true.
• Resource-backed cards require a matching resources row where service = source_service and resource_key = source_resource_id, plus a resource_permissions grant for one of the viewer’s groups (Public always included).

Note: resources are unique on (resource_type, resource_key), while card discovery joins on (service, resource_key).

Migrations

Mechanism	Present?
Versioned migrations	No
EnsureSchema on startup	Yes
Standalone SQL (deploy/sql/portal_cards.sql)	Cards only
Rollback scripts	No

Verified discrepancies

1. Standalone SQL omits auth/ACL/session/PAT tables.
2. Existing tables are not fully reconciled; only some ALTERs exist (notably for portal_cards and limited dashboard_users columns).
3. Discovery may emit card types such as tilelayer / pipeline that create-card validation rejects.
4. Mixed TIMESTAMPTZ vs TIMESTAMP across tables.
5. Redundant uniqueness/indexes on some columns (dashboard_users.id, PAT token_hash).

Backups

No backup/restore implementation exists in Central. Operators should back up:

• PostgreSQL database referenced by the DSN
• Thumbnail directory (CENTRAL_THUMBNAILS_DIR)
• /etc/central.env and deployed binary/assets