DataTest - Database Consistency Testing Application

A modern, full-stack Python application for creating, scheduling, and monitoring database consistency tests with a beautiful React UI.

Features

Core Functionality

• Test Management: Create SQL-based tests to check data consistency
• Scheduling: Schedule tests with flexible cron-based scheduling (days, hours, frequency)
• Conditions: Multiple condition types (row count, value matching, etc.)
• Triggers: Execute actions on test success/failure:
  • Run SQL queries
  • Send email notifications
  • Call webhooks
• Test Execution: Manual or scheduled test execution with detailed results

User Roles

• USER Role:

  • Propose new tests
  • Edit pending/rejected tests
  • View test results and executions
  • Manually trigger approved tests

• ADMIN Role:

  • Approve or reject proposed tests
  • Archive or delete tests
  • All user permissions

Modern UI

• Dashboard with KPI visualizations (24h, 7d, 30d, 6m)
• Real-time test execution monitoring
• Interactive charts showing success/failure rates
• Responsive Material-UI design

Technology Stack

Backend

• FastAPI: Modern, fast Python web framework
• SQLAlchemy: SQL toolkit and ORM
• APScheduler: Advanced Python task scheduling
• JWT: Secure authentication
• PostgreSQL/MySQL/SQLite: Flexible database support

Frontend

• React 18: Modern UI framework
• Material-UI: Beautiful, accessible components
• Recharts: Interactive data visualizations
• Axios: HTTP client

Installation

Prerequisites

• Python 3.9+
• Node.js 16+ (for frontend)
• PostgreSQL/MySQL (optional, SQLite included)

Backend Setup

1. Clone the repository

cd DataTests

2. Create virtual environment

python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install dependencies

pip install -r requirements.txt

4. Configure environment

cp .env.example .env
# Edit .env with your settings

5. Initialize database

python -c "from backend.core.database import engine, Base; from backend.models import *; Base.metadata.create_all(bind=engine)"

6. Create first admin user

python -c "
from backend.core.database import SessionLocal
from backend.models.user import User
from backend.core.security import get_password_hash

db = SessionLocal()
admin = User(
    username='admin',
    email='[email protected]',
    hashed_password=get_password_hash('admin123'),
    is_admin=True
)
db.add(admin)
db.commit()
print('Admin user created: admin/admin123')
"

7. Run backend server

python backend/main.py
# Or with uvicorn:
# uvicorn backend.main:app --reload --host 0.0.0.0 --port 8000

Backend will be available at: http://localhost:8000 API documentation: http://localhost:8000/docs

Frontend Setup

1. Navigate to frontend directory

cd frontend

2. Install dependencies

npm install

3. Run development server

npm run dev

Frontend will be available at: http://localhost:3000

Configuration

Environment Variables

Edit .env file:

# Database for application data
DATABASE_URL=sqlite:///./datatest.db
# Or PostgreSQL: postgresql://user:password@localhost/dbname
# Or MySQL: mysql+pymysql://user:password@localhost/dbname

# Target database to test
TARGET_DB_URL=sqlite:///./target_database.db

# Security
SECRET_KEY=your-secret-key-change-this-in-production
ACCESS_TOKEN_EXPIRE_MINUTES=30

# Email (for notifications)
SMTP_HOST=smtp.gmail.com
SMTP_PORT=587
[email protected]
SMTP_PASSWORD=your-app-password
[email protected]

Email Configuration

For Gmail:

1. Enable 2-factor authentication
2. Generate an App Password: https://myaccount.google.com/apppasswords
3. Use the app password in SMTP_PASSWORD

Usage

Creating a Test

1. Login to the application

2. Navigate to "Tests" → "Create Test"

3. Fill in the test details:

   • Name: Descriptive test name
   • SQL Query: Query to check data consistency
   • Condition: How to evaluate the result (e.g., "Has Rows", "Row Count > 10")
   • Triggers: Actions to execute on success/failure
   • Schedule: Optional scheduling configuration

4. Submit for approval (if you're not admin)

Example Tests

Check for duplicate emails:

SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1

Condition: no_rows (expect no duplicates) Trigger on failure: Send email to admin

Check daily transactions:

SELECT COUNT(*) FROM transactions
WHERE DATE(created_at) = CURRENT_DATE

Condition: row_count_gt with value 100 (expect >100 transactions) Schedule: Daily at 6 PM

Check system health:

SELECT 'OK' as status

Condition: value_equals with value OK Schedule: Every 15 minutes

Scheduling Configuration

Schedule Config Format:

{
  "days": [0, 1, 2, 3, 4],  // Monday-Friday (0=Monday, 6=Sunday)
  "hours": [9, 12, 18],      // At 9 AM, 12 PM, 6 PM
  "frequency_minutes": null   // Or set to run every X minutes
}

Or use frequency:

{
  "frequency_minutes": 15  // Run every 15 minutes
}

Triggers

Email Trigger:

{
  "type": "email",
  "config": {
    "to": "[email protected]",
    "subject": "Test Alert: {test_name}",
    "body": "Test {test_name} has {status}. Row count: {row_count}"
  }
}

SQL Trigger:

{
  "type": "sql_query",
  "config": {
    "query": "UPDATE system_status SET last_check = NOW()"
  }
}

Webhook Trigger:

{
  "type": "webhook",
  "config": {
    "url": "https://your-webhook.com/notify",
    "method": "POST"
  }
}

API Endpoints

Authentication

• POST /api/auth/register - Register new user
• POST /api/auth/login - Login and get token

Tests

• GET /api/tests/ - List all tests
• POST /api/tests/ - Create new test
• GET /api/tests/{id} - Get test details
• PUT /api/tests/{id} - Update test
• POST /api/tests/{id}/approve - Approve test (admin)
• POST /api/tests/{id}/reject - Reject test (admin)
• POST /api/tests/{id}/execute - Execute test manually
• GET /api/tests/{id}/executions - Get execution history

Dashboard

• GET /api/dashboard/kpis - Get KPI metrics
• GET /api/dashboard/recent-executions - Get recent executions

Full API documentation available at: http://localhost:8000/docs

Architecture

DataTests/
├── backend/
│   ├── core/           # Configuration, database, security
│   ├── models/         # SQLAlchemy models
│   ├── schemas/        # Pydantic schemas
│   ├── routers/        # API endpoints
│   ├── services/       # Business logic (scheduler, executor, triggers)
│   └── main.py         # FastAPI application
├── frontend/
│   └── src/
│       ├── components/ # React components
│       ├── pages/      # Page components
│       ├── services/   # API client
│       └── context/    # React context (auth)
└── requirements.txt

Development

Running Tests

# Backend tests
pytest

# Frontend tests
cd frontend && npm test

Database Migrations

Using Alembic:

alembic init alembic
alembic revision --autogenerate -m "Initial migration"
alembic upgrade head

Production Deployment

Backend

# Use production WSGI server
gunicorn backend.main:app --workers 4 --worker-class uvicorn.workers.UvicornWorker --bind 0.0.0.0:8000

Frontend

cd frontend
npm run build
# Serve the build/ directory with nginx or similar

Docker (Optional)

Create Dockerfile:

FROM python:3.11-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install -r requirements.txt
COPY backend/ backend/
CMD ["uvicorn", "backend.main:app", "--host", "0.0.0.0", "--port", "8000"]

Security Considerations

• Change SECRET_KEY in production
• Use HTTPS in production
• Store sensitive credentials in environment variables
• Validate SQL queries to prevent injection
• Implement rate limiting
• Regular security updates

Troubleshooting

Backend won't start:

• Check Python version (3.9+)
• Verify all dependencies installed
• Check database connection string

Frontend won't connect:

• Ensure backend is running on port 8000
• Check CORS settings in backend/main.py
• Verify API_BASE_URL in frontend

Scheduler not working:

• Check test status is "approved"
• Verify schedule_enabled is True
• Check logs for errors

Contributing

1. Fork the repository
2. Create feature branch
3. Commit changes
4. Push to branch
5. Create Pull Request

License

MIT License - See LICENSE file for details

Support

For issues and questions:

• GitHub Issues: [Create an issue]
• Documentation: See /docs endpoint
• Email: [email protected]