Database Explorer - Design & Implementation

Overview

The Database Explorer is a read-only web-based interface for browsing and querying raw data from PQAP's SQLite databases. It provides admin users with direct access to underlying data for debugging, analysis, and validation.

Architecture

Components

1. DatabaseExplorer (src/admin/database_explorer.py)
2. Core backend logic
3. Database connection management
4. Query execution and validation
5. CSV export functionality

6. Security enforcement (read-only mode)

7. Template (src/admin/templates/database.html)

8. Web UI for database exploration
9. Table browsing interface
10. Search/filter controls
11. Pagination

12. SQL query editor

13. Integration (src/admin/api.py)

14. Routes registered in AdminAPI
15. Accessible at /database

Features

1. Database Selection

Two databases are available: - pqap_dev.db - Trading state (trades, signals, positions, metrics) - market_history.db - Market data (snapshots, orderbooks, price history)

Users can switch between databases using a dropdown selector.

2. Table Browsing

Tables in pqap_dev.db: - trades - Trade execution history (18,911 rows) - signals - Signal generation history (47,635 rows) - strategy_state - Strategy runtime state (7 rows) - position_snapshots - Position snapshots for equity curves - daily_metrics - Daily performance summaries - risk_events - Risk limit breaches - strategy_configs - Strategy configuration history - performance_snapshots - Rolling performance metrics

Tables in market_history.db: - market_snapshots - Market price snapshots (20,550 rows) - market_resolutions - Market resolution results - orderbook_snapshots - Orderbook depth snapshots - price_history - Historical price data - collection_stats - Data collection statistics

Each table card displays: - Table name - Row count - Clickable to view data

3. Data Viewing

When a table is selected: - Schema Display - Shows CREATE TABLE statement - Column Headers - All columns from the table - Data Grid - Paginated rows (default 100 rows per page) - JSON Detection - JSON columns are highlighted and clickable to view formatted - Pagination Controls - Previous/Next navigation

4. Search & Filter

Search functionality supports two modes: - Column-specific: column_name:search_value - Global: Searches across all text columns

Example searches: - strategy_id:dual_arb_v1 - Find trades for specific strategy - market_id:0x123 - Find data for specific market - executed - Global search for "executed" in any column

5. CSV Export

• Export button on every table view
• Downloads filtered data as CSV
• Filename format: {table_name}_{database}.csv
• Respects current search filters
• No row limit (exports all matching rows)

6. Advanced SQL Query

Custom SQL query editor with: - Read-only enforcement - Only SELECT queries allowed - Security validation - Blocks DROP, DELETE, UPDATE, INSERT, ALTER, CREATE - Syntax highlighting - Monospace font for readability - Direct execution - Results displayed inline

Example queries:

SELECT strategy_id, COUNT(*) as trade_count, SUM(CAST(pnl AS REAL)) as total_pnl
FROM trades
WHERE DATE(executed_at) = DATE('2024-01-15')
GROUP BY strategy_id
ORDER BY total_pnl DESC;

Security

Read-Only Mode

1. SQLite PRAGMA - PRAGMA query_only = ON set on all connections
2. Query Validation - Only SELECT statements allowed
3. Keyword Filtering - Dangerous operations blocked
4. No Write Access - Physical impossibility of data modification

Admin-Only Access

• Deployed as part of Admin API (port 8080)
• Same authentication as other admin endpoints
• Not exposed to public internet

Audit Trail

All queries are logged via Python logging: - Database accessed - Table queried - Search parameters - Error messages

Usage Examples

Debugging Trade Execution

1. Navigate to /database
2. Select "Trading Database (pqap_dev.db)"
3. Click "trades" table
4. Search for strategy_id:dual_arb_v1
5. Review recent trades for that strategy

Analyzing Signal Generation

1. Select "signals" table
2. Search for executed:0 to find rejected signals
3. Review rejected_reason column
4. Export to CSV for further analysis

Market Data Analysis

1. Switch to "Market History (market_history.db)"
2. Select "market_snapshots" table
3. Use custom SQL: sql SELECT question, yes_price, no_price, (yes_price + no_price) as sum FROM market_snapshots WHERE ABS((yes_price + no_price) - 1.0) > 0.05 ORDER BY snapshot_time DESC LIMIT 100;
4. Identify arbitrage opportunities

Performance Investigation

1. Select "daily_metrics" table
2. Search for specific date range
3. Review realized_pnl, win_rate, sharpe_ratio
4. Export for charting in external tools

Technical Implementation

Database Connection

def _get_connection(self, db_name: str) -> sqlite3.Connection:
    """Get database connection."""
    db_path = self._get_db_path(db_name)
    conn = sqlite3.connect(str(db_path))
    conn.row_factory = sqlite3.Row  # Dict-like access
    conn.execute("PRAGMA query_only = ON")  # Read-only mode
    return conn

Query Building

Search queries are built dynamically:

query = f"SELECT * FROM {table_name}"
if ":" in search:
    col, val = search.split(":", 1)
    query += f" WHERE {col} LIKE ?"
    params.append(f"%{val}%")
else:
    # Search all columns
    conditions = [f"{col} LIKE ?" for col in columns]
    query += " WHERE " + " OR ".join(conditions)

Template Filters

Custom Jinja2 filters: - is_json - Detect JSON strings for highlighting - number_format - Format numbers with thousand separators

Routes

Future Enhancements

Potential Improvements

1. Query History - Save recent queries for reuse
2. Bookmarks - Save favorite queries
3. Visualizations - Inline charts for numeric data
4. Cross-Database Joins - Query across both databases
5. Export Formats - JSON, Excel support
6. Real-time Updates - Auto-refresh for monitoring
7. Query Templates - Pre-built queries for common tasks
8. Column Filtering - Show/hide specific columns
9. Advanced Filters - Date pickers, numeric ranges
10. Download Schema - Export CREATE TABLE statements

Performance Optimizations

1. Result Caching - Cache frequently accessed queries
2. Lazy Loading - Load data as user scrolls
3. Index Hints - Suggest indexes for slow queries
4. Query Explanation - Show EXPLAIN QUERY PLAN

Monitoring & Observability

The database explorer is monitored through: - Request logs (access patterns) - Error logs (failed queries) - Performance metrics (query execution time) - Usage statistics (most accessed tables)

Best Practices

For Users

1. Start with table browsing before writing custom SQL
2. Use search filters to narrow results before exporting
3. Export large datasets instead of paginating through UI
4. Test queries on small date ranges before expanding
5. Use column-specific search for better performance

For Developers

1. Never disable read-only mode in production
2. Validate all user input before query execution
3. Log all queries for audit trail
4. Test with large datasets to ensure pagination works
5. Handle edge cases (empty tables, malformed JSON)

Testing

Manual Testing Checklist

• [ ] Table listing displays correctly
• [ ] Row counts are accurate
• [ ] Schema display works
• [ ] Basic pagination functions
• [ ] Search filters work (column-specific and global)
• [ ] CSV export downloads
• [ ] Custom SQL executes
• [ ] Dangerous SQL is blocked
• [ ] JSON cells are clickable
• [ ] Navigation between databases works

Automated Tests

Located in tests/unit/test_database_explorer.py (to be created): - Database connection tests - Query validation tests - Security enforcement tests - CSV export tests - Template rendering tests

Deployment

The Database Explorer is automatically deployed when the Admin API starts:

# In src/admin/api.py
self._database_explorer = DatabaseExplorer(base_path=".")
self._database_explorer.setup_routes(self._app)

No additional configuration required. Access at:

http://localhost:8080/database

Conclusion

The Database Explorer provides essential visibility into PQAP's data layer, enabling: - Debugging - Trace issues through raw data - Analysis - Validate strategy performance - Auditing - Review system behavior - Development - Understand data structures

It maintains security through read-only enforcement while providing powerful querying capabilities.