schema.sql

-- Arsenal Ship Tracker Database Schema
-- SQLite database for tracking vessels with suspected military modifications

PRAGMA foreign_keys = ON;

-- Vessels table - core entity tracking suspected arsenal ships
CREATE TABLE IF NOT EXISTS vessels (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    mmsi TEXT UNIQUE,
    imo TEXT UNIQUE,
    call_sign TEXT,
    flag_state TEXT,
    vessel_type TEXT,
    length_m REAL,
    beam_m REAL,
    draught REAL,  -- Current draught in meters (from AIS)
    gross_tonnage INTEGER,
    year_built INTEGER,
    owner TEXT,
    classification TEXT CHECK(classification IN ('confirmed', 'suspected', 'monitoring', 'cleared')) DEFAULT 'monitoring',
    threat_level TEXT CHECK(threat_level IN ('critical', 'high', 'medium', 'low', 'unknown')) DEFAULT 'unknown',
    intel_notes TEXT,
    weapons_config TEXT,  -- JSON description of observed weapons
    first_observed DATE,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- Photo storage
    photo_url TEXT,
    -- AI analysis results
    ai_analysis TEXT,
    ai_bluf TEXT,
    ai_analyzed_at TIMESTAMP,
    -- Confidence scoring (from confidence.py)
    confidence_score REAL,
    ais_consistency REAL,
    behavioral_normalcy REAL,
    sar_corroboration REAL,
    deception_likelihood REAL,
    confidence_factors TEXT,  -- JSON breakdown
    confidence_calculated TEXT  -- ISO timestamp
);

-- Position history - AIS track data
CREATE TABLE IF NOT EXISTS positions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vessel_id INTEGER NOT NULL,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    heading REAL,
    speed_knots REAL,
    course REAL,
    nav_status TEXT,
    destination TEXT,  -- Voyage destination (from AIS Type 5)
    eta TEXT,  -- Estimated arrival (from AIS Type 5)
    source TEXT DEFAULT 'manual',  -- ais, satellite, manual
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE CASCADE
);

-- Shipyards and facilities of interest
CREATE TABLE IF NOT EXISTS shipyards (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    geofence_radius_km REAL DEFAULT 2.0,
    facility_type TEXT CHECK(facility_type IN ('shipyard', 'naval_base', 'port', 'anchorage')) DEFAULT 'shipyard',
    threat_association TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Events - timeline of significant vessel activities
CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vessel_id INTEGER NOT NULL,
    event_type TEXT NOT NULL CHECK(event_type IN (
        'shipyard_entry', 'shipyard_exit', 'ais_dark', 'ais_resume',
        'modification_detected', 'weapons_observed', 'registry_change',
        'ownership_change', 'flag_change', 'position_update', 'osint_report',
        'geofence_enter', 'geofence_exit', 'anomaly_detected'
    )),
    severity TEXT CHECK(severity IN ('critical', 'high', 'medium', 'low', 'info')) DEFAULT 'info',
    title TEXT NOT NULL,
    description TEXT,
    source TEXT,
    source_url TEXT,
    latitude REAL,
    longitude REAL,
    metadata TEXT,  -- JSON for additional structured data
    acknowledged INTEGER DEFAULT 0,
    acknowledged_by TEXT,
    acknowledged_at TIMESTAMP,
    event_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE CASCADE
);

-- OSINT reports - news articles, analysis, intelligence
CREATE TABLE IF NOT EXISTS osint_reports (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vessel_id INTEGER,  -- nullable, some reports may be general
    title TEXT NOT NULL,
    source_name TEXT NOT NULL,
    source_url TEXT,
    author TEXT,
    publish_date DATE,
    summary TEXT,
    full_content TEXT,
    key_findings TEXT,  -- JSON array of key points
    reliability TEXT CHECK(reliability IN ('confirmed', 'likely', 'possible', 'unconfirmed', 'doubtful')) DEFAULT 'unconfirmed',
    classification TEXT DEFAULT 'open_source',
    tags TEXT,  -- comma-separated tags
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE SET NULL
);

-- Watchlist - vessels under active monitoring
CREATE TABLE IF NOT EXISTS watchlist (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vessel_id INTEGER UNIQUE NOT NULL,
    priority INTEGER DEFAULT 1,
    alert_on_position INTEGER DEFAULT 1,
    alert_on_dark INTEGER DEFAULT 1,
    alert_on_geofence INTEGER DEFAULT 1,
    notes TEXT,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE CASCADE
);

-- Alerts - generated notifications
CREATE TABLE IF NOT EXISTS alerts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vessel_id INTEGER,
    event_id INTEGER,
    alert_type TEXT NOT NULL,
    severity TEXT CHECK(severity IN ('critical', 'high', 'medium', 'low')) DEFAULT 'medium',
    title TEXT NOT NULL,
    message TEXT,
    acknowledged INTEGER DEFAULT 0,
    acknowledged_by TEXT,
    acknowledged_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE CASCADE,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL
);

-- SAR (Synthetic Aperture Radar) ship detections
CREATE TABLE IF NOT EXISTS sar_detections (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    timestamp TEXT NOT NULL,
    length_m REAL,
    width_m REAL,
    confidence REAL DEFAULT 0.8,
    source_file TEXT,
    detection_id TEXT,
    matched_vessel_id INTEGER,
    match_distance_km REAL,
    is_dark_vessel INTEGER DEFAULT 1,  -- 1 = no AIS match (potential dark vessel)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (matched_vessel_id) REFERENCES vessels(id) ON DELETE SET NULL
);

-- ============================================================================
-- LIVE AIS TRACKING (all vessels in view)
-- ============================================================================

-- All vessels seen via AIS (lightweight, auto-populated)
-- This stores basic identity for every vessel seen, not just watchlisted ones
CREATE TABLE IF NOT EXISTS ais_vessels (
    mmsi TEXT PRIMARY KEY,
    imo TEXT,
    name TEXT,
    call_sign TEXT,
    vessel_type INTEGER,  -- AIS ship type code (0-99)
    vessel_type_text TEXT,  -- Human readable type
    flag TEXT,  -- ISO country code
    length_m REAL,
    beam_m REAL,
    draught REAL,
    destination TEXT,
    eta TEXT,
    first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    position_count INTEGER DEFAULT 0,
    -- Link to full vessel record if promoted to watchlist
    vessel_id INTEGER,
    FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE SET NULL
);

-- Recent positions for all AIS vessels (auto-pruned)
-- Stores last N days of positions for pattern analysis
CREATE TABLE IF NOT EXISTS ais_positions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    mmsi TEXT NOT NULL,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    speed_knots REAL,
    course REAL,
    heading REAL,
    nav_status INTEGER,  -- AIS navigation status code
    timestamp TIMESTAMP NOT NULL,
    FOREIGN KEY (mmsi) REFERENCES ais_vessels(mmsi) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_positions_vessel_id ON positions(vessel_id);
CREATE INDEX IF NOT EXISTS idx_positions_timestamp ON positions(timestamp);
CREATE INDEX IF NOT EXISTS idx_events_vessel_id ON events(vessel_id);
CREATE INDEX IF NOT EXISTS idx_events_event_date ON events(event_date);
CREATE INDEX IF NOT EXISTS idx_events_severity ON events(severity);
CREATE INDEX IF NOT EXISTS idx_osint_vessel_id ON osint_reports(vessel_id);
CREATE INDEX IF NOT EXISTS idx_alerts_acknowledged ON alerts(acknowledged);

-- Additional indexes for query optimization
CREATE INDEX IF NOT EXISTS idx_vessels_mmsi ON vessels(mmsi);
CREATE INDEX IF NOT EXISTS idx_vessels_imo ON vessels(imo);
CREATE INDEX IF NOT EXISTS idx_vessels_threat_level ON vessels(threat_level);
CREATE INDEX IF NOT EXISTS idx_vessels_classification ON vessels(classification);
CREATE INDEX IF NOT EXISTS idx_watchlist_vessel_id ON watchlist(vessel_id);
CREATE INDEX IF NOT EXISTS idx_alerts_vessel_id ON alerts(vessel_id);
CREATE INDEX IF NOT EXISTS idx_positions_composite ON positions(vessel_id, timestamp DESC);

-- SAR detection indexes
CREATE INDEX IF NOT EXISTS idx_sar_detections_timestamp ON sar_detections(timestamp);
CREATE INDEX IF NOT EXISTS idx_sar_detections_dark ON sar_detections(is_dark_vessel);
CREATE INDEX IF NOT EXISTS idx_sar_detections_coords ON sar_detections(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_sar_detections_vessel ON sar_detections(matched_vessel_id);

-- AIS vessel tracking indexes
CREATE INDEX IF NOT EXISTS idx_ais_vessels_name ON ais_vessels(name);
CREATE INDEX IF NOT EXISTS idx_ais_vessels_imo ON ais_vessels(imo);
CREATE INDEX IF NOT EXISTS idx_ais_vessels_last_seen ON ais_vessels(last_seen);
CREATE INDEX IF NOT EXISTS idx_ais_vessels_vessel_id ON ais_vessels(vessel_id);
CREATE INDEX IF NOT EXISTS idx_ais_positions_mmsi ON ais_positions(mmsi);
CREATE INDEX IF NOT EXISTS idx_ais_positions_timestamp ON ais_positions(timestamp);
CREATE INDEX IF NOT EXISTS idx_ais_positions_coords ON ais_positions(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_ais_positions_composite ON ais_positions(mmsi, timestamp DESC);

-- ============================================================================
-- SEED DATA
-- ============================================================================

-- Insert ZHONGDA 79 (primary target vessel)
INSERT INTO vessels (name, mmsi, imo, flag_state, vessel_type, length_m, classification, threat_level, intel_notes, weapons_config, first_observed) VALUES
(
    'ZHONG DA 79',
    '413000000',  -- Placeholder - actual MMSI needs verification
    NULL,
    'China',
    'Container Feeder',
    97.0,
    'confirmed',
    'critical',
    'Commercial container feeder converted to arsenal ship. Observed with containerized VLS, CIWS, and radar systems at Shanghai shipyard. Underwent refit at Longhai shipyard April-August 2025. Currently moored at industrial pier on Huangpu River, Shanghai. NOT listed in PLAN registry - retains civilian designation despite visible military modifications.',
    '{"vls_cells": 60, "vls_type": "Containerized VLS (4 cells per container)", "ciws": "Type 1130 30mm", "decoys": "Type 726", "radar": "Unknown type (containerized)", "possible_missiles": ["CJ-10", "YJ-18", "YJ-21"]}',
    '2025-12-20'
);

-- Insert monitored shipyards
INSERT INTO shipyards (name, location, latitude, longitude, geofence_radius_km, facility_type, threat_association, notes) VALUES
('Hudong-Zhonghua Shipbuilding', 'Shanghai, China', 31.3456, 121.5234, 3.0, 'shipyard', 'confirmed', 'CSSC subsidiary. Primary location for ZHONG DA 79 final conversion. Builds PLAN destroyers and carriers.'),
('Longhai Shipyard', 'Longhai, Fujian, China', 24.4456, 117.8234, 2.0, 'shipyard', 'suspected', 'Location of initial ZHONG DA 79 refit April-August 2025.'),
('Jiangnan Shipyard', 'Shanghai, China', 31.3789, 121.5678, 4.0, 'shipyard', 'confirmed', 'CSSC major shipyard. Type 055 destroyer production.'),
('Dalian Shipbuilding', 'Dalian, Liaoning, China', 38.9234, 121.6345, 4.0, 'shipyard', 'confirmed', 'Major naval shipyard. Aircraft carrier construction.'),
('Wuchang Shipbuilding', 'Wuhan, Hubei, China', 30.5567, 114.3234, 3.0, 'shipyard', 'suspected', 'Inland shipyard. Submarine and frigate production.'),
('Guangzhou Shipyard', 'Guangzhou, Guangdong, China', 23.0789, 113.3456, 3.0, 'shipyard', 'suspected', 'CSSC subsidiary. Coast guard and auxiliary vessels.'),
('Huangpu Shipbuilding', 'Guangzhou, Guangdong, China', 23.1123, 113.4567, 2.5, 'shipyard', 'suspected', 'CSSC. Frigate and corvette construction.'),
('Shanghai Huangpu River Industrial Pier', 'Shanghai, China', 31.2456, 121.4890, 1.5, 'port', 'monitoring', 'Current mooring location of ZHONG DA 79.');

-- Insert position history for ZHONG DA 79
INSERT INTO positions (vessel_id, latitude, longitude, heading, speed_knots, source, timestamp) VALUES
(1, 24.4456, 117.8234, 0, 0, 'satellite', '2025-04-15 08:00:00'),
(1, 24.4456, 117.8234, 0, 0, 'satellite', '2025-06-01 10:00:00'),
(1, 24.4456, 117.8234, 0, 0, 'satellite', '2025-08-10 14:00:00'),
(1, 26.1234, 119.9876, 45, 10.5, 'ais', '2025-08-18 06:00:00'),
(1, 28.9876, 121.2345, 30, 11.2, 'ais', '2025-08-20 18:00:00'),
(1, 31.2456, 121.4890, 180, 2.1, 'ais', '2025-08-22 08:00:00'),
(1, 31.2456, 121.4890, 0, 0, 'satellite', '2025-12-20 12:00:00'),
(1, 31.2456, 121.4890, 0, 0, 'manual', '2025-12-26 09:00:00');

-- Insert events timeline for ZHONG DA 79
INSERT INTO events (vessel_id, event_type, severity, title, description, source, source_url, latitude, longitude, event_date) VALUES
(1, 'shipyard_entry', 'high', 'Entered Longhai Shipyard', 'ZHONG DA 79 entered Longhai shipyard for extended stay. Purpose unknown at time of entry.', 'AIS tracking', NULL, 24.4456, 117.8234, '2025-04-15 08:00:00'),
(1, 'ais_dark', 'medium', 'AIS Signal Lost', 'Vessel went dark for extended period during shipyard stay. Common for refit activities.', 'AIS monitoring', NULL, 24.4456, 117.8234, '2025-04-20 00:00:00'),
(1, 'modification_detected', 'critical', 'Structural Modifications Observed', 'Satellite imagery indicates deck modifications consistent with weapons platform installation.', 'Satellite OSINT', NULL, 24.4456, 117.8234, '2025-06-15 00:00:00'),
(1, 'shipyard_exit', 'high', 'Departed Longhai Shipyard', 'ZHONG DA 79 departed Longhai after approximately 4 months of refit work.', 'AIS tracking', NULL, 24.4456, 117.8234, '2025-08-15 06:00:00'),
(1, 'ais_resume', 'info', 'AIS Signal Resumed', 'Vessel AIS transponder active during transit to Shanghai.', 'AIS monitoring', NULL, 26.1234, 119.9876, '2025-08-18 06:00:00'),
(1, 'shipyard_entry', 'high', 'Moored at Shanghai Industrial Pier', 'Vessel arrived at industrial pier on Huangpu River near Hudong-Zhonghua facilities.', 'AIS tracking', NULL, 31.2456, 121.4890, '2025-08-22 08:00:00'),
(1, 'weapons_observed', 'critical', 'Containerized VLS Confirmed', 'Photographs emerged showing 48-60 containerized VLS cells arranged on deck. Type 1130 CIWS and Type 726 decoy launchers also visible.', 'OSINT - Social Media', 'https://x.com/RomboutLuc/status/2004304247811379628', 31.2456, 121.4890, '2025-12-20 12:00:00'),
(1, 'osint_report', 'critical', 'Major Media Coverage', 'Multiple international news outlets report on arsenal ship conversion. United24 Media, Naval News, The War Zone, Newsweek all publish articles.', 'Multiple sources', NULL, 31.2456, 121.4890, '2025-12-26 00:00:00');

-- Insert OSINT report - United24 Media article (THE NEWS ARTICLE)
INSERT INTO osint_reports (vessel_id, title, source_name, source_url, publish_date, summary, full_content, key_findings, reliability, tags) VALUES
(
    1,
    'Cargo Ship or Warship? China Arms Civilian Vessel With 60 Missiles in Plain Sight',
    'United24 Media',
    'https://united24media.com/latest-news/cargo-ship-or-warship-china-arms-civilian-vessel-with-60-missiles-in-plain-sight-14585',
    '2025-12-26',
    'China converted a civilian container ship (ZHONGDA 79) into an arsenal ship carrying 60 containerized missiles, radar, and CIWS - openly visible at Shanghai shipyard. Ship retains civilian designation despite military conversion.',
    'China has converted the commercial feeder container ship ZHONG DA 79 into an arsenal ship equipped with containerized missile launchers capable of launching up to 60 rockets. The 97-metre vessel was photographed at the Hudong-Zhonghua Shipbuilding facility in Shanghai with modular missile launchers disguised as standard shipping containers mounted on its deck.

The vessel spent several months at a shipyard in Longhai for retrofitting between April and August 2025, before moving to Shanghai for what appears to have been the final phase of the conversion. Its track line suggests it underwent a refit from mid-April to mid-August and has since been moored at an industrial pier on Shanghai''s Huangpu River.

The ship is fitted with multiple containerized vertical launch systems. Observers report at least 48 vertical launch cells arranged in three rows of 16 cells, with each container believed to hold four launch cells. Other assessments suggest the total missile capacity could be as high as 60.

Images also show radar systems, close-in weapon systems (CIWS), and decoy launchers installed on the deck. The ship is armed with a 30-mm Type 1130 CIWS short-range anti-aircraft artillery system, as well as Type 726 decoy launchers.

The specific missiles the vessel could carry remain unclear, but the container VLS cells could likely fire Chinese anti-ship and land-attack missiles such as the CJ-10, YJ-18, and YJ-21. Despite the visible weaponry, ZHONG DA 79 is not listed as part of the People''s Liberation Army Navy or its auxiliary fleet and appears to retain civilian status.

The U.S. Department of Defense noted in its 2024 annual report to Congress: "It is possible the [People''s Republic of China] is developing a launcher that can fit inside a standard commercial shipping container for covert employment of the YJ-18 aboard merchant ships." This development validates those concerns.',
    '["97-meter container feeder converted to arsenal ship", "48-60 containerized VLS cells observed", "Type 1130 CIWS and Type 726 decoys installed", "Refit at Longhai shipyard April-August 2025", "Currently moored at Shanghai Huangpu River", "Retains civilian classification despite weapons", "Possible missiles: CJ-10, YJ-18, YJ-21", "DoD 2024 report predicted containerized YJ-18 development"]',
    'confirmed',
    'arsenal_ship,containerized_weapons,gray_zone,ZHONGDA_79,China,PLAN,dual_use'
);

-- Insert additional OSINT reports from other sources
INSERT INTO osint_reports (vessel_id, title, source_name, source_url, publish_date, summary, key_findings, reliability, tags) VALUES
(1, 'Container Ship Turned Missile Battery Spotted in China', 'Naval News', 'https://www.navalnews.com/naval-news/2025/12/container-ship-turned-missile-battery-spotted-in-china/', '2025-12-26', 'Naval News analysis of ZHONG DA 79 conversion with expert commentary on strategic implications.', '["VLS cells could fire CJ-10, YJ-18, YJ-21 missiles", "Some analysts question if this is proof of concept or mockup", "Noted vessel appears photo-ready for these images"]', 'confirmed', 'arsenal_ship,naval_analysis,expert_commentary'),
(1, 'Photos Show Chinese Cargo Ship Armed With Missile Launchers', 'Newsweek', 'https://www.newsweek.com/photos-chinese-cargo-ship-missile-launchers-11270114', '2025-12-26', 'Newsweek coverage of the arsenal ship revelation with geopolitical context.', '["International media attention on gray zone warfare capability", "Taiwan Strait implications discussed"]', 'confirmed', 'arsenal_ship,media_coverage,geopolitics'),
(1, 'Chinese Cargo Ship Packed Full Of Modular Missile Launchers Emerges', 'The War Zone', 'https://www.twz.com/sea/chinese-cargo-ship-packed-full-of-modular-missile-launchers-emerges', '2025-12-26', 'The War Zone technical analysis of containerized weapons systems and strategic implications.', '["Could turn every ship into a target during conflict", "China could leverage massive cargo fleet for this capability"]', 'confirmed', 'arsenal_ship,technical_analysis,strategic_implications');

-- Add ZHONG DA 79 to watchlist
INSERT INTO watchlist (vessel_id, priority, alert_on_position, alert_on_dark, alert_on_geofence, notes) VALUES
(1, 1, 1, 1, 1, 'Primary tracking target. First confirmed Chinese civilian-to-arsenal conversion. Monitor for movement from Shanghai, AIS gaps, or additional modifications.');

-- Create initial alert
INSERT INTO alerts (vessel_id, alert_type, severity, title, message) VALUES
(1, 'weapons_observed', 'critical', 'Arsenal Ship Confirmed: ZHONG DA 79', 'Photographic evidence confirms containerized VLS installation on civilian vessel. 48-60 missile cells, CIWS, radar, and decoy systems visible. Vessel retains civilian classification.');