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 schemas:

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_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 – power generation per gram (generators)
• 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

structs.struct_attribute

Key-value attributes per struct instance.

structs.struct_defender

Defense assignments.

structs.planet

structs.planet_attribute

structs.planet_raid

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.

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

planet_activity (Event Log)

TimescaleDB hypertable for all planet-level events.

Event Categories

Polling Pattern (Real-Time Monitoring)

-- Initialize: set high-water mark
SELECT COALESCE(MAX(seq), 0) as last_seq
FROM structs.planet_activity
WHERE planet_id IN ('2-105');

-- Poll every ~6 seconds
SELECT seq, planet_id, category, detail::text
FROM structs.planet_activity
WHERE planet_id IN ('2-105', '2-127')
    AND seq > $LAST_SEQ
ORDER BY seq ASC;

The detail column for struct_attack includes attackerStructId, targetStructId, and eventAttackShotDetail with per-shot damage breakdowns.

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:

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. 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

Use views, not raw tables, when building leaderboard 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)

Ledger Action Types

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

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 (v0.16.0)

The PLAYER_PENDING_JOIN_PROXY trigger was modified to thread 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 becomes:

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)