← Back to Index

Architecture · ops.tradeit.gg

Database Schema — pricing

---

The pricing database on the analytics replica contains the active pricing pipeline tables plus replication infrastructure. It is a smaller, focused schema used by the pricing engine that runs alongside (and feeds into) the main steamarbitrage platform.

Related: [[pricing-domain]] · [[analytics-replica]] · [[database-schema-steamarbitrage]]

Key identifiers:

• item_id — catalog key, same namespace as steamarbitrage.items.id
• All monetary amounts are in cents (int). 100 = $1.00.

---

1. Replication Infrastructure

_repl_state

Purpose: Watermark table for the Fargate incremental replicator — tracks last-synced cursor per table Size: ~45 rows / 0.0 MB Replication: not-replicated (written by the replicator itself, not sourced from production) PII: no Key columns:

Column	Type	Meaning
(schema not captured)		table_name, last_id or last_updated_at watermark, updated_at

Query gotchas: This is internal replication state — do not use for business analytics. Useful for diagnosing replication lag: compare last_id / last_updated_at vs live production values. See [[analytics-replica]] for full replication architecture.

---

2. Active Pricing Tables

item_prices_raw

Purpose: Raw price feeds from external sources (Steam, Buff163, CSMoney, Lootbear, etc.) per item — the input layer to the pricing algorithm Size: ~199k rows / 28.6 MB Replication: replace-into PII: no Key columns:

Column	Type	Meaning
item_id	int	PK (or part of composite) — FK to items.id
(source-specific price columns)	int	Cents per source: steam_median, buff_sell, buff_buy, csmoney_base, loot_price, etc.

Key joins:

• item_id → steamarbitrage.items.id
• item_id → item_price_adjust_percent.item_id

Query gotchas: Full-refresh on each replication run (TRUNCATE + INSERT). Snapshot of current prices only — no history. For historical prices see price_update_log in steamarbitrage.

item_price_adjust_percent

Purpose: Per-item manual price adjustment overrides (percentage modifiers applied on top of algorithm output) Size: small reference table Replication: replace-into PII: no Key columns:

Column	Type	Meaning
item_id	int	FK → items.id
adjust_percent	float/double	Multiplier override (e.g. 1.05 = +5%)

Query gotchas: Full-replace — reflects current overrides only. These are set by the trading operations team.

store_item_stats

Purpose: Aggregated store-mode statistics per item (views, clicks, conversion metrics for the store/P2P interface) Size: varies Replication: replace-into PII: no Key columns:

Column	Type	Meaning
item_id	int	FK → items.id
(stat columns)	int/float	Store interaction metrics

Query gotchas: Replace-into — current snapshot. No history.

trade_item_stats

Purpose: Aggregated trade-mode statistics per item (deposit/withdraw counts, velocity metrics) Size: varies Replication: replace-into PII: no Key columns:

Column	Type	Meaning
item_id	int	FK → items.id
(stat columns)	int/float	Trade interaction metrics (deposit_count, withdraw_count, velocity)

Query gotchas: Replace-into — current snapshot only.

item_eod

Purpose: End-of-day item pricing snapshots — one row per item per day capturing EOD price state Size: varies (daily cadence, grows ~199k rows/day) Replication: incremental-id (daily cadence) PII: no Key columns:

Column	Type	Meaning
item_id	int	FK → items.id
date	date	EOD snapshot date
(price columns)	int	Cents — deposit price, withdrawal price, stable price, external reference prices

Key joins:

• item_id → steamarbitrage.items.id
• item_id → item_prices_raw.item_id (current)

Query gotchas: Best source for time-series price analysis. Join with steamarbitrage.items or steamarbitrage.items_meta for item names/metadata. Cardinality: ~199k items × trading days.

---

3. Bot Trade Item Logs

bot_trade_item_logs

Purpose: Line-item detail for every item involved in every bot trade — the atomic trade event log Size: ~118M rows (estimated from context; not in pricing table inventory above — sourced from steamarbitrage production, replicated into pricing schema on the replica) Replication: incremental-updated-at PII: yes (columns: steam_id — user involved in the trade) Key columns:

Column	Type	Meaning
id	int/bigint	PK
bot_trade_id	int	FK → steamarbitrage.bot_trades.id
steam_id	varchar(20)	User Steam ID
item_id	int	FK → steamarbitrage.items.id
asset_id	varchar(20)	Steam asset ID (instance)
type	text	'deposit' or 'withdraw'
price	int	Cents — trade price for this item
(additional audit columns)		status, app_id, context_id

Key joins:

• bot_trade_id → steamarbitrage.bot_trades.id
• item_id → steamarbitrage.items.id
• asset_id → steamarbitrage.container_item.asset_id

Query gotchas: ~118M rows — the most detailed trade event log. Always filter by updated_at (or equivalent date column) range. Avoid COUNT(*) or aggregations without a date predicate — will hit 30s timeout. To analyze a specific item's trading history: filter item_id + date range. To analyze a user's trades: filter steam_id + date range. Never cross-join with guess_questions or balance_transactions without date-bounded CTEs.

---

4. Pricing History & Audit

price_history

Purpose: Historical price feed archive per item × timestamp (legacy pricing pipeline artifact) Size: 0 rows — currently empty / legacy table, no longer written Replication: not-replicated PII: no Key columns:

Column	Type	Meaning
item_id	int	PK part — FK → items.id
timestamp	bigint	PK part — Unix epoch
csmoney_lowest/base	int	CSMoney price variants
buff_sell / buff_buy	int	Buff163 order book
steam_median	int	Steam market median
tradeit_price	int	Our price at snapshot time
loot_stock / loot_price	int	Lootbear stock and price

Query gotchas: Empty — do not query for data. Table schema retained for reference. Use item_eod for EOD historical prices, or steamarbitrage.price_update_log for granular pricing algorithm audit trail.

price_update_log

Purpose: Granular audit trail of every pricing algorithm price-change event (legacy, from steamarbitrage) Size: ~20.9M rows / 2,459.0 MB (in steamarbitrage schema) Replication: incremental-id (sourced from steamarbitrage) PII: no Key columns:

Column	Type	Meaning
item_id	int	PK part — FK → items.id
timestamp	double	PK part — Unix epoch
modifier	double	Applied price modifier
type	varchar(80)	Trigger type (stock-change, manual, etc.)
deposit / withdrawal	int	Cents — new deposit/withdrawal price
current_stock / wanted_stock	int	Stock levels at trigger time
newmax / oldmax	int	Stock max before/after
newprice / oldprice	int	Cents — prices before/after
iratio	double	Internal ratio metric
stepamm_level	int	STEPAMM algorithm level
loot_stock / loot_max / loot_price	int	Lootbear context
buff_sell / buff_buy	int	Buff163 context
csm_price	int	CSMoney context
bitskins_lowest / steam_lowest	int	Market floor prices

Query gotchas: 20.9M rows and 2.5 GB. Always filter by timestamp range. Best used to understand WHY a specific item's price changed — join item_id filter with date predicate. The type column identifies the pricing rule that fired.

price_updates

Purpose: Control table for pricing update batch jobs (start/end timestamps per batch key) Size: 0 rows — currently empty Replication: not-replicated PII: no Key columns:

Column	Type	Meaning
myKey	varchar(45)	PK — batch job identifier
start_update / end_update	bigint	Unix epoch range of the batch

Query gotchas: Empty — not in active use.

---

Cross-Schema Notes

• item_id is consistent across pricing and steamarbitrage schemas — joins work directly.
• bot_trade_item_logs lives in the pricing schema on the replica but references steamarbitrage.bot_trades and steamarbitrage.items. Cross-schema joins work on the replica since both databases are on the same MySQL instance.
• The replica's _repl_state table is the authoritative source for "what was last synced when." If you need to know replication freshness for a specific table, query SELECT * FROM pricing._repl_state WHERE table_name = 'X'.
• For the full replication strategy matrix (which tables use which strategy), see [[analytics-replica]].
• Pricing algorithm context (modifiers, external source weights, STEPAMM): see [[pricing-domain]].

---

Main application database for tradeit.gg. Contains all transactional, user, inventory, trading, and platform data. Available on the analytics replica as a read-only copy.

Related: [[domain-model]] · [[trading-lifecycle]] · [[analytics-replica]] · [[database-schema-pricing]] · [[configurations-registry]]

Key identifiers across all tables:

• steam_id — Steam 64-bit user ID (PII, present in most user-facing tables)
• item_id — catalog key for a skin type (joins to items, items_meta, etc.)
• asset_id — Steam asset ID for a specific item instance (changes on each trade)
• All monetary amounts are in cents (int). 100 = $1.00.

---

1. User & Authentication

admins

Purpose: Internal admin account registry for the tradeit back-office panel Size: ~11 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: username, password hash) Key columns:

Column	Type	Meaning
role	text	Admin permission role string
password	varchar(255)	Bcrypt hash — never expose

admin_activities

Purpose: Audit log of every back-office action taken by an admin Size: ~98k rows / 13.5 MB Replication: incremental-updated-at PII: no (references admin_id, not steam_id) Key columns:

Column	Type	Meaning
admin_id	int	FK → admins.id
model	varchar(50)	Entity type acted upon (e.g. "User", "Item")
action	varchar(255)	Action performed (create, update, ban, etc.)
data	text	New state JSON
old_data	text	Previous state JSON

banned_users

Purpose: Registry of users banned from the platform Size: ~54k rows / 3.5 MB Replication: full-refresh PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(50)	PK — banned user's Steam ID
reason	varchar(255)	Ban reason text

whitelisted_users

Purpose: Users exempted from certain platform restrictions Size: ~12 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(50)	PK

user_settings

Purpose: Per-user communication preferences (email opt-ins) Size: ~3.8M rows / 184.9 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(50)	PK
allow_trigger_emails	int	Transactional email consent (0/1)
allow_marketing_emails	int	Marketing email consent (0/1)

email_verification

Purpose: Email address verification tokens and state per user Size: ~3.8M rows / 421.0 MB Replication: incremental-updated-at PII: yes (columns: steam_id, email, token, code) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	PK
email	varchar(100)	Verified email address
verified_at	datetime	NULL if not yet verified
last_sent_at	datetime	Throttle verification emails

email_blacklist

Purpose: Email domain/string blacklist to block disposable or fraudulent emails Size: ~0 rows / 0.0 MB — currently empty / feature not active Replication: not-replicated PII: no Key columns:

Column	Type	Meaning
email_part_str	varchar(256)	Substring match pattern

verification

Purpose: Legacy verification table (SMS or alternative code-based verification) Size: ~4 rows / 0.0 MB — nearly empty, superseded by email_verification Replication: incremental-updated-at (unconfirmed) PII: yes (columns: steam_id, email, code) Key columns:

Column	Type	Meaning
steam_id	varchar(50)	PK
tried	int	Failed attempt count
last_sent	datetime	Last code dispatch time

api_keys

Purpose: API key registry for external integrations / developer access Size: ~0 rows / 0.0 MB — currently empty Replication: not-replicated PII: yes (columns: steamid) Key columns:

Column	Type	Meaning
steamid	varchar(20)	Owner Steam ID
api_key	varchar(255)	The key value — treat as secret
note	varchar(255)	Purpose description

---

2. Items & Catalog

items

Purpose: Core item catalog — one row per tradeable item variant (skin + wear + quality) Size: ~173k rows / 173.0 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
id	int	PK — the item_id referenced everywhere
(other columns not captured in schema query)		See items_meta for enriched metadata

Key joins:

• id → items_meta_items.item_id
• id → item_prices.item_id (pricing schema)
• id → category_items.item_id

items_april

Purpose: Snapshot/clone of items table taken ~April 2023 — operational backup Size: ~170k rows / 167.9 MB Replication: DMS-only-bootstrap PII: no Query gotchas: Do not use for analysis — use items instead. Stale snapshot.

items_steam

Purpose: Mirror of Steam market item data (market_hash_name, Steam price/listing info) Size: ~58k rows / 10.5 MB Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
(schema not captured)		Likely: market_hash_name, app_id, steam price fields

items_meta

Purpose: Enriched skin metadata — descriptions, exterior ranges, community ratings per skin class Size: ~19k rows / 23.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
id	int	PK — items_meta_id referenced in join tables
app_id	int	Steam app (730=CS2, 440=TF2, 252490=Rust, 570=Dota2)
title	varchar(191)	Skin display name
url_slug	varchar(191)	SEO slug
skin_type	varchar(100)	Weapon type category
skin_name	varchar(100)	Skin design name
skin_group	varchar(100)	Grouping (e.g. collection)
supply	longtext	JSON supply data
votes / rating	int / float	Community rating aggregates

Key joins:

• id → items_meta_items.items_meta_id
• id → items_meta_classes.item_meta_id
• id → items_meta_collections.item_meta_id
• id → items_meta_image_urls.items_meta_id

items_meta_items

Purpose: Junction — maps items_meta (skin class) to individual item variants Size: ~35k rows / 1.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
items_meta_id	int	FK → items_meta.id
item_id	int	FK → items.id

items_meta_classes

Purpose: Junction — links skin classes (weapon types) to items_meta entries Size: ~31k rows / 1.5 MB Replication: incremental-updated-at PII: no

items_meta_collections

Purpose: Junction — links CS2 collections to items_meta entries Size: ~1.8k rows / 0.1 MB Replication: incremental-updated-at PII: no

items_meta_colors

Purpose: Junction — links skin rarity colors to items_meta entries Size: ~3.2k rows / 0.2 MB Replication: incremental-updated-at PII: no

items_meta_containers

Purpose: Junction — links cases/containers to items_meta entries Size: ~20k rows / 1.5 MB Replication: incremental-updated-at PII: no

items_meta_image_urls

Purpose: Full-resolution image URLs per item_id + items_meta combination Size: ~35k rows / 11.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
items_meta_id	int	FK → items_meta.id
item_id	int	FK → items.id
img_url	longtext	Full image URL

items_meta_summary

Purpose: Key-value summary attributes per items_meta entry (e.g. "Wear", "Float Range") Size: ~138k rows / 7.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
item_meta_id	int	FK → items_meta.id
name	varchar(191)	Attribute name
value	varchar(191)	Attribute value
external_url	varchar(191)	Optional reference URL

items_meta_pro_players

Purpose: Junction — links CS2 pro player sticker capsules to items_meta entries (large: sticker variants × players) Size: ~2.7M rows / 112.6 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
item_meta_id	int	FK → items_meta.id
pro_player_id	int	FK → csgo_professional_players.id

items_meta_professional_players

Purpose: Alternative/newer pro player junction (distinct from items_meta_pro_players) Size: ~10k rows / 0.4 MB Replication: incremental-updated-at PII: no

item_img_full

Purpose: Full-resolution variant-level image URLs for all item instances Size: ~2.6M rows / 670.0 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
(schema not captured)		item_id or asset_id + img_url

item_configurations

Purpose: Per-item configuration overrides (deposit on/off, stock limits, special pricing rules) Size: ~186k rows / 16.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
item_id	int	FK → items.id
(config key/value structure)		Override flags per item

item_deposit_lock

Purpose: Locks preventing deposit of specific items/assets (fraud prevention, overstock) Size: ~191k rows / 8.5 MB Replication: incremental-updated-at PII: no

item_stocks

Purpose: Current stock levels per item_id (how many units bots hold) Size: ~50k rows / 4.5 MB Replication: replace-into PII: no

item_trends

Purpose: Price trend snapshot per item — percent changes over 1d/7d/30d/90d horizons with stock/price Size: ~50k rows / 35.6 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
items_meta_id	int	FK → items_meta.id
item_id	int	FK → items.id
app_id	int	Game identifier
stable_price	int	Cents — our stable reference price
bot_trade_price	int	Cents — current trade price
percent_24h/7d/30d/90d	decimal(10,2)	Price change % per window
amount	int	Available stock count
enable_deposit	tinyint	Whether item accepts deposits

item_types

Purpose: Item type taxonomy (weapon category definitions per app) Size: ~26 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
app_id	int	Steam game ID
name	varchar(255)	Type name
value	int	Internal type code

junk_items

Purpose: Items designated as "junk" (below-threshold items excluded from normal trading) Size: ~70 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
name	varchar(255)	Item name
balancer_bot	int	Which balancer bot handles these

category

Purpose: Item category taxonomy tree (game → category → subcategory) Size: ~123 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
parent_id	int	Self-referential — 0 for root categories
game_id	bigint	Steam app ID
slug	varchar(50)	URL slug
order	tinyint	Display sort order
for_type	tinyint	Applicable item type filter
is_container_group	tinyint	Whether this groups containers (cases)

category_items

Purpose: Maps item_id to category with base pricing data Size: ~35k rows / 2.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
item_id	int	FK → items.id
base_date	date	Date the base_price was set
base_price	int	Cents — reference price at categorization time
cat_id	int	FK → category.id
cat_parent_id	int	FK → category.parent_id

insight_contents

Purpose: Editorial/analytical content for item detail pages (written insights) Size: ~565 rows / 1.5 MB Replication: incremental-updated-at (unconfirmed) PII: no

---

3. CS2 / Game Reference Data

csgo_classes

Purpose: CS2 weapon classes with rarity color and drop chance Size: ~26 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
type	varchar(20)	Class type identifier
name	varchar(20)	Display name
color_hex	varchar(7)	Rarity color
drop_chance_percent	varchar(5)	Case unbox probability

csgo_collections

Purpose: CS2 skin collections (e.g. "The Nuke Collection") Size: ~107 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: no

csgo_containers

Purpose: CS2 containers (cases, capsules) reference data Size: ~459 rows / 0.1 MB Replication: incremental-updated-at (unconfirmed) PII: no

csgo_professional_players

Purpose: CS2 pro player registry (for sticker capsule metadata) Size: ~427 rows / 0.1 MB Replication: incremental-updated-at (unconfirmed) PII: no

csgo_skin_colors

Purpose: Rarity color reference (14 colors) Size: ~14 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: no

csgo_types

Purpose: CS2 item type reference (weapons, knives, gloves, etc.) Size: ~61 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: no

pro_players

Purpose: Extended pro player profiles with Steam info, crosshair/mouse settings, ratings Size: ~945 rows / 1.5 MB Replication: incremental-updated-at PII: yes (columns: steam_id, steam_url, birthday) Key columns:

Column	Type	Meaning
steam_id	varchar(60)	Player's Steam ID
nickname	varchar(50)	In-game name
url_slug	varchar(50)	SEO slug
rating	float	Community rating
votes	int	Vote count
last_inv_updated_at	datetime	When player's inventory was last scraped
mouse_settings / crosshair_settings / view_model_settings	mediumtext	JSON settings blobs

pro_teams

Purpose: CS2 professional teams reference Size: ~251 rows / 0.1 MB Replication: incremental-updated-at (unconfirmed) PII: no

pro_teams_players

Purpose: Junction — maps pro players to their current teams Size: ~992 rows / 0.1 MB Replication: incremental-updated-at (unconfirmed) PII: no

skin_class

Purpose: Skin class definitions (similar to items_meta but class-level, not variant-level) Size: ~6.6k rows / 15.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
app_id	int	Game identifier
skin_type / skin_name / skin_group	varchar(100)	Classification hierarchy
votes / rating	int / float	Community aggregates

skin_class_colors / skin_class_items / skin_class_summary

Purpose: Junction and attribute tables for skin_class (colors, item mappings, summary KV) Size: 1.6k / 21k / 67k rows respectively Replication: incremental-updated-at PII: no

skin_collections

Purpose: User-curated skin collections (community feature) Size: ~9 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: steam_id — collection creator)

skin_collection_items / skin_collection_tags / skin_collection_tag_trending / skin_collection_votes

Purpose: Supporting tables for user skin collection feature (items, tags, trends, votes) Size: <10k rows each Replication: incremental-updated-at (unconfirmed) PII: yes (skin_collection_votes has steam_id)

skin_tags

Purpose: Tag vocabulary for skin classification Size: ~1.2k rows / 0.1 MB Replication: incremental-updated-at (unconfirmed) PII: no

tf2_steam_prices / tf2_backpack_prices

Purpose: TF2 price reference data from Steam and Backpack.tf Size: 58k rows / 8.5 MB (tf2_steam_prices); 0 rows (tf2_backpack_prices — empty) Replication: replace-into (unconfirmed) PII: no

items_csgoskins / items_csgoskins_prices / csgoskins_sessions

Purpose: CS:GO Skins third-party integration data (price feeds, session tracking) Size: 31k / 310k / 356 rows respectively Replication: incremental-updated-at (unconfirmed) PII: no

---

4. Bot & Inventory

bot_accounts

Purpose: Registry of all tradeit trading bots with Steam identity and status Size: ~540 rows / 0.1 MB Replication: incremental-id + DMS-bootstrap PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Bot's Steam ID
bot_id	int	Internal bot index (referenced in trades)
public_bot_id	varchar(20)	Public-facing bot identifier
active	tinyint	Whether bot is currently trading
trade_url	varchar(120)	Bot's Steam trade URL
steam_level	int	Bot's Steam account level
join_date	date	When bot joined the platform

bot_balance

Purpose: Bot balance on external platforms (IGXE, OP.GG) Size: ~4 rows / 0.0 MB Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
bot	int	PK — bot_id
igxe_balance	int	Cents on IGXE
op_balance	int	Cents on OP.GG

bot_inventory_tracker

Purpose: Historical snapshots of bot inventory value and item count over time Size: ~206M rows / 10,460.0 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
bot_index	varchar(50)	Bot identifier string
value	int	Total inventory value in cents
item_count	int	Number of items held

Query gotchas: 206M rows and 10 GB — unbounded queries will hit 30s replica timeout. Always filter by created_at range and/or specific bot_index.

bot_pricechanges

Purpose: Periodic snapshot of inventory value changes per game across all bots Size: ~152k rows / 13.5 MB Replication: replace-into PII: no Key columns:

Column	Type	Meaning
timestamp	double	PK — Unix epoch
csgo / dota / tf2 / rust_*	int	Inventory value deltas per game
bitTotal / balanceTotal	int	Platform-wide bit and balance totals
oindex_730/570/252490/440	int	Overall inventory index per app_id

bot_trades

Purpose: Every Steam trade offer sent/received by bots — core trading transaction log Size: ~25M rows / 8,105.0 MB Replication: incremental-id + DMS-bootstrap PII: yes (columns: implicit — joined via bot_id/steam_id) Key columns:

Column	Type	Meaning
id	int/bigint	PK
(full schema not captured in context)		type: 'deposit'/'withdraw', steam_id, bot_id, status, offer_id

Key joins:

• Joins to bot_trade_item_logs (in pricing schema) for line-item detail
• Joins to reserved_transactions for reservation tracking

Query gotchas: 25M rows and 8 GB — always filter by date range. Avoid full-table aggregations.

bot_trades_sent

Purpose: Record of trade offers sent by bots to users (outbound withdrawal offers) Size: ~27M rows / 1,888.0 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: steamid) Key columns:

Column	Type	Meaning
steamid	varchar(20)	Recipient user Steam ID
offerid	double	PK — Steam trade offer ID
cprice	int	Customer price in cents
sprice	int	Store/bot price in cents
timestamp	double	Unix epoch
botid	int	Which bot sent the offer
token	varchar(128)	Trade token used

Query gotchas: 27M rows — filter by timestamp range. offerid as double can cause precision issues in WHERE clauses; cast carefully.

container_item

Purpose: Bot's actual item holdings registry — one row per item instance currently held by a bot Size: ~583k rows / 1,038.0 MB Replication: incremental-with-cdc-deletes (unconfirmed) PII: no Key columns:

Column	Type	Meaning
(schema not captured)		asset_id, bot_id, item_id, price, status

Key joins:

• asset_id → reserved_items.asset_id (currently reserved)
• item_id → items.id

container_item_transactions

Purpose: Audit log of all container_item mutations (deposits in, withdrawals out, transfers) Size: ~73M rows / 5,798.0 MB Replication: incremental-id PII: no Key columns:

Column	Type	Meaning
operation	text	'INSERT'/'DELETE'/'UPDATE'
context	text	Business context (deposit/withdraw/transfer)
container_id	bigint	FK → container_item
app_id	int	Steam game ID
context_id	int	Related trade/offer ID
asset_id	bigint	Steam asset ID
bot_id	int	Bot holding the item

Query gotchas: 73M rows and 5.8 GB — always filter by created_at. Do not run without date predicate.

reserved_items

Purpose: Items currently locked mid-trade (user clicked buy, awaiting Steam trade confirmation) Size: ~335k rows / 691.0 MB Replication: incremental-with-cdc-deletes PII: yes (columns: owner — user steam_id) Key columns:

Column	Type	Meaning
asset_id	varchar(60)	PK — Steam asset ID
owner	varchar(30)	Buyer's Steam ID
bot_id	int	Holding bot
price	int	Cents — agreed price
reserved_at	double	Unix epoch of reservation
tradeable_at	double	Unix epoch when item becomes tradeable
stage	int	Reservation state machine stage
locked	int	Hard lock flag
type	varchar(45)	Reservation type (trade, store, etc.)
purchase_id	int	FK → associated purchase
giveaway_id	int	FK → giveaway if applicable
app_id / context_id	int	Steam app and context

Query gotchas: Deletions are replicated — this table reflects current state only. Historical reservations are not retained here.

reserved_items_auto_remove

Purpose: Reservations scheduled for automatic removal (expired/timed-out reservations) Size: ~11k rows / 23.6 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: owner) Key columns:

Column	Type	Meaning
asset_id	varchar(60)	PK
stage	int	Removal stage
purchase_id / giveaway_id	int	Related IDs

reserved_transactions

Purpose: Transaction log linking reservations to bot trades — tracks the deposit/withdraw flow Size: ~12.6M rows / 1,022.0 MB Replication: incremental-id + DMS-bootstrap PII: yes (columns: implicit via steam_id references) Key columns:

Column	Type	Meaning
(full schema not captured)		reservation → bot_trade linkage

Query gotchas: 12.6M rows. DMS did bulk load; Fargate does ongoing incremental by id.

reserved_lootbear_items

Purpose: Items reserved for Lootbear integration withdrawals Size: ~32k rows / 16.5 MB Replication: incremental-updated-at PII: yes (columns: steam_id, steam_tradebot_id) Key columns:

Column	Type	Meaning
asset_id	varchar(20)	Steam asset ID
class_id / instance_id	varchar(20)	Steam item class/instance
status	text	Current withdrawal status
withdrawn_at	datetime	Completion timestamp

---

5. Trading & Sale Offers

sale_offers

Purpose: P2P marketplace listings — items listed by users for sale to other users Size: ~284k rows / 984.0 MB Replication: incremental-updated-at PII: yes (columns: owner, buyer — Steam IDs) Key columns:

Column	Type	Meaning
asset_id	varchar(20)	Listed item's Steam asset ID
owner	varchar(30)	Seller's Steam ID
buyer	varchar(30)	Buyer's Steam ID (NULL until sold)
item_id	int	FK → items.id
bot_id	int	Holding bot
price	int	Cents — listing price
revenue	int	Cents — seller receives after fee
status	text	'active', 'sold', 'cancelled', etc.
only_store	tinyint	Store-only listing flag
is_instant	tinyint	Instant trade eligible
type	text	Offer type classification
sale_revenue_payout_id	int	FK → sale_revenue_payout.id

Key joins:

• asset_id → container_item.asset_id
• id → saleoffer_trades.offer_id
• id → sale_offer_logs.id

sale_offer_logs

Purpose: Audit log of all status changes on sale_offers Size: ~569k rows / 2,253.0 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
asset_id	varchar(20)	Item asset ID
steam_id	varchar(20)	Acting user Steam ID
item_id	int	FK → items.id
bot_id	int	Bot involved
price	int	Cents
reason	text	Why status changed
full_info	text	JSON detail blob

saleoffer_trades

Purpose: Steam trade records for completed sale offer transactions Size: ~279k rows / 1,588.0 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
offer_id	bigint	PK — FK → sale_offers.id
steam_id	varchar(30)	Buyer Steam ID
bot_id	int	Bot that sent trade
timestamp	double	Unix epoch
realprice	int	Cents — actual paid price
status	int	Trade status code
completed	int	1 = trade completed
data	longtext	Full Steam trade offer JSON

sale_revenue_payout

Purpose: Payouts to sellers from P2P sale offer revenue (crypto, balance credit) Size: ~40k rows / 20.5 MB Replication: incremental-updated-at PII: yes (columns: steam_id, crypto_address) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Seller receiving payout
amount	int	Cents
type	varchar(20)	Payout method
status	text	Payment status
crypto_unit	varchar(100)	Crypto currency if applicable
hash	varchar(255)	Blockchain tx hash
crypto_address	varchar(255)	Destination address — PII
note	text	Manual notes

sell_offers

Purpose: User-to-tradeit cash-out offers — users selling items for real money via external payout Size: ~60k rows / 186.6 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: customer — Steam ID, email, payout_info) Key columns:

Column	Type	Meaning
sell_id	int	PK
customer	varchar(20)	Seller's Steam ID
bot	int	Receiving bot ID
customer_items	longtext	JSON of items being sold
customer_price	int	Cents — what customer expects
customer_real_price	int	Cents — actual evaluated price
pay_price	int	Cents — what tradeit pays out
payout_method	varchar(45)	Payment method (crypto, paypal, etc.)
currency	varchar(45)	Payout currency
email	varchar(100)	Payout email — high-sensitivity PII
payout_info	longtext	JSON with payout details — high-sensitivity PII
instant	int	Instant payout flag
status	varchar(45)	Offer status

sold_offers

Purpose: Completed sale records (summary of finalized sell_offers or sale_offers) Size: ~190k rows / 14.5 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: buyer_id) Key columns:

Column	Type	Meaning
offer_id	varchar(20)	Reference to originating offer
buyer_id	varchar(30)	Buyer Steam ID
bot_price	int	Cents — bot's price
payment_price	int	Cents — what buyer paid

relist_reasons

Purpose: Log of why items were relisted (price change, stock rebalance, rule trigger) Size: ~36k rows / 9.5 MB Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
asset_id	varchar(20)	Item's Steam asset ID
item_id	int	FK → items.id
baseprice	int	Cents — base price at relist time
markup	double	Applied markup multiplier
reason	varchar(20)	Short reason code
rule	int	Which pricing rule triggered
sold	int	1 if item subsequently sold
sell_time	double	Unix epoch of eventual sale

scam_offer_logs

Purpose: Log of detected scam/fraudulent trade offers intercepted by bots Size: ~1.1k rows / 0.2 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: scammer_steam_id, our_bot_steam_id) Key columns:

Column	Type	Meaning
offer_id / trade_id	bigint	Steam offer/trade IDs
our_bot_id	int	Our bot that received the scam attempt
scammer_steam_id	varchar(20)	Attacker's Steam ID
market_hash_name	varchar(100)	Item targeted
asset_id / class_id / instance_id	bigint	Item identifiers

steam_trade_verifications

Purpose: Verification codes for Steam trades requiring manual user confirmation Size: ~134 rows / 0.1 MB Replication: incremental-updated-at PII: yes (columns: steam_id, trade_url) Key columns:

Column	Type	Meaning
verification_code	varchar(32)	Code sent to user
trade_offer_id	bigint	Steam offer ID
status	text	Verification status
attempt	int	Attempt count
expires_at	datetime	Code expiry

trade_reverts

Purpose: Records of trade reversals initiated (admin-triggered undos of completed trades) Size: ~47k rows / 66.6 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Affected user
offer_id	varchar(20)	Original trade offer ID
bot_id	int	Bot involved
trade_raw	text	Full original trade data JSON

trade_revert_items

Purpose: Individual items involved in trade reversals Size: ~158k rows / 14.5 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Affected user
offer_id	varchar(20)	FK → trade_reverts
bot_id	int	Bot involved
item_id	int	FK → items.id
asset_id	varchar(20)	Steam asset ID
revert_type	text	Type of revert action

trade_revert_reserved_items

Purpose: Snapshot of reserved_items state at time of trade reversal (full-refresh of revert-in-progress items) Size: ~19k rows / 40.6 MB Replication: full-refresh PII: yes (columns: owner) Key columns:

Column	Type	Meaning
asset_id	varchar(60)	PK
owner	varchar(30)	User Steam ID
confirmed_at	datetime	When revert was confirmed

trade_revert_item_missings

Purpose: Items reported missing during trade revert process (discrepancy tracking) Size: ~947 rows / 0.2 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(255)	Affected user
name	varchar(255)	Item name
price	int	Cents — expected value
asset_id	varchar(20)	Steam asset ID
float	varchar(30)	Item float value
type	varchar(20)	Discrepancy type

---

6. Finance & Payments

balance_transactions

Purpose: Master financial ledger — every credit and debit to user balances Size: ~75M rows / 11,994.0 MB Replication: incremental-id + DMS-bootstrap PII: yes (columns: steam_id implicit via joins) Key columns:

Column	Type	Meaning
(full schema not captured)		steam_id, amount, type, reference_id, created_at

Query gotchas: 75M rows and 12 GB — largest transactional table. Always filter by created_at range. Types include: trade, topup, coupon, affiliate, store, challenge. Never run without a selective WHERE predicate.

topup_transactions

Purpose: Real-money deposit records (Stripe, crypto, etc.) — all user fund top-ups Size: ~1.6M rows / 228.8 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
(full schema not captured)		steam_id, amount, provider, status, paid_at

Key joins:

• Referenced by card_verification.topup_transaction_id

purchases

Purpose: Stripe checkout session records for direct purchases Size: ~376k rows / 39.6 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Buyer
amount	int	Cents
stripe_checkout_session_id	varchar(100)	Stripe session reference
coinbase_charge_id	varchar(100)	Coinbase alternative
status	text	Payment status
paid_at	datetime	Confirmation timestamp

card_verification

Purpose: Stripe card verification and 3DS authentication records for fraud prevention Size: ~338k rows / 61.6 MB Replication: incremental-updated-at PII: yes (columns: steam_id, stripe_card_last4, stripe_card_fingerprint, stripe_payment_intent_id, pan) Key columns:

Column	Type	Meaning
topup_transaction_id	int	FK → topup_transactions.id
steam_id	varchar(20)	User Steam ID
stripe_card_last4	varchar(4)	Card last 4 digits
stripe_card_fingerprint	varchar(64)	Stripe's unique card hash
stripe_card_funding	varchar(100)	'credit'/'debit'/'prepaid'
needed_verification	tinyint	Whether 3DS was required
three_d_secure_result	varchar(255)	3DS outcome
id_verification	varchar(255)	ID check result
status	varchar(255)	Overall verification status
refunded_at	bigint	Unix epoch of refund if any

Query gotchas: Contains highly sensitive PII — limit column selection in any exported queries.

coinbase_charges

Purpose: Coinbase Commerce crypto payment charges Size: ~26k rows / 5.5 MB Replication: incremental-updated-at PII: yes (columns: user_id — Steam ID) Key columns:

Column	Type	Meaning
user_id	varchar(20)	Steam ID
charge_id	varchar(128)	Coinbase charge ID
type	varchar(24)	Charge type
hosted_url	varchar(128)	Coinbase hosted payment page
code	varchar(16)	Charge code
amount	int	Cents
failed_reason	varchar(256)	Failure description if applicable

nowpayment_charge

Purpose: NOWPayments crypto payment charges (alt-coin support) Size: ~74k rows / 61.6 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
nowpayment_id	varchar(20)	NOWPayments transaction ID
token_id	varchar(20)	Crypto token identifier
amount	int	Cents
is_limited	tinyint	Limited payment flag
balance_type	text	Which balance receives credit

monnect_transaction

Purpose: Monnect payment gateway transactions (regional payment method) Size: ~94k rows / 36.6 MB Replication: incremental-updated-at PII: yes (columns: steam_id, ip, pan, cardholder) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
ip	varchar(20)	Client IP — PII
order_number	varchar(50)	Payment reference
amount	double	Payment amount
pan	varchar(30)	Card PAN — high-sensitivity PII
cardholder	varchar(50)	Card holder name — PII
hash / control_string	varchar	Payment verification hashes
trans_id	varchar(255)	Gateway transaction ID
balance_type	text	Which balance type credited

haloskin_order / uuskin_orders

Purpose: Third-party marketplace orders from Haloskin and UUSkin integrations Size: 4.8k / 6.3k rows respectively Replication: incremental-updated-at (unconfirmed) PII: yes (steam_id implied)

cashout_request

Purpose: User requests to cash out balance (withdrawal requests) Size: ~36k rows / 4.5 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: address — crypto/payment address) Key columns:

Column	Type	Meaning
uuid	varchar(50)	PK
address	varchar(45)	Payout destination address
type	int	Cashout type code
amount	int	Cents
handled	int	1 = processed

payment_transactions

Purpose: Legacy payment transaction log — currently empty Size: 0 rows — currently empty / feature not active Replication: not-replicated PII: yes (columns: steam_id, token)

payment_blocked

Purpose: Users blocked from specific payment methods Size: 0 rows — currently empty Replication: not-replicated PII: yes (columns: steam_id)

payment_nonce

Purpose: One-time nonces for payment request deduplication Size: ~4 rows Replication: not-replicated PII: yes (columns: steam_id)

payment_id_verifications

Purpose: Identity verification records for high-value payment limits Size: ~246 rows / 0.1 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: steam_id, transaction_id) Key columns:

Column	Type	Meaning
provider	text	ID verification provider
reason_key	varchar(30)	Why verification was triggered
amount	int	Cents — transaction that triggered it
status	text	Verification outcome

payment_limit_rules

Purpose: Configuration for per-provider payment limits and rules Size: ~8 rows Replication: incremental-updated-at (unconfirmed) PII: no

payment_process_log

Purpose: Low-level audit log of payment processing steps Size: ~2.8M rows / 4,275.0 MB Replication: incremental-updated-at PII: yes (columns: steam_id, data blob) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
order_id	varchar(50)	Payment order reference
message	varchar(255)	Processing step description
data	text	JSON detail blob

Query gotchas: 2.8M rows and 4.3 GB — filter by created_at and specific order_id for lookups.

crypto_adresses

Purpose: User crypto withdrawal addresses on file (note: typo in table name — "adresses") Size: ~5.2k rows / 0.4 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: steam_id implied, crypto address)

crypto_balance

Purpose: User crypto balances — currently empty Size: 0 rows — currently empty / feature not active Replication: not-replicated PII: yes (if active)

refund_orders

Purpose: Refund records for reversed payments Size: ~3.4k rows / 0.5 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
order_id	varchar(50)	FK to original payment order
amount	int	Cents refunded
balance_type	text	What was refunded (cash/balance)
reason	varchar(255)	Refund reason

---

7. Affiliates & Coupons

affiliates

Purpose: Affiliate program registry — streamers/influencers with referral slugs Size: ~41k rows / 3.5 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Affiliate's Steam ID
coupon_id	int	Associated coupon FK
slug	varchar(12)	Public referral code (e.g. "STREAMER")
first_trade_bonus	int	Cents bonus for new user's first trade

affiliate_users

Purpose: Users who signed up via an affiliate referral link Size: ~1.4M rows / 101.6 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Referred user
affiliate_id	int	FK → affiliates.id
trade_volume	double	Cents of total trades by this user
affiliated_at	datetime	When user applied the code

Key joins:

• affiliate_id → affiliates.id

affiliates_revenues

Purpose: Revenue earned by affiliates from referred users' trades Size: ~6.6M rows / 656.0 MB Replication: incremental-updated-at PII: yes (columns: steam_id, from_steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Affiliate receiving revenue
from_steam_id	varchar(20)	Referred user who traded
trade_volume	double	Cents of the qualifying trade
earning	double	Cents earned by affiliate
status	text	Payment status

coupons

Purpose: Coupon definitions — discount codes with type, limits, and expiry Size: ~80k rows / 7.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
type	text	Coupon type classification
code	varchar(50)	The coupon code string
user_topup_percent	float	% bonus on top-up
user_store_percent	float	% discount on store
affiliate_percent	float	% revenue share for affiliate
expired_at	datetime	Expiry
store_limit	double	Max store discount value
ticket	int	Ticket reward amount
used	int	Usage count
limited	int	1 if single-use
balance	int	Cents balance coupon adds
store_balance	int	Cents store credit

coupon_registers

Purpose: Record of users applying coupons — tracks every coupon redemption Size: ~2.5M rows / 168.7 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Redeeming user
coupon_id	int	FK → coupons.id
status	text	Redemption status
register_source	tinyint	How user applied it (web, API, etc.)

Key joins:

• coupon_id → coupons.id

referers

Purpose: Legacy referrer tracking (pre-affiliate system) Size: ~15 rows — near-empty, likely superseded by affiliates Replication: not-replicated PII: yes (columns: steamid)

---

8. User Activity & Engagement

first_transaction

Purpose: Records the first-ever transaction date per user — used for new user cohort analysis Size: ~1M rows / 53.6 MB Replication: incremental-id PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
id	bigint	PK
steam_id	varchar(20)	User Steam ID
created_at	datetime	Date of first transaction

Query gotchas: One row per user — good for cohort joins. created_at = first trade date, not account creation.

user_daily_tracker

Purpose: Per-user daily metric snapshots (trading volume, activity counts per day) Size: ~5.4M rows / 389.9 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
date	date	Tracking date
key	varchar(50)	Metric name
value	double	Metric value

Query gotchas: EAV structure — pivot on key values for wide-format analysis.

daily_tracker

Purpose: Platform-wide daily metric snapshots (aggregate KPIs, not per-user) Size: ~15.5k rows / 1.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
date	date	Tracking date
key	varchar(50)	Metric name
value	double	Metric value

Query gotchas: EAV structure — same key names as user_daily_tracker but platform-aggregate.

kpi_log

Purpose: Timestamped snapshots of key platform KPIs (stock levels, price modifiers, inventory values per game) Size: ~107k rows / 30.6 MB Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
timestamp	double	PK — Unix epoch
csgo_steam_mod1/mod2	int	CS2 Steam price modifiers
csgo_steam_istock/rstock	int	CS2 in-stock / reserved-stock counts
csgo_steam_vol / csgo_buff_vol	int	Trade volumes
csgo_ratio_*	double	Price ratio comparisons vs external markets
oindex_730/570/252490/440	int	Overall inventory index per app_id

market_metrics

Purpose: Daily per-item market index and market cap snapshots (price × stock) Size: ~9.7M rows / 497.0 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
item_id	int	FK → items.id
market_index	float	Composite market health index
market_cap	int	Cents — total value (price × quantity)
date	date	Snapshot date

Query gotchas: 9.7M rows — filter by date range and/or specific item_id.

market_trends

Purpose: Current market trend summary per item with multi-horizon change metrics Size: ~34k rows / 3.5 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
item_id	int	FK → items.id
market_index	float	Current composite index
market_cap	int	Cents
index_change_1d/7d/30d/90d	float	Index % change per window
cap_change_1d/7d/30d/90d	float	Market cap % change per window

user_inventory_value

Purpose: Snapshots of user inventory values per game (what their items are worth) Size: ~3.5M rows / 316.8 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
csgo / rust / dota2 / tf2	int	Cents value of current tradeit inventory per game
csgo_total / rust_total / etc.	int	Cents including items not yet deposited
total	int	Cents — grand total across all games

user_item_sales

Purpose: Record of item sales between users (historical sale log) Size: ~60k rows / 5.5 MB Replication: incremental-updated-at (unconfirmed) PII: yes (columns: buyer, seller — Steam IDs) Key columns:

Column	Type	Meaning
buyer / seller	varchar(20)	Steam IDs of both parties
item_id	int	FK → items.id
price	int	Cents — transaction price
guide_price	int	Cents — reference price at time of sale
timestamp	bigint	Unix epoch

user_liked_items

Purpose: Items users have hearted/liked on the marketplace Size: ~615k rows / 40.6 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
item_id	int	FK → items.id
asset_id	varchar(20)	Specific asset instance liked

user_favorite_items

Purpose: User's favorited item asset IDs (stored as blob, not normalized) Size: ~11k rows / 1.5 MB Replication: full-refresh PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(50)	PK
item_asset_ids	longtext	JSON array of favorited asset IDs

Query gotchas: Not normalized — requires JSON parsing to work with individual asset IDs.

termly_cookie_consents

Purpose: Cookie consent records from Termly consent banner Size: ~26.5M rows / 2,814.0 MB Replication: incremental-updated-at PII: yes (columns: steam_id implied, consent timestamps) Query gotchas: 26.5M rows and 2.8 GB — GDPR-relevant table. Always filter by date. Do not expose raw records externally.

server_downtime_reports

Purpose: User-submitted downtime/issue reports Size: ~23k rows / 1.5 MB Replication: incremental-updated-at PII: yes (columns: steam_id)

---

9. Challenges, Tickets & SkinIQ

tickets

Purpose: Internal ticket currency ledger — earned and spent ticket records per user Size: ~25.3M rows / 1,873.0 MB Replication: incremental-id + DMS-bootstrap PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
type	text	How ticket was earned/spent
info	text	Context JSON
balance	int	Ticket balance delta
ticket	int	Ticket count delta
purchase_type	text	Purchase category if applicable

Query gotchas: 25.3M rows — filter by created_at. DMS did bulk load; Fargate does ongoing incremental.

challenge_config

Purpose: Configuration for challenge reward tiers (balance/ticket amounts per challenge type) Size: ~11 rows — small reference table Replication: incremental-updated-at (unconfirmed) PII: no

challenge_bonus

Purpose: Bonuses awarded to users from completing trading challenges Size: ~5.3M rows / 404.9 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
challenge_config_id	int	FK → challenge_config.id
balance	int	Cents bonus awarded
ticket	int	Ticket bonus awarded
status	text	Award status

trade_bonus

Purpose: Bonuses tied to specific trades (referral bonus, milestone rewards, etc.) Size: ~1.9M rows / 201.7 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
bonus	int	Cents bonus
type	text	Bonus type (referral, milestone, etc.)
status	text	Payment status
config	text	Configuration context JSON

guess_questions

Purpose: SkinIQ price-guessing game — each row is one question presented to a user Size: ~10.7M rows / 53,037.0 MB (53 GB) Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	Player
guess_season_id	int	FK → guess_seasons.id
price	int	Cents — correct item price
date	date	Question date
steam_30d_volume / steam_7d_volume / steam_2d_volume	int	Steam market volumes
steam_30d_median / steam_7d_median / steam_2d_median	int	Steam median prices
steam_highest_buyorder / steam_lowest_sellorder	int	Order book snapshot
end_time	datetime	When question expired

Query gotchas: 53 GB — the largest table on the replica. ANY query without a highly selective predicate will timeout. Always filter by date AND steam_id or guess_season_id. COUNT(*) alone will fail. Use date range covering at most a few days.

guess_seasons

Purpose: SkinIQ season definitions (periods of the guessing game) Size: ~2 rows — minimal Replication: incremental-updated-at (unconfirmed) PII: no

guess_plans

Purpose: SkinIQ subscription/plan tiers Size: ~76 rows Replication: incremental-updated-at (unconfirmed) PII: no

guess_rewards

Purpose: Reward definitions for SkinIQ game outcomes Size: ~18 rows Replication: incremental-updated-at (unconfirmed) PII: no

guess_users

Purpose: SkinIQ player profiles and subscription status Size: ~130k rows / 15.5 MB Replication: incremental-updated-at PII: yes (columns: steam_id)

giveaway_config / giveaway_list

Purpose: Giveaway feature configuration and active/historical giveaway entries Size: 5 / 16k rows respectively Replication: incremental-updated-at (unconfirmed) PII: yes (giveaway_list likely has steam_id)

---

10. OAuth2 & Integrations

oauth2_clients

Purpose: OAuth2 client applications authorized to use tradeit as identity provider Size: ~2 rows — minimal (only 2 active OAuth clients) Replication: incremental-updated-at (unconfirmed) PII: no (but client_secret is sensitive) Key columns:

Column	Type	Meaning
client_id	varchar(36)	OAuth client ID
client_secret	varchar(36)	Secret — treat as credential
redirect_uris	text	Allowed redirect URIs
is_skip_confirm	tinyint	Skip user consent screen
linked_steam_id	varchar(20)	Linked platform steam account
linked_affiliate_steam_id	varchar(20)	Revenue-share affiliate link

oauth2_users

Purpose: Users who have connected their tradeit account to an OAuth2 client app Size: ~612k rows / 77.6 MB Replication: incremental-updated-at PII: yes (columns: steam_id, email) Key columns:

Column	Type	Meaning
steam_id	varchar(20)	User
email	varchar(320)	Email shared with OAuth client
client_id	varchar(36)	FK → oauth2_clients.client_id
scope	text	Granted permissions
connected_at	datetime	When user authorized
user_passes_identification_at	datetime	When identity was verified

oauth2_authorization_codes

Purpose: Short-lived OAuth2 authorization codes (standard OAuth flow) Size: ~676k rows / 131.6 MB Replication: incremental-updated-at PII: yes (columns: code, oauth2_user_id) Key columns:

Column	Type	Meaning
code	varchar(255)	One-time auth code
client_id	varchar(36)	Requesting client
oauth2_user_id	int	FK → oauth2_users.id
expires_at	datetime	Short expiry (minutes)

oauth2_refresh_tokens

Purpose: OAuth2 long-lived refresh tokens — currently empty Size: 0 rows — currently empty / not in use Replication: not-replicated PII: yes (if active)

oauth2_client_transactions

Purpose: Balance transactions initiated through OAuth2 clients (partner-initiated credits/debits) Size: ~53k rows / 4.5 MB Replication: incremental-updated-at PII: yes (columns: steam_id) Key columns:

Column	Type	Meaning
oauth2_client_id	int	FK → oauth2_clients.id
type	text	Transaction type
transaction_key	varchar(64)	Idempotency key
amount	int	Cents
steam_id	varchar(20)	User receiving/losing balance

csgoskins_sessions

Purpose: Session tracking for CS:GO Skins integration Size: ~356 rows — minimal Replication: incremental-updated-at (unconfirmed) PII: no

---

11. Configuration & Infrastructure

configurations

Purpose: Central key-value configuration store for platform settings (feature flags, thresholds, multipliers) Size: ~222 rows / 0.0 MB Replication: incremental-updated-at PII: no Key columns:

Column	Type	Meaning
category	varchar(15)	Config group (e.g. 'trade', 'payment')
key	varchar(255)	Config key name
value	varchar(2000)	Config value (string)
type	varchar(100)	Value type hint
key_hash	varchar(10)	Short hash for quick lookup

Query gotchas: See [[configurations-registry]] for documented key meanings.

marketplace_configs

Purpose: Per-marketplace trading rules (price limits, markup percents, stock limits per game/mode) Size: ~2 rows Replication: incremental-updated-at (unconfirmed) PII: no Key columns:

Column	Type	Meaning
name	text	Marketplace identifier
is_trade_enabled / is_store_enabled	tinyint	Feature toggles
min_price / max_price	int	Cents trading price bounds
trade_markup_percent / store_markup_percent	int	Markup percentages
max_per_total_item / max_per_instant_item	int	Per-item stock limits
buffer_price_max / buffer_price_percent	int	Price buffer rules
stable_price_multiplier_limit	double	Max allowed stable price multiplier
metadata	longtext	JSON extra config

mods

Purpose: Legacy pricing modifier table (one row — stores active price multipliers per game mode) Size: ~0 rows (effectively empty/unused) Replication: not-replicated PII: no Key columns:

Column	Type	Meaning
withdraw_ratio / deposit_ratio	double	Trade direction modifiers
steam_ratio	double	Steam price reference ratio
c5_rate	double	C5Game exchange rate

migrations

Purpose: Database migration tracking (Laravel/Sequelize migration history) Size: 0 rows — empty on replica Replication: not-replicated PII: no

distribution_queue

Purpose: Queue for distributing items or rewards — currently empty Size: 0 rows — currently empty / feature not active Replication: not-replicated PII: no

---

12. Miscellaneous / Investments

invest_payouts

Purpose: Payouts from skin investment/futures feature Size: ~2.3k rows / 0.2 MB Replication: incremental-updated-at (unconfirmed) PII: yes (steam_id implied)

invest_position_history

Purpose: Historical positions in skin investment feature Size: ~445k rows / 30.6 MB Replication: incremental-updated-at PII: yes (steam_id implied)

nft_placeholder

Purpose: NFT experiment table — dead feature, retained for reference Size: ~475 rows / 10.5 MB Replication: DMS-only-bootstrap PII: no Query gotchas: Do not use for production analytics — feature never launched.

---

Operational Snapshots / Backup Tables

The following tables are point-in-time snapshots or operational backups. Do not use for analysis — use the live equivalents.

• items_april — Clone of items taken ~April 2023

If additional backup tables appear (e.g. container_item_bot_225_12_4_23), treat them the same way — snapshot, not live data.

---

Replication Infrastructure

The _repl_state table lives in the pricing schema on the replica (not steamarbitrage). See [[analytics-replica]] for replication architecture and [[database-schema-pricing]] for the pricing schema.

---

ops.tradeit.gg — Internal Engineering Docs