giglez/docs/database_schema.md

Database Schema Design

Overview

The GigLez database stores RF signal captures with GPS coordinates, device signatures, and community contributions. The schema supports efficient querying by location, frequency, protocol, and device type.

Core Tables

1. captures

Primary table storing raw RF signal captures with GPS attribution.

CREATE TABLE captures (
    id SERIAL PRIMARY KEY,
    session_id INTEGER REFERENCES sessions(id) ON DELETE CASCADE,

    -- GPS Data
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    altitude DECIMAL(8, 2),
    gps_accuracy DECIMAL(6, 2),

    -- Timestamp
    captured_at TIMESTAMP NOT NULL DEFAULT NOW(),

    -- RF Signal Data
    frequency INTEGER NOT NULL,  -- in Hz
    rssi INTEGER,  -- Received Signal Strength Indicator
    modulation VARCHAR(50),  -- OOK, 2FSK, etc.
    preset VARCHAR(100),

    -- Protocol Information (if decoded)
    protocol VARCHAR(100),
    bit_length INTEGER,
    key_data BYTEA,
    timing_element INTEGER,  -- TE value in microseconds

    -- Raw Signal Data
    raw_data TEXT,  -- RAW_Data timings
    raw_format VARCHAR(20),  -- 'RAW', 'BinRAW', 'KEY'

    -- File Storage
    file_path VARCHAR(500),  -- Path to .sub file if stored separately
    file_hash VARCHAR(64),  -- SHA-256 hash for deduplication

    -- Matching
    device_id INTEGER REFERENCES devices(id),
    match_confidence DECIMAL(5, 4),  -- 0.0 to 1.0
    match_method VARCHAR(50),  -- 'auto', 'user', 'community'

    -- Indexes for geospatial queries
    CONSTRAINT valid_latitude CHECK (latitude >= -90 AND latitude <= 90),
    CONSTRAINT valid_longitude CHECK (longitude >= -180 AND longitude <= 180)
);

CREATE INDEX idx_captures_location ON captures USING GIST (ll_to_earth(latitude, longitude));
CREATE INDEX idx_captures_frequency ON captures(frequency);
CREATE INDEX idx_captures_protocol ON captures(protocol);
CREATE INDEX idx_captures_timestamp ON captures(captured_at DESC);
CREATE INDEX idx_captures_session ON captures(session_id);
CREATE INDEX idx_captures_device ON captures(device_id);
CREATE INDEX idx_captures_hash ON captures(file_hash);

2. sessions

Wardriving/capture sessions to group related captures.

CREATE TABLE sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),

    -- Session Metadata
    name VARCHAR(200),
    description TEXT,
    started_at TIMESTAMP NOT NULL DEFAULT NOW(),
    ended_at TIMESTAMP,

    -- Privacy Settings
    is_public BOOLEAN DEFAULT true,
    anonymize_gps BOOLEAN DEFAULT false,
    gps_precision_meters INTEGER DEFAULT 10,

    -- Session Statistics (computed)
    total_captures INTEGER DEFAULT 0,
    unique_devices INTEGER DEFAULT 0,
    distance_km DECIMAL(10, 2),

    -- Bounding Box (for quick filtering)
    min_latitude DECIMAL(10, 8),
    max_latitude DECIMAL(10, 8),
    min_longitude DECIMAL(11, 8),
    max_longitude DECIMAL(11, 8)
);

CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_sessions_started ON sessions(started_at DESC);

3. devices

Known IoT device types with signature information.

CREATE TABLE devices (
    id SERIAL PRIMARY KEY,

    -- Device Identification
    manufacturer VARCHAR(200),
    model VARCHAR(200),
    device_type VARCHAR(100),  -- 'garage_door', 'weather_station', 'key_fob', etc.
    description TEXT,

    -- RF Characteristics
    typical_frequency INTEGER,  -- Most common frequency in Hz
    frequency_range_low INTEGER,
    frequency_range_high INTEGER,
    modulation_types TEXT[],  -- Array: ['OOK', '2FSK']

    -- Protocol Information
    protocol VARCHAR(100),
    bit_length INTEGER,
    encoding VARCHAR(50),  -- 'PWM', 'PPM', 'Manchester', etc.

    -- Metadata
    fcc_id VARCHAR(50),
    manufacturer_code VARCHAR(50),  -- For protocols like KeeLoq

    -- Community Data
    created_at TIMESTAMP DEFAULT NOW(),
    verified BOOLEAN DEFAULT false,
    verification_count INTEGER DEFAULT 0,

    -- Source
    source VARCHAR(50),  -- 'flipper', 'rtl433', 'urh', 'community'
    source_url TEXT
);

CREATE INDEX idx_devices_manufacturer ON devices(manufacturer);
CREATE INDEX idx_devices_type ON devices(device_type);
CREATE INDEX idx_devices_frequency ON devices(typical_frequency);
CREATE INDEX idx_devices_protocol ON devices(protocol);

4. signatures

Protocol signatures for device matching.

CREATE TABLE signatures (
    id SERIAL PRIMARY KEY,
    device_id INTEGER REFERENCES devices(id) ON DELETE CASCADE,

    -- Signature Pattern
    protocol VARCHAR(100) NOT NULL,
    frequency INTEGER,
    modulation VARCHAR(50),

    -- Matching Criteria
    bit_pattern BYTEA,
    bit_mask BYTEA,  -- Which bits to match (1=match, 0=ignore)
    timing_min INTEGER,  -- TE range in microseconds
    timing_max INTEGER,

    -- Raw Pattern (for regex-like matching)
    pattern_regex TEXT,

    -- Confidence Weighting
    weight DECIMAL(4, 3) DEFAULT 1.0,  -- Higher = more reliable signature

    -- Metadata
    created_at TIMESTAMP DEFAULT NOW(),
    source VARCHAR(50),
    source_file VARCHAR(500),  -- Original .sub or config file

    UNIQUE(device_id, protocol, bit_pattern)
);

CREATE INDEX idx_signatures_device ON signatures(device_id);
CREATE INDEX idx_signatures_protocol ON signatures(protocol);
CREATE INDEX idx_signatures_frequency ON signatures(frequency);

5. users

User accounts for community features.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,

    -- Authentication
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,

    -- Profile
    display_name VARCHAR(100),
    avatar_url TEXT,
    bio TEXT,

    -- Statistics
    total_captures INTEGER DEFAULT 0,
    total_identifications INTEGER DEFAULT 0,
    reputation_score INTEGER DEFAULT 0,

    -- Settings
    api_key VARCHAR(64) UNIQUE,
    email_verified BOOLEAN DEFAULT false,

    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    last_login TIMESTAMP
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_api_key ON users(api_key);

6. identifications

User-submitted device identifications (with photos).

CREATE TABLE identifications (
    id SERIAL PRIMARY KEY,
    capture_id INTEGER REFERENCES captures(id) ON DELETE CASCADE,
    device_id INTEGER REFERENCES devices(id),
    user_id INTEGER REFERENCES users(id),

    -- Identification Details
    confidence VARCHAR(20),  -- 'certain', 'likely', 'guess'
    notes TEXT,

    -- Visual Evidence
    photo_urls TEXT[],  -- Array of image URLs

    -- Community Validation
    upvotes INTEGER DEFAULT 0,
    downvotes INTEGER DEFAULT 0,
    verified BOOLEAN DEFAULT false,
    verified_by INTEGER REFERENCES users(id),
    verified_at TIMESTAMP,

    -- Timestamps
    submitted_at TIMESTAMP DEFAULT NOW(),

    UNIQUE(capture_id, user_id, device_id)
);

CREATE INDEX idx_identifications_capture ON identifications(capture_id);
CREATE INDEX idx_identifications_device ON identifications(device_id);
CREATE INDEX idx_identifications_user ON identifications(user_id);

7. votes

Voting on device identifications.

CREATE TABLE votes (
    id SERIAL PRIMARY KEY,
    identification_id INTEGER REFERENCES identifications(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id),

    vote_type INTEGER NOT NULL,  -- 1 = upvote, -1 = downvote
    voted_at TIMESTAMP DEFAULT NOW(),

    UNIQUE(identification_id, user_id)
);

CREATE INDEX idx_votes_identification ON votes(identification_id);
CREATE INDEX idx_votes_user ON votes(user_id);

8. flipper_signatures

Imported Flipper Zero .sub file signatures.

CREATE TABLE flipper_signatures (
    id SERIAL PRIMARY KEY,
    signature_id INTEGER REFERENCES signatures(id) ON DELETE CASCADE,

    -- Flipper-Specific Fields
    filetype VARCHAR(50),  -- 'Flipper SubGhz Key File' or 'RAW File'
    version INTEGER,
    preset VARCHAR(100),

    -- Custom Preset Data
    custom_preset_module VARCHAR(50),
    custom_preset_data BYTEA,

    -- Protocol Data
    protocol VARCHAR(100),
    bit INTEGER,
    key BYTEA,
    te INTEGER,  -- Timing element

    -- RAW Data
    raw_data TEXT,
    bin_raw_bit INTEGER,
    bin_raw_te INTEGER,
    bin_raw_data BYTEA,

    -- Source
    source_file VARCHAR(500),
    imported_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_flipper_protocol ON flipper_signatures(protocol);
CREATE INDEX idx_flipper_preset ON flipper_signatures(preset);

9. rtl433_protocols

Imported RTL_433 protocol definitions.

CREATE TABLE rtl433_protocols (
    id SERIAL PRIMARY KEY,
    signature_id INTEGER REFERENCES signatures(id) ON DELETE CASCADE,

    -- Protocol Identification
    protocol_number INTEGER,
    protocol_name VARCHAR(200),
    model VARCHAR(200),

    -- RF Characteristics
    frequency INTEGER,
    modulation VARCHAR(50),  -- 'OOK_PWM', 'FSK_PCM', etc.

    -- Timing Information (in microseconds)
    short_width INTEGER,
    long_width INTEGER,
    reset_limit INTEGER,
    gap_limit INTEGER,

    -- Decoding
    decoder_type VARCHAR(50),
    bit_count INTEGER,

    -- JSON Fields Mapping
    json_fields JSONB,  -- Expected output fields

    -- Source
    source_file VARCHAR(500),
    imported_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_rtl433_protocol_num ON rtl433_protocols(protocol_number);
CREATE INDEX idx_rtl433_model ON rtl433_protocols(model);
CREATE INDEX idx_rtl433_modulation ON rtl433_protocols(modulation);

Materialized Views

device_statistics

Pre-computed statistics for device types.

CREATE MATERIALIZED VIEW device_statistics AS
SELECT
    d.id as device_id,
    d.manufacturer,
    d.model,
    COUNT(c.id) as total_captures,
    COUNT(DISTINCT c.session_id) as total_sessions,
    MIN(c.captured_at) as first_seen,
    MAX(c.captured_at) as last_seen,
    AVG(c.rssi) as avg_rssi,
    ST_Collect(ST_MakePoint(c.longitude, c.latitude)) as capture_locations
FROM devices d
LEFT JOIN captures c ON c.device_id = d.id
GROUP BY d.id, d.manufacturer, d.model;

CREATE INDEX idx_device_stats_device ON device_statistics(device_id);

geographic_heatmap

Aggregated capture density for mapping.

CREATE MATERIALIZED VIEW geographic_heatmap AS
SELECT
    ROUND(latitude::numeric, 3) as lat_bucket,
    ROUND(longitude::numeric, 3) as lon_bucket,
    COUNT(*) as capture_count,
    COUNT(DISTINCT device_id) as unique_devices,
    array_agg(DISTINCT protocol) as protocols_seen
FROM captures
WHERE device_id IS NOT NULL
GROUP BY lat_bucket, lon_bucket;

CREATE INDEX idx_heatmap_location ON geographic_heatmap(lat_bucket, lon_bucket);

Functions

calculate_distance

Calculate distance between two GPS coordinates.

CREATE OR REPLACE FUNCTION calculate_distance(
    lat1 DECIMAL, lon1 DECIMAL,
    lat2 DECIMAL, lon2 DECIMAL
) RETURNS DECIMAL AS $$
DECLARE
    R DECIMAL := 6371.0;  -- Earth radius in km
    dLat DECIMAL;
    dLon DECIMAL;
    a DECIMAL;
    c DECIMAL;
BEGIN
    dLat := radians(lat2 - lat1);
    dLon := radians(lon2 - lon1);
    a := sin(dLat/2) * sin(dLat/2) +
         cos(radians(lat1)) * cos(radians(lat2)) *
         sin(dLon/2) * sin(dLon/2);
    c := 2 * atan2(sqrt(a), sqrt(1-a));
    RETURN R * c;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

match_signature

Match a capture against all known signatures.

CREATE OR REPLACE FUNCTION match_signature(
    p_capture_id INTEGER
) RETURNS TABLE(device_id INTEGER, confidence DECIMAL) AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.device_id,
        CASE
            WHEN c.protocol = s.protocol
                AND c.frequency = s.frequency
                AND c.bit_length = s.bit_length THEN 1.0
            WHEN c.protocol = s.protocol
                AND c.frequency = s.frequency THEN 0.8
            WHEN c.protocol = s.protocol THEN 0.5
            ELSE 0.0
        END as confidence
    FROM captures c
    CROSS JOIN signatures s
    WHERE c.id = p_capture_id
        AND c.protocol IS NOT NULL
    ORDER BY confidence DESC
    LIMIT 10;
END;
$$ LANGUAGE plpgsql;

Triggers

update_session_statistics

Automatically update session statistics when captures are added.

CREATE OR REPLACE FUNCTION update_session_stats()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE sessions SET
        total_captures = (SELECT COUNT(*) FROM captures WHERE session_id = NEW.session_id),
        unique_devices = (SELECT COUNT(DISTINCT device_id) FROM captures WHERE session_id = NEW.session_id),
        min_latitude = LEAST(min_latitude, NEW.latitude),
        max_latitude = GREATEST(max_latitude, NEW.latitude),
        min_longitude = LEAST(min_longitude, NEW.longitude),
        max_longitude = GREATEST(max_longitude, NEW.longitude)
    WHERE id = NEW.session_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_session_stats
    AFTER INSERT ON captures
    FOR EACH ROW
    EXECUTE FUNCTION update_session_stats();

update_device_verification

Update device verification status based on identification votes.

CREATE OR REPLACE FUNCTION update_device_verification()
RETURNS TRIGGER AS $$
DECLARE
    net_votes INTEGER;
BEGIN
    SELECT (upvotes - downvotes) INTO net_votes
    FROM identifications
    WHERE id = NEW.identification_id;

    IF net_votes >= 5 THEN
        UPDATE identifications SET verified = true
        WHERE id = NEW.identification_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_verification
    AFTER INSERT OR UPDATE ON votes
    FOR EACH ROW
    EXECUTE FUNCTION update_device_verification();

Sample Queries

Find all captures near a location

SELECT c.*, d.manufacturer, d.model
FROM captures c
LEFT JOIN devices d ON c.device_id = d.id
WHERE calculate_distance(c.latitude, c.longitude, 40.7128, -74.0060) <= 1.0  -- Within 1km
ORDER BY c.captured_at DESC;

Get device density heatmap

SELECT lat_bucket, lon_bucket, capture_count, unique_devices
FROM geographic_heatmap
WHERE capture_count > 5
ORDER BY capture_count DESC;

Find unidentified captures

SELECT c.id, c.frequency, c.protocol, c.latitude, c.longitude, c.captured_at
FROM captures c
WHERE c.device_id IS NULL
    AND c.protocol IS NOT NULL
ORDER BY c.captured_at DESC
LIMIT 100;

Top device types by capture count

SELECT d.manufacturer, d.model, d.device_type, COUNT(c.id) as captures
FROM devices d
JOIN captures c ON c.device_id = d.id
GROUP BY d.id, d.manufacturer, d.model, d.device_type
ORDER BY captures DESC
LIMIT 20;