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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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).

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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

```sql
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

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

### Find unidentified captures

```sql
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

```sql
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;
```