Database Query

Query the GOAT PostgreSQL database to inspect data, debug issues, and understand state.

Connection

There is no host psql; go through the running Postgres container. The container name changes across setups (goat-db, goat-db18, …), so discover it rather than hardcoding:

source /home/p4b/goat/.env
DBC=$(docker ps --format '{{.Names}}' | grep -E '^goat-db' | head -1)
docker exec -e PGPASSWORD=$POSTGRES_PASSWORD "$DBC" psql -h 127.0.0.1 -U $POSTGRES_USER -d $POSTGRES_DB

One-off query:

source /home/p4b/goat/.env
DBC=$(docker ps --format '{{.Names}}' | grep -E '^goat-db' | head -1)
docker exec -e PGPASSWORD=$POSTGRES_PASSWORD "$DBC" psql -h 127.0.0.1 -U $POSTGRES_USER -d $POSTGRES_DB -c "YOUR SQL HERE"

Schemas

Schema	Purpose
customer	Everything: users, orgs, teams, roles/permissions, projects, layers, jobs, workflows
ducklake	DuckLake catalog (managed by geoapi, don't modify directly)

Key Tables & Relationships

The SQLModel definitions in apps/core are the source of truth — introspect when unsure:

SELECT table_name FROM information_schema.tables WHERE table_schema='customer' ORDER BY 1;
\d customer.layer

Identity & sharing (all in customer)

• user (id uuid) — Keycloak-synced. firstname, lastname, avatar
• organization (id uuid) — name, avatar; organization_domain, organization_analytics
• team (id uuid) — belongs to org. name, avatar
• role (id uuid) — permission roles; RBAC via permission, role_permission, user_role, resource, resource_grant, resource_permission
• user_team — M2M user ↔ team; invitation — pending org/team invites
• layer_organization / layer_team / layer_user — layer sharing with role
• project_organization / project_team / project_user — project sharing with role

Projects & layers (customer)

• project (id uuid) — user_id, folder_id, layer_order[], basemap, tags[]
• layer (id uuid) — user_id, folder_id. Key fields: name, type, data_type, tool_type, job_id, feature_layer_type, feature_layer_geometry_type, extent (geometry), properties (jsonb), other_properties (jsonb — holds catalog_item / catalog_materialize for promoted catalog layers), field_config (jsonb, per-column metadata), url, size, in_catalog, tags[], catalog_external_uid, catalog_version
• layer_project (id int) — M2M layer ↔ project. name, properties (jsonb style config), other_properties, query (jsonb filters), charts, order, layer_project_group_id
• layer_project_group (id int) — layer groups. project_id, parent_id (self-ref nesting), order
• folder (id uuid) — user_id, name
• job (id uuid) — user_id. type, status, payload (jsonb)
• workflow (id uuid) — project_id, name, config (jsonb), is_default
• report / report_layout (id uuid) — project_id, name, config (jsonb), is_default
• project_public — public sharing config: password, config (jsonb snapshot)
• user_project — user ↔ project with initial_view_state (jsonb)
• system_setting — per-user: client_theme, preferred_language, unit
• uploaded_asset — user uploads: s3_key, file_name, mime_type, file_size, asset_type, content_hash
• cost / credit_usage — credit metering

Common Queries

-- Projects with layer counts
SELECT p.id, p.name, p.created_at, COUNT(lp.id) AS layer_count
FROM customer.project p
LEFT JOIN customer.layer_project lp ON lp.project_id = p.id
GROUP BY p.id ORDER BY p.created_at DESC;

-- Layers in a project with styles
SELECT lp.id, lp.name, lp.order, l.type, l.feature_layer_type, l.feature_layer_geometry_type
FROM customer.layer_project lp
JOIN customer.layer l ON l.id = lp.layer_id
WHERE lp.project_id = 'PROJECT_UUID'
ORDER BY lp.order;

-- Job status
SELECT id, type, status, created_at, payload->>'tool_type' AS tool
FROM customer.job ORDER BY created_at DESC LIMIT 10;

Important Notes

• Layer metadata lives in PostgreSQL (customer.layer), layer data lives in DuckLake (managed by geoapi)
• layer_project.properties = style/rendering config (jsonb); layer_project.query = active filters (jsonb)
• Scenarios are gone (2026-08-27): the feature, the three tables and project.active_scenario_id were all removed. scenario_feature was the last table using generic columns (integer_attr1..25); nothing in GOAT uses that scheme any more
• A catalog layer has no owner: layer.user_id and layer.folder_id are NULL for promoted catalog layers. Joins from layer to user must be LEFT joins or those rows vanish
• A public dashboard reads the project_public.config snapshot, not the live project — re-publish to reflect changes
• Always use READ-ONLY queries. Never INSERT/UPDATE/DELETE unless explicitly asked
• Use ST_AsText() or ST_AsGeoJSON() to read geometry columns