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

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 (UGC mirror)

structs.player_meta (UGC mirror, v0.16.0)

The webapp no longer writes player_meta directly. The PLAYER_PENDING_MERGE trigger was modified in v0.16.0 to skip the meta insert – the chain UGC update is the only path that populates username and pfp. The trigger still writes the lastAction grid seed so newly merged players have a complete grid.

structs.substation (with UGC fields)

structs.planet_meta

Planet name is written by cache.handle_event_planet. As of v0.16.0 the chain only updates the planet name when the chain event carries a non-empty value, so an auto-generated default name is never overwritten by an empty UGC update.

structs.permission_guild_rank

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

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

Seven new enum values were added in v0.16.0 for the UGC transactions:

signer.tx_* wrappers (v0.16.0)

The signing layer adds 12 new wrapper functions:

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

signer.UPDATE_PENDING_ACCOUNT was also bumped from the old PermAll = 16777215 to the v0.16.0 PermAll = 33554431 so newly provisioned signer addresses receive the new bit 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, populates player_meta via cache trigger

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
• knowledge/entities/entity-relationships.md – Full entity graph and ID format
• knowledge/entities/struct-types.md – Struct type stats (mirrors struct_type table)