Anthem PostgreSQL schema

Backend data model for the Node BIGS server (code/api/api_reimplementaton.js).

• Target runtime: PostgreSQL 18.1+ (enforced at top of the schema via a server_version_num check).
• Schema name: anthem.
• Source: reverse-engineered from pcap captures of EA's live Anthem servers - all row shapes match the JSON wire format the client expects, so populated tables can be reassembled into byte-compatible responses.

Loading the schema

From pgAdmin Query Tool -> open the file -> F5. Or from psql:

"/c/Program Files/PostgreSQL/18/bin/psql.exe" -U postgres -h localhost -d anthem \
  -f "C:/Users/calvi/Documents/New folder/anthemproject/code/postgres/anthem_backend_schema.sql"

The file is idempotent - every CREATE uses IF NOT EXISTS and the migration block at the bottom uses DROP ... IF EXISTS / ALTER TABLE ... DROP COLUMN IF EXISTS. Safe to re-run.

Expected messages on a re-run:

NOTICE:  relation "xxx" already exists, skipping     (xmany - all good)
ALTER TABLE                                          (migration block, once per change)
DROP TABLE                                           (if retiring tables)
UPDATE N                                             (migration data cleanups)

Only a red ERROR line is a real problem.

Populating with data

The parser lives in code/tools/apkl_api_to_pg.py. Requires psycopg (v3) or psycopg2:

pip install psycopg     # or: pip install psycopg2-binary

Run against one or more pcap captures:

py code\tools\apkl_api_to_pg.py `
   --db-url "postgresql://user:pass@localhost:5432/anthem" `
   --apkl-input "packets\SigmaHeroh Logs\SigmaHeroh_ChangePilot_JavelinCustomization_1768146419.apkl"

--apkl-input takes a file or a folder (recursed for *.apkl / *.pcap). Multiple --apkl-input flags allowed. The parser walks each pcap twice:

1. Pass 1 - seeds personas + character_profiles so FK-dependent upserts have a parent row to attach to.
2. Pass 2 - runs every upsert_* function and tracks active-character transitions (PUT /characters/active exchanges in the capture) so persistence writes land on the correct character at each pcap moment.

Watch the console output - you'll see lines like:

[active] persona=380325440 -> bb052578-... (PUT character characters active)
[persistence] snapshot persona=380325440 character=bb052578-... entries=216 props=1847

Entity map

Table(s)	Serves	Notes
personas, character_profiles	/api/character/*, /api/playercard*	Partial unique index enforces at most one active character per persona.
character_persistence_state, character_persistence_properties	/api/character/{pid}/persistence	Decomposed per-character. Row per (persona, character, definition_id, property_id, value). Not a blob.
inventory_items, inventory_currency_balances	/api/inventory*
loadouts, loadout_attachments, skins, skin_*	/api/loadout/*	Skin params split by type (int / float / vec2 / vec3 / vec4 / texture) to preserve Frostbite precision.
unlockables_catalog, persona_unlocks	/api/unlock/unlocks*
activity_milestones, activity_progress, activity_milestone_criteria, activity_milestone_levels, activity_achievements	/api/activity/*	Challenges + progression.
expedition_sessions, expedition_players, expedition_player_*	/api/expedition/personas*	Captured mission snapshots.
leaderboards, leaderboard_rows, leaderboard_custom_attributes, leaderboard_rewards	/api/leaderboards*
mtx_balances, mtx_offers, mtx_offer_cost_points, mtx_offer_cost_amounts, mtx_offer_result_currencies, mtx_persona_purchases	/api/mtx/*	Store catalog + per-persona purchase history (the purchased flag in /offers responses is computed from a LEFT JOIN against mtx_persona_purchases).
quest_definitions, quest_reward_snapshots	/api/character/quests/available, /api/gameconfig/questreward/<id>	Launch-bay expedition catalog + per-quest reward payloads.
playercard_state	/api/playercard*	Current playercard per persona (materialized to avoid re-joining loadouts on every read).
gameconfig_snapshots	/api/gameconfig/*	One row per distinct captured response body. Payload stored as text to preserve exact wire bytes.
localization_snapshots	/api/localization/*	Same idea, one row per language.
event_ingest_log	/api/events, /pinEvents, /bugsentry/*, /em/*	Client-reported telemetry events (tagged by _source in the jsonb payload).
persona_favorites, persona_ignores	/api/character/favorites, /api/character/ignore

Design decisions worth knowing

Persistence is decomposed, not a blob

character_persistence_state is metadata only (transaction_id, server_epoch_time). The actual progression flags live as individual rows in character_persistence_properties, keyed on (persona_id, character_id, definition_id, property_id). On GET /persistence the Node server queries the persona's currently-active character's rows and reassembles EA's persistenceJsonData JSON array from them. This gives full per-character isolation - switching pilots switches which set of rows getPers() reads.

No captured "end" timestamps stored

Three fields were removed because captured endTimes are always in the past by the time the server replays them:

• leaderboards.active_end_time_epoch, locked_end_time_epoch
• mtx_offers.end_time_epoch
• activeMutators[].endTime (stripped from the gameconfig_snapshots jsonb payload at parse time)

The API generates fresh deadlines at response time via futureEpoch() in Node. Client never sees a stale rotation window.

Per-character FK integrity

Every character-scoped table has a composite FK onto character_profiles(persona_id, character_id) with ON DELETE CASCADE. Deleting a character drops its inventory, loadouts, skins, persistence rows, activity progress, and everything else in one transactional sweep - no orphans.

payload_json is text, not jsonb

gameconfig_snapshots.payload_json and localization_snapshots.live_strings_json are text columns. The schema deliberately preserves the exact bytes EA's server returned, so byte-for-byte diffs against captured responses work. UNIQUE (endpoint_name, payload_hash) - where payload_hash bytea GENERATED ALWAYS AS (digest(payload_json, 'sha256')) STORED - dedupes identical captures while allowing different versions of the same endpoint over time. When you need jsonb operators, cast inline: payload_json::jsonb ? 'activeMutators'.

Convenience views

character_persistence_reassembled

Per character, returns the persistenceJsonData array the Node server would emit as a jsonb column. Read-only debugging aid.

SELECT persona_id, name, is_active,
       jsonb_array_length(persistence_json) AS entries,
       md5(persistence_json::text) AS content_hash
FROM anthem.character_persistence_reassembled
WHERE persona_id = 1018670076;

Different content_hash per character = per-character isolation is working. Same hash for all three = the parser wrote identical data to every character (rare; a capture issue).

v_latest_gameconfig

The most-recent snapshot per endpoint_name in gameconfig_snapshots, picked via DISTINCT ON (endpoint_name) ... ORDER BY endpoint_name, captured_at DESC.

SELECT endpoint_name, captured_at, total_count
FROM anthem.v_latest_gameconfig
ORDER BY endpoint_name;

Useful for "what gameconfig endpoints does my DB know about right now?" - anything missing will 404 when the client polls it.

Common diagnostic queries

Did re-parsing actually populate a persona's persistence?

SELECT cp.character_id, cp.name, cp.is_active,
       COUNT(pp.*) AS persistence_rows
FROM anthem.character_profiles cp
LEFT JOIN anthem.character_persistence_properties pp
  ON pp.persona_id = cp.persona_id AND pp.character_id = cp.character_id
WHERE cp.persona_id = <YOUR_PERSONA>
GROUP BY cp.character_id, cp.name, cp.is_active
ORDER BY cp.is_active DESC, cp.name;

Which characters live under each persona?

SELECT p.persona_id, p.player_name, COUNT(cp.*) AS characters
FROM anthem.personas p
LEFT JOIN anthem.character_profiles cp ON cp.persona_id = p.persona_id
GROUP BY p.persona_id, p.player_name
ORDER BY p.persona_id;

Is is_active correct after a PUT /active?

SELECT character_id, name, is_active, updated_at
FROM anthem.character_profiles
WHERE persona_id = <YOUR_PERSONA>
ORDER BY is_active DESC;

What's the current mutator rotation?

SELECT jsonb_array_elements(payload_json::jsonb -> 'activeMutators') AS mutator
FROM anthem.v_latest_gameconfig
WHERE endpoint_name = 'mutators';

Reset + rebuild

Nuke all data but keep the schema:

TRUNCATE TABLE
  anthem.personas,
  anthem.gameconfig_snapshots,
  anthem.localization_snapshots,
  anthem.event_ingest_log,
  anthem.unlockables_catalog
RESTART IDENTITY CASCADE;

Because every other table cascades off personas (or off character_profiles, which itself cascades off personas), CASCADE empties everything transitively.

Nuke the whole schema:

DROP SCHEMA anthem CASCADE;

Then reload the schema file.

File layout

postgres/
+-- README.md                              <- this file
+-- anthem_backend_schema.sql              <- the schema (+ a migration block at the bottom)
+-- seed_quest_definitions.sql             <- launch-bay "available expeditions" list (28 quest ids + content names)
+-- seed_quest_reward_snapshots.sql        <- per-quest reward payloads (13 captured from real EA responses)
+-- seed_data/
    +-- questreward_catalog.json           <- raw source for seed_quest_reward_snapshots.sql

The Node BIGS server consumes this schema but lives in a separate repo. The parser that originally populated it from pcap captures (apkl_api_to_pg.py) also lives outside this repo.