Database partner guide

The login is in view_sub (inherits pogo_meta). SELECT on Game Master (pogo_gm), PvP (pogo_pvp), geography catalogs (geo), and live views (pogo_live.sub_*). No DML. Live base tables aren't granted.

All live timestamps are timestamptz. Rows with a point also carry timezone_id / timezone_revision for the matched IANA zone; join geo.timezone for tz_name (valid in pg_timezone_names / AT TIME ZONE).

Connect

postgresql://[email protected]:5432/pokecoords?sslmode=require

Set application_name to your project.

SELECT session_user, current_user, current_database();

session_user is your login.

Access

Live views call cuno.check_sub_access. Staff enable that from the subscription. pogo_gm and pogo_pvp aren't row-gated.

An empty result is either the subscription (dataset off) or your WHERE—for example expires > now() when you wanted history.

Each live view belongs to a dataset:

Dataset Views
spawns sub_spawns, sub_v2_spawns
raids sub_raids, sub_v2_raids
quests sub_quests, sub_v2_quests, sub_v2_quest_rewards
gyms sub_v2_gyms
pokestops sub_v2_pokestops
invasions sub_v2_invasions, sub_v2_invasions_lineup
power_spots sub_v2_power_spots, sub_v2_stations
max_battles sub_v2_max_battles, sub_v2_max_battle_events
weather sub_v2_weather

Use pogo_live.sub_v2_* for new queries.

Roles and schemas

Role SELECT
pogo_meta pogo_gm, pogo_pvp
view_sub Live sub_* views. Inherits pogo_meta.
Schema Contents
pogo_gm Game Master
pogo_pvp PvP ranks
pogo_live Live views
geo IANA timezones (timezone, timezone_release) and Who's On First
public Unqualified sub_spawns / sub_raids on some logins. Prefer pogo_live.

geo grants SELECT on timezone, timezone_release, wof_places, wof_names, wof_hierarchy, wof_supersession, wof_concordances, wof_release, and catalog_publication, and EXECUTE on wof_place_search, wof_placetypes, and wof_in_place. timezone_slice, wof_geometries, wof_shard, and wof_locality_knn aren't granted. No CREATE in these schemas.

Usage

Session TimeZone only affects display of timestamptz. Keep transactions short. Slow query: send the SQL.

SET statement_timeout = '30s';

Raise or skip that for a long cursor read.

Catalog change notifications

If you cache GM/PvP/geo, LISTEN and reload from the metadata row. The notify does not update your cache.

Channel Metadata row
cuno_wake_pogo_gm pogo_gm.gm_version
cuno_wake_pogo_pvp pogo_pvp.pvp_derived_meta WHERE id = 1
cuno_wake_geo geo.catalog_publication (catalog in timezone, wof)

Payloads are empty. Anyone with CONNECT can NOTIFY. No replay. On connect and every reconnect:

  1. LISTEN and commit (or autocommit).
  2. In a new transaction, read the singleton rows and reconcile.
  3. Reconcile again on each notification, and periodically for missed wakes.

Record only metadata you loaded. Notifies can duplicate; another commit can land while you handle one. Keep the listener transaction short. LISTEN startup ordering.

Listener connection:

BEGIN;
LISTEN cuno_wake_pogo_gm;
LISTEN cuno_wake_pogo_pvp;
LISTEN cuno_wake_geo;
COMMIT;

Then load from one snapshot on a separate connection:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT batch_id, modified FROM pogo_gm.gm_version;
SELECT gm_batch_id, graph_fingerprint, ranking_fingerprint,
       ranking_algo_version, ranking_input_sha256, modified
FROM pogo_pvp.pvp_derived_meta WHERE id = 1;
SELECT catalog, revision, published_at FROM geo.catalog_publication;
-- Read the GM/PvP/geo tables you cache in this transaction too.
COMMIT;

Publish the new cache with that snapshot's metadata. On failure keep the old cache. Don't stamp an older cache with a newer metadata read.

GM can lead PvP between commits. Matching batches: batch_id = gm_batch_id, else retry. Same batch_id can be republished; compare modified too (GM/PvP pages).

More

Each HTML page has a .md sibling. Agents: /llms.txt (full: /llms-full.txt).