Live data¶

Live events live in pogo_live. You read views, not base tables. Each view calls cuno.check_sub_access; staff set that from your subscription.

Which view to use¶

Prefer sub_v2_* for new work. Those columns match the live table.

The older views sub_spawns, sub_raids, and sub_quests keep a joined historical shape: raids and quests already include the gym or pokestop name and point. pogo_live.sub_* is the canonical name; some logins also have unqualified public.sub_spawns and public.sub_raids.

View	Dataset	Row
pogo_live.sub_v2_spawns	spawns	Spawn
pogo_live.sub_v2_raids	raids	Raid (gym_id)
pogo_live.sub_v2_quests	quests	Quest (pokestop_id)
pogo_live.sub_v2_quest_rewards	quests	Reward slot
pogo_live.sub_v2_gyms	gyms	Gym
pogo_live.sub_v2_pokestops	pokestops	Pokestop
pogo_live.sub_v2_invasions	invasions	Invasion (pokestop_id)
pogo_live.sub_v2_invasions_lineup	invasions	Grunt slot
pogo_live.sub_v2_power_spots	power_spots	Window (station_id, start_time)
pogo_live.sub_v2_stations	power_spots	Power Spot POI
pogo_live.sub_v2_max_battles	max_battles	Fight (bread_battle_seed, station_id)
pogo_live.sub_v2_max_battle_events	max_battles	Current hour per station
pogo_live.sub_v2_weather	weather	Weather cell

Geography¶

lat/lon numeric(11,7), location geography(Point, 4326). ST_DWithin is meters, SRID 4326. Longitude first in ST_MakePoint:

ST_DWithin(s.location, ST_MakePoint(lon, lat)::geography, 1000)

Time and timezones¶

All timestamps are timestamptz. Filter on the end column for currently live rows:

View	End column
Spawns	expires
Raids	raid_end
Quests	expires
Invasions	expiration
Power spots	end_time
Max battles	battle_end

The point tables also have timezone_id and timezone_revision. timezone_revision = 0 means no match (timezone_id NULL); a positive revision is a completed match. geo.timezone.tz_name is IANA:

SELECT s.spawn_id, s.expires, tz.tz_name,
       s.expires AT TIME ZONE tz.tz_name AS expires_local
FROM pogo_live.sub_v2_spawns AS s
JOIN geo.timezone AS tz USING (timezone_id)
WHERE s.expires > now();

geo.timezone_slice isn't granted. Catalog: geo.timezone_release / geo.catalog_publication (catalog = 'timezone').

Receipt: first_added, first_seen, last_received, modified (game-state change; can stay null), update_count.

Spawns (sub_v2_spawns)¶

PK spawn_id (snowflake). encounter_id is unique in practice. Partitioned by spawn_id. Indexes: GiST location, expires, (pokemon_id, form_id, IVs), (stat_pool_hash, iv_key) where hash isn't null. Geography + time: both predicates in one WHERE.

WOF columns: Places (WOF). Gender is 1/2/3 (male/female/genderless). Only full encounters land: CP ≥ 10, IVs 0–15 (zero is valid); map sightings without encounter data are dropped.

Legacy sub_spawns omits hashes, form_group_id, iv_key, and some receipt columns.

Raids (sub_v2_raids)¶

PK raid_id. Times are raid_spawn, raid_start, raid_battle, and raid_end (indexed on raid_end and on (raid_pokemon_id, raid_pokemon_form_id)). Boss columns use the raid_pokemon_ prefix.

is_hatched means a mapper reported the boss. Crossing raid_start does not flip it; an egg can remain the last observed state until another payload arrives.

An egg is NULL in every boss column (form, costume, CP, moves, gender, temporary evolution, form group). A reported boss has species, form, and positive CP; optional attachments may still be NULL. Event times and tier don't depend on boss presence.

Legacy sub_raids already includes gym name, point, and team.

Zero and NULL¶

Attribute	Canonical meaning
Form 0	Default form on a present Pokémon; no boss means NULL form.
Costume, disguise, temporary evolution, background, lure	NULL means no attachment; positive IDs identify one.
Gender / size	NULL means unspecified or inapplicable. Genderless is 3; size 0 is not Medium.
Team / weather / alignment	Preserve observed Neutral/NONE/normal zero; missing information stays NULL.
IVs, counts, booleans	Zero and false are real values.

Events and their forts can arrive in either order; outer-join the parent if a missing gym or stop shouldn't drop the event.

Quests (sub_v2_quests)¶

PK quest_id (expires is indexed). quest_rewards is JSONB; for item or encounter filters join sub_v2_quest_rewards (type, amount, item_id, pokemon_id, form_id, encounter_shiny). Names are on pogo_gm.items and pogo_gm.quest_reward_types.

Reward columns follow their variant: an item, currency, or candy reward doesn't carry encounter-only form, costume, gender, or shiny attributes. A species on a candy or energy reward identifies the resource's Pokémon; it isn't a caught encounter.

Places¶

Point, WOF, and timezone columns are on sub_v2_spawns, sub_v2_gyms, sub_v2_pokestops, and sub_v2_stations. Events FK to those. Spawnpoints have no sub_* view; spawnpoint_id is on the spawn.

Invasions¶

PK id (text). character / grunt_type are the grunt; lineup is JSONB; expiration is the end time.

sub_v2_invasions_lineup is one row per slot (invasion_id, slot, pokemon_id, form_id, form_group_id), same invasions dataset. Filter by species like sub_v2_quest_rewards.

Power spots and max battles¶

A power spot is a non-overlapping window on a station, PK (station_id, start_time).

A max battle is one fight, PK bread_battle_seed. sub_v2_max_battle_events is the current hour at a station (is_battle_available, boss columns, battle_start / battle_end). An available event can keep its schedule before the boss is identified. An explicit unavailable event clears both boss and window; omitted availability doesn't overwrite the current event.

Weather¶

PK s2_cell_id (signed bigint, S2 level 10). gameplay_condition is the weather ID (pogo_gm.weather). Boosted spawns use weather_boosted / weather_id on the spawn, not the cell.

ID types¶

Kind	Type
Snowflake (spawn_id, raid_id, quest_id)	bigint
Game enum (pokemon_id, form_id, move_id)	smallint
Fort ID (gym_id, pokestop_id, station_id)	text collation C
Encounter (encounter_id)	Opaque bigint

Encounter IDs¶

Opaque. Golbat decimal uint64 string; stored as signed bigint. ::bigint overflows — use numeric or:

u64_to_int8('17408421884750151680')   -- wire → stored
int8_to_u64(encounter_id)             -- stored → wire

-- wire → stored
CASE WHEN n >= 9223372036854775808
     THEN (n - 18446744073709551616)::bigint ELSE n::bigint END

-- stored → wire
CASE WHEN encounter_id < 0
     THEN encounter_id::numeric + 18446744073709551616
     ELSE encounter_id::numeric END

Snowflakes¶

spawn_id, raid_id, and quest_id are Discord-epoch snowflakes: milliseconds since 2015-01-01 UTC in the high bits (ms << 22). The low 22 bits are node and sequence; time conversion shifts them off.

Helpers in public (EXECUTE is PUBLIC):

Function	Returns
snowflake_to_timestamp(id bigint)	timestamptz of the id's millisecond
timestamp_to_snowflake(ts timestamptz)	Lower bound id for that instant (low 22 bits zero)
snowflake_to_interval(delta bigint)	Interval for an id delta
interval_to_snowflake(dt interval)	Id delta for a duration
snowflake_now()	Lower bound for "now" (clock_timestamp())

timestamp_to_snowflake / snowflake_now are range bounds. A lower bound on spawn_id prunes partitions; snowflake_to_timestamp(spawn_id) does not.

SELECT spawn_id, snowflake_to_timestamp(spawn_id) AS minted
FROM pogo_live.sub_v2_spawns
WHERE spawn_id >= timestamp_to_snowflake(now() - interval '15 minutes')
  AND expires > now();