Sam Rolfe
c83c9df0a3
- Go service with its own multi-stage Dockerfile and docker-compose.yml; runs as container webrestrict-control on port 8791. - SQLite schema for the full AGENTS.md §7 data model via versioned migrations (PRAGMA user_version); audit log is append-only. - Configuration from the process environment; the real env file is gitignored and a template is committed. No secrets in the repo. - Separate session login (signed HMAC cookie + CSRF) for Sam. - GET /health; HTMX + server-rendered dashboard styled with the Family Home Lab tokens.css (copied) and Inter fonts; dark mode; phone-friendly. - Caddy vhost for webrestrict.lab.audasmedia.com.au applied on .35 (Let's Encrypt cert issued); route recorded in deploy/caddy/webrestrict.caddy. - Unit tests for config, auth and store. Co-Authored-By: Paperclip <noreply@paperclip.ing>
90 lines
3.3 KiB
SQL
90 lines
3.3 KiB
SQL
-- Migration 001 — Phase 1 foundation (AGENTS.md §7).
|
|
-- Tables may grow in later phases. Never hard-delete devices or history.
|
|
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
child TEXT NOT NULL,
|
|
platform TEXT NOT NULL CHECK (platform IN ('android', 'windows')),
|
|
topic TEXT NOT NULL UNIQUE,
|
|
online INTEGER NOT NULL DEFAULT 0,
|
|
last_seen TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS schedules (
|
|
id INTEGER PRIMARY KEY,
|
|
device_id INTEGER NOT NULL REFERENCES devices(id),
|
|
weekday INTEGER NOT NULL CHECK (weekday BETWEEN 0 AND 6), -- 0 = Monday
|
|
start_time TEXT NOT NULL, -- HH:MM
|
|
end_time TEXT NOT NULL, -- HH:MM
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_schedules_device ON schedules(device_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS blocklist (
|
|
id INTEGER PRIMARY KEY,
|
|
device_id INTEGER REFERENCES devices(id), -- NULL = applies to every device
|
|
kind TEXT NOT NULL CHECK (kind IN ('app', 'site')),
|
|
value TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_blocklist_device ON blocklist(device_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS allowlist (
|
|
id INTEGER PRIMARY KEY,
|
|
device_id INTEGER REFERENCES devices(id), -- NULL = applies to every device
|
|
kind TEXT NOT NULL CHECK (kind IN ('app', 'site')),
|
|
value TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_allowlist_device ON allowlist(device_id);
|
|
|
|
-- Desired state per device. The Control Service is the only writer.
|
|
CREATE TABLE IF NOT EXISTS state (
|
|
device_id INTEGER PRIMARY KEY REFERENCES devices(id),
|
|
locked INTEGER NOT NULL DEFAULT 0,
|
|
until TEXT,
|
|
allow_json TEXT,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS telemetry (
|
|
id INTEGER PRIMARY KEY,
|
|
device_id INTEGER NOT NULL REFERENCES devices(id),
|
|
app TEXT,
|
|
domain TEXT,
|
|
seconds INTEGER NOT NULL DEFAULT 0,
|
|
day TEXT NOT NULL, -- YYYY-MM-DD, local day
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_telemetry_device_day ON telemetry(device_id, day);
|
|
|
|
CREATE TABLE IF NOT EXISTS overrides (
|
|
id INTEGER PRIMARY KEY,
|
|
device_id INTEGER NOT NULL REFERENCES devices(id),
|
|
actor TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
until TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_overrides_device ON overrides(device_id);
|
|
|
|
-- Append-only audit log. Size-capped by a later phase, never edited in place.
|
|
CREATE TABLE IF NOT EXISTS audit (
|
|
id INTEGER PRIMARY KEY,
|
|
actor TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
device_id INTEGER REFERENCES devices(id),
|
|
detail TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit(created_at);
|