Frank Schwenk
88bdf6f34b
- Split monolithic app.py into server/api/ routers - Add Pydantic schemas for input validation - Add /agent/* API with separate AGENT_TOKEN auth - Replace static schema.sql with versioned migrations (datenerhaltend) - Add tools/backup_db.py for SQLite backups - Add Dockerfile HEALTHCHECK and compose.yml AGENT_TOKEN env - Add pytest tests for user and agent API - Update README with API docs and deployment safety notes - Bump version to 1.2.0
41 lines
997 B
SQL
41 lines
997 B
SQL
-- Migration 001: initial schema
|
|
-- Idempotent via IF NOT EXISTS so it safely runs against existing databases.
|
|
|
|
CREATE TABLE IF NOT EXISTS intake_log (
|
|
id TEXT PRIMARY KEY,
|
|
slot_id TEXT NOT NULL,
|
|
day TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
logged_at TEXT NOT NULL,
|
|
source TEXT DEFAULT 'app'
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_intake_day ON intake_log(day);
|
|
CREATE INDEX IF NOT EXISTS idx_intake_slot_day ON intake_log(slot_id, day);
|
|
|
|
CREATE TABLE IF NOT EXISTS push_subscription (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
endpoint TEXT UNIQUE NOT NULL,
|
|
p256dh TEXT NOT NULL,
|
|
auth TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_cache (
|
|
key TEXT PRIMARY KEY,
|
|
content TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS milestones (
|
|
id TEXT PRIMARY KEY,
|
|
unlocked_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS snooze (
|
|
slot_id TEXT NOT NULL,
|
|
day TEXT NOT NULL,
|
|
snooze_until TEXT NOT NULL,
|
|
PRIMARY KEY (slot_id, day)
|
|
);
|