100 lines
2.6 KiB
Markdown
100 lines
2.6 KiB
Markdown
# ABE — Database Specification (SQLite)
|
|
|
|
## Rationale
|
|
File-based storage loses all data on container restart.
|
|
SQLite requires zero extra services and is perfect for self-hosted deployment.
|
|
|
|
## Library
|
|
Use `better-sqlite3` (synchronous, faster than async alternatives for this use case).
|
|
|
|
## Location
|
|
Database file: `data/abe.db` (persisted via Docker volume)
|
|
|
|
## Schema
|
|
|
|
### Table: sessions
|
|
```sql
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id TEXT PRIMARY KEY,
|
|
url TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'running',
|
|
seed INTEGER NOT NULL,
|
|
max_states INTEGER NOT NULL DEFAULT 50,
|
|
states_visited INTEGER NOT NULL DEFAULT 0,
|
|
anomalies_found INTEGER NOT NULL DEFAULT 0,
|
|
started_at INTEGER NOT NULL,
|
|
finished_at INTEGER,
|
|
config_json TEXT NOT NULL DEFAULT '{}'
|
|
);
|
|
```
|
|
|
|
### Table: states
|
|
```sql
|
|
CREATE TABLE IF NOT EXISTS states (
|
|
id TEXT PRIMARY KEY,
|
|
session_id TEXT NOT NULL REFERENCES sessions(id),
|
|
url TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
dom_snapshot_path TEXT,
|
|
visit_count INTEGER NOT NULL DEFAULT 0,
|
|
discovered_at INTEGER NOT NULL
|
|
);
|
|
```
|
|
|
|
### Table: actions
|
|
```sql
|
|
CREATE TABLE IF NOT EXISTS actions (
|
|
id TEXT PRIMARY KEY,
|
|
session_id TEXT NOT NULL REFERENCES sessions(id),
|
|
state_id TEXT NOT NULL REFERENCES states(id),
|
|
type TEXT NOT NULL,
|
|
selector TEXT,
|
|
value TEXT,
|
|
url TEXT,
|
|
seed INTEGER NOT NULL,
|
|
executed_at INTEGER NOT NULL,
|
|
sequence_order INTEGER NOT NULL
|
|
);
|
|
```
|
|
|
|
### Table: anomalies
|
|
```sql
|
|
CREATE TABLE IF NOT EXISTS anomalies (
|
|
id TEXT PRIMARY KEY,
|
|
session_id TEXT NOT NULL REFERENCES sessions(id),
|
|
type TEXT NOT NULL,
|
|
severity TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
action_trace_json TEXT NOT NULL,
|
|
evidence_json TEXT NOT NULL,
|
|
screenshot_path TEXT,
|
|
dom_snapshot_path TEXT,
|
|
detected_at INTEGER NOT NULL
|
|
);
|
|
```
|
|
|
|
### Table: notifications
|
|
```sql
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id TEXT PRIMARY KEY,
|
|
anomaly_id TEXT NOT NULL REFERENCES anomalies(id),
|
|
channel TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
sent_at INTEGER,
|
|
error TEXT
|
|
);
|
|
```
|
|
|
|
## Repository Pattern
|
|
|
|
Create `src/db/` with:
|
|
- `src/db/connection.ts` — singleton SQLite connection, runs migrations on startup
|
|
- `src/db/SessionRepository.ts` — CRUD for sessions
|
|
- `src/db/AnomalyRepository.ts` — CRUD for anomalies, includes filter by session/severity
|
|
- `src/db/migrations.ts` — runs all CREATE TABLE IF NOT EXISTS on startup
|
|
|
|
## Rules
|
|
- All DB operations are synchronous (better-sqlite3 is sync)
|
|
- Repositories are injected into the API server, never imported directly by core engine
|
|
- The engine emits events → the API server listens and persists to DB
|