Database Schema

xrat uses relational databases (SQLite or PostgreSQL) with the same schema across both backends.

Schema Overview

Tables

subscriptions

Tracks import sources (URLs, files, raw text).

CREATE TABLE subscriptions (
    id INTEGER PRIMARY KEY,
    ref TEXT UNIQUE,
    source_url TEXT,
    source_kind TEXT NOT NULL CHECK(source_kind IN ('url', 'file', 'raw_text')),
    name TEXT,
    last_refreshed_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

configs

Stores normalized proxy nodes.

CREATE TABLE configs (
    id INTEGER PRIMARY KEY,
    ref TEXT UNIQUE,
    subscription_id INTEGER REFERENCES subscriptions(id),
    dedup_key TEXT NOT NULL UNIQUE,
    protocol TEXT NOT NULL,
    address TEXT NOT NULL,
    port INTEGER NOT NULL,
    username TEXT,
    uuid TEXT,
    password TEXT,
    method TEXT,
    network TEXT NOT NULL,
    tls TEXT,
    sni TEXT,
    host TEXT,
    path TEXT,
    name TEXT,
    raw_config TEXT NOT NULL,
    extensions_json TEXT,
    is_active INTEGER NOT NULL DEFAULT 0,
    is_enabled INTEGER NOT NULL DEFAULT 1,
    is_deleted INTEGER NOT NULL DEFAULT 0,
    imported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Indexes:

• dedup_key — UNIQUE
• subscription_id — FK index
• is_enabled, is_active — filter queries
• is_deleted — soft-delete queries

connection_tests

Stores individual test results per config.

CREATE TABLE connection_tests (
    id INTEGER PRIMARY KEY,
    run_id INTEGER REFERENCES connection_test_runs(id),
    config_id INTEGER NOT NULL REFERENCES configs(id),
    icmp_ok INTEGER,
    icmp_ms INTEGER,
    tcp_ok INTEGER,
    tcp_ms INTEGER,
    real_delay_ok INTEGER,
    real_delay_ms INTEGER,
    connect_ms INTEGER,
    ttfb_ms INTEGER,
    http_status INTEGER,
    download_mbps REAL,
    upload_mbps REAL,
    failure_kind TEXT,
    failure_reason TEXT,
    dial_endpoint_ip TEXT,
    dial_endpoint_location TEXT,
    dial_endpoint_country TEXT,
    dial_endpoint_asn TEXT,
    dial_endpoint_geoip_source TEXT,
    dial_endpoint_fronting TEXT,
    tested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Indexes:

• config_id — per-config queries
• run_id — per-run queries
• (config_id, tested_at) — latest test per config

connection_test_runs

Groups test results into batches.

CREATE TABLE connection_test_runs (
    id INTEGER PRIMARY KEY,
    kind TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

runtime_sessions

Tracks proxy process lifecycle.

CREATE TABLE runtime_sessions (
    id INTEGER PRIMARY KEY,
    config_id INTEGER REFERENCES configs(id),
    status TEXT NOT NULL
        CHECK(status IN ('starting', 'running', 'stopping', 'stopped', 'failed')),
    socks_host TEXT,
    socks_port INTEGER,
    http_host TEXT,
    http_port INTEGER,
    shadowsocks_host TEXT,
    shadowsocks_port INTEGER,
    process_id INTEGER,
    failure_reason TEXT,
    owner_kind TEXT,
    owner_instance_id TEXT,
    last_transition_reason_code TEXT,
    last_transition_reason_detail TEXT,
    last_transition_origin TEXT,
    cooldown_until TEXT,
    last_failed_at TEXT,
    last_failed_reason_code TEXT,
    started_at TIMESTAMP,
    stopped_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Indexes:

• config_id — per-config queries
• status — running session lookup
• (owner_kind, owner_instance_id) — daemon reattach queries

cf_scan_results

Stores IP scan results.

CREATE TABLE cf_scan_results (
    id INTEGER PRIMARY KEY,
    ip TEXT NOT NULL UNIQUE,
    latency_ms INTEGER,
    download_mbps REAL,
    upload_mbps REAL,
    error TEXT,
    last_scanned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

events

Structured application/runtime event log surfaced by xrat logs.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    level TEXT NOT NULL,
    source TEXT NOT NULL,
    kind TEXT NOT NULL,
    config_id INTEGER,
    session_id INTEGER,
    message TEXT NOT NULL,
    detail TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Rows are inserted fire-and-forget; a failed insert is logged but never breaks the operation that produced the event.

Migrations

Migrations are run automatically on startup using SQLx.

Migration List

Migration Location

migrations/sqlite/0001_init.sql
migrations/sqlite/0002_add_connection_test_download_mbps.sql
...
migrations/sqlite/0015_add_config_soft_delete.sql

PostgreSQL migrations have equivalent files in migrations/postgres/.

Schema Diagram

subscriptions
    │
    ├── configs (1:N via subscription_id)
    │       │
    │       ├── connection_tests (1:N via config_id)
    │       │       │
    │       │       └── connection_test_runs (1:N via run_id)
    │       │
    │       └── runtime_sessions (1:N via config_id)
    │
    └── (none)

cf_scan_results (standalone, not linked to configs)