Database Observability Platform - MVP

A single-tenant database observability platform that ingests, parses, and analyzes SQL queries.

Design direction: this project is evolving into an OpenLineage-native observability backend for data pipelines AND warehouses ("Jaeger for OpenLineage" + first-class query workload analytics). See docs/data-observability/ for the full design, phased task breakdown, and codebase audit.

Features

• User Authentication: Clerk-based authentication for human users
• Database Registration: Register and manage database connections from the UI
• Agent Integration: Generate per-database agent tokens for secure query ingestion
• Query Ingestion: REST API endpoint for agents to send batches of query logs
• SQL Parsing: Parse SQL queries using SQLGlot to extract tables, columns, and operations
• Query Analysis: View queries with detailed parsing results and metadata

Architecture

Backend (Python/FastAPI)

• FastAPI: REST API framework
• SQLAlchemy: ORM for PostgreSQL
• Alembic: Database migrations
• SQLGlot: SQL parsing engine
• Clerk: JWT authentication

Frontend (React/TypeScript)

• React 18: UI framework
• Vite: Build tool
• TailwindCSS: Styling
• Clerk React: Authentication
• React Router: Client-side routing

Setup Instructions

Prerequisites

• Python 3.12+
• Node.js 18+
• PostgreSQL 14+
• Clerk account (for authentication)

1. Clone the Repository

git clone <repository-url>
cd baawm-app

2. Database Setup

Create a PostgreSQL database:

createdb baawm_db

Or using SQL:

CREATE DATABASE baawm_db;

3. Backend Setup

Install Dependencies

cd backend
pip install -r requirements.txt

Configure Environment

Create a .env file in the backend directory:

cp .env.example .env

Edit .env with your settings:

# Database
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/baawm_db

# Clerk Authentication
CLERK_JWKS_URL=https://your-clerk-domain.clerk.accounts.dev/.well-known/jwks.json
CLERK_ISSUER=https://your-clerk-domain.clerk.accounts.dev

# API
API_PREFIX=/api
DEBUG=True

To get your Clerk configuration:

1. Sign up at clerk.com
2. Create a new application
3. Copy the JWKS URL and Issuer from the JWT Template settings

Run Migrations

cd backend
alembic upgrade head

Start the Backend Server

cd backend
uvicorn app.main:app --reload --port 8000

The API will be available at http://localhost:8000

4. Frontend Setup

Install Dependencies

cd frontend
npm install

Configure Environment

Create a .env file in the frontend directory:

cp .env.example .env

Edit .env with your Clerk publishable key:

VITE_CLERK_PUBLISHABLE_KEY=pk_test_your_clerk_publishable_key_here
VITE_API_URL=http://localhost:8000

Get your Clerk publishable key from your Clerk dashboard.

Start the Frontend Development Server

cd frontend
npm run dev

The frontend will be available at http://localhost:5173

Usage

1. Sign Up / Sign In

Navigate to http://localhost:5173 and sign up using Clerk authentication.

2. Register a Database

1. Click "Add Database" on the Databases page
2. Enter a name, description, and select the engine type
3. Save the agent token shown (it will only be displayed once)

3. Configure Your Agent

Use the agent token to configure your data collection agent. Example:

export OBS_PLATFORM_URL="http://localhost:8000"
export OBS_DATABASE_ID="<your-database-id>"
export OBS_AGENT_TOKEN="<your-agent-token>"

curl -X POST "$OBS_PLATFORM_URL/api/ingest/batch" \
  -H "Content-Type: application/json" \
  -H "X-Agent-Token: $OBS_AGENT_TOKEN" \
  -d '{
    "queries": [
      {
        "external_id": "test:1",
        "text": "SELECT id, email FROM users WHERE active = true",
        "db_user": "app_user",
        "app_name": "my-app",
        "started_at": "2025-01-15T10:15:30.123Z",
        "duration_ms": 42,
        "rows": 100,
        "cpu_time_ms": 30,
        "io_read_bytes": 8192,
        "io_write_bytes": 0
      }
    ]
  }'

4. Parse Queries

After ingesting queries, trigger parsing:

curl -X POST "http://localhost:8000/api/maintenance/parse-new-queries" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer <your-clerk-jwt-token>" \
  -d '{
    "limit": 500
  }'

Or use the UI to trigger parsing manually (future feature).

5. View Queries

Navigate to the Queries page to view ingested queries with their parsing results.

API Documentation

Once the backend is running, visit http://localhost:8000/docs for interactive API documentation (Swagger UI).

Key Endpoints

• GET /health - Health check (public)
• GET /api/me - Get current user
• GET /api/databases - List databases
• POST /api/databases - Create database
• GET /api/databases/{id} - Get database details
• POST /api/databases/{id}/rotate-agent-token - Rotate agent token
• POST /api/ingest/batch - Ingest query batch (agent token auth)
• GET /api/queries - List queries
• GET /api/queries/{id} - Get query detail
• POST /api/maintenance/parse-new-queries - Parse unparsed queries

Project Structure

baawm-app/
├── backend/
│   ├── alembic/              # Database migrations
│   ├── app/
│   │   ├── api/
│   │   │   ├── routes/       # API route handlers
│   │   │   └── schemas/      # Pydantic schemas
│   │   ├── core/             # Core configuration
│   │   ├── models/           # SQLAlchemy models
│   │   └── services/         # Business logic (parsing, etc.)
│   ├── requirements.txt      # Python dependencies
│   └── alembic.ini          # Alembic configuration
├── frontend/
│   ├── src/
│   │   ├── components/       # React components
│   │   ├── pages/           # Page components
│   │   ├── services/        # API client
│   │   ├── types/           # TypeScript types
│   │   ├── App.tsx          # Main app component
│   │   └── main.tsx         # Entry point
│   ├── package.json         # Node dependencies
│   └── vite.config.ts       # Vite configuration
└── README.md

Database Schema

Tables

• users: User accounts (synced with Clerk)
• databases: Registered database connections
• raw_queries: Ingested query logs
• parsed_queries: Query parsing results
• query_tables: Tables referenced in queries
• query_columns: Columns referenced in queries

Development

Running Tests

Backend tests (when implemented):

cd backend
pytest

Frontend tests (when implemented):

cd frontend
npm test

Building for Production

Backend:

cd backend
# Deploy using your preferred method (Docker, systemd, etc.)

Frontend:

cd frontend
npm run build
# Serve the dist/ directory

Future Enhancements

• Background job processing for parsing
• Advanced query analytics and insights
• Query performance tracking over time
• Data lineage visualization
• Multi-database comparison
• Alerts and notifications
• Query optimization recommendations

License

MIT

Support

For issues and questions, please open an issue on GitHub.