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 '[]',
    --
    message TEXT,
    attributes JSONTEXT,    --
    origin TEXT,  -- e.g., "facebook/web", "facebook/mobile_app", "apple/system", "unknown"
    tags JSONTEXT DEFAULT "[]",
    labels JSONTEXT DEFAULT "[]",
    --
    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,
    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 event_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
);



CREATE TABLE IF NOT EXISTS device_instance_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_instances (
    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,
    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,
    --
    FOREIGN KEY(upload_id) REFERENCES uploads(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_instance_events (
    device_instance_id TEXT,
    event_id TEXT,
    PRIMARY KEY (device_instance_id, event_id),
    FOREIGN KEY(device_instance_id) REFERENCES device_instances(id) ON DELETE CASCADE,
    FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_instance_raw_devices (
    device_instance_id TEXT,
    devices_raw_id TEXT,
    PRIMARY KEY (device_instance_id, devices_raw_id),
    FOREIGN KEY(device_instance_id) REFERENCES device_instances(id) ON DELETE CASCADE,
    FOREIGN KEY(devices_raw_id) REFERENCES devices_raw(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS device_profiles_v2 (
    id TEXT PRIMARY KEY,
    model TEXT,
    manufacturer TEXT,
    os_type TEXT,
    user_label TEXT,
    notes TEXT,
    created_at REAL,
    updated_at REAL
);

CREATE TABLE IF NOT EXISTS device_profile_instances (
    device_profile_id TEXT,
    device_instance_id TEXT,
    PRIMARY KEY (device_profile_id, device_instance_id),
    FOREIGN KEY(device_profile_id) REFERENCES device_profiles_v2(id) ON DELETE CASCADE,
    FOREIGN KEY(device_instance_id) REFERENCES device_instances(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 '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
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 '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 event_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 
    die.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 device_instance_events die
JOIN device_profile_instances dpi ON die.device_instance_id = dpi.device_instance_id
JOIN device_profiles_v2 dp ON dpi.device_profile_id = dp.id
GROUP BY die.event_id;