Skip to content

Database reference

This reference is derived from installer/setup.sql, both files in installer/migrations/, sql/001_geosync_user_bridge.sql, sql/002_geosync_service_resource.sql, and the PHP data-access classes. It describes declared DDL, not an introspection of any deployed database.

Engine, initialization, and connections

GeoSync uses PostgreSQL for application metadata and PostgreSQL/PostGIS for synchronized spatial targets. The PHP control plane uses the procedural pgsql extension and opens ordinary pg_connect connections; no application connection-pool configuration or pooler such as PgBouncer is supplied. PHP/process-level connection lifecycle therefore applies. Remote target connections are also opened directly from stored pglink credentials.

For a fresh installation, create the role/database and apply a reviewed copy of installer/setup.sql once. The checked-in file has a forward-reference ordering defect documented below, so it cannot be treated as an unattended initializer. Apply numbered migrations explicitly for upgrades; there is no migration runner, schema-version table, automatic rollback, or seed framework. installer/setup.php creates the first Admin and a sentinel rsync row after running the schema, then deletes itself and setup.sql.

PostgreSQL enums

  • public.userlevel: Admin, User.
  • public.project_type: qf (QField), db (Mergin Maps/db-sync), gdb (GeoDatabase).
  • public.rsync_sync_type: remote, local.

Tables created by installer/setup.sql

Every SERIAL PRIMARY KEY below creates an integer, non-null primary key and its backing sequence/index. Unless stated otherwise, columns have no default.

public.user

  • id SERIAL PRIMARY KEY
  • name varchar(250) NOT NULL
  • email varchar(250) NOT NULL UNIQUE
  • password varchar(250) NOT NULL (PHP password_hash; never expose it)
  • pg_password varchar(250) NULL
  • accesslevel public.userlevel NOT NULL
  • owner_id integer NOT NULL REFERENCES public.user(id); self-referential, with PostgreSQL's default NO ACTION update/delete behavior.
  • Added by sql/001: enabled boolean NOT NULL DEFAULT true and auth_source text NULL.

Application creation generates pg_password; local login verifies password.

public.server

  • id SERIAL PRIMARY KEY
  • type public.project_type NOT NULL
  • name varchar(250) NOT NULL UNIQUE
  • url varchar(250) NOT NULL
  • username varchar(250) NOT NULL
  • password varchar(250) NOT NULL
  • owner_id integer NOT NULL REFERENCES public.user(id)

This is the provider credential/configuration table. Credentials are stored as plain database values in the current class, not through sync_secrets.

  • id SERIAL PRIMARY KEY
  • name varchar(250) NOT NULL
  • host varchar(250) NOT NULL
  • port integer NOT NULL DEFAULT 5432
  • username varchar(250) NOT NULL
  • password varchar(250) NOT NULL
  • dbname varchar(80) NOT NULL
  • owner_id integer NOT NULL REFERENCES public.user(id)

There is no declared uniqueness constraint on name or connection coordinates.

public.rsync

  • id SERIAL PRIMARY KEY
  • name varchar(250) NOT NULL
  • sync_type public.rsync_sync_type NOT NULL DEFAULT 'remote'
  • local_project_id integer NULL REFERENCES public.project(id)
  • host varchar(250) NOT NULL
  • port integer NOT NULL DEFAULT 5432
  • username varchar(250) NOT NULL
  • password varchar(250) NOT NULL
  • folder varchar(250) NOT NULL
  • usr_grp varchar(50) NULL DEFAULT 'www-data:www-data'
  • owner_id integer NOT NULL REFERENCES public.user(id)

For local links the PHP class writes synthetic values (localhost, port 0, -, -) into remote-only required columns and derives folder from the published project. The FK has default NO ACTION behavior.

Important ordering issue: setup.sql declares rsync.local_project_id referencing public.project before it creates public.project. In a fresh, otherwise empty schema PostgreSQL cannot resolve that FK at that point. The idempotent migration below is the safer upgrade path after project exists.

public.geonode

  • id SERIAL PRIMARY KEY
  • name varchar(250) NOT NULL
  • host varchar(250) NOT NULL
  • port integer NOT NULL DEFAULT 22
  • username varchar(250) NOT NULL
  • password varchar(250) NOT NULL
  • folder varchar(250) NOT NULL
  • owner_id integer NOT NULL REFERENCES public.user(id)

public.project

  • id SERIAL PRIMARY KEY
  • name varchar(250) NOT NULL UNIQUE
  • server_id integer NOT NULL REFERENCES public.server(id)
  • project_id varchar(250) NOT NULL (upstream provider identifier)
  • geopackage varchar(250) NOT NULL
  • pglink_id integer NOT NULL REFERENCES public.pglink(id)
  • schema varchar(50) NOT NULL
  • owner_id integer NOT NULL REFERENCES public.user(id)
  • publisher_project_id varchar(64) NULL
  • publisher_viewer_url varchar(500) NULL
  • published_at timestamp without time zone NULL
  • publish_status varchar(32) NULL
  • publish_error text NULL

All FKs use the default NO ACTION; no FK columns have explicit indexes.

public.project_rsync

  • id SERIAL PRIMARY KEY
  • project_id integer NOT NULL REFERENCES public.project(id)
  • rsync_id integer NOT NULL REFERENCES public.rsync(id) UNIQUE

The unusual uniqueness rule permits many rsync links per project but permits a given rsync link in only one project. There is no explicit unique constraint on (project_id, rsync_id) and no explicit index on project_id.

public.group

  • id SERIAL PRIMARY KEY
  • name varchar(250) NOT NULL UNIQUE
  • project_ids varchar(250) NOT NULL; comma-separated IDs, not an FK/junction.
  • owner_id integer NOT NULL REFERENCES public.user(id)

This legacy application group is distinct from Central auth_groups.

public.gstore

  • id SERIAL PRIMARY KEY
  • name varchar(250) NOT NULL UNIQUE
  • url varchar(250) NULL DEFAULT 'http://localhost:8080/geoserver'
  • username varchar(250) NULL
  • password varchar(250) NULL
  • workspace varchar(250) NOT NULL
  • project_id integer NOT NULL REFERENCES public.project(id)
  • owner_id integer NOT NULL REFERENCES public.user(id)

public.signup

  • id SERIAL PRIMARY KEY
  • name varchar(250) NOT NULL
  • email varchar(250) NOT NULL UNIQUE
  • password varchar(250) NOT NULL
  • verify varchar(250) NOT NULL

Public registration is currently blocked before the signup/verify handlers run.

Additional migration table

sql/001_geosync_user_bridge.sql creates public.geosync_user_bridge:

  • geosync_user_id integer PRIMARY KEY REFERENCES public.user(id) ON DELETE CASCADE
  • dashboard_user_id integer NOT NULL UNIQUE
  • provisioned_at timestamptz NOT NULL DEFAULT now()
  • provision_source text NOT NULL DEFAULT 'central_sso'
  • created_by_dashboard_id integer NOT NULL
  • Explicit non-unique index geosync_user_bridge_dashboard_user_id_idx(dashboard_user_id).

The explicit index duplicates lookup capability already supplied by the unique constraint, although the names differ. Current Central auth does not use this bridge: it hydrates a synthetic legacy Admin session without provisioning.

Migrations and runtime DDL

  1. installer/migrations/001_publish_metadata.sql idempotently adds the five nullable publish columns listed under project, using DO blocks that ignore duplicate_column.
  2. installer/migrations/002_rsync_local.sql idempotently creates rsync_sync_type, adds rsync.sync_type NOT NULL DEFAULT 'remote', and adds nullable rsync.local_project_id REFERENCES project(id).
  3. sql/001_geosync_user_bridge.sql creates the bridge and adds user.enabled and user.auth_source.
  4. sql/002_geosync_service_resource.sql targets the separate Central metadata database. It upserts resource ('service','geosync'), then grants its admin permission to the case-insensitive administrators Central group. It assumes Central tables/constraints (resources, resource_permissions, auth_groups) exist; it does not create them.
  5. project_Class::ensurePublishColumns() and rsync_Class::ensureColumns() repeat best-effort runtime DDL. Errors are suppressed. Runtime code is therefore not a substitute for applying and auditing migrations.

Unmigrated sync_secrets reference

admin/class/secrets.php inserts/selects public.sync_secrets(id, name, ciphertext) and encrypts blobs with libsodium using GEOSYNC_SECRET_KEY_B64. No checked SQL file creates that table, its sequence, constraints, defaults, or indexes. This is a verified code/schema gap; those properties cannot be documented without inventing DDL. The class is also not used by the provider CRUD classes reviewed here.

Index and constraint summary

Besides primary-key indexes, setup declares unique indexes through constraints on user.email, server.name, project.name, project_rsync.rsync_id, group.name, gstore.name, and signup.email. The bridge adds uniqueness on dashboard_user_id and one explicit index of the same column. No other secondary, partial, expression, or composite indexes are declared in the reviewed migrations.