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

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 manages a pool of signing accounts. Services insert rows into signer.tx with status='pending'. TSA claims them, signs, and broadcasts.

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)