schema.sql

CREATE TABLE IF NOT EXISTS uploads (
    id TEXT PRIMARY KEY, 
    given_name TEXT,         
    platform TEXT,           
    upload_timestamp REAL,
    updated_at REAL,
    color TEXT
);

CREATE TABLE IF NOT EXISTS uploaded_files ( -- filled during extraction step   
    id TEXT PRIMARY KEY, 
    manifest_file_id TEXT,
    upload_id TEXT, 
    opfs_filename TEXT,                
    manifest_filename TEXT,        
    file_hash TEXT,               
    upload_timestamp REAL,        
    file_size_bytes INTEGER,
    parse_status TEXT,
    FOREIGN KEY(upload_id) REFERENCES uploads(id) ON DELETE CASCADE
);


CREATE TABLE IF NOT EXISTS raw_data ( -- filled during extraction step
    id TEXT PRIMARY KEY,
    upload_id TEXT,
    file_id TEXT,             
    data JSONTEXT,
    line_numbers JSONTEXT,  -- JSON list of line numbers where this record appears in the source file (1-indexed)
    FOREIGN KEY(upload_id) REFERENCES uploads(id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES uploaded_files(id) ON DELETE CASCADE
);



CREATE TABLE IF NOT EXISTS events ( -- filled during semantic map
    id TEXT PRIMARY KEY,
    upload_id TEXT,
    file_ids JSONTEXT,  -- multiple possible after deduplication
    raw_data_ids JSONTEXT,  -- can be multiple raw data entries that map to the same event, stored as JSON list of raw_data ids
    --             
    timestamp REAL,
    event_action TEXT,
    event_kind TEXT,
    event_category JSONTEXT DEFAULT '[]',
    event_type JSONTEXT DEFAULT '[]',
    --
    event_type_msg TEXT,
    attributes JSONTEXT,    --
    origin TEXT,  -- e.g., "facebook/web", "facebook/mobile_app", "apple/system", "unknown"
    tags JSONTEXT DEFAULT "[]",
    labels JSONTEXT DEFAULT "[]",
    starred INTEGER DEFAULT 0,
    --
    treat_as_auth_device BOOLEAN DEFAULT 0, 
    --
    deduplicated BOOLEAN DEFAULT 0,
    extra_timestamps JSONTEXT DEFAULT "[]",
    --
    FOREIGN KEY(upload_id) REFERENCES uploads(id) ON DELETE CASCADE
);


CREATE TABLE IF NOT EXISTS event_comments (
    id TEXT PRIMARY KEY,
    event_id TEXT,
    comment TEXT,
    created_at REAL,
    updated_at REAL,
    FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE
);



CREATE TABLE IF NOT EXISTS devices_raw ( -- filled during semantic map
    id TEXT PRIMARY KEY,
    upload_id TEXT,
    file_id TEXT,
    raw_data_id TEXT,
    --
    entity_type TEXT,
    entity_sub_type TEXT,
    event_kind TEXT,
    event_category JSONTEXT DEFAULT '[]',
    --
    attributes JSONTEXT,
    origin TEXT,  -- e.g., "facebook/web", "facebook/mobile_app", "apple/system", "unknown"
    --        
    FOREIGN KEY(upload_id) REFERENCES uploads(id) ON DELETE CASCADE,
    FOREIGN KEY(file_id) REFERENCES uploaded_files(id) ON DELETE CASCADE,
    FOREIGN KEY(raw_data_id) REFERENCES raw_data(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_group_edges ( -- for device/event grouping
    id_a TEXT, 
    id_b TEXT,  -- dropped from main dataframe in level0
    type TEXT,
    provenance TEXT,
    upload_id TEXT,
    UNIQUE(id_a, id_b, type),
    FOREIGN KEY(upload_id) REFERENCES uploads(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_groups (
    id TEXT PRIMARY KEY,
    upload_id TEXT,
    --
    platform TEXT,
    manufacturer TEXT,
    model TEXT,
    client_name TEXT,
    os_name TEXT,
    os_type TEXT,
    --
    apple_masking TEXT,
    has_conflicting_hardware_ids INTEGER DEFAULT 0,  -- see device_grouping2/graph.py:DeviceGroup._evaluate_hardware_conflict
    first_seen REAL,
    last_seen REAL,
    last_seen_dt TEXT,
    event_count INTEGER,
    latest_os_version TEXT,            
    latest_client_version TEXT,        
    latest_client_ip TEXT, 
    --           
    os_versions TEXT,    -- TODO jsonstring????
    client_versions TEXT,         -- -- TODO jsonstring????
    client_ips TEXT,                 -- -- TODO jsonstring????
    locations TEXT,  -- -- TODO jsonstring????
    --
    created_at REAL,
    tags JSONTEXT DEFAULT '[]',
    --
    FOREIGN KEY(upload_id) REFERENCES uploads(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_group_events (
    device_group_id TEXT,
    event_id TEXT,
    PRIMARY KEY (device_group_id, event_id),
    FOREIGN KEY(device_group_id) REFERENCES device_groups(id) ON DELETE CASCADE,
    FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_group_raw_devices (
    device_group_id TEXT,
    devices_raw_id TEXT,
    PRIMARY KEY (device_group_id, devices_raw_id),
    FOREIGN KEY(device_group_id) REFERENCES device_groups(id) ON DELETE CASCADE,
    FOREIGN KEY(devices_raw_id) REFERENCES devices_raw(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS resolved_sessions_registrations (
    id TEXT PRIMARY KEY,
    upload_id TEXT,
    entity_type TEXT,
    entity_sub_type TEXT,
    origin TEXT,
    model_name TEXT,
    client_name TEXT,
    os_name TEXT,
    os_version TEXT,
    os_type TEXT,
    attributes JSONTEXT,
    raw_data_ids JSONTEXT,  -- multiple after merging registrations sharing a hardware ID
    is_reduced_ua INTEGER DEFAULT 0,
    has_trusted_cookie INTEGER DEFAULT 0,
    trusted_cookie_id TEXT,
    has_passkey INTEGER DEFAULT 0,
    registration_device TEXT,
    event_count INTEGER DEFAULT 0,
    tags JSONTEXT DEFAULT '[]',
    FOREIGN KEY(upload_id) REFERENCES uploads(id) ON DELETE CASCADE
);

-----------------------------------------
--------          VIEWS          --------
-----------------------------------------


-- view for Events Mappings
DROP VIEW IF EXISTS v_event_field_mappings;
CREATE VIEW IF NOT EXISTS v_event_field_mappings AS
-- static columns
SELECT 'id' AS field, 'text' AS type
UNION SELECT 'timestamp', 'timestamp'
UNION SELECT 'event_type_msg', 'text'
UNION SELECT 'event_category', 'category'
UNION SELECT 'event_action', 'text'
UNION SELECT 'event_kind', 'category'
UNION SELECT 'platform', 'text'
UNION
-- dynamic from JSON attributes
SELECT DISTINCT key AS field, 'text' AS type
FROM events, json_each(events.attributes)
WHERE events.attributes IS NOT NULL AND events.attributes != '';





-- view for Auth Devices Mappings
DROP VIEW IF EXISTS v_device_field_mappings;
CREATE VIEW IF NOT EXISTS v_device_field_mappings AS
-- static columns
SELECT 'id' AS field, 'text' AS type
UNION SELECT 'entity_type', 'category'
UNION SELECT 'entity_sub_type', 'category'
UNION SELECT 'event_kind', 'category'
UNION SELECT 'event_category', 'category'
UNION SELECT 'platform', 'text'
UNION
-- dynamic from JSON attributes
SELECT DISTINCT key AS field, 'text' AS type
FROM devices_raw, json_each(devices_raw.attributes)
WHERE devices_raw.attributes IS NOT NULL AND devices_raw.attributes != '';



-- all event action types
CREATE VIEW IF NOT EXISTS v_event_actions AS
SELECT DISTINCT event_action
FROM events
WHERE event_action IS NOT NULL AND event_action != '';

-- Speeds up ORDER BY e.timestamp (used on every search) + LIMIT pagination.
CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);