- Introduction
- Database Design
- Functionality Details
- Implementation Details
- Experiences and Challenges
- References
- Dylan Connolly (dtc22h@fsu.edu)
- Zack Lima (zol21@fsu.edu)
Repository: https://github.com/dylantcon/countertrak
The CounterTrak application represents an innovative approach to performance tracking for players of Valve Corporation's popular first-person-shooter Counter Strike 2 (CS2). Developed as a semester-long project for the COP4710 Theory and Structure of Databases course, this web-based informatics system harnesses the power of relational database management systems (RDBMS) to provide players with meaningful insights into their gameplay patterns and performance metrics.
What distinguishes CounterTrak from existing statistics tracking platforms is our focus on lightweight design coupled with sophisticated analytics. While services like HLTV.org and csgostats.gg offer comprehensive but resource-intensive tracking, CounterTrak prioritizes simplicity and accessibility without sacrificing analytical depth. Our application demonstrates that intelligent database design can yield powerful insights without requiring massive computational resources or complex infrastructure.
The system consists of several key components working in concert: an asynchronous HTTP server that receives game state data, a match manager that routes payloads to appropriate processors, a sophisticated database schema optimized for performance analysis, and an intuitive web interface that visualizes player statistics. Together, these components create a seamless pipeline from in-game actions to actionable performance insights, all powered by PostgreSQL and implemented through a Django-based Python backend system.
In the following sections, we detail our database design decisions, functionality implementation, system architecture, and the valuable lessons learned throughout the development process.
The inspiration that sparked the creation of the CounterTrak application emanated from the team's appreciation for online gaming, and professional game development. Both Zach and Dylan cultivated an admiration for computers and computing concepts from an early age. These formative circumstances resulted in a natural gravitation towards the gaming community, with online gaming presenting itself as yet another opportunity to engage with the machines they found so captivating. They made many fond memories during these times, and explored the vast range of video-game genres and subgenres. Over time, they began to perceive gaming not just as a mere diversion, but a rich culture that naturally aligned with their systems-oriented cognitive profiles.
Modern games are immensely complex works of art, born from precise collaborative efforts characteristic of highly seasoned software engineers and computer scientists. The team's discovery of CS2's GSI system presented itself as an opportunity to exercise their collective appreciation for game-development concepts, by applying it towards a data-driven informatics system. The rich dataset facilitated by CS2 GSI was the ideal foundation for a database-powered application. Being highly motivated software developer aspirants, the team members wanted their academic work to be truly distinctive. As a result, CounterTrak was born.
The foundation of CounterTrak's functionality lies in its carefully designed database structure. Our database design process began with identifying the key entities in the CS2 gameplay domain and establishing their relationships, followed by translating this conceptual model into a normalized relational schema suitable for implementation in PostgreSQL.
To accurately capture the complex data generated by CS2's Game State Integration system, we developed an entity-relationship model that identifies eight core entities and their interrelationships. This model needed to account for the hierarchical nature of CS2 matches (matches containing rounds, rounds containing player states) while maintaining flexibility for analytical queries.
The entities we identified include:
- Users - CounterTrak account holders who access the application
- SteamAccounts - Steam identities linked to user accounts for authentication
- Matches - Individual CS2 games with metadata such as map, mode, and scores
- Rounds - Discrete gameplay units within matches
- PlayerRoundStates - Player status data during specific rounds
- Weapons - Reference data for all available CS2 weapons
- PlayerWeapons - Relationship entity tracking equipped weapons during gameplay
- PlayerMatchStats - Aggregate performance statistics for players in matches
These entities and their relationships are visualized in the comprehensive ER diagram below:
This diagram illustrates several important design decisions. First, we established a clear separation between user accounts and Steam identities, allowing a single user to track multiple Steam accounts. Second, we implemented a hierarchical structure from matches to rounds to player states, enabling detailed temporal analysis. Third, we created a specialized entity for weapon tracking to support our advanced analytics functions.
To transform our conceptual ER model into an implementable database design, we developed a detailed relational schema that preserves all entity relationships while optimizing for both data integrity and query performance. The resulting schema consists of eight tables with carefully defined primary and foreign key relationships.
This schema includes several noteworthy features. We implemented composite primary keys in the Rounds, PlayerRoundStates, and PlayerWeapons tables to efficiently represent hierarchical relationships. The temporal aspect of game state tracking is captured through timestamps in appropriate tables, enabling precise sequence analysis. Additionally, we created a pre-populated Weapons table to serve as a reference for all in-game weapons, improving both performance and data consistency.
Our schema design adheres to Boyce-Codd Normal Form (BCNF), ensuring efficient data storage and minimizing anomalies. We identified and addressed the following functional dependencies:
- In the Users table,
user_idfunctionally determinesusernameandpassword_hash - In the SteamAccounts table,
steam_idfunctionally determinesuser_id,auth_token, andplayer_name - In the Matches table,
match_idfunctionally determines all match attributes - In the Rounds table, the composite key
(match_id, round_number)functionally determines round attributes - In the PlayerRoundStates table, the composite key
(match_id, round_number, steam_id)functionally determines player state attributes - In the Weapons table,
weapon_idfunctionally determines weapon attributes, withnameas an alternative candidate key - In the PlayerWeapons table, the composite key
(match_id, round_number, steam_id, weapon_id)functionally determines weapon state attributes - In the PlayerMatchStats table, the composite key
(steam_id, match_id)functionally determines all player match statistics
This normalization approach provided several advantages for our application. It minimized data redundancy, reducing storage requirements for the potentially large volume of game state data. It eliminated update anomalies that could compromise data integrity during high-frequency state changes. Most importantly, it created a schema structure that naturally supported our advanced temporal sequence analysis for player performance patterns.
The schema also includes several integrity constraints beyond normal foreign key relationships. We implemented check constraints on valid value ranges for attributes like health, armor, and money. We created triggers to automatically update match scores when rounds are completed. Additionally, we developed custom validation logic in our Django models to ensure that incoming GSI data conforms to expected patterns before being committed to the database.
This comprehensive database design forms the foundation for CounterTrak's functionality, enabling both efficient storage of game state data and sophisticated analytical queries that reveal meaningful patterns in player performance.
CounterTrak implements a comprehensive set of database operations to manage game state data efficiently, focusing on the CRUD (Create, Read, Update, Delete) paradigm while handling the complexity of hierarchical and temporal relationships in our data model.
The record creation process is carefully orchestrated to maintain referential integrity across related entities. When a new match begins, the system creates a match record with initial state data, followed by round records and player state records as the match progresses. This insertion logic is particularly complex as it must handle transactional consistency across multiple related tables.
For example, when a new player action is detected through the GSI system, our application executes a series of interdependent database operations:
INSERT INTO stats_playerroundstate
(match_id, round_number, steam_account_id, health, armor,
money, equip_value, round_kills, team, state_timestamp)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
RETURNING idWithin the CounterTrak system, record retrieval operations from a basic perspective center around duplicate record checks during match data ingress. Here is an example of a check for duplicate PlayerRoundState records, within backend/gsi/django_integration.py:
# ...
state_time = convert_unix_timestamp_to_datetime(player_state.state_timestamp)
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT COUNT(*) FROM stats_playerroundstate
WHERE
match_id = %s AND
round_number = %s AND
steam_account_id = %s AND
state_timestamp = %s
""",
[
match_id,
round_number,
player_state.steam_id,
state_time
]
)
return cursor.fetchone()[0] > 0
# ...Updates are handled carefully to ensure data integrity, particularly for player statistics that change frequently during a match. The system uses atomic updates to modify specific fields without affecting unrelated data:
UPDATE stats_playermatchstat
SET
kills = EXCLUDED.kills,
deaths = EXCLUDED.deaths,
assists = EXCLUDED.assists,
mvps = EXCLUDED.mvps,
score = EXCLUDED.scoreFor data deletion, we implemented cascading delete operations that maintain referential integrity while efficiently removing all related records. This is particularly important for match deletion, which must clean up all associated round, player state, and weapon records:
DELETE FROM stats_playerweapon WHERE match_id = %s;
DELETE FROM stats_playerroundstate WHERE match_id = %s;
DELETE FROM stats_playermatchstat WHERE match_id = %s;
DELETE FROM matches_round WHERE match_id = %s;
DELETE FROM matches_match WHERE match_id = %s;These basic operations form the foundation upon which our advanced analytical features are built, ensuring data integrity and performance even under high-volume data processing conditions.
The core of CounterTrak's value proposition lies in its sophisticated Performance Pattern Recognition engine, which goes beyond simple statistics aggregation to identify meaningful patterns in player performance. This engine represents our most advanced functionality and employs complex temporal sequence analysis to extract actionable insights.
The Performance Pattern Recognition engine focuses on three key analytical dimensions:
- Weapon Effectiveness Analysis: This component analyzes how effectively a player uses different weapons across various contexts. Unlike basic statistics systems that simply count kills per weapon, our system considers the temporal relationship between weapon activation and successful eliminations:
WITH weapon_performance AS (
SELECT
m.map_name,
w.name AS weapon_name,
w.type AS weapon_type,
COUNT(DISTINCT pw.match_id) AS matches_used,
SUM(prs.round_kills) AS total_kills,
COUNT(DISTINCT CASE WHEN prs.round_kills > 0 THEN prs.round_number ELSE NULL END) AS rounds_with_kills,
COUNT(DISTINCT prs.round_number) AS total_rounds_used,
ROUND(SUM(prs.round_kills)::numeric / NULLIF(COUNT(DISTINCT prs.round_number), 0), 3) AS kills_per_round,
ROUND(COUNT(DISTINCT CASE WHEN prs.round_kills > 0 THEN prs.round_number ELSE NULL END)::numeric /
NULLIF(COUNT(DISTINCT prs.round_number), 0), 3) AS kill_consistency,
ROUND(SUM(prs.round_kills)::numeric / NULLIF(SUM(prs.equip_value)/1000, 0), 3) AS kills_per_1000_spent
FROM
stats_weapon w
JOIN
stats_playerweapon pw ON w.weapon_id = pw.weapon_id
JOIN
stats_playerroundstate prs ON
pw.match_id = prs.match_id AND
pw.round_number = prs.round_number AND
pw.steam_account_id = prs.steam_account_id
JOIN
matches_match m ON prs.match_id = m.match_id
JOIN
stats_playermatchstat pms ON
prs.match_id = pms.match_id AND
prs.steam_account_id = pms.steam_account_id
WHERE
pw.state = 'active'
AND prs.steam_account_id = ${steam_id}
AND w.type NOT IN ('Knife', 'Grenade', 'C4', 'Other')
GROUP BY
m.map_name, w.name, w.type
HAVING
COUNT(DISTINCT prs.round_number) >= 5
)
SELECT
map_name,
weapon_name,
weapon_type,
matches_used,
total_kills,
kills_per_round,
kill_consistency,
kills_per_1000_spent,
ROUND(
(kills_per_round * 0.4) +
(kill_consistency * 0.3) +
(kills_per_1000_spent * 0.3)
, 3) AS weapon_effectiveness_score,
CASE
WHEN kills_per_round >= 0.75 AND kill_consistency >= 0.5 THEN 'S-Tier'
WHEN kills_per_round >= 0.5 AND kill_consistency >= 0.4 THEN 'A-Tier'
WHEN kills_per_round >= 0.3 AND kill_consistency >= 0.3 THEN 'B-Tier'
WHEN kills_per_round >= 0.2 THEN 'C-Tier'
ELSE 'D-Tier'
END AS recommendation_tier
FROM
weapon_performance
ORDER BY
map_name, weapon_effectiveness_score DESC;- This query not only tracks kill statistics but calculates derived metrics like kill consistency (frequency of successful rounds), economic efficiency (kills per money spent), and a composite effectiveness score that weights these factors appropriately.
- Economic Impact Analysis: Our system examines how economic decisions affect performance by analyzing the relationship between equipment value, money management, and round outcomes. It analyzes various economic decisions including Full-Buy, Semi-Buy, Semi-Eco, and Eco strategies, and the round outcomes that result from them:
-- ECONOMIC ANALYSIS QUERIES
-- Demonstrates economic decision analysis and impact on performance
-- Economic round state analysis
WITH round_economics AS (
SELECT
m.match_id,
m.map_name,
r.round_number,
r.winning_team,
CASE
WHEN AVG(prs.equip_value) < 1000 THEN 'Eco (< $1000)'
WHEN AVG(prs.equip_value) BETWEEN 1000 AND 2500 THEN 'Semi-Eco ($1000-$2500)'
WHEN AVG(prs.equip_value) BETWEEN 2501 AND 4000 THEN 'Semi-Buy ($2501-$4000)'
WHEN AVG(prs.equip_value) > 4000 THEN 'Full Buy (> $4000)'
END AS economic_state,
AVG(prs.money) AS avg_remaining_money,
AVG(prs.equip_value) AS avg_equip_value,
SUM(prs.round_kills) AS total_kills,
COUNT(DISTINCT prs.steam_account_id) AS player_count
FROM
matches_match m
JOIN
matches_round r ON m.match_id = r.match_id
JOIN
stats_playerroundstate prs ON
r.match_id = prs.match_id AND
r.round_number = prs.round_number
WHERE
prs.steam_account_id = ${steam_id}
GROUP BY
m.match_id, m.map_name, r.round_number, r.winning_team
)
-- Economic strategy effectiveness analysis
SELECT
economic_state,
COUNT(DISTINCT (match_id, round_number)) AS rounds_played,
ROUND(COUNT(DISTINCT CASE WHEN winning_team = 'CT' OR winning_team = 'T' THEN (match_id, round_number) END)::numeric /
NULLIF(COUNT(DISTINCT (match_id, round_number)), 0) * 100, 2) AS win_rate,
ROUND(SUM(total_kills)::numeric / NULLIF(COUNT(DISTINCT (match_id, round_number)), 0), 2) AS avg_kills_per_round,
ROUND(AVG(avg_equip_value), 2) AS avg_investment,
ROUND(AVG(avg_remaining_money), 2) AS avg_remaining_money,
ROUND(SUM(total_kills)::numeric / NULLIF(SUM(avg_equip_value) / 1000, 0), 2) AS kills_per_1000_spent
FROM
round_economics
GROUP BY
economic_state
ORDER BY
win_rate DESC;
-- Economic transitions analysis: impact of previous round's economy
WITH round_sequence AS (
SELECT DISTINCT ON (prs1.match_id, prs1.round_number)
prs1.match_id,
prs1.round_number,
prs1.steam_account_id,
prs1.equip_value AS current_equip_value,
prs1.money AS current_money,
prs1.round_kills,
prs1.team,
r1.winning_team = prs1.team AS round_won,
LAG(prs1.equip_value) OVER (
PARTITION BY prs1.match_id
ORDER BY prs1.round_number
) AS prev_equip_value,
LAG(r1.winning_team = prs1.team) OVER (
PARTITION BY prs1.match_id
ORDER BY prs1.round_number
) AS prev_round_won
FROM
stats_playerroundstate prs1
JOIN
matches_round r1 ON
prs1.match_id = r1.match_id AND
prs1.round_number = r1.round_number
WHERE
prs1.steam_account_id = ${steam_id}
)
SELECT
CASE
WHEN prev_equip_value < 1000 THEN 'After Eco (< $1000)'
WHEN prev_equip_value BETWEEN 1000 AND 2500 THEN 'After Semi-Eco ($1000-$2500)'
WHEN prev_equip_value BETWEEN 2501 AND 4000 THEN 'After Semi-Buy ($2501-$4000)'
WHEN prev_equip_value > 4000 THEN 'After Full Buy (> $4000)'
ELSE 'First Round'
END AS previous_economic_state,
CASE
WHEN prev_round_won IS TRUE THEN 'Won'
WHEN prev_round_won IS FALSE THEN 'Lost'
ELSE 'First Round'
END AS previous_round_outcome,
COUNT(*) AS rounds_played,
ROUND(AVG(current_equip_value), 2) AS avg_current_investment,
ROUND(AVG(current_money), 2) AS avg_current_money,
ROUND(AVG(round_kills), 2) AS avg_kills,
ROUND(COUNT(CASE WHEN round_won THEN 1 END)::numeric /
NULLIF(COUNT(*), 0) * 100, 2) AS win_percentage
FROM
round_sequence
WHERE
round_number > 1 -- Exclude first round since it doesn't have previous state
GROUP BY
previous_economic_state, previous_round_outcome
HAVING
COUNT(*) >= 3 -- Only include scenarios with at least 3 occurrences
ORDER BY
previous_economic_state, previous_round_outcome;Our advanced analytics functions are implemented through a combination of complex SQL queries and Python-based post-processing. The weapon_analyzer.py module, for instance, uses a sophisticated temporal relationship algorithm to accurately attribute kills to specific weapons based on the sequence of state changes:
def get_weapon_analysis(steam_id: str) -> List[Dict[Any, Any]]:
try:
with connection.cursor() as cursor:
cursor.execute("""
-- ADVANCED WEAPON ANALYSIS QUERY
-- This query performs temporal sequence analysis to attribute kills
-- to the correct weapons based on which weapon was active when kills occurred.
WITH PlayerStateChanges AS (
-- STEP 1: Identify points where round_kills increases
-- This CTE analyzes the PlayerRoundState table to find moments when
-- a player's kill count increases, indicating they got a kill.
-- The LAG window function compares the current round_kills with the
-- previous round_kills value (ordered by timestamp) to calculate the
-- kill_increase (how many kills were just added).
SELECT
prs1.match_id,
prs1.round_number,
prs1.state_timestamp,
-- Calculate kill increase using LAG window function
-- This finds the exact moments when kills increased
prs1.round_kills - LAG(prs1.round_kills, 1, 0) OVER (
PARTITION BY prs1.match_id, prs1.round_number
ORDER BY prs1.state_timestamp
) AS kill_increase,
prs1.money
FROM stats_playerroundstate prs1
WHERE
prs1.steam_account_id = %s
AND prs1.round_kills > 0 -- Only include states with kills
),
ActiveWeapons AS (
-- STEP 2: Identify which weapon was active at each point in time
-- This CTE tracks which weapons were 'active' (being used) at which times.
-- We use ROW_NUMBER to identify unique weapon activations, avoiding
-- counting every state update as a separate usage instance.
SELECT
pw.match_id,
pw.round_number,
pw.weapon_id,
pw.state_timestamp,
w.name AS weapon_name,
w.type AS weapon_type,
-- Rank each weapon state by timestamp within round
-- This helps identify distinct weapon activations
ROW_NUMBER() OVER (
PARTITION BY pw.match_id, pw.round_number, pw.weapon_id
ORDER BY pw.state_timestamp
) AS activation_rank
FROM stats_playerweapon pw
JOIN stats_weapon w ON pw.weapon_id = w.weapon_id
WHERE
pw.steam_account_id = %s
AND pw.state = 'active' -- Only include active weapons
-- Exclude non-weapon items like grenades, C4, etc.
AND w.type NOT IN ('Grenade', 'C4')
AND w.type IS NOT NULL
AND w.type != 'StackableItem'
),
WeaponKills AS (
-- STEP 3: Join kills with active weapons to attribute kills correctly
-- This CTE correlates kills with active weapons based on timestamps.
-- We join PlayerStateChanges with ActiveWeapons where:
-- 1. They're in the same match and round
-- 2. The kill timestamp is >= the weapon activation timestamp
-- 3. There was an actual kill increase
-- This temporal relationship analysis is the key to attributing
-- kills to the correct weapons.
SELECT
aw.weapon_id,
aw.weapon_name,
aw.weapon_type,
-- Count distinct rounds where this weapon was used
COUNT(DISTINCT CONCAT(aw.match_id, '-', aw.round_number)) AS rounds_used,
-- Count total state records (approximates usage frequency)
COUNT(aw.match_id) AS state_count,
-- Sum kill increases that occurred while this weapon was active
SUM(CASE WHEN psc.kill_increase > 0 THEN psc.kill_increase ELSE 0 END) AS total_kills,
-- Track money for economic analysis
SUM(psc.money) AS total_money
FROM ActiveWeapons aw
LEFT JOIN PlayerStateChanges psc ON
-- Join on match and round
aw.match_id = psc.match_id
AND aw.round_number = psc.round_number
-- The kill happened AFTER this weapon became active
AND psc.state_timestamp >= aw.state_timestamp
-- Only include actual kill increases
AND (psc.kill_increase > 0)
GROUP BY aw.weapon_id, aw.weapon_name, aw.weapon_type
)
-- STEP 4: Final output with calculated metrics
-- This final query calculates performance metrics like:
-- - Average kills per round
-- - Average money during weapon usage
-- - Overall weapon effectiveness
SELECT
weapon_id,
weapon_name,
weapon_type,
rounds_used,
state_count,
total_kills,
-- Calculate average kills per round
CASE WHEN rounds_used > 0 THEN total_kills::float / rounds_used ELSE 0 END AS avg_kills,
-- Calculate average money during weapon usage
CASE WHEN state_count > 0 THEN total_money::float / state_count ELSE 0 END AS avg_money
FROM WeaponKills
ORDER BY state_count DESC; -- Sort by usage frequency
""", [steam_id, steam_id])
# process the query results into a list of dictionaries
# this makes the data easy to work with in django views
weapon_analysis = []
for row in cursor.fetchall():
weapon_analysis.append({
'weapon_id': row[0],
'weapon__name': row[1],
'weapon__type': row[2],
'rounds_used': row[3],
'times_active': row[4],
'total_kills': row[5],
'avg_kills_when_active': row[6],
'avg_money': row[7]
})
return weapon_analysis
except Exception as e:
logger.error(f"Error in weapon analysis for {steam_id}: {str(e)}")
return []This advanced functionality sets CounterTrak apart from simpler statistical tracking systems, providing players with actionable insights rather than just raw numbers. The Performance Pattern Recognition engine identifies strengths, weaknesses, and optimal strategies based on each player's unique gameplay patterns.
CounterTrak employs a multi-tier, event-driven architecture designed to efficiently process game state data in real-time while maintaining data consistency and system responsiveness. The architecture consists of several key components, each with specific responsibilities in the data flow pipeline. The high-level architecture is illustrated in the diagram below:
At the top level, the Asynchronous GSI Server component (implemented in backend/gsi/async_server.py) receives game state payloads from multiple CS2 clients simultaneously. This component uses Python's asyncio framework to provide non-blocking I/O, allowing it to handle many concurrent connections efficiently without creating a thread per connection. The server authenticates incoming payloads using token validation and routes them to the appropriate match processor.
The Match/Player Manager component (backend/gsi/match_manager.py) serves as a central coordination point, tracking active matches and creating new match processors as needed. This component maintains a dictionary of active match processors keyed by match ID, ensuring that each match's data remains isolated and consistent. The manager is primarily responsible for routing payloads to their appropriate MatchProcessors, but does perform some basic preliminary validation to ensure that the data hails from a new or in-progress match.
Individual Match Processors (backend/gsi/match_processor.py) handle the game state for specific matches, maintaining state between updates and managing the temporal sequence of events. Each processor operates independently, parsing game events using a delegate object called PayloadExtractor, tracking round transitions, and persisting data at appropriate points (typically at round boundaries). This isolation ensures that data from one match cannot interfere with another, while also enabling parallel processing of multiple matches.
At the bottom tier, the Async Database Connection Pool provides efficient, non-blocking database access. This component manages connections to the PostgreSQL database, using asyncio-compatible database drivers to perform database operations without blocking the event loop. All INSERT and UPDATE operations are handled in gsi/backend/django_integration.py, with calls being made from any instantiated MatchProcessors. The connection pool ensures that database operations are properly sequenced and transactionally consistent.
This architecture provides several key advantages:
- Scalability: The system can handle many concurrent matches without proportional resource consumption, as the async I/O model is much more efficient than traditional threading.
- Fault Isolation: Problems in one match processor don't affect others, improving system resilience.
- Data Consistency: By processing each match independently and using appropriate transaction boundaries (typically round completion), the system ensures data is consistently persisted.
- Performance: The non-blocking I/O model keeps the system responsive even under heavy load, as it can process other requests while waiting for I/O operations to complete.
The architecture also includes a separate Django Web Application that provides the user interface and REST API for accessing match statistics and analysis results. This component operates independently from the GSI processing pipeline, accessing the same database but focusing on query operations rather than data ingestion.
The CounterTrak application is built on a modern, scalable technology stack designed to efficiently handle real-time game data processing, storage, and analysis:
- Database Layer:
- PostgreSQL 14: Primary RDBMS providing robust data storage, complex querying capabilities, and transaction support.
- Database Schema: Carefully normalized to BCNF with optimized indexes for query performance.
- Connection Pooling: Implemented through Django's connection pool for efficient database access.
- Backend Processing:
- Python 3.10+: Core programming language for backend development.
- Django 5.2: Web framework providing ORM, authentication, and routing capabilities.
- Django REST Framework 3.16: For building the REST API endpoints.
- asyncio: Python's asynchronous I/O framework, used for non-blocking operations.
- aiohttp 3.11+: Asynchronous HTTP client/server for Python, used for the GSI server.
- Frontend:
- HTML5/CSS3/JavaScript: Standard web technologies for UI development.
- Bootstrap 5.3: Frontend CSS framework for responsive design.
- Development & DevOps:
- Git: Version control system for code management.
- GitHub: Repository hosting.
- dotenv: Environment variable management for secure configuration.
- pexpect: Used for automation in development scripts.
Our technology choices reflect a focus on scalability, maintainability, and performance. The use of asynchronous processing with asyncio and aiohttp is particularly notable, as it allows our system to handle many concurrent game clients efficiently without the overhead of traditional threading models.
The CounterTrak data flow pipeline is designed to efficiently process game state information from raw GSI payloads to structured database records and ultimately to meaningful analytical insights. This pipeline consists of several key stages:
- Payload Reception and Authentication:
- CS2 clients send HTTP POST requests containing GSI payloads to the async server.
- The server authenticates payloads using the auth token from the request.
- This stage is implemented in
backend/gsi/async_server.py.
- Payload Routing and Match Association:
- The match manager extracts the match identifier from the payload.
- If this is a new match, a new match processor is created.
- The payload is routed to the appropriate match processor.
- This stage is implemented in
backend/gsi/match_manager.py.
- Game State Extraction and Normalization:
- The
PayloadExtractorclass (inbackend/gsi/payloadextractor.py) parses the raw JSON payload. - The extractor creates structured data objects for match state, player state, round state, and weapon states.
- These structured objects map to database tables in the relational schema, an example for the Match table is seen below:
- The
def extract_match_state(self, payload: Dict, timestamp: int) -> Optional[MatchState]:
if 'map' not in payload or 'provider' not in payload:
return None
map_data = payload['map']
provider_data = payload['provider']
# use centralized utility function to get base_match_id
base_match_id = extract_base_match_id(payload)
if not base_match_id:
return None
# get raw round number from map payload portion
raw_round = map_data.get('round', 0)
# add 1 to convert from zero-indexed to one-indexed
adjusted_round = raw_round + 1
# return full match state with the provided timestamp
return MatchState(
match_id=base_match_id,
mode=map_data.get('mode', 'casual'),
map_name=map_data.get('name', 'unknown_map'),
phase=map_data.get('phase', 'unknown'),
round=adjusted_round,
team_ct_score=map_data.get('team_ct', {}).get('score', 0),
team_t_score=map_data.get('team_t', {}).get('score', 0),
timestamp=timestamp
)The multi-tier architecture and data flow pipeline of CounterTrak demonstrate a sophisticated approach to real-time game data processing. By implementing asynchronous I/O throughout the system, we've created a solution that efficiently handles the high-volume, bursty nature of GSI data while maintaining responsiveness. The well-defined responsibility boundaries between components (GSI server, match processors, database layer) provide clear separation of concerns, making the system both maintainable and extensible. This architecture not only meets the requirements of our current implementation but also establishes a solid foundation for future enhancements, such as more advanced analytics algorithms or integration with additional data sources.
Throughout the development of CounterTrak, our team encountered and overcame several significant technical challenges:
- Multiprocess Dual-Server Architecture: Given our choice of PostgreSQL with the Django backend framework, we were forced to reconcile with how we would integrate our original Game State Integration server with proper database persistence. We realized that the GSI Server needed to listen for incoming payloads within a main loop (
python async_server.py), and Django required its own main loop (python manage.py runserver 0.0.0.0:8000) for communication with the PSQL instance—both were blocking processes. To overcome this, we decided to implement a dual-server approach inbackend/run_servers.py, which compartmentalized each server's main loop within its ownsubprocess. To consolidate the disparate output streams to a common destination, we maintained two separate threads for the servers, each of which piped their output to standard output via the methodstream_output(process, prefix). Each call then iteratively logged the output from the two threads usingfor line in iter(process.stdout.readline):,if line:,print(f"[{prefix}] {line}", end="", flush=True), with propertry-excepthandling forIOError(s) andValueError(s). Additionally, we implemented intelligent thread and subprocess cleanup, to ensure that no child threads or subprocesses were left hanging after server shutdown. - Asynchronous Processing Complexity: Implementing a fully asynchronous system using Python's asyncio library required a substantial paradigm shift in our programming approach. We needed to carefully manage concurrency, especially in the match processor components where race conditions could lead to data inconsistencies. This was addressed through strategic use of locks (
asyncio.Lock) and careful transaction management. - Temporal Sequence Analysis: One of our most complex challenges was implementing accurate temporal relationship analysis for weapon effectiveness. Unlike simpler statistics systems that only count kills, we needed to determine which weapon was active at the exact moment a kill occurred. This required sophisticated SQL queries using window functions and temporal joins:
WITH PlayerStateChanges AS (
SELECT
prs1.match_id,
prs1.round_number,
prs1.state_timestamp,
prs1.round_kills - LAG(prs1.round_kills, 1, 0) OVER (
PARTITION BY prs1.match_id, prs1.round_number
ORDER BY prs1.state_timestamp
) AS kill_increase
FROM stats_playerroundstate prs1
WHERE
prs1.steam_account_id = %s
AND prs1.round_kills > 0
),
ActiveWeapons AS (
-- Track active weapons at each timestamp
-- ...
)- Database Performance Optimization: Managing the potentially high volume of GSI payloads required careful database design. We implemented several optimizations:
- Strategic denormalization for frequently accessed data
- Composite indexes for common query patterns
- Batch processing of state updates to reduce database load
- Authentication and Security: Ensuring that only authorized clients could submit game state data required a token-based authentication system. We implemented a token cache with periodic refreshing to maintain security while minimizing database queries.
The CounterTrak project provided numerous valuable insights that have enhanced our understanding of both technical and project management aspects:
- Asynchronous Design Benefits: The decision to use an asynchronous architecture with Python's asyncio framework proved extremely beneficial. It allowed our system to handle many concurrent connections with minimal resource consumption, making the application scalable without proportional resource requirements. This approach is significantly more efficient than traditional multi-threading for I/O-bound operations like our GSI server.
- Importance of Domain Modeling: Starting with a carefully designed entity-relationship model before implementing database schemas was crucial. The time invested in proper domain modeling paid dividends throughout the project by providing a clear conceptual framework that guided implementation decisions.
- Data Structure Separation: Maintaining clear separation between raw payload data structures and database models prevented data format changes in the GSI system from directly affecting our database schema. This abstraction layer, implemented through our
PayloadExtractorclass, provided important flexibility. - Progressive Enhancement: Building the system incrementally - first focusing on correct data capture, then basic statistics, and finally advanced analytics - allowed us to deliver value at each stage while continuing to enhance functionality.
- Centralized Logging: Implementing a comprehensive logging system early in development proved invaluable for debugging complex asynchronous behaviors and monitoring system performance.
Looking forward, we envision several promising directions for extending CounterTrak:
- Advanced Pattern Recognition: The current Performance Pattern Recognition engine could be enhanced with machine learning algorithms (using
pytorchortensorflow) to identify more complex patterns in player behavior and provide more personalized recommendations. - Comparative Analytics: Implementing functionality to compare player performance against population averages or professional player benchmarks would provide additional context for performance evaluation.
- Real-time Feedback System: Developing a system to provide real-time feedback during matches could help players adjust their strategies on-the-fly based on historical performance data.
- API Expansion: Creating a more comprehensive API would enable third-party developers to build additional tools and visualizations using CounterTrak's data and analytics engine.
- Django Project. (2025). Django Documentation. https://docs.djangoproject.com/en/5.2/
- Python Software Foundation. (2025). asyncio — Asynchronous I/O. https://docs.python.org/3.10/library/asyncio.html
- aiohttp. (2025). aiohttp Documentation. https://docs.aiohttp.org/en/stable/
- Valve Corporation. (2024). Counter-Strike: Global Offensive Game State Integration. https://developer.valvesoftware.com/wiki/Counter-Strike:_Global_Offensive_Game_State_Integration
- Plotnikov, A. (2024). Development of an esports HUD for Counter-Strike 2. Bachelor's thesis, Esports Business.
- PostgreSQL Global Development Group. (2025). PostgreSQL Documentation. https://www.postgresql.org/docs/14/
- The Python Package Index (PyPI). (2025). colorlog 6.9.0. https://pypi.org/project/colorlog/
- Django REST Framework. (2025). Django REST Framework Documentation. https://www.django-rest-framework.org/