Database Schema & Models

Relevant source files

The following files were used as context for generating this wiki page:

• models/team.py
• models/submission.py
• app.py
• database/init_db.py
• routes/submit_routes.py

Introduction

The database schema for the Debug Arena platform is designed to manage competitive debugging sessions, tracking team progress, security compliance, and historical submission data. Built using SQLAlchemy ORM, the system primarily utilizes a relational structure to link participating teams with their respective code submissions and scoring metrics.

The architecture supports both SQLite for local development and PostgreSQL for production environments. It ensures data persistence for team credentials, real-time scoring, and proctoring violations, which are critical for maintaining the integrity of the coding event.

Sources: app.py:27-33, README.md:55-58

Core Data Models

The system architecture revolves around two primary entities: the Team and the Submission. These models define how data is stored, validated, and related within the application.

Team Model

The Team model is the central entity, storing authentication details, demographic information (College), and real-time state data such as current level progression and security violation counts.

Field	Type	Constraints	Description
id	Integer	Primary Key	Unique identifier for the team.
team_name	String(50)	Unique, Not Null	The display name for the squad.
college	String(100)	Not Null	Academic institution affiliation.
email	String(120)	Unique, Not Null	Contact email for the team.
password	String(255)	Not Null	Hashed credentials for login.
current_level	Integer	Default: 1	The level the team is currently solving.
total_score	Integer	Default: 0	Cumulative points earned across levels.
violations	Integer	Default: 0	Count of anti-cheat proctoring breaches.

Sources: models/team.py:5-24

Submission Model

The Submission model captures every successful code execution. It serves as an audit log for what code was submitted, how long it took, and the specific score awarded for that individual level attempt.

Field	Type	Constraints	Description
id	Integer	Primary Key	Unique identifier for the submission.
team_id	Integer	Foreign Key (team.id)	Links the submission to a specific team.
level_id	Integer	Not Null	The specific challenge level solved.
code_submitted	Text	Not Null	The actual Python source code provided.
status	String(20)	Not Null	Execution result (e.g., 'Correct').
time_taken	Integer	-	Duration in seconds to solve the level.
score_earned	Integer	-	Points awarded for this specific level.
timestamp	DateTime	Default: UTC Now	Date and time of the submission.

Sources: models/submission.py:4-14

Relationships and Data Flow

The models maintain a One-to-Many relationship between teams and submissions. One team can have multiple submissions (one for each level cleared), while each submission belongs to exactly one team.

Entity Relationship Diagram

The following diagram illustrates the cardinality and link between the core entities.

Submission Logic Flow

When a team successfully solves a level, the application performs a series of atomic database operations to update the team's state and record the achievement.

Database Initialization and Configuration

The project uses Flask-SQLAlchemy for database management. The initialization process ensures that the storage directory exists and tables are created automatically upon application startup or via a dedicated script.

Configuration Parameters

The application detects its environment to determine the database backend.

Configuration	Source	Default Value
SQLALCHEMY_DATABASE_URI	DATABASE_URL Env	sqlite:///instance/db.sqlite3
SQLALCHEMY_TRACK_MODIFICATIONS	Hardcoded	False
SECRET_KEY	SECRET_KEY Env	dev-key-123

Sources: app.py:20-33

Initialization Process

The database can be initialized through app.py context or the database/init_db.py utility.

# From database/init_db.py
with app.app_context():
    db.create_all()
    print("✅ Database initialized successfully at database/db.sqlite3")

Sources: database/init_db.py:16-18, app.py:51-54

Integrity and Security Tracking

A unique aspect of the schema is the violations field in the Team model. This field is used by the log_violation route to persist security breaches detected by the frontend proctoring system. If the violations count reaching 3, the team is effectively locked out of the arena.

Sources: models/team.py:20, routes/arena_routes.py:102-120, admin_reset.py:15-18

Summary

The Database Schema & Models provide a robust foundation for tracking the competitive lifecycle of a debugging event. By strictly separating team identity from submission history and incorporating real-time violation tracking, the system maintains a high degree of auditability and competitive integrity. The use of SQLAlchemy ensures that the schema is portable across different SQL dialects, facilitating easy deployment from development to production.