Time-Travel Analytics on Operational Data (MariaDB System-Versioned)

Bring “undo the past” to business data. This project uses MariaDB system-versioned tables to query and visualize data “as of” any point in time for audits, rollbacks, and KPI drift analysis. It includes a Spring Boot backend, Next.js frontend, and Docker Compose deployment.

A full-stack application that enables temporal queries on operational data using MariaDB's system-versioned tables. Query data "as of" any point in time for audits, rollbacks, and KPI drift analysis.

📦 Complete File Structure

time-travel-analytics/
│
├── 📄 README.md                    # Main documentation
├── 📄 QUICKSTART.md                # 5-minute setup guide
├── 📄 ARCHITECTURE.md              # System design documentation
├── 📄 API.md                       # Complete API reference
├── 📄 CONTRIBUTING.md              # Contribution guidelines
├── 📄 TROUBLESHOOTING.md           # Common issues & solutions
├── 📄 CHANGELOG.md                 # Version history
├── 📄 LICENSE                      # MIT License
├── 📄 .gitignore                   # Git ignore rules
├── 📄 .dockerignore                # Docker ignore rules
├── 📄 .env.example                 # Environment variables template
├── 📄 docker-compose.yml           # Docker orchestration
├── 📄 Makefile                     # Common commands
├── 📄 setup.sh                     # Automated setup script
│
├── 📁 .github/
│   └── workflows/
│       └── ci.yml                  # CI/CD pipeline (GitHub Actions)
│
├── 📁 db/                          # Database files
│   ├── schema.sql                  # System-versioned table definitions
│   ├── seed.sql                    # Sample data with history
│   └── triggers.sql                # Audit logging triggers
│
├── 📁 backend/                     # Spring Boot application
│   ├── pom.xml                     # Maven dependencies
│   ├── Dockerfile                  # Backend container image
│   │
│   └── src/
│       ├── main/
│       │   ├── java/com/example/timetravel/
│       │   │   ├── TimeTravelApplication.java        # Main entry point
│       │   │   ├── controller/
│       │   │   │   └── TimeTravelController.java     # REST endpoints
│       │   │   └── service/
│       │   │       └── TimeTravelService.java        # Business logic
│       │   │
│       │   └── resources/
│       │       └── application.properties            # Configuration
│       │
│       └── test/
│           ├── java/com/example/timetravel/
│           │   └── AsOfIntegrationTest.java          # Integration tests
│           └── resources/
│               └── application-test.properties       # Test configuration
│
└── 📁 frontend/                    # Next.js application
    ├── package.json                # npm dependencies
    ├── next.config.js              # Next.js configuration
    ├── tailwind.config.js          # Tailwind CSS config
    ├── postcss.config.js           # PostCSS config
    ├── .eslintrc.json              # ESLint config
    ├── Dockerfile                  # Frontend container image
    │
    ├── pages/
    │   ├── index.js                # Main UI (snapshot & diff views)
    │   └── _app.js                 # App wrapper
    │
    └── styles/
        └── globals.css             # Global styles + Tailwind imports

🚀 Quick Start Commands

# 1. Setup (automated)
chmod +x setup.sh && ./setup.sh

# 2. Or manual setup
docker compose up -d --build

# 3. Access
# Frontend: http://localhost:3000
# Backend:  http://localhost:8080
# MariaDB:  localhost:3306 (root/example)

# 4. Test
cd backend && mvn test

# 5. Stop
docker compose down

🏗️ Architecture

┌─────────────────────────────────────────────────────────┐
│                    USER BROWSER                         │
└─────────────────────┬───────────────────────────────────┘
                      │ HTTP
                      ▼
┌─────────────────────────────────────────────────────────┐
│  FRONTEND (Next.js + React + Tailwind CSS)             │
│  - Snapshot view UI                                     │
│  - Diff comparison UI                                   │
│  - Responsive design                                    │
│  Port: 3000                                             │
└─────────────────────┬───────────────────────────────────┘
                      │ REST API (JSON)
                      ▼
┌─────────────────────────────────────────────────────────┐
│  BACKEND (Spring Boot + JdbcTemplate)                   │
│  - /api/asof       → Snapshot queries                   │
│  - /api/diff       → Diff comparison                    │
│  - /api/audit      → Audit trail                        │
│  - PII masking     → Privacy protection                 │
│  - Input validation → Security                          │
│  Port: 8080                                             │
└─────────────────────┬───────────────────────────────────┘
                      │ JDBC
                      ▼
┌─────────────────────────────────────────────────────────┐
│  DATABASE (MariaDB 10.11+ with System Versioning)      │
│  - inventory       → Product data (versioned)           │
│  - users           → User accounts (versioned)          │
│  - orders          → Customer orders (versioned)        │
│  - inventory_audit → Audit log (trigger-based)          │
│  Port: 3306                                             │
└─────────────────────────────────────────────────────────┘

🔑 Key Features

1. Temporal Queries

-- See data as it was at any point in time
SELECT * FROM inventory 
FOR SYSTEM_TIME AS OF TIMESTAMP '2025-09-01 12:00:00';

2. Diff Comparison

Compare two snapshots to see what changed:

• Added rows
• Deleted rows
• Modified rows

3. Audit Trail

Automatic logging of:

• What changed (old vs new values)
• Who made the change
• When it happened

4. PII Masking

Historical personal data automatically masked:

• email → ***@***.***
• address → *** *** ***

5. Security

• SQL injection prevention
• Table name whitelisting
• Input validation
• CORS configuration

📊 Use Cases

1. Audit Compliance: Track who changed what and when
2. Data Recovery: Restore accidentally deleted/modified data
3. Drift Analysis: See how KPIs evolved over time
4. Regulatory Compliance: Maintain immutable audit trails
5. Debugging: Investigate when data corruption occurred
6. A/B Testing: Compare metrics before/after changes

🛠️ Technology Stack

Layer	Technology	Version
Frontend	Next.js	14.0.4
Frontend	React	18.2.0
Frontend	Tailwind CSS	3.4.0
Backend	Java	17
Backend	Spring Boot	3.2.0
Backend	Maven	3.9+
Database	MariaDB	10.11+
Deployment	Docker	Latest
Deployment	Docker Compose	Latest
CI/CD	GitHub Actions	Latest

📚 Documentation Files

File	Purpose
README.md	Main project documentation with setup instructions
QUICKSTART.md	5-minute getting started guide
ARCHITECTURE.md	Detailed system design and architecture
API.md	Complete REST API reference with examples
CONTRIBUTING.md	Guidelines for contributing to the project
TROUBLESHOOTING.md	Common issues and solutions
CHANGELOG.md	Version history and release notes
LICENSE	MIT License

🧪 Testing

Backend Tests

cd backend
mvn test

Tests include:

• ✅ Snapshot queries return data
• ✅ Diff endpoint returns before/after
• ✅ PII masking works correctly
• ✅ Audit log captures changes
• ✅ Input validation prevents errors

Integration Tests

• Full API endpoint testing
• Database temporal query verification
• End-to-end workflows

🔒 Security Features

1. SQL Injection Prevention: Parameterized queries
2. Input Validation: Whitelist validation for tables
3. PII Protection: Automatic masking of historical data
4. CORS Configuration: Controlled cross-origin access
5. No Exposed Credentials: Environment variables only

📈 Performance Considerations

• Indexes: Timestamp columns indexed for fast queries
• Limits: Result sets can be limited
• Caching: Future enhancement (Redis)
• Connection Pooling: Managed by Spring Boot
• Resource Limits: Configurable in Docker Compose

🚢 Deployment Options

1. Docker Compose (Development)

docker compose up -d

2. Kubernetes (Production)

• StatefulSet for MariaDB
• Deployments for backend/frontend
• PersistentVolumes for data
• Ingress for external access

3. Cloud Platforms

• AWS ECS + RDS
• Google Cloud Run + Cloud SQL
• Azure Container Instances + Azure Database

📋 API Endpoints

Endpoint	Method	Description
/api/asof	GET	Get snapshot at timestamp
/api/diff	GET	Compare two snapshots
/api/audit/{table}/{id}	GET	Get audit log for row
/api/tables	GET	List available tables
/api/health	GET	Health check

🔄 CI/CD Pipeline

GitHub Actions workflow includes:

• ✅ Backend tests with MariaDB
• ✅ Frontend build verification
• ✅ Docker image builds
• ✅ Integration testing
• ✅ Automated on push/PR

🎯 Future Enhancements

High Priority

• RBAC (Role-Based Access Control)
• Data export (CSV, Excel)
• Prometheus metrics
• Kubernetes manifests
• Comprehensive frontend tests

Medium Priority

• Enhanced diff algorithm (field-level)
• Data retention policies
• Grafana dashboards
• API rate limiting
• Multi-tenant support

Nice to Have

• Real-time notifications
• Advanced search/filtering
• TypeScript migration
• GraphQL API
• Mobile app

🤝 Contributing

We welcome contributions! See CONTRIBUTING.md for:

• Development setup
• Code standards
• Testing requirements
• Pull request process

📞 Support

• Documentation: Read all .md files in root
• Issues: GitHub Issues
• Logs: docker compose logs -f
• Health: curl http://localhost:8080/api/health

📄 License

MIT License - see LICENSE file

✅ Project Status

Version: 1.0.0
Status: Production Ready
Last Updated: October 2025

What Works

✅ Full temporal query support
✅ Web UI with snapshot and diff views
✅ Audit logging with triggers
✅ PII masking for privacy
✅ Docker deployment
✅ Integration tests
✅ CI/CD pipeline
✅ Comprehensive documentation

Known Limitations

⚠️ No caching (all queries hit DB)
⚠️ Limited to 3 predefined tables
⚠️ No pagination for large results
⚠️ No authentication/authorization
⚠️ Frontend tests not yet implemented

🎓 Learning Resources

• MariaDB System Versioning: https://mariadb.com/kb/en/system-versioned-tables/
• Spring Boot: https://spring.io/projects/spring-boot
• Next.js: https://nextjs.org/docs
• Docker Compose: https://docs.docker.com/compose/

---

Ready to time-travel through your data? Start with QUICKSTART.md! ⏰🚀