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,
    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 '[]',
    --
    message TEXT,
    attributes JSONTEXT,    --
    origin TEXT,  -- e.g., "facebook/web", "facebook/mobile_app", "apple/system", "unknown"
    tags JSONTEXT DEFAULT "[]",
    labels JSONTEXT DEFAULT "[]",
    --
    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,
    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 atomic_devices ( -- hard merge based on static device identifiers. user cannot edit this.
    id TEXT PRIMARY KEY,
    upload_ids JSONTEXT NOT NULL,  -- JSON list of uploads that contributed to this merged device
    file_ids JSONTEXT NOT NULL,    -- ^^ for uploaded_files.id 
    devices_raw_ids JSONTEXT NOT NULL,  -- ^^ for devices_raw.id
    --
    attributes JSONTEXT,    -- merged attributes
    origins JSONTEXT,  -- JSON list of origin values (e.g., ["facebook/web", "facebook/mobile_app"])
    specificity INTEGER DEFAULT 1  -- 1=generic, 2=model+version, 3=hard_id
);


CREATE TABLE IF NOT EXISTS device_profiles (
    id TEXT PRIMARY KEY,
    atomic_devices_ids JSONTEXT NOT NULL,  -- JSON list of atomic_devices.id that are in this cluster
    --
    attributes JSONTEXT,  -- merged attributes from all atomics
    specificity INTEGER DEFAULT 1,  -- max specificity from atomics
    model TEXT,  -- best model name from attributes
    manufacturer TEXT,  -- best manufacturer from attributes
    origins JSONTEXT,  -- merged origins from all atomics
    --
    system_soft_merge BOOLEAN DEFAULT 0,  -- 1 if profile created by system soft-merge AND has 2+ atomics, else 0
    is_generic BOOLEAN DEFAULT 0,  -- 1 if specificity < 2 and Apple
    --
    user_label TEXT,
    notes TEXT,
    --
    created_at REAL,
    updated_at REAL,
    --
    tags JSONTEXT DEFAULT "[]",
    labels JSONTEXT DEFAULT "[]"
);

CREATE TABLE IF NOT EXISTS device_profile_comments (
    id TEXT PRIMARY KEY,
    device_profile_id TEXT,
    comment TEXT,
    created_at REAL,
    updated_at REAL,
    FOREIGN KEY(device_profile_id) REFERENCES device_profiles(id) ON DELETE CASCADE
);



CREATE TABLE IF NOT EXISTS events_assoc (
    event_id TEXT,
    atomic_device_id TEXT,
    event_specificity INTEGER,  -- 1=generic, 2=model+version, 3=hard_id
    match_reason TEXT, 
    PRIMARY KEY (event_id, atomic_device_id),
    FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY(atomic_device_id) REFERENCES atomic_devices(id) ON DELETE CASCADE
);






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


-- view for Events 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 'message', '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
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 '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 != '';



DROP VIEW IF EXISTS v_device_profiles;
CREATE VIEW v_device_profiles AS
SELECT 
    dg.id AS profile_id,
    json_group_array(json(ad.attributes)) AS attributes,
    json_group_array(ad.specificity) AS specificity,
    json(json_group_array(DISTINCT j2.value)) AS origins
FROM device_profiles dg
JOIN json_each(dg.atomic_devices_ids) as j ON 1=1
JOIN atomic_devices ad ON ad.id = j.value
JOIN json_each(ad.origins) as j2 ON 1=1
GROUP BY dg.id;


DROP VIEW IF EXISTS v_events2profile;
CREATE VIEW v_events2profile AS
SELECT 
    ea.event_id,
    dp.id AS device_profile_id,
    ea.match_reason,
    ea.event_specificity
FROM events_assoc ea
JOIN device_profiles dp ON 1=1
JOIN json_each(dp.atomic_devices_ids) as j ON j.value = ea.atomic_device_id;


DROP VIEW IF EXISTS v_events2profile_indexed;
CREATE VIEW v_events2profile_indexed AS
SELECT 
    ea.event_id,
    json_group_array(
        json_object(
            'id', dp.id,
            'model', COALESCE(dp.model, ''),
            'user_label', COALESCE(dp.user_label, '')
        )
    ) AS device_profiles_data
FROM events_assoc ea
JOIN device_profiles dp ON 1=1
JOIN json_each(dp.atomic_devices_ids) as j ON j.value = ea.atomic_device_id
GROUP BY ea.event_id;