Database Schema Reference

Purpose: AI-readable reference for the Structs Guild Stack PostgreSQL database. Covers core game state tables, the key-value grid pattern, event categories, and ready-to-use query patterns.

Requires: Guild Stack running locally. See .cursor/skills/structs-guild-stack/SKILL.md for setup.

Schema Overview

PostgreSQL 17 with TimescaleDB extension. Six application schemas (plus cron for pg_cron jobs and the _timescaledb_* internals):

Full table enumeration: schemas/database-schema.md.

Chain events are ingested by the structs-sync-state service (not structsd). It polls the chain RPC and writes directly into structs.* and sync_state.*.

Database Roles

For agent queries, use structs_indexer via the GRASS container (see guild-stack skill).

Core Game State Tables

structs.player

structs.fleet

structs.struct

structs.struct_type

The authoritative reference for all game balance data (~60 columns). Key columns:

Weapons (primary + secondary prefixed):

• *_weapon_control – guided or unguided
• *_weapon_charge – charge cost per attack
• *_weapon_ambits – bitmask of targetable ambits
• *_weapon_ambits_array – JSONB readable form
• *_weapon_shots, *_weapon_damage – shots per attack, damage per shot
• *_weapon_blockable, *_weapon_counterable – boolean
• *_weapon_armour_piercing – boolean; ignores the target’s armour damage reduction (added v0.18.0)
• *_weapon_recoil_damage – self-damage after firing
• *_weapon_shot_success_rate_numerator/denominator – per-shot hit rate

Defense:

• unit_defenses – signalJamming, armour, defensiveManeuver, stealthMode, indirectCombatModule, noUnitDefenses
• guided_defensive_success_rate_numerator/denominator – evasion vs guided
• unguided_defensive_success_rate_numerator/denominator – evasion vs unguided
• counter_attack – counter-attack damage (cross-ambit)
• counter_attack_same_ambit – counter-attack damage (same ambit)

Other:

• planetary_shield_contribution – shield value for planet structs
• generating_rate_p – power generation per gram (generators); this is the writable column
• ore_mining_difficulty, ore_refining_difficulty – PoW difficulty
• build_difficulty – PoW difficulty for construction
• Charge costs: activate_charge, build_charge, defend_change_charge, move_charge, stealth_activate_charge
• Display: class, class_abbreviation, unit_description

_p precision columns are the base; their bare companions are GENERATED. Three pairs on this table are split: generating_rate_p / generating_rate (= generating_rate_p * 1000), build_draw_p / build_draw, and passive_draw_p / passive_draw (both formatted via unit_legacy_format(…, 'milliwatt')). The bare names are still readable — a SELECT generating_rate keeps working — but only the _p column is writable, so it is the one sync-state upserts and the one to use in a WHERE on an exact integer. The same pattern applies to structs.ledger.amount_p, structs.infusion.fuel_p/power_p/ratio_p/defusing_p, and structs.guild.join_infusion_minimum_p. The *_array bitmask-derivative columns (primary_weapon_ambits_array, possible_ambit_array, …) are likewise GENERATED.

structs.struct_attribute

Key-value attributes per struct instance.

A row is deleted rather than zeroed when the attribute clears, so absence means zero.

structs.struct_defender

Defense assignments. One row per defender (defending_struct_id is the whole primary key), so a struct defends at most one target at a time.

The planet_activity struct_defense_add / struct_defense_remove detail uses the key defender_struct_id (no -ing) for what this table calls defending_struct_id. Joining the timeline to the table requires translating the name.

structs.planet

There is no seized_ore column on structs.planet. Seized ore is tracked per raid on structs.planet_raid and per token movement in structs.ledger.

structs.planet_attribute

As with struct_attribute, a cleared attribute is deleted rather than stored as 0.

structs.planet_raid

planet_raid is latest-state, not history. Because the primary key is planet_id alone, each planet holds exactly one row that is overwritten by every subsequent raid. Do not use this table for raid history or for summing a planet’s cumulative losses — it only answers “what is the most recent raid on this planet.”

Per-raid seized ore: read planet_activity. For raid history, use planet_activity rows with category = 'raid_status'; their detail carries seized_ore alongside planet_id, fleet_id, and status. This key was added to emitRaidStatusActivity recently, so rows indexed before the fix are missing it entirely — on structstestnet-111 roughly half of raid_status rows still lack the key. Treat detail ? 'seized_ore' as false meaning unknown, not zero, and gate on it explicitly:

SELECT time, planet_id, detail->>'fleet_id' AS fleet_id, detail->>'status' AS status,
    (detail->>'seized_ore')::numeric AS seized_ore
FROM structs.planet_activity
WHERE category = 'raid_status'
    AND detail ? 'seized_ore'
ORDER BY time DESC;

Upstream ships a one-time backfill, sync-state/sql/repair-raid-status-seized-ore.sql, that reconstructs the missing values from the ledger. It is an operator task on the guild-stack host, not something an agent should run.

The ledger remains the source of truth for token movement. Seized ore lands as structs.ledger rows with action = 'seized', direction = 'credit', denom = 'ore', credited to the thief with the victim in counterparty. The ledger preserves 0-gram seized rows — a raid that reached the planet but stole nothing (a probe repelled, or ore already refined away) still writes a meaningful seized entry. On structstestnet-111 they are common (~40% of seized credits), not the majority — treat them as real outcomes, not noise. Correlate a ledger row to a raid via block_height plus the thief’s player_address → player.fleet_id.

The Grid Table (Key-Value Pattern)

structs.grid is a key-value store for resource attributes. This is the most common source of query errors.

GRASS publishing: grid changes fire structs.GRID_NOTIFY(), which pg_notifys each change on subject structs.grid.{object_type}.{object_id}.{player_id} with a top-level player_id field (owner resolved via player_object, falling back to planet.owner; noPlayer when unresolved; for object_type='player' the owner is the object itself). The owner segment/field was added 2026-07-07.

Wrong (no ore column exists):

SELECT ore FROM structs.grid WHERE object_id = '1-142';

Correct (filter by attribute_type):

SELECT val FROM structs.grid WHERE object_id = '1-142' AND attribute_type = 'ore';

Multiple attributes (use JOINs):

SELECT p.id,
    COALESCE(g_ore.val, 0) as ore,
    COALESCE(g_cap.val, 0) as capacity,
    COALESCE(g_load.val, 0) as structs_load
FROM structs.player p
LEFT JOIN structs.grid g_ore ON g_ore.object_id = p.id AND g_ore.attribute_type = 'ore'
LEFT JOIN structs.grid g_cap ON g_cap.object_id = p.id AND g_cap.attribute_type = 'capacity'
LEFT JOIN structs.grid g_load ON g_load.object_id = p.id AND g_load.attribute_type = 'structsLoad'
WHERE p.id = '1-142';

Grid Attribute Types

Per-infusion power is not in the grid — it lives on structs.infusion as power / power_p, keyed (destination_id, address). Grid power on an allocation is the energy that allocation routes; on a struct it is what that generator produces.

planet_activity (Event Log)

TimescaleDB hypertable for all planet-level events.

seq is scoped to a planet, not to the table. It is drawn from structs.planet_activity_sequence, a (planet_id, counter) table whose counter is bumped per planet, so every planet has its own series starting at 0 and thousands of rows share any given seq. A single scalar high-water mark applied across several planets silently skips events on every planet whose counter is behind the largest one. Track one cursor per planet, or order globally on time / block_height and use seq only to disambiguate within a planet.

Event Categories

category is the shared structs.grass_category enum, which also labels GRASS notifications that never become planet_activity rows (block, guild_consensus, guild_meta, guild_membership, player_consensus, player_meta). The planet-scoped subset actually written to this table:

shield_change and block_raid_start (added v0.18.0) have no dedicated chain event — sync-state derives them from planet_attribute writes for planetaryShield and blockStartRaid, emitting a row only when the value actually changes and carrying both the old and new value. shield_change is high-volume but not second after struct_status on current structstestnet-111 — typical rank is struct_status > struct_health > struct_block_build_start > struct_defense_add > shield_change. Always filter by category; unfiltered pollers drown in status/health/build noise.

struct_defense_remove is emitted from the struct_defender_clear handler, which is the only path that clears a defender’s protectedStructIndex. Before that fix the category existed in the enum but was never written, so defense-removal history is absent from older indexed data even though additions are present.

Polling Pattern (Real-Time Monitoring)

Because seq is per-planet, initialize and carry a cursor per planet:

-- Initialize: one high-water mark per watched planet
SELECT planet_id, COALESCE(MAX(seq), 0) AS last_seq
FROM structs.planet_activity
WHERE planet_id IN ('2-105', '2-127')
GROUP BY planet_id;

-- Poll every ~6 seconds, comparing each planet against its own cursor
SELECT pa.planet_id, pa.seq, pa.category, pa.detail::text
FROM structs.planet_activity pa
JOIN (VALUES ('2-105', $LAST_SEQ_2_105), ('2-127', $LAST_SEQ_2_127))
    AS cur(planet_id, last_seq) ON cur.planet_id = pa.planet_id
WHERE pa.seq > cur.last_seq
ORDER BY pa.planet_id, pa.seq ASC;

A planet with no rows yet is absent from the initialize query, so default its cursor to -1 rather than 0 — seq starts at 0 and seq > 0 would drop that planet’s first event.

For a single-cursor poller across arbitrary planets, use block_height instead, which is globally ordered:

SELECT planet_id, seq, category, detail::text
FROM structs.planet_activity
WHERE block_height > $LAST_HEIGHT
ORDER BY block_height, planet_id, seq;

The detail column for struct_attack carries the attacker at the top level (attackerStructId, attackerPlayerId, attackerStructType, weaponSystem, weaponControl, attacker health before/after) plus an eventAttackShotDetail array with the per-shot breakdown. targetStructId exists only inside the shot entries, not at the top level — filtering attacks on a target requires descending into the array (e.g. detail->'eventAttackShotDetail' @> '[{"targetStructId":"5-6438"}]'). Each shot entry carries damage, damageDealt, evaded/evadedCause, blocked/blockedByStructId, armourPiercing, damageReduction/damageReductionCause, targetCountered/targetCounteredDamage, targetDestroyed, and evadedByPlanetaryDefenses/Cause.

GRASS publishing: each planet_activity insert fires the structs.PLANET_ACTIVITY_NOTIFY() trigger, which pg_notify('grass', …)s the row with a subject of structs.planet.{planet_id}.{player_id} (the owning player id was appended 2026-07-07; noPlayer when unresolved) plus a top-level player_id field. Payloads over ~7995 bytes are sent as a stub — {subject, planet_id, player_id, seq, category, time, stub:'true'} with no detail — so live consumers pull the full detail from this table by seq/planet_id. See structs-streaming SKILL.

Energy Commerce Tables

structs.guild

structs.guild_meta (off-chain guild config)

Guild presentation and infrastructure metadata — not chain UGC name/pfp (those live on structs.guild).

structs.substation (with UGC fields)

structs.permission_guild_rank

Primary key: (object_id, guild_id, permission). See permissions.md for the guild rank permission system.

The view.permission_player (keyed by player_id) and view.permission_address (keyed by address) views project structs.permission rows joined with permission_guild_rank, exposing one boolean column per permission bit (perm_play, perm_admin, …, perm_hash_build/perm_hash_mine/perm_hash_refine/perm_hash_raid). The raw integer bitmask (val) lives on the base table structs.permission. The PermAll mask is 33554431 (bits 0..24 set).

sync_state schema (indexer)

Written by structs-sync-state. Key operator tables:

structs.current_block mirrors the same cursor (chain, height, status, lag_blocks, tip_height) as an unlogged single-row table, which is the cheaper read when all you need is “what block are we on.”

Monitor indexer health:

SELECT chain_id, last_height, status, lag_blocks, tip_height
FROM sync_state.sync_cursor;

The cache schema exposes four views (blocks, tx_results, events, attributes) over sync_state.raw_* for webapp backward compatibility.

structs.banned_word

Seed data for UGC name validation. The chain rejects any Msg*UpdateName (player, guild, planet, substation) whose name contains a substring matching any row in this table; webapps surface the same list via api/webapp/banned-word.md so client-side forms can preflight.

structs.address_tag

Labelled address records. Each (address, label) pair tags a Cosmos address with a human-readable label, plus an entry integer for ordering.

Primary key: (address, label); reverse-lookup index (label, entry) (table-address-tag-idx-label-entry).

structs.setting

Live tunables — chain economy and gameplay constants exposed unauthenticated through api/webapp/setting.md.

Seeded keys: REACTOR_RATIO, PLAYER_RESUME_CHARGE, PLANETARY_SHIELD_BASE, PLAYER_PASSIVE_DRAW, PLANET_STARTING_ORE, PLANET_STARTING_SLOTS, STRUCT_SWEEP_DELAY. Treat the table as an open name/value map — keys are added over time.

structs.defusion

In-flight reactor defusion records — Alpha Matter being unbonded from a reactor.

Old rows are reaped by the structs.CLEAN_DEFUSION() cron. Read endpoints live in api/webapp/defusion.md.

Aggregated Views

The view schema holds 21 views. The ones worth reaching for:

Use views, not raw tables, when building leaderboard, inventory, or treasury surfaces — the views absorb the seized_ore, ledger, and infusion joins so the upstream surface stays stable when underlying tables change.

Other Time-Series Tables (TimescaleDB)

Type-implied stat tables (silent-wrong-row trap). Five hypertables — stat_structs_load, stat_connection_capacity, stat_connection_count, stat_struct_health, stat_struct_status — have no object_type column (verified in structs-pg/deploy/table-stat.sql). The object type is implied by the table itself (e.g. stat_connection_* and stat_structs_load are player/substation series; stat_struct_* are struct series). A generic helper that joins every stat table on (object_type, object_index) will silently read the wrong rows against these five — filter on object_index alone and rely on the table name for the type. This mirrors the note in api/webapp/stat.md.

Ledger Action Types

genesis, received, sent, migrated, infused, defusion_started, defusion_cancelled, defusion_completed, mined, refined, seized, forfeited, minted, burned, diversion_started, diversion_completed

Each row is one side of a movement: address is the party the row belongs to, counterparty is the other side, and direction (credit/debit) gives the sign. amount_p is the raw integer; amount is GENERATED via unit_legacy_format(amount_p, denom) for display. Use amount_p for arithmetic and for matching.

Historical duplicate rows on refined and infused (affects any balance query). Two chain messages each produced two ledger rows for the same movement, because sync-state’s event handler and the Cosmos bank-event handler both wrote one:

Message	Correct row	Spurious duplicate
MsgStructOreRefineryComplete	refined credit, ualpha	received credit, ualpha
MsgStructGeneratorInfuse	infused debit, ualpha	sent debit, ualpha

The duplicate shares the same address, time, block_height, and amount_p as the correct row. Current sync-state suppresses the bank-side row, but the duplicates persist in already-indexed data — on structstestnet-111 there are 123 refine and 13 infusion pairs still present. Any query of the form SUM(CASE direction WHEN 'credit' THEN amount ELSE -amount END) over ualpha therefore double-counts every historical refine and infusion. Until an operator runs the upstream repairs (sync-state/sql/repair-refine-ledger-dupes.sql, repair-infusion-ledger-dupes.sql), exclude the shadowed actions when computing a net position:

SELECT address, denom,
    SUM(CASE direction WHEN 'credit' THEN amount_p ELSE -amount_p END) AS net
FROM structs.ledger l
WHERE NOT (
        l.denom = 'ualpha'
    AND l.action IN ('received', 'sent')
    AND EXISTS (
        SELECT 1 FROM structs.ledger d
         WHERE d.address = l.address
           AND d.time = l.time
           AND d.block_height = l.block_height
           AND d.amount_p = l.amount_p
           AND d.denom = 'ualpha'
           AND d.action = CASE l.action WHEN 'received' THEN 'refined'::structs.ledger_action
                                        ELSE 'infused'::structs.ledger_action END
    ))
GROUP BY address, denom;

seized and mined were never duplicated, so ore analytics are unaffected.

Signer Schema (Transaction Signing Agent)

The TSA (Transaction Signing Agent, playstructs/structs-tsa) manages a pool of signing accounts. Services insert rows into signer.tx with status='pending'. TSA claims them, signs, and broadcasts.

signer.signer_tx_type (UGC enum values)

Seven enum values cover the UGC chain message types:

signer.tx_* wrappers

The signing layer ships 12 PL/pgSQL wrapper functions that queue UGC updates into signer.tx. They split into two groups by permission preflight only — both groups ultimately broadcast the same chain messages (MsgPlayerUpdateName, MsgPlayerUpdatePfp, MsgPlanetUpdateName, MsgSubstationUpdateName, MsgSubstationUpdatePfp):

• 7 self-service wrappers (one per UGC tx type above) that require PermUpdate (4) on the target object before queueing the tx. These are used when a player updates their own UGC.
• 5 guild-moderation wrappers (tx_guild_moderate_player_name, _player_pfp, _planet_name, _substation_name, _substation_pfp) that require PermGuildUGCUpdate (16777216) on the target owner’s guild before queueing the tx. These are used when a guild moderator overrides a member’s UGC.

There is no MsgGuildModerate* chain message — moderation is the same Msg*Update* message, gated by the actor’s PermGuildUGCUpdate on the owner’s guild. The chain emits a ugc_moderated event whenever the actor differs from the target object’s owner.

signer.UPDATE_PENDING_ACCOUNT defaults to PermAll = 33554431 (bits 0..24) so newly provisioned signer addresses receive every permission, including PermGuildUGCUpdate, by default.

PLAYER_PENDING_JOIN_PROXY

The PLAYER_PENDING_JOIN_PROXY trigger threads username and pfp from the pending row through to signer.CREATE_TRANSACTION for the guild-membership-join-proxy command, packaged into the ugc JSONB argument. The webapp’s signup flow is:

webapp signup
  -> structs.player_pending row (with username, pfp)
  -> PLAYER_PENDING_JOIN_PROXY trigger
  -> signer.tx (command=guild-membership-join-proxy, args.ugc={username, pfp, ...})
  -> TSA claims, signs MsgGuildMembershipJoinProxy with playerName/playerPfp
  -> chain validates name/pfp, creates player, sync-state writes username/pfp to structs.player

ID Format Reference

All game object IDs follow {type_prefix}-{index}. See entity-relationships.md for the complete type code table.

See Also

• .cursor/skills/structs-guild-stack/SKILL.md – Setup and common queries
• knowledge/infrastructure/guild-stack.md – Architecture overview
• schemas/database-schema.md – Full structural schema catalog
• knowledge/entities/entity-relationships.md – Full entity graph and ID format
• knowledge/entities/struct-types.md – Struct type stats (mirrors struct_type table)