name: db-explorer-architect
description: Architect and develop the DB Explorer project with Python backend and React frontend following hexagonal architecture
argument-hint: [component] [task]
Database Explorer Architect
Goal
Build and maintain a full-stack database exploration tool with:
- Backend: Python (FastAPI + hexagonal architecture) for robust, type-safe database connectivity
- Frontend: React TypeScript with modern tooling (Vite, Vitest) for responsive UI
- Architecture: Hexagonal/Ports & Adapters pattern ensuring clean separation of concerns
- Databases: Multi-database support (Oracle, ClickHouse, Databricks) via pluggable adapters
This skill provides comprehensive workflows, coding standards, and tools for developing both backend and frontend components while preserving architectural boundaries.
Project Structure
db-explorer/
├── README.md # Project overview and setup
├── docker-compose.yml # Container orchestration
├── .gitignore # Git ignore patterns
├── .pre-commit-config.yaml # Pre-commit hooks
│
├── backend/ # Python Backend (FastAPI)
│ ├── pyproject.toml # Poetry dependencies and config
│ ├── poetry.lock # Locked dependencies
│ ├── pytest.ini # Pytest configuration
│ ├── .env.example # Environment variables template
│ │
│ ├── src/ # Source code
│ │ ├── main.py # FastAPI application entry point
│ │ ├── config.py # Application configuration
│ │ │
│ │ ├── core/ # Domain Layer (Pure Python, no external deps)
│ │ │ ├── domain/ # Domain models and types
│ │ │ │ ├── types.py # UniversalDataType, value objects
│ │ │ │ └── models.py # Domain entities
│ │ │ └── ports/ # Port interfaces
│ │ │ └── database.py # DatabasePort interface
│ │ │
│ │ ├── application/ # Application Layer (Use Cases)
│ │ │ ├── query_executor.py # Query execution service
│ │ │ └── schema_reader.py # Schema reading service
│ │ │
│ │ └── adapters/ # Adapters Layer (External dependencies)
│ │ ├── driving/ # Driving adapters (API, CLI)
│ │ │ └── api/
│ │ │ └── v1/ # FastAPI routes
│ │ │ ├── queries.py
│ │ │ └── schemas.py
│ │ │
│ │ └── driven/ # Driven adapters (Database connectors)
│ │ ├── oracle.py # Oracle connector
│ │ ├── clickhouse.py # ClickHouse connector
│ │ ├── databricks.py # Databricks connector
│ │ └── factory.py # Connector factory
│ │
│ └── tests/ # Test suite
│ ├── conftest.py # Pytest fixtures
│ ├── unit/ # Unit tests
│ ├── integration/ # Integration tests
│ └── test_data/ # Test fixtures and data
│
├── web-ui/ # React TypeScript Frontend
│ ├── package.json # npm dependencies
│ ├── tsconfig.json # TypeScript configuration
│ ├── vite.config.ts # Vite build configuration
│ ├── .env.example # Environment variables template
│ ├── index.html # HTML entry point
│ ├── public/ # Static assets
│ │
│ └── src/ # Source code
│ ├── main.tsx # React entry point
│ ├── App.tsx # Root component
│ ├── vite-env.d.ts # Vite type definitions
│ │
│ ├── components/ # React components
│ │ ├── QueryEditor/ # SQL editor component
│ │ ├── SchemaExplorer/ # Schema browser component
│ │ └── ResultsTable/ # Results display component
│ │
│ ├── hooks/ # Custom React hooks
│ │ ├── useQuery.ts # Query execution hook
│ │ └── useSchema.ts # Schema fetching hook
│ │
│ ├── services/ # API service layer
│ │ ├── api.ts # Axios client setup
│ │ ├── queryService.ts # Query API calls
│ │ └── schemaService.ts # Schema API calls
│ │
│ ├── types/ # TypeScript type definitions
│ │ ├── query.ts # Query-related types
│ │ └── schema.ts # Schema-related types
│ │
│ ├── utils/ # Utility functions
│ └── styles/ # Global styles
│
└── skills/ # Claude Code skills
├── agent-architect/ # General agent skill
└── db-explorer-architect/ # This skill
├── SKILL.md # Main skill documentation
├── scripts/ # Automation scripts
│ ├── lint_backend.sh # Backend code quality
│ └── lint_frontend.sh # Frontend code quality
└── references/ # Reference documentation
├── architecture.md # Architecture rules
├── design-patterns.md # Design patterns
├── python-standards.md # Python coding standards
└── react-standards.md # React coding standards
Workflow
1. Backend Development (Python)
Location: All Python code resides in the backend/ directory.
Entry Point: backend/src/main.py - FastAPI application
Architecture: Hexagonal (Ports & Adapters)
- Domain Layer (
src/core/domain): Pure Python, zero external dependencies
- Ports (
src/core/ports): Abstract interfaces (e.g., DatabasePort)
- Application (
src/application): Use cases orchestrating domain logic, including the CleaningEngine
- Adapters (
src/adapters): External integrations (FastAPI, databases)
Data Flow (fetch → clean → serve):
API (FastAPI) → DataService → CleaningEngine → DatabasePort → Remote DB
DatabasePort fetches raw, unmodified rows from the remote database (read-only).
CleaningEngine (src/application/cleaning_engine.py) transforms raw rows in memory:
- Normalization: standardize string encoding, trim whitespace, unify
NULL-like values.
- Deduplication: remove exact-duplicate rows before returning to the API.
- Type Casting: map provider-specific types to
UniversalDataType values.
- API returns cleaned, uniformly-formatted JSON — the frontend always receives a consistent schema.
Read-Only Enforcement:
- Every
DatabasePort implementation must use a read-only connection (e.g., SET TRANSACTION READ ONLY, readonly=True driver option, or a DB user with SELECT-only privileges).
- The
execute_safe_read method must parse the SQL statement and raise ReadOnlyViolationError if it detects INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CREATE, or MERGE keywords before sending the query to the database.
Adding New Database Connector:
- Create adapter:
backend/src/adapters/driven/[db-name].py
- Implement
DatabasePort interface from src/core/ports/database.py; enforce read-only mode in connect().
- Add to factory:
backend/src/adapters/driven/factory.py
- Add integration tests:
backend/tests/integration/test_[db-name].py
- Update configuration:
backend/src/config.py
Code Quality Standards:
- Formatting: Black (line-length 100)
- Import Sorting: isort (--profile black)
- Type Checking: Mypy (--strict)
- Linting: Ruff
- Testing: Pytest with 80%+ coverage
- Security: Safety check for vulnerabilities
Configuration: backend/pyproject.toml
Commands (run from backend/ directory):
black src/ tests/ --line-length 100
isort src/ tests/ --profile black
mypy src/ --strict
ruff check src/ tests/
pytest tests/ --cov=src --cov-report=term-missing
../skills/db-explorer-architect/scripts/lint_backend.sh --fix
2. Frontend Development (React + TypeScript)
Location: All React code resides in the web-ui/ directory.
Entry Point: web-ui/src/main.tsx - React application root
Technology Stack:
- React 18+ with TypeScript
- Build Tool: Vite
- Testing: Vitest
- HTTP Client: Axios
- Styling: CSS Modules / Tailwind
Code Quality Standards:
- Linting: ESLint with React & TypeScript plugins
- Formatting: Prettier (semi, singleQuote, printWidth: 100)
- Type Checking: TypeScript strict mode
- Testing: Vitest with React Testing Library
Configuration: web-ui/tsconfig.json, web-ui/vite.config.ts
Commands (run from web-ui/ directory):
npm run dev
npm run lint
npm run lint:fix
npm run format
npm run format:check
npm run type-check
npm run test
npm run test:run
npm run build
../skills/db-explorer-architect/scripts/lint_frontend.sh --fix
Adding New React Component:
- Create directory:
web-ui/src/components/[ComponentName]/
- Create files:
[ComponentName].tsx - Component implementation
[ComponentName].module.css - Styles (optional)
[ComponentName].test.tsx - Tests
index.ts - Barrel export
- Follow component standards (see
references/react-standards.md)
- Add tests with React Testing Library
3. User Cleaning & View Rules
Users can configure how fetched data is displayed and cleaned without touching the remote database. All rules are applied client-side or by the CleaningEngine in the backend.
Supported cleaning / view rules (examples):
- Hide null values: rows or cells where a value is
NULL / empty are excluded from the displayed result set.
- Standardize date format: all
TIMESTAMP / date columns are converted to ISO-8601 (YYYY-MM-DDTHH:mm:ssZ) before display.
- Trim whitespace: leading and trailing whitespace is stripped from string columns.
- Column aliasing: users can rename columns in the UI without changing the underlying query.
- Type override: force a column to be displayed as a specific
UniversalDataType when the connector returns an ambiguous type.
How rules are applied (backend flow):
- The API receives a
CleaningConfig alongside the SQL query.
DataService forwards raw rows from DatabasePort to CleaningEngine.apply(rows, config).
CleaningEngine applies each enabled rule in order and returns the cleaned List[UniversalRow].
- The API serialises the result to JSON and returns it to the frontend.
Frontend contract: the frontend always receives a UniversalFormat response — a list of objects with { column: string, type: UniversalDataType, value: any } entries — regardless of the source database.
4. Integration & Deployment
Docker Setup:
- File:
docker-compose.yml at project root
- Services: backend (FastAPI), frontend (Nginx/Vite), databases (dev)
Environment Configuration:
API Communication:
- Frontend calls backend at
http://localhost:8000/api/v1
- CORS configured in
backend/src/main.py:
app.add_middleware(
CORSMiddleware,
allow_origins=["http://localhost:5173"],
allow_credentials=True,
allow_methods=["*"],
allow_headers=["*"],
)
Deployment:
docker-compose up -d
cd backend && uvicorn src.main:app --reload
cd web-ui && npm run dev
Code Quality Standards
Backend (Python)
- Formatting: Black with line-length 100
- Import Sorting: isort with --profile black
- Type Checking: Mypy with --strict mode
- Linting: Ruff for fast, comprehensive checks
- Testing: Pytest with 80%+ coverage
- Security: Safety check for dependency vulnerabilities
- Documentation: Google-style docstrings (PEP 257)
See references/python-standards.md for complete Python coding standards.
Frontend (React + TypeScript)
- Formatting: Prettier (semi, singleQuote, printWidth: 100)
- Linting: ESLint with React, TypeScript, and accessibility plugins
- Type Checking: TypeScript strict mode
- Testing: Vitest with React Testing Library
- Component Structure: Feature-based organization with colocated tests
- Naming: PascalCase for components, camelCase for functions
See references/react-standards.md for complete React/TypeScript coding standards.
Design Patterns
Backend Patterns
- Hexagonal Architecture: Ports & Adapters for clean separation
- Factory Pattern: Connector creation via ConnectorFactory
- Adapter Pattern: Database-specific implementations of DatabasePort
- Strategy Pattern: Pluggable query execution strategies
- Iterator Pattern: Streaming query results with generators
Frontend Patterns
- Component Composition: Reusable, composable React components
- Custom Hooks: Encapsulate stateful logic (useQuery, useSchema)
- Service Layer: Centralized API communication
- Context API: Global state management (auth, theme)
- Error Boundaries: Graceful error handling
See references/design-patterns.md for detailed pattern implementations.
Scripts
Backend Scripts
Frontend Scripts
Combined Scripts
scripts/test_all.sh: Run all tests (backend + frontend)
scripts/docker_dev.sh: Start development environment with Docker
References
Comprehensive documentation for architecture, patterns, and coding standards:
references/architecture.md: Hexagonal architecture rules, layer dependencies, DatabasePort interface contract, streaming & transport requirements, security & secrets management
references/python-standards.md: Python coding standards including:
- Code style, formatting, and linting (Black, isort, Ruff)
- Type hinting best practices (PEP 484, 585, 604)
- Google-style docstrings (PEP 257)
- Error handling and SOLID principles
- Testing requirements (pytest, 80% coverage)
- Performance, security, and logging standards
references/react-standards.md: React/TypeScript coding standards including:
- TypeScript strict mode configuration
- Component structure and organization
- Custom hooks best practices
- API service layer patterns
- Testing with Vitest and React Testing Library
- Performance optimization (memoization, code splitting)
- Accessibility guidelines
references/design-patterns.md: Design pattern implementations:
- Creational: Factory, Builder
- Structural: Adapter, Decorator
- Behavioral: Strategy, Iterator, Observer
Development Workflow
Adding a New Database Connector
-
Create Adapter (backend/src/adapters/driven/[db-name].py):
from src.core.ports.database import DatabasePort
class MyDBConnector(DatabasePort):
def connect(self) -> None: ...
def execute_query_stream(self, sql: str, params=None): ...
def fetch_schema(self, table: str): ...
-
Register in Factory (backend/src/adapters/driven/factory.py):
CONNECTORS = {
"mydb": MyDBConnector,
}
-
Add Configuration (backend/src/config.py):
class MyDBConfig(BaseModel):
host: str
port: int = 5432
database: str
-
Write Tests (backend/tests/integration/test_mydb.py):
def test_mydb_connection():
connector = MyDBConnector(config)
connector.connect()
assert connector.is_connected()
Adding a New API Endpoint
-
Define Route (backend/src/adapters/driving/api/v1/[resource].py):
@router.post("/queries/execute")
async def execute_query(request: QueryRequest):
result = query_executor.execute(request.sql)
return {"data": result}
-
Frontend Service (web-ui/src/services/queryService.ts):
export const executeQuery = async (sql: string): Promise<QueryResult> => {
const response = await api.post('/queries/execute', { sql });
return response.data;
};
-
React Hook (web-ui/src/hooks/useQuery.ts):
export const useQuery = () => {
const [data, setData] = useState<QueryResult | null>(null);
const execute = useCallback(async (sql: string) => {
const result = await executeQuery(sql);
setData(result);
}, []);
return { data, execute };
};
Adding a New React Component
-
Create Component Directory:
web-ui/src/components/MyComponent/
├── MyComponent.tsx
├── MyComponent.module.css
├── MyComponent.test.tsx
└── index.ts
-
Implement Component (MyComponent.tsx):
import React from 'react';
import styles from './MyComponent.module.css';
interface MyComponentProps {
title: string;
onAction: () => void;
}
export const MyComponent: React.FC<MyComponentProps> = ({ title, onAction }) => {
return (
<div className={styles.container}>
<h2>{title}</h2>
<button onClick={onAction}>Action</button>
</div>
);
};
-
Add Tests (MyComponent.test.tsx):
import { render, screen, fireEvent } from '@testing-library/react';
import { MyComponent } from './MyComponent';
describe('MyComponent', () => {
it('calls onAction when button clicked', () => {
const mockAction = vi.fn();
render(<MyComponent title="Test" onAction={mockAction} />);
fireEvent.click(screen.getByRole('button'));
expect(mockAction).toHaveBeenCalled();
});
});
Environment Variables
Backend (.env)
DATABASE_URL=oracle://user:password@host:1521/service
CLICKHOUSE_URL=http://localhost:8123
DATABRICKS_HOST=your-workspace.cloud.databricks.com
DATABRICKS_TOKEN=your-token
LOG_LEVEL=INFO
ENVIRONMENT=development
SECRET_KEY=your-secret-key
CORS_ORIGINS=http://localhost:5173,http://localhost:3000
MAX_QUERY_ROWS=10000
QUERY_TIMEOUT_SECONDS=300
Frontend (.env)
VITE_API_BASE_URL=http://localhost:8000/api/v1
VITE_ENABLE_QUERY_HISTORY=true
VITE_ENABLE_SCHEMA_EXPLORER=true
VITE_APP_TITLE=DB Explorer
VITE_MAX_RESULT_ROWS=1000
Quick Start
Initial Setup
-
Clone Repository:
git clone https://github.com/your-org/db-explorer.git
cd db-explorer
-
Backend Setup:
cd backend
poetry install
cp .env.example .env
poetry run alembic upgrade head
poetry run uvicorn src.main:app --reload
-
Frontend Setup:
cd web-ui
npm install
cp .env.example .env
npm run dev
-
Access Application:
Development Workflow
-
Create Feature Branch:
git checkout -b feature/your-feature-name
-
Make Changes: Follow coding standards for backend or frontend
-
Run Quality Checks:
cd backend
../skills/db-explorer-architect/scripts/lint_backend.sh --fix
cd web-ui
../skills/db-explorer-architect/scripts/lint_frontend.sh --fix
-
Run Tests:
cd backend && pytest tests/ -v
cd web-ui && npm run test:run
-
Commit and Push:
git add .
git commit -m "feat: add your feature"
git push origin feature/your-feature-name
-
Create Pull Request: Follow PR template guidelines