db

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

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 & scenarios (customer)

• project (id uuid) — user_id, folder_id, active_scenario_id, layer_order[], basemap, tags[]
• layer (id uuid) — user_id, folder_id, data_store_id. Key fields: name, type, data_type, tool_type, feature_layer_type, feature_layer_geometry_type, extent (geometry), properties (jsonb), url, size, attribute_mapping (jsonb), in_catalog, tags[]
• 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
• data_store (id uuid) — storage backends. type
• folder (id uuid) — user_id, name
• job (id uuid) — user_id. type, status, payload (jsonb)
• scenario (id uuid) — project_id, user_id, name
• scenario_feature (id uuid) — layer_project_id, feature_id (text), edit_type, geom, h3_3, h3_6. Generic typed columns: integer_attr1..25, float_attr1..25, text_attr1..25, plus bigint/jsonb/boolean/array/timestamp attrs
• scenario_scenario_feature — M2M scenario ↔ scenario_feature
• 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;

-- Scenario features for a scenario
SELECT sf.id, sf.feature_id, sf.edit_type, ST_AsText(sf.geom) AS geom
FROM customer.scenario_feature sf
JOIN customer.scenario_scenario_feature ssf ON ssf.scenario_feature_id = sf.id
WHERE ssf.scenario_id = 'SCENARIO_UUID';

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)
• scenario_feature uses generic columns (integer_attr1..25 etc.) — check layer.attribute_mapping for which attr maps to which real column name
• 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