giglez/docs/DATABASE_SETUP.md

GigLez Database Setup Guide

Prerequisites

• ✅ PostgreSQL 16+ installed
• ✅ PostGIS 3.4+ extension available
• ✅ Python 3.8+ with dependencies from requirements.txt

Quick Start

Step 1: Run Database Setup Script

cd /path/to/giglez
./scripts/setup_database.sh

This script will:

1. Create PostgreSQL user giglez_user
2. Create database giglez
3. Enable PostGIS extension
4. Grant necessary permissions

Default Credentials:

• User: giglez_user
• Password: giglez_secure_password_2026
• Database: giglez
• Host: localhost
• Port: 5432

⚠️ Security Note: Change the password in production!

Step 2: Create Database Schema

psql -U giglez_user -d giglez -h localhost -f scripts/create_schema.sql

Or if you have sudo access:

sudo -u postgres psql -d giglez -f scripts/create_schema.sql

This will create:

• ✅ 15 tables (captures, devices, sessions, signatures, etc.)
• ✅ PostGIS geometry columns and spatial indexes
• ✅ Materialized views for performance
• ✅ Triggers for automatic statistics updates
• ✅ Functions for geospatial calculations

Step 3: Configure Environment

# Copy environment template
cp .env.example .env

# Edit with your configuration
nano .env

Update these values:

GIGLEZ_DB_PASSWORD=your_secure_password_here
GIGLEZ_STORAGE_PATH=/your/storage/path

Step 4: Test Connection

python3 config/database.py

Expected output:

Testing database connection...
✅ Database connection test successful
Testing PostGIS extension...
✅ PostGIS available: 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
✅ Database fully operational

Database Schema Overview

Core Tables

captures

Primary storage for RF signal captures with GPS coordinates.

• Primary Key: file_hash (SHA256 of .sub file)
• Geospatial: geom column with GIST index
• Deduplication: Automatic via primary key constraint

devices

Known IoT device types from signature databases.

• Manufacturer, model, device type
• RF characteristics (frequency, modulation, protocol)
• Full-text search enabled

sessions

Wardriving session grouping (Wigle pattern).

• Groups related captures
• Tracks statistics (total captures, unique devices)
• Bounding box for geographic extent

signatures

Protocol signatures for device matching.

• Bit patterns with masks
• Timing patterns for RAW signals
• Confidence weighting

capture_matches

Many-to-many mapping of captures to devices.

• Multiple devices can match one capture
• Confidence scores (0.0 - 1.0)
• Match method tracking

Supporting Tables

• users: Optional user accounts
• identifications: Community device submissions
• votes: Voting on identifications
• upload_markers: Incremental sync tracking (Wigle pattern)
• flipper_signatures: Flipper Zero specific data
• rtl433_protocols: RTL_433 specific data

Materialized Views

device_statistics

Pre-computed device statistics for performance.

REFRESH MATERIALIZED VIEW CONCURRENTLY device_statistics;

geographic_heatmap

Aggregated capture density by location.

REFRESH MATERIALIZED VIEW CONCURRENTLY geographic_heatmap;

Common Operations

Query Captures Near Location

-- Within 1km radius
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
ORDER BY c.captured_at DESC;

-- Or using PostGIS (faster)
SELECT c.*, d.manufacturer, d.model
FROM captures c
LEFT JOIN devices d ON c.device_id = d.id
WHERE ST_DWithin(
    c.geom,
    ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326)::geography,
    1000  -- meters
)
ORDER BY c.captured_at DESC;

Query by Bounding Box

SELECT c.*
FROM captures c
WHERE c.geom && ST_MakeEnvelope(-74.1, 40.6, -73.9, 40.8, 4326)
LIMIT 100;

Get Unidentified Captures

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

Top Devices by Capture Count

SELECT
    d.manufacturer,
    d.model,
    d.device_type,
    COUNT(c.file_hash) 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;

Heatmap Data

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

Performance Tuning

Indexes

All critical indexes are created automatically:

• Geospatial: GIST indexes on geom column
• Foreign keys: B-tree indexes
• Query fields: Indexes on frequency, protocol, timestamp

Batch Inserts

Use transaction batching for bulk inserts (Wigle pattern):

from config.database import DatabaseSession

BATCH_SIZE = 512  # Wigle optimal batch size

with DatabaseSession() as session:
    for i in range(0, len(captures), BATCH_SIZE):
        batch = captures[i:i+BATCH_SIZE]
        session.bulk_insert_mappings(Capture, batch)

Connection Pooling

Configured in config/database.py:

• Pool size: 10 connections
• Max overflow: 20 connections
• Pre-ping: True (verify before use)

Materialized View Refresh

Set up cron job for periodic refresh:

# Add to crontab
0 */6 * * * psql -U giglez_user -d giglez -c "REFRESH MATERIALIZED VIEW CONCURRENTLY device_statistics;"
0 */6 * * * psql -U giglez_user -d giglez -c "REFRESH MATERIALIZED VIEW CONCURRENTLY geographic_heatmap;"

Backup & Maintenance

Backup Database

pg_dump -U giglez_user -d giglez -h localhost -F c -f giglez_backup_$(date +%Y%m%d).dump

Restore Database

pg_restore -U giglez_user -d giglez -h localhost giglez_backup_20260112.dump

Vacuum and Analyze

psql -U giglez_user -d giglez -h localhost -c "VACUUM ANALYZE;"

Check Database Size

SELECT
    pg_size_pretty(pg_database_size('giglez')) as db_size,
    pg_size_pretty(pg_total_relation_size('captures')) as captures_size,
    pg_size_pretty(pg_total_relation_size('devices')) as devices_size;

Troubleshooting

Connection Refused

# Check PostgreSQL status
sudo systemctl status postgresql

# Start PostgreSQL
sudo systemctl start postgresql

# Enable on boot
sudo systemctl enable postgresql

Permission Denied

# Grant permissions
sudo -u postgres psql -d giglez -c "GRANT ALL ON SCHEMA public TO giglez_user;"
sudo -u postgres psql -d giglez -c "GRANT ALL ON ALL TABLES IN SCHEMA public TO giglez_user;"
sudo -u postgres psql -d giglez -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO giglez_user;"

PostGIS Not Found

# Install PostGIS extension
sudo apt install postgresql-16-postgis-3

# Enable in database
psql -U giglez_user -d giglez -h localhost -c "CREATE EXTENSION postgis;"

Test PostGIS

SELECT PostGIS_Version();
SELECT ST_AsText(ST_MakePoint(-74.0060, 40.7128));

Architecture Decisions

Based on Wigle.net analysis (see docs/wigle_analysis.md):

1. SHA256 primary key: Automatic deduplication of .sub files
2. PostGIS geometry: Efficient geospatial queries (GIST indexes)
3. 3-table design: captures → capture_matches → devices
4. Upload markers: Incremental sync for resumable uploads
5. Materialized views: Pre-computed statistics for performance
6. Batch transactions: 512 operations per commit
7. Connection pooling: 10 base + 20 overflow connections

Next Steps

After database setup:

1. ✅ Implement SQLAlchemy ORM models (src/database/models.py)
2. ✅ Create GPS validator module (src/gps/validator.py)
3. ✅ Set up Alembic migrations (alembic/)
4. ✅ Build FastAPI upload endpoints (src/api/)
5. ✅ Import signature databases (Flipper Zero, RTL_433)

See IMPLEMENTATION_PLAN.md for complete roadmap.

Resources

• Schema Documentation: docs/database_schema.md
• Wigle Analysis: docs/wigle_analysis.md
• Architecture Decisions: docs/architecture_decisions.md
• PostGIS Manual: https://postgis.net/docs/
• PostgreSQL Documentation: https://www.postgresql.org/docs/

---

Created: 2026-01-12 Database Version: PostgreSQL 16 + PostGIS 3.4 Schema Version: 1.0.0