Overview
TestVault is an enterprise QA test management system that provides centralized test case management, test execution tracking, and reporting for quality assurance teams. Built with Flask and SQLite, it offers a lightweight yet powerful solution for teams that need structured test management without the overhead of complex infrastructure. The server-rendered architecture ensures fast page loads and straightforward deployment.
Problem Statement
QA teams need centralized test case management to maintain consistency across projects and releases. Spreadsheets become unmanageable at scale, and enterprise tools are often over-engineered for small-to-mid-size teams. The challenge was building a tool that provides structured test hierarchies, execution tracking, and reporting without requiring complex infrastructure or expensive licensing.
Architecture Overview
The system is built on Flask with server-rendered Jinja2 templates for the UI layer. SQLite provides persistent storage with a carefully designed schema supporting test suites, test cases, execution runs, and results. The architecture prioritizes simplicity — a single Python process serves both the API and rendered pages, making deployment straightforward on any system with Python installed.
Tech Stack
Key Features
Test Case CRUD
Full lifecycle management for test cases with rich metadata including priority, preconditions, steps, and expected results
Test Execution Tracking
Record and track test execution runs with pass/fail/skip/blocked status, execution notes, and defect linking
Test Suite Organization
Hierarchical test suite structure with folders, tags, and filtering for organizing large test libraries
Reporting Dashboard
Execution summary reports with pass rates, trend charts, and exportable results for stakeholder communication
Engineering Challenges
Data modeling for hierarchical test structures
Designed a nested set model in SQLite for test suite hierarchies, enabling efficient subtree queries for rendering folder structures and calculating aggregate pass rates across nested suites.
Execution state management across concurrent test runs
Implemented a state machine for test execution lifecycle (pending → running → completed) with SQLite transactions ensuring consistency. Partial completion is tracked per-case within a run.
Reporting performance with large test histories
Built pre-computed summary tables updated via SQLite triggers on execution result inserts. Dashboard queries read from summaries rather than aggregating raw results, keeping page loads fast.
@app.route('/api/runs/<int:run_id>/results', methods=['POST'])
def record_result(run_id):
run = TestRun.query.get_or_404(run_id)
data = request.get_json()
result = TestResult(
run_id=run.id,
case_id=data['case_id'],
status=data['status'], # pass, fail, skip, blocked
notes=data.get('notes', ''),
executed_at=datetime.utcnow()
)
db.session.add(result)
# Update run progress
run.completed_count += 1
if run.completed_count >= run.total_count:
run.status = 'completed'
db.session.commit()
return jsonify(result.to_dict()), 201class TestCase(db.Model):
id = db.Column(db.Integer, primary_key=True)
suite_id = db.Column(db.Integer, db.ForeignKey('test_suite.id'), nullable=False)
title = db.Column(db.String(200), nullable=False)
priority = db.Column(db.String(20), default='medium')
preconditions = db.Column(db.Text)
steps = db.Column(db.Text, nullable=False)
expected_result = db.Column(db.Text, nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
suite = db.relationship('TestSuite', backref='cases')
results = db.relationship('TestResult', backref='case', lazy='dynamic')Testing Strategy
pytest handles all backend testing with fixtures providing fresh database state for each test. Functional tests cover the full HTTP request/response cycle for all routes including form submissions. Model tests verify data integrity constraints and cascade behaviors. The test suite runs against an in-memory SQLite database for speed.
Deployment
The application deploys as a single Python process running Flask with a SQLite database file for storage. This simplicity means deployment requires only Python and pip — no external database servers, message queues, or container orchestration needed. Gunicorn serves production traffic behind a reverse proxy.
Lessons Learned
- SQLite is remarkably capable for single-instance tools — its simplicity eliminates entire categories of deployment issues
- Server-rendered templates with Flask are still the fastest path from idea to working product for CRUD applications
- Hierarchical data in SQL requires careful schema design upfront — nested sets outperform recursive CTEs for read-heavy workloads
- Pre-computed summary tables via triggers trade write complexity for dramatic read performance gains in reporting