KytheraDB -- Architecture

Technical architecture reference for KytheraDB.

---

Table of Contents

1. Project Structure
2. Backend Architecture
3. Frontend Architecture
4. Database Models
5. Service Layer
6. API Route Structure
7. Authentication
8. Job Queue System
9. Preflight Adapter Architecture
10. Terminal Panel Architecture

---

Project Structure

auralith/
├── backend/
│   ├── app/
│   │   ├── main.py                    # FastAPI app, lifespan, router registration
│   │   ├── api/
│   │   │   ├── deps.py                # Dependency injection (auth, plan, project access)
│   │   │   └── routes/                # All API route modules
│   │   │       ├── auth.py
│   │   │       ├── projects.py
│   │   │       ├── connections.py
│   │   │       ├── schemas.py
│   │   │       ├── crud.py
│   │   │       ├── graphs.py
│   │   │       ├── seeds.py
│   │   │       ├── qa_templates.py
│   │   │       ├── anonymization.py
│   │   │       ├── bridge.py
│   │   │       ├── export.py
│   │   │       ├── import_routes.py
│   │   │       ├── validation.py
│   │   │       ├── gdpr.py
│   │   │       ├── mass_patch.py
│   │   │       ├── sql_console.py
│   │   │       ├── performance.py
│   │   │       ├── preflight.py
│   │   │       ├── jobs.py
│   │   │       ├── project_config.py
│   │   │       ├── teams.py
│   │   │       ├── governance.py
│   │   │       ├── plans.py
│   │   │       ├── profiling.py
│   │   │       ├── activity.py
│   │   │       ├── references.py
│   │   │       ├── annotations.py
│   │   │       ├── playground.py
│   │   │       └── health.py
│   │   ├── core/
│   │   │   ├── config.py              # Pydantic Settings (env vars)
│   │   │   ├── database.py            # SQLAlchemy async engine and session
│   │   │   ├── security.py            # JWT token creation/verification
│   │   │   └── encryption.py          # Fernet encryption for connection credentials
│   │   ├── domain/
│   │   │   ├── models/                # SQLAlchemy ORM models
│   │   │   ├── services/              # Business logic layer
│   │   │   ├── schemas/               # Canonical schema types (introspection output)
│   │   │   ├── heuristics/            # Table role detection, domain patterns, enum detection
│   │   │   └── seeders/               # Seed data generation
│   │   │       ├── generators/        # Value generators (type-aware fake data)
│   │   │       ├── strategies/        # Seed strategies (standard, entity, QA)
│   │   │       └── planners/          # FK-aware seed plan builder
│   │   └── infrastructure/
│   │       ├── connectors/            # Database engine connectors
│   │       │   ├── base_connector.py  # Abstract connector interface
│   │       │   ├── postgres_connector.py
│   │       │   ├── mysql_connector.py
│   │       │   ├── sqlite_connector.py
│   │       │   └── ssh_tunnel_connector.py
│   │       └── repositories/          # Data access layer
│   ├── requirements.txt
│   └── Dockerfile
├── frontend/
│   ├── src/
│   │   ├── app/
│   │   │   ├── app.routes.ts          # Route definitions with guards
│   │   │   ├── core/
│   │   │   │   ├── services/          # Angular services (HTTP clients, state)
│   │   │   │   ├── guards/            # Route guards (auth, role, plan, project access)
│   │   │   │   └── models/            # TypeScript interfaces
│   │   │   ├── features/              # Feature modules (one per route)
│   │   │   │   ├── landing/
│   │   │   │   ├── auth/
│   │   │   │   ├── projects/
│   │   │   │   ├── dashboard/
│   │   │   │   ├── connections/
│   │   │   │   ├── schema-viewer/
│   │   │   │   ├── graph-viewer/
│   │   │   │   ├── data-explorer/
│   │   │   │   ├── seed-studio/
│   │   │   │   ├── anonymization/
│   │   │   │   ├── bridge/
│   │   │   │   ├── export/
│   │   │   │   ├── import/
│   │   │   │   ├── validation/
│   │   │   │   ├── gdpr/
│   │   │   │   ├── mass-patch/
│   │   │   │   ├── performance/
│   │   │   │   ├── preflight/
│   │   │   │   ├── profiling/
│   │   │   │   ├── activity/
│   │   │   │   ├── references/
│   │   │   │   ├── governance/
│   │   │   │   ├── teams/
│   │   │   │   ├── admin/
│   │   │   │   ├── settings/
│   │   │   │   ├── pricing/
│   │   │   │   └── terminal-detached/
│   │   │   └── shared/
│   │   │       └── components/
│   │   │           └── sidebar/       # Main navigation component
│   │   └── styles.css                 # Global design system tokens
│   ├── package.json
│   └── Dockerfile
├── docker-compose.yml
└── docs/

---

Backend Architecture

Framework and Patterns

The backend is built with FastAPI using the following architecture patterns:

• Service Layer Pattern: All business logic lives in domain/services/. Routes are thin -- they validate input, call a service, and return the response.
• Repository Pattern: Data access is handled through SQLAlchemy ORM models and the infrastructure/repositories/ layer.
• Connector Pattern: Database engine connectivity is abstracted through infrastructure/connectors/ with a base connector interface.
• Dependency Injection: FastAPI's Depends() system is used for database sessions, authentication, plan checks, and project access validation.

Key Technologies

Component	Technology	Purpose
Web framework	FastAPI 0.115	Async HTTP API with automatic OpenAPI docs
ORM	SQLAlchemy 2.0	Async ORM with asyncpg driver
DB driver	asyncpg 0.30	High-performance async PostgreSQL driver
MySQL driver	PyMySQL 1.1	MySQL/MariaDB connectivity
Validation	Pydantic v2	Request/response schema validation
Auth	PyJWT 2.9	JWT token generation and verification
Passwords	argon2-cffi 23.1	Argon2id password hashing
SQL parsing	sqlglot 30.1	SQL analysis, dialect translation, linting
Fake data	Faker 30.0	Realistic test data generation
Encryption	cryptography 43.0	Fernet symmetric encryption for credentials
SSH	sshtunnel 0.4	SSH tunnel for remote database access
Settings	pydantic-settings	Environment variable management

Request Flow

HTTP Request
  → FastAPI Router
    → Dependency Injection (get_db, get_current_user, require_plan, require_project_access)
      → Route Handler (thin controller)
        → Service Layer (business logic)
          → Connector / ORM (data access)
            → External Database or Platform DB

---

Frontend Architecture

Framework and Patterns

The frontend is built with Angular 21 using modern patterns:

• Standalone Components: No NgModules. Every component is standalone with explicit imports.
• Signals: Angular signals for reactive state management (replacing RxJS subjects for local state).
• Lazy Loading: All feature routes use loadComponent() for code splitting.
• Service-based State: Core services manage HTTP communication and shared state.

Key Technologies

Component	Technology	Purpose
Framework	Angular 21	SPA framework with signals and standalone
Language	TypeScript 5.9	Type-safe JavaScript
CSS	Tailwind CSS v4	Utility-first CSS with custom theme tokens
SQL Editor	CodeMirror 6	SQL editing with syntax highlighting
Graph	Cytoscape.js	Interactive graph visualization
Graph layout	dagre	Hierarchical directed graph layout
HTTP	Angular HttpClient	API communication
Routing	Angular Router	SPA navigation with guards

Component Architecture

Features are organized by route, each in its own directory under features/:

features/
├── mass-patch/
│   ├── mass-patch.component.ts         # List view
│   └── components/
│       ├── patch-form.component.ts     # Create/edit
│       ├── patch-detail.component.ts   # Detail view
│       ├── patch-builder.component.ts  # Builder UI
│       ├── diff-viewer.component.ts    # Before/after diff
│       ├── dry-run-panel.component.ts  # Dry-run results
│       ├── guard-rails-panel.component.ts
│       ├── execution-panel.component.ts
│       ├── export-panel.component.ts
│       ├── sql-analysis-panel.component.ts
│       └── template-builder.component.ts

Route Guards

Guard	Purpose
adminGuard	Requires admin role
superAdminGuard	Requires super_admin role
teamAdminGuard	Requires admin role (team management)
notSuperAdminGuard	Blocks super_admin from project routes
projectAccessGuard	Validates team membership or direct permission

---

Database Models

All models use SQLAlchemy 2.0 declarative style and are registered in domain/models/__init__.py.

Model	Table	Purpose
User	app_users	Platform users with email, password hash, role, plan, email_verified, account_type, stripe_customer_id
Subscription	subscriptions	Stripe subscriptions: plan, billing_cycle, status, period dates
Project	projects	Top-level project container, optional team_id
Connection	connections	Database connection configs (encrypted credentials)
SchemaSnapshot	schema_snapshots	Captured database schema state (JSON)
ReferenceDefinition	reference_definitions	Enum/reference value definitions per column
SeedHistory	seed_histories	Record of seed operations with row counts
SeedScenario	seed_scenarios	QA template definitions for repeatable seeds
AuditLog	audit_logs	Action audit trail (user, action, timestamp)
ProjectMember	project_members	User-project membership
Team	teams	Team groupings for governance
TeamMember	team_members	User-team membership with role
ValidationRule	validation_rules	Data quality rules (regex, not_null, unique, etc)
ValidationScanRun	validation_scan_runs	Scan execution results with violations
Annotation	annotations	User annotations on tables/columns
PatchFiche	patch_fiches	Mass patch definitions (SQL, status, metadata)
PatchSnapshot	patch_snapshots	Before/after data snapshots for patches
GuardRailPolicy	guard_rail_policies	Project-level SQL execution policies
Job	jobs	Async job queue entries (progress, status, result)
AnonymizationConfig	anonymization_configs	Saved anonymization configurations per project
ProjectConfig	project_configs	Project settings (guard rails, environments, etc)
ProjectPermission	project_permissions	Per-user project access grants (READ, WRITE, etc)

---

Service Layer

All services are located in backend/app/domain/services/.

Service	Responsibility
auth_service	User registration, login, JWT verification, password reset
introspection_service	Database schema introspection via connectors
crud_service	Dynamic CRUD operations on user databases
graph_service	Build graph representations of table relationships
seed_service	Standard and entity-aware seed generation
qa_template_service	QA template CRUD and execution
anonymization_service	Column anonymization with multiple strategies
bridge_service	Data transfer between connections
export_service	Data export (CSV, SQL, docs)
import_service	Data import (CSV, SQL, bulk)
validation_service	Validation rule management and scan execution
gdpr_service	Compliance scanning against regulation patterns
patch_service	Mass patch CRUD and workflow management
dry_run_service	Patch dry-run simulation in transaction
snapshot_service	Before/after data snapshot capture
execution_service	Patch execution with progress tracking
patch_execution_engine	Core patch SQL execution engine
guard_rails_service	Guard rail policy evaluation
sql_analysis_service	SQL parsing and risk analysis
sql_console_service	SQL execution, preview, EXPLAIN
sql_parser	SQL statement parsing and classification
sql_playground_service	Legacy SQL playground execution
performance_service	pg_stat_statements analysis, index recommendations
preflight_service	SQL preflight analysis with multi-DB adapters
plan_service	Plan definitions, feature checks, limit validation
job_service	Async job queue management with SSE streaming
connection_service	Connection CRUD and testing
reference_service	Reference data management
profiling_service	Column-level data profiling (distributions, stats)
health_service	Schema health audit
domain_classifier_service	Table domain classification
normalization_service	Schema normalization
capability_service	Per-engine capability detection
migration_service	Migration script generation
email_service	Async email sending via aiosmtplib (Mailpit in dev)
stripe_service	Stripe integration: products, subscriptions, invoices
confirm_service (frontend)	Generic confirmation dialog service (Promise-based)
doc_service	Documentation generation (Markdown, HTML)
changeset_export_service	Liquibase changeset XML export
regulations_registry	Regulation definitions for compliance scanning
entity_view_service	360-degree entity view builder
template_sql_generator	SQL generation from patch templates
snapshot_utils	Snapshot utility functions

---

API Route Structure

All routes are registered in main.py. Routes are grouped by functional module:

Module	Prefix	Auth Required
Health	GET /health	No
Auth	/auth/*	Varies
Projects	/projects/*	Yes
Connections	/projects/{id}/connections/*	Yes
Schemas	/projects/{id}/schema/*	Yes
Data (CRUD)	/projects/{id}/data/*	Yes
Graphs	/projects/{id}/graph/*	Yes
SQL Console	/projects/{id}/sql/*	Yes
Mass Patch	/projects/{id}/patches/*	Yes
Bridge	/projects/{id}/bridge/*	Yes
Anonymization	/projects/{id}/anonymize/*	Yes
Seeds	/projects/{id}/seed/*	Yes
QA Templates	/projects/{id}/qa-templates/*	Yes
Validation	/projects/{id}/validation/*	Yes
Performance	/projects/{id}/performance/*	Yes
Preflight	/projects/{id}/preflight/*	Yes
GDPR	/projects/{id}/gdpr/*	Yes
Export	/projects/{id}/export/*	Yes
Import	/projects/{id}/import/*	Yes
Jobs	/projects/{id}/jobs/*	Yes
Configuration	/projects/{id}/config/*	Yes
Profiling	/projects/{id}/profiling/*	Yes
Activity	/projects/{id}/activity/*	Yes
References	/projects/{id}/references/*	Yes
Annotations	/projects/{id}/annotations/*	Yes
Plans	/plans/*	Yes
Teams	/teams/*	Yes (admin)
Governance	/governance/*	Yes (super_admin)
Billing	/billing/*	Yes

New Auth Endpoints (v2)

Endpoint	Method	Auth	Purpose
/auth/register	POST	No	Register with email verification
/auth/login	POST	No	Login (rejects unverified emails)
/auth/verify-email	POST	No	Verify email with token
/auth/resend-verification	POST	No	Resend verification email
/auth/forgot-password	POST	No	Request password reset
/auth/reset-password	POST	No	Reset password with token

Billing Endpoints

Endpoint	Method	Auth	Purpose
/billing/config	GET	No	Stripe publishable key
/billing/subscribe	POST	Yes	Create subscription + return client_secret
/billing/sync	POST	Yes	Sync subscription status from Stripe
/billing/cancel	POST	Yes	Cancel subscription
/billing/subscription	GET	Yes	Current subscription info
/billing/invoices	GET	Yes	Invoice history from Stripe
/billing/webhook	POST	No	Stripe webhook handler

---

Authentication

Flow

1. Registration: POST /auth/register -- creates user with Argon2id hashed password, returns JWT.
2. Login: POST /auth/login -- verifies credentials, returns JWT.
3. Token Verification: Every authenticated request includes Authorization: Bearer <token>. The get_current_user dependency extracts and verifies the JWT.

Dependencies

get_current_user          # Extract JWT, return User or None
require_role(*roles)      # Check user role (super_admin passes all)
require_plan(min_plan)    # Check user plan meets minimum
require_project_access()  # Check team membership or direct permission

Plan Hierarchy

starter (0) < pro (1) < enterprise (2)

Role Hierarchy

viewer < qa < dev < admin < super_admin

super_admin implicitly passes any role check but is explicitly blocked from project access routes (governance only).

---

Job Queue System

Long-running operations (mass patch execution, bulk imports, bridge transfers) use an async job queue with SSE (Server-Sent Events) streaming.

Architecture

1. Job Creation: A route creates a Job record in the database with status pending.
2. Background Execution: The operation runs asynchronously, updating Job.progress, Job.message, Job.status.
3. SSE Streaming: GET /projects/{id}/jobs/{job_id}/stream opens an SSE connection that polls the job and emits progress events.
4. Completion: When the job finishes, status is set to done (or error), and the SSE stream closes.
5. Cancellation: POST /projects/{id}/jobs/{job_id}/cancel sets status to cancelled.

Job Statuses

pending → running → done
                  → error
                  → cancelled

Job Fields

Field	Type	Description
id	UUID	Unique job identifier
project_id	UUID	Associated project
user_id	UUID	User who initiated the job
type	string	Job type (patch_execute, etc.)
label	string	Human-readable description
status	string	Current status
progress	float	0.0 to 1.0
message	string	Current step description
result	JSON	Final result payload
error	string	Error message if failed
created_at	timestamp	Creation time
started_at	timestamp	Execution start time
completed_at	timestamp	Completion time

---

Preflight Adapter Architecture

The Preflight Guard uses a multi-database adapter pattern to support different SQL dialects and database-specific analysis.

Design

PreflightService
  ├── PostgresAdapter
  │     ├── parse_statement()
  │     ├── estimate_rows()
  │     └── assess_risk()
  ├── MySQLAdapter
  │     ├── parse_statement()
  │     ├── estimate_rows()
  │     └── assess_risk()
  └── SQLiteAdapter
        ├── parse_statement()
        ├── estimate_rows()
        └── assess_risk()

Analysis Pipeline

1. Parse: Extract statement type, affected tables, WHERE clause presence using sqlglot.
2. Estimate: Run EXPLAIN (or equivalent) to estimate affected row count.
3. Environment Check: Apply stricter rules for production-tagged connections.
4. Risk Classification: Assign risk level (safe, low, medium, high, critical).
5. Decision: Return proceed, warn, or block based on risk level and guard rail policies.

---

Terminal Panel Architecture

The SQL Console terminal supports four layout modes to accommodate different workflows:

Layout Modes

Mode	Description
pill	Compact inline button that expands on click
overlay	Floating panel over the current view
terminal	Full-width panel anchored to the bottom of the viewport
detached	Separate browser window (/terminal-detached route)

Detached Terminal

The detached mode opens a new browser window at /terminal-detached. Communication between the main window and the detached terminal uses browser postMessage API or shared state through the Angular service layer.

Editor Stack

CodeMirror 6 Editor
  ├── @codemirror/lang-sql      # SQL language support
  ├── @codemirror/state         # Editor state management
  ├── @codemirror/view          # View layer
  └── @codemirror/theme-one-dark # Dark theme