Dual Database System

BugTraceAI uses two independent databases that serve different purposes. Understanding this architecture is essential for deployment, data management, and troubleshooting.

Overview

Database	Component	Role	Stores
SQLite	BugTraceAI-CLI	Source of truth for all scans	Scans, findings, targets, reports, metrics
PostgreSQL	BugTraceAI-WEB	Local to each WEB instance	Chat history, settings, analysis reports

SQLite in the CLI

Location: BugTraceAI-CLI/bugtrace.db (resolved via settings.BASE_DIR)

SQLite is the single source of truth for all scan-related data. Every scan, finding, and report generated by the scanning engine is persisted here.

What SQLite Stores

Scans: Scan metadata, status, configuration, timestamps
Targets: URLs, domains, and endpoints discovered during scanning
Findings: Vulnerabilities with severity, confidence, validation status
Reports: Generated reports in various formats
Metrics: Scan performance data, throughput, latency

Why SQLite

Zero configuration — no separate database server needed
Portable — single file, easy to back up and move
Fast for single-writer workloads (one CLI instance)
No network dependency — works completely offline
Ideal for CLI and headless environments

Accessing SQLite Data

Via the CLI REST API:

# List all scans
curl http://localhost:8000/api/scans

# Get scan findings
curl http://localhost:8000/api/scans/{id}/findings

# Get scan report
curl http://localhost:8000/api/scans/{id}/report/json

Direct access (for debugging):

sqlite3 BugTraceAI-CLI/bugtrace.db ".tables"
sqlite3 BugTraceAI-CLI/bugtrace.db "SELECT * FROM scans;"

PostgreSQL in the WEB

Connection: Configured via DATABASE_URL environment variable in the WEB backend.

PostgreSQL stores data that is local to each WEB instance. It does not store scan data — that lives in SQLite via the CLI.

What PostgreSQL Stores

Chat History: Conversations with the 20+ security toolkit tools
Settings: User preferences, API key references, tool configurations
Analysis Reports: AI-generated analysis reports from the security toolkit
User Data: Authentication credentials, session data

Why PostgreSQL

Robust relational database for structured WEB data
Prisma ORM provides type-safe database access
Supports concurrent connections from the Express backend
Standard technology — no vendor lock-in
Easy to back up and migrate

Accessing PostgreSQL

Via the WEB backend API:

# WEB backend endpoints (port 3001)
GET  /api/chats
GET  /api/settings
GET  /api/analysis-reports

Direct access (for debugging):

docker exec -it <postgres-container> psql -U bugtrace bugtrace_db

The Origin Field

Every scan in SQLite includes an origin field that tracks where the scan was launched from:

Value	Meaning
`cli`	Scan was launched from the CLI directly
`web`	Scan was launched from the WEB dashboard
`unknown`	Origin could not be determined

Design Philosophy

“It’s OK to not know. It’s bad to lie.”

The default value is unknown — never assume origin if it cannot be verified
Only set cli or web when the code path is explicit and certain
This principle extends to all data integrity decisions in BugTraceAI

How the Databases Work Together

The two databases operate autonomously OR together:

Autonomous Mode

Each database functions independently:

BugTraceAI-CLI              BugTraceAI-WEB
+----------------+          +----------------+
| SQLite         |          | PostgreSQL     |
| - Scans        |          | - Chats        |
| - Findings     |   (no    | - Settings     |
| - Reports      | <-link-> | - AI Reports   |
| - Metrics      |          | - User Data    |
+----------------+          +----------------+

Connected Mode (Full Platform)

When WEB connects to CLI API, the WEB reads scan data from SQLite via the API:

BugTraceAI-CLI                        BugTraceAI-WEB
+----------------+   REST API         +----------------+
| SQLite         | <----------------- | PostgreSQL     |
| - Scans        |   /api/scans       | - Chats        |
| - Findings     |   /api/findings    | - Settings     |
| - Reports      |   /api/reports     | - AI Reports   |
| - Metrics      |                    | - User Data    |
+----------------+                    +----------------+

Key points:

WEB reads scan data from CLI via REST API — it does not copy it to PostgreSQL
WEB writes its own local data (chats, settings) to PostgreSQL
Multiple WEB instances can connect to a single CLI server
If CLI goes offline, WEB continues to function with its own tools (no scan management)

Backup and Migration

SQLite Backup

# Simple file copy
cp BugTraceAI-CLI/bugtrace.db bugtrace-backup.db

# SQL dump
sqlite3 BugTraceAI-CLI/bugtrace.db ".dump" > backup.sql

PostgreSQL Backup

# Using pg_dump
docker exec <postgres-container> pg_dump -U bugtrace bugtrace_db > backup.sql

# Restore
docker exec -i <postgres-container> psql -U bugtrace bugtrace_db < backup.sql

Data Portability

All data can be exported in standard formats:

SQLite: .db file or SQL dump
PostgreSQL: SQL dump or CSV export
Scan reports: JSON, HTML, or Markdown via the API

No proprietary formats are used. You can always access and migrate your data.

Parent: Architecture

See also: API Reference | Configuration