-- ============================================================================ -- FamilyGuard - MySQL Schema -- Run this in phpMyAdmin (or: mysql < schema.sql) once, before first use. -- ============================================================================ SET NAMES utf8mb4; -- --------------------------------------------------------------------------- -- Users: parent accounts that control one or more child devices. -- Children are DEVICES, not users. Only parents log in on web/app dashboards. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, theme ENUM('light','dark','system') NOT NULL DEFAULT 'system', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------------- -- Devices: one row per child phone. Linked to a parent (owner_id). -- A child device is enrolled via a one-time 6 digit pairing CODE. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS devices ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, owner_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, -- friendly name, e.g. "Sophie's phone" pairing_code CHAR(6) NOT NULL, -- 6-digit enrol code (shown to parent) pairing_used TINYINT(1) NOT NULL DEFAULT 0, device_token CHAR(64) NULL, -- per-device secret issued after pairing platform VARCHAR(40) NOT NULL DEFAULT 'android', -- android model VARCHAR(120) NULL, os_version VARCHAR(64) NULL, app_version VARCHAR(32) NULL, vpn_enabled TINYINT(1) NOT NULL DEFAULT 0, -- web filter on? (Phase 5) nls_enabled TINYINT(1) NOT NULL DEFAULT 0, -- notification capture? (Phase 5) owner_enabled TINYINT(1) NOT NULL DEFAULT 0, -- device-owner mode? (Phase X) screen_time_paused TINYINT(1) NOT NULL DEFAULT 0, -- kill-switch (Phase X) banned_apps TEXT NULL, -- JSON array of banned pkg ids (Phase X) last_seen_at DATETIME NULL, -- last successful ping created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_dev_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX idx_dev_owner ON devices (owner_id); -- --------------------------------------------------------------------------- -- usage_logs: per-app screen-time, bucketed (hourly by default). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS usage_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, device_id INT UNSIGNED NOT NULL, package_id VARCHAR(190) NOT NULL, app_name VARCHAR(190) NOT NULL DEFAULT '', minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0, bucket_utc DATETIME NOT NULL, -- rounding start (e.g. top of hour) recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_ul_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE, UNIQUE KEY uq_bucket (device_id, package_id, bucket_utc) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------------- -- location_logs: point-in-time location fixes + optional history trail. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS location_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, device_id INT UNSIGNED NOT NULL, latitude DECIMAL(10,7) NOT NULL, longitude DECIMAL(10,7) NOT NULL, accuracy_m FLOAT DEFAULT NULL, speed_kmh FLOAT DEFAULT NULL, provider ENUM('gps','network','passive','fused') NOT NULL DEFAULT 'gps', captured_at DATETIME NOT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_ll_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX idx_ll_dev_cap ON location_logs (device_id, captured_at); -- --------------------------------------------------------------------------- -- event_logs: installs, uninstalls, screen on/off, boot, network changes. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS event_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, device_id INT UNSIGNED NOT NULL, event_type ENUM('app_installed','app_uninstalled','screen_on','screen_off', 'boot_completed','network_change','vpn_state','nls_captured', 'owner_enabled','screen_time_paused') NOT NULL, package_id VARCHAR(190) NULL, app_name VARCHAR(190) NULL, detail TEXT NULL, occurred_at DATETIME NOT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_el_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX idx_el_dev_occ ON event_logs (device_id, occurred_at); -- --------------------------------------------------------------------------- -- commands: outbox for remote actions (child app polls & executes). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS commands ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, device_id INT UNSIGNED NOT NULL, kind ENUM('ping','lock','search','alert','pause_screen_time', 'unpause_screen_time','enable_vpn','disable_vpn') NOT NULL DEFAULT 'ping', payload TEXT NULL, status ENUM('queued','acked','done','failed') NOT NULL DEFAULT 'queued', issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, acked_at DATETIME NULL, done_at DATETIME NULL, CONSTRAINT fk_cmd_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX idx_cmd_dev_status ON commands (device_id, status); -- --------------------------------------------------------------------------- -- alerts: filter hits, battery, erratic movement, pairing, breaches... -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS alerts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, device_id INT UNSIGNED NOT NULL, severity ENUM('info','warning','critical') NOT NULL DEFAULT 'info', title VARCHAR(190) NOT NULL, detail TEXT NULL, acknowledged TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_al_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX idx_al_dev_created ON alerts (device_id, created_at); -- --------------------------------------------------------------------------- -- message_logs: text captured from SMS / messaging-app notifications. -- direction: 'in' received, 'out' sent. body may be partial (Android limits). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS message_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, device_id INT UNSIGNED NOT NULL, app_package VARCHAR(190) NOT NULL DEFAULT '', -- e.g. com.whatsapp sender VARCHAR(190) NULL, -- contact name / phone recipient VARCHAR(190) NULL, -- local user if 'out' body TEXT NOT NULL, direction ENUM('in','out','unknown') NOT NULL DEFAULT 'unknown', captured_at DATETIME NOT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_ml_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX idx_ml_dev_cap ON message_logs (device_id, captured_at); -- --------------------------------------------------------------------------- -- call_logs: best-effort call history (READ_CALL_LOG; blocked on Android 9+). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS call_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, device_id INT UNSIGNED NOT NULL, phone_number VARCHAR(40) NULL, contact_name VARCHAR(190) NULL, call_type ENUM('in','out','missed','rejected','blocked','unknown') NOT NULL DEFAULT 'unknown', duration_sec INT UNSIGNED NOT NULL DEFAULT 0, started_at DATETIME NOT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_cl_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX idx_cl_dev_start ON call_logs (device_id, started_at); -- --------------------------------------------------------------------------- -- media_logs: metadata for photos/videos on the device (no content uploaded). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS media_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, device_id INT UNSIGNED NOT NULL, media_type ENUM('image','video','audio','other') NOT NULL DEFAULT 'other', file_name VARCHAR(255) NULL, mime VARCHAR(120) NULL, size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NULL, -- file creation time captured_at DATETIME NOT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_mdl_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE INDEX idx_mdl_dev_cap ON media_logs (device_id, captured_at); -- --------------------------------------------------------------------------- -- keywords: parent-configured terms scanned against captured message text. -- A match produces an 'alert' on the owning device. Scoped to a parent owner -- (not a single device) so all of their children share the same list. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS keywords ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, owner_id INT UNSIGNED NOT NULL, keyword VARCHAR(190) NOT NULL, severity ENUM('info','warning','critical') NOT NULL DEFAULT 'warning', active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_kw_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY uq_kw (owner_id, keyword) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;