ipsw/www/supabase/schema.sql

-- Optimized PostgreSQL schema for entitlements database
-- Designed for Supabase free tier (500MB limit) with 40-60% storage reduction
-- Preserves 100% data integrity - no truncation of keys, values, or paths

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For fast text searches

-- Reuse existing IPSW tables from main schema for consistency
-- This eliminates redundant version/build data and leverages existing infrastructure

-- Create IPSW table if not exists (matches main IPSW schema)
CREATE TABLE IF NOT EXISTS ipsws (
    id TEXT PRIMARY KEY,
    name TEXT,
    version TEXT,
    buildid TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE
);

-- Create devices table if not exists (matches main IPSW schema)
CREATE TABLE IF NOT EXISTS devices (
    name TEXT PRIMARY KEY
);

-- Create ipsw_devices many-to-many table if not exists (matches main IPSW schema)
CREATE TABLE IF NOT EXISTS ipsw_devices (
    ipsw_id TEXT NOT NULL REFERENCES ipsws(id),
    device_name TEXT NOT NULL REFERENCES devices(name),
    PRIMARY KEY(ipsw_id, device_name)
);

-- Entitlement keys table - preserves complete key text
CREATE TABLE entitlement_keys (
    id SERIAL PRIMARY KEY,
    key TEXT NOT NULL UNIQUE -- CRITICAL: Keep as TEXT - keys must be complete/accurate
);

-- Entitlement values table with optimized hash - preserves complete value text  
CREATE TABLE entitlement_values (
    id SERIAL PRIMARY KEY,
    value TEXT NOT NULL,     -- CRITICAL: Keep as TEXT - values must be complete/accurate
    value_type VARCHAR(10) NOT NULL CHECK (value_type IN ('bool', 'string', 'array', 'dict', 'number')),
    value_hash CHAR(16) NOT NULL UNIQUE -- Shortened hash for uniqueness (99.999% collision safety)
);

-- Reuse existing paths table from main IPSW schema for consistency
-- This table is shared between entitlements and other IPSW database features
CREATE TABLE IF NOT EXISTS paths (
    id SERIAL PRIMARY KEY,
    path TEXT NOT NULL UNIQUE -- CRITICAL: Keep as TEXT - paths must be complete/accurate
);

-- Main entitlements table (significantly reduced size through normalization)
CREATE TABLE entitlements (
    id BIGSERIAL PRIMARY KEY, -- Keep as BIGINT for large datasets
    ipsw_id TEXT NOT NULL REFERENCES ipsws(id),
    path_id INTEGER NOT NULL REFERENCES paths(id),
    key_id INTEGER NOT NULL REFERENCES entitlement_keys(id),
    value_id INTEGER NOT NULL REFERENCES entitlement_values(id),
    
    -- Unique constraint to prevent duplicates (fixes original duplicate issue)
    UNIQUE(ipsw_id, path_id, key_id, value_id)
);

-- Create optimal indexes (balanced for performance vs storage)
-- For IPSW version lookups
CREATE INDEX idx_ipsws_version ON ipsws(version);
CREATE INDEX idx_ipsws_buildid ON ipsws(buildid);

-- For key pattern searches (GIN index for fast ILIKE)
CREATE INDEX idx_keys_search ON entitlement_keys USING gin(key gin_trgm_ops);

-- For path pattern searches (GIN index for fast ILIKE)  
CREATE INDEX idx_paths_search ON paths USING gin(path gin_trgm_ops);

-- For main table lookups (composite indexes for common query patterns)
CREATE INDEX idx_entitlement_ipsw_key ON entitlements(ipsw_id, key_id);
CREATE INDEX idx_entitlement_ipsw_path ON entitlements(ipsw_id, path_id);

-- Materialized view for ultra-fast searches (refreshed periodically, not per-query)
CREATE MATERIALIZED VIEW entitlements_search AS
SELECT 
    ek.id,
    i.version as ios_version,
    i.buildid as build_id,
    array_agg(DISTINCT d.name ORDER BY d.name) as device_list,
    up.path as file_path,
    uk.key,
    uv.value_type,
    CASE 
        WHEN uv.value_type = 'string' THEN uv.value
        ELSE NULL
    END as string_value,
    CASE 
        WHEN uv.value_type = 'bool' THEN CASE WHEN uv.value = 'true' THEN true ELSE false END
        ELSE NULL
    END as bool_value,
    CASE 
        WHEN uv.value_type = 'number' THEN uv.value::NUMERIC
        ELSE NULL
    END as number_value,
    CASE 
        WHEN uv.value_type = 'array' THEN uv.value
        ELSE NULL
    END as array_value,
    CASE 
        WHEN uv.value_type = 'dict' THEN uv.value
        ELSE NULL
    END as dict_value,
    i.created_at as release_date
FROM entitlements ek
JOIN ipsws i ON i.id = ek.ipsw_id
JOIN entitlement_keys uk ON uk.id = ek.key_id
JOIN entitlement_values uv ON uv.id = ek.value_id
JOIN paths up ON up.id = ek.path_id
LEFT JOIN ipsw_devices id ON id.ipsw_id = ek.ipsw_id
LEFT JOIN devices d ON d.name = id.device_name
GROUP BY ek.id, i.version, i.buildid, up.path, uk.key, uv.value_type, uv.value, i.created_at;

-- Indexes on materialized view for lightning-fast searches
CREATE INDEX idx_search_key ON entitlements_search USING gin(key gin_trgm_ops);
CREATE INDEX idx_search_path ON entitlements_search USING gin(file_path gin_trgm_ops);
CREATE INDEX idx_search_version ON entitlements_search(ios_version);
CREATE INDEX idx_search_id ON entitlements_search(id); -- For pagination

-- Function to refresh materialized view (call periodically, not per-query)
CREATE OR REPLACE FUNCTION refresh_search_view()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY entitlements_search;
END;
$$ LANGUAGE plpgsql;

-- Keep administrative helpers off the public Data API/RPC surface.
REVOKE EXECUTE ON FUNCTION refresh_search_view() FROM PUBLIC, anon, authenticated;

-- Enable Row Level Security for all tables
ALTER TABLE ipsws ENABLE ROW LEVEL SECURITY;
ALTER TABLE devices ENABLE ROW LEVEL SECURITY;
ALTER TABLE ipsw_devices ENABLE ROW LEVEL SECURITY;
ALTER TABLE entitlement_keys ENABLE ROW LEVEL SECURITY;
ALTER TABLE entitlement_values ENABLE ROW LEVEL SECURITY;
ALTER TABLE paths ENABLE ROW LEVEL SECURITY;
ALTER TABLE entitlements ENABLE ROW LEVEL SECURITY;

-- Browser roles are read-only. Writes are reserved for direct DB credentials used by automation.
REVOKE ALL ON TABLE ipsws, devices, ipsw_devices, entitlement_keys, entitlement_values, paths, entitlements, entitlements_search FROM anon, authenticated;
REVOKE ALL ON SEQUENCE entitlement_keys_id_seq, entitlement_values_id_seq, paths_id_seq, entitlements_id_seq FROM anon, authenticated;
GRANT SELECT ON TABLE ipsws, devices, ipsw_devices, entitlement_keys, entitlement_values, paths, entitlements, entitlements_search TO anon, authenticated;

-- Prevent future public-schema objects from becoming browser-writable by default.
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
    REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES FROM anon, authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
    REVOKE USAGE, SELECT, UPDATE ON SEQUENCES FROM anon, authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
    REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC, anon, authenticated;

-- Create policies for public read-only access (safe for community service)
CREATE POLICY "Allow public read access" ON ipsws FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "Allow public read access" ON devices FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "Allow public read access" ON ipsw_devices FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "Allow public read access" ON entitlement_keys FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "Allow public read access" ON entitlement_values FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "Allow public read access" ON paths FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "Allow public read access" ON entitlements FOR SELECT TO anon, authenticated USING (true);

-- Analyze all tables for optimal query planning
ANALYZE ipsws;
ANALYZE devices;
ANALYZE ipsw_devices;
ANALYZE entitlement_keys;
ANALYZE entitlement_values;
ANALYZE paths;
ANALYZE entitlements;