{
  "table": "DEMO.core.Fused",
  "note": "Few-shot pairs for an NL-to-SQL front end. Every example bounds space AND time. Every time predicate uses interval overlap.",
  "boxes": {
    "antakya_25km": "lat_e5 BETWEEN 3597710 AND 3642710 AND lon_e5 BETWEEN 3588160 AND 3643960",
    "antakya_60km": "lat_e5 BETWEEN 3566160 AND 3674260 AND lon_e5 BETWEEN 3549060 AND 3683060",
    "iskenderun_120km": "lat_e5 BETWEEN 3552000 AND 3768000 AND lon_e5 BETWEEN 3466000 AND 3734000",
    "theater": "lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000",
    "antakya_60km_video": "lat_e5 BETWEEN 3595000 AND 3715000 AND lon_e5 BETWEEN 3560000 AND 3695000   (Antakya to Iskenderun corridor, reaches the video centroid at 37.00 N)"
  },
  "windows": {
    "first_48h": "t_ms < 1675819054000 AND t_end_ms > 1675646254000",
    "first_72h": "t_ms < 1675905454000 AND t_end_ms > 1675646254000",
    "first_96h": "t_ms < 1675991854000 AND t_end_ms > 1675646254000",
    "full_9_days": "t_ms < 1676246400000 AND t_end_ms > 1675468800000"
  },
  "examples": [
    {
      "persona": "Intel Analyst",
      "nl": "What happened across the Antakya to Iskenderun corridor in the first three days?",
      "sql": "SELECT t_ms, kind, src_sys, lat_e5, lon_e5, conf, payload, extent_m, src_ref FROM DEMO.core.Fused WHERE lat_e5 BETWEEN 3595000 AND 3715000 AND lon_e5 BETWEEN 3560000 AND 3695000 AND t_ms < 1675905454000 AND t_end_ms > 1675646254000 ORDER BY kind, t_ms",
      "why": "The flagship shape. One table, one bounded box, one overlapping window, all twelve modalities interleaved in time order. 3,444 rows: 198 imagery cells, 155 video segments, 1,569 destroyed buildings, 1,279 open-source events and the rest. The box reaches 37.00 N so it includes the video rows, which sit at the theater centroid because the clips carry no published geotag."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Same question but only real data, nothing simulated.",
      "sql": "SELECT t_ms, kind, src_sys, lat_e5, lon_e5, payload, src_ref FROM DEMO.core.Fused WHERE lat_e5 BETWEEN 3595000 AND 3715000 AND lon_e5 BETWEEN 3560000 AND 3695000 AND t_ms < 1675905454000 AND t_end_ms > 1675646254000 AND src_sys NOT LIKE '%-SIM' ORDER BY t_ms",
      "why": "Provenance is a column, so real and simulated separate with one predicate and never need different tables."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Show me the worst structural damage visible in imagery near Antakya.",
      "sql": "SELECT row_id, lat_e5, lon_e5, conf, payload, extent_m, src_ref FROM DEMO.core.Fused WHERE kind = 6 AND conf >= 50 AND lat_e5 BETWEEN 3597710 AND 3642710 AND lon_e5 BETWEEN 3588160 AND 3643960 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY conf DESC",
      "why": "kind 6 is electro-optical imagery. conf carries damage severity scaled by 25, so conf >= 50 is severity 2 and above. Pass src_ref to /resolve for the chip."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Which vessels stopped reporting AIS around the time of the quake?",
      "sql": "SELECT entity_id, t_ms, t_end_ms, lat_e5, lon_e5, extent_m, payload, src_ref FROM DEMO.core.Fused WHERE kind = 2 AND lat_e5 BETWEEN 3552000 AND 3768000 AND lon_e5 BETWEEN 3466000 AND 3734000 AND t_ms < 1675991854000 AND t_end_ms > 1675646254000 ORDER BY t_ms",
      "why": "entity_id is the MMSI for vessels. extent_m is how far the unit could have travelled unobserved during the gap."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Find radar contacts that had no AIS track.",
      "sql": "SELECT s.t_ms, s.lat_e5, s.lon_e5, s.conf, g.entity_id AS mmsi, g.payload AS ais_gap, s.payload, s.src_ref FROM DEMO.core.Fused s JOIN DEMO.core.Fused g ON s.ref_row = g.row_id WHERE s.kind = 10 AND s.ref_row > 0 AND g.kind = 2 AND s.lat_e5 BETWEEN 3552000 AND 3768000 AND s.lon_e5 BETWEEN 3466000 AND 3734000 AND s.t_ms < 1675991854000 AND s.t_end_ms > 1675468800000 ORDER BY s.t_ms",
      "why": "A SAR detection whose ref_row is set fell inside a known AIS reporting gap. Two sensors, one conclusion. Follow ref_row to the gap row for the vessel identity."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Pull up the reporting gap that radar contact sits inside.",
      "sql": "SELECT row_id, entity_id, t_ms, t_end_ms, payload, detail, src_ref FROM DEMO.core.Fused WHERE row_id = :ref_row",
      "why": "The citation pattern. Never join. Read ref_row from the first result and fetch that single row by primary key."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Where did the relief flights actually land?",
      "sql": "SELECT lat_e5, lon_e5, COUNT(*) AS flights, SUM(CASE WHEN t_ms > 1675646254000 THEN 1 ELSE 0 END) AS after_mainshock, MIN(payload) AS example FROM DEMO.core.Fused WHERE kind = 11 AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 GROUP BY lat_e5, lon_e5 ORDER BY flights DESC",
      "why": "Gaziantep takes the bulk. Hatay takes none, because its runway was unusable despite sitting 8.9 km from 41 damage cells. The absence is the finding."
    },
    {
      "persona": "Intel Analyst",
      "nl": "What open-source reporting came out of Iskenderun during the port disruption?",
      "sql": "SELECT t_ms, lat_e5, lon_e5, conf, payload, src_ref FROM DEMO.core.Fused WHERE kind = 7 AND lat_e5 BETWEEN 3630000 AND 3690000 AND lon_e5 BETWEEN 3590000 AND 3650000 AND t_ms < 1675991854000 AND t_end_ms > 1675646254000 ORDER BY conf DESC",
      "why": "kind 7 is GDELT. conf is mention count capped at 100, so ordering by it surfaces the most-reported events first. src_ref is the article URL."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Show the earthquake sequence itself, biggest first.",
      "sql": "SELECT t_ms, kind, lat_e5, lon_e5, extent_m, payload, src_ref FROM DEMO.core.Fused WHERE kind = 12 AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY extent_m DESC",
      "why": "kind 12 is the USGS catalog. extent_m is derived from magnitude, so ordering by it ranks by size."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Which response teams were working near the worst damage?",
      "sql": "SELECT t_ms, t_end_ms, lat_e5, lon_e5, payload, ref_row, src_ref FROM DEMO.core.Fused WHERE kind = 3 AND lat_e5 BETWEEN 3597710 AND 3642710 AND lon_e5 BETWEEN 3588160 AND 3643960 AND t_ms < 1676246400000 AND t_end_ms > 1675646254000 ORDER BY t_ms",
      "why": "Teams are placed on the highest-severity imagery cells, so ref_row resolves each one to the chip that justified sending them."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Give me everything at the Ceyhan oil terminal in the first four days.",
      "sql": "SELECT t_ms, kind, src_sys, lat_e5, lon_e5, payload, extent_m, src_ref FROM DEMO.core.Fused WHERE lat_e5 BETWEEN 3660000 AND 3714000 AND lon_e5 BETWEEN 3560000 AND 3627000 AND t_ms < 1675991854000 AND t_end_ms > 1675646254000 ORDER BY t_ms",
      "why": "A 30 km box on the BTC marine terminal at 36.8747 N 35.9297 E. Returns the fixed site, vessel activity, radar contacts and any reporting, without naming a kind."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Everything, whole theater, whole week.",
      "sql": "SELECT t_ms, kind, src_sys, lat_e5, lon_e5, conf, payload, extent_m, src_ref FROM DEMO.core.Fused WHERE lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY t_ms",
      "why": "The widest legal query. Still bounded in both dimensions, so the gateway accepts it."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Where does our imagery damage assessment agree with the independent one?",
      "sql": "SELECT c.src_ref, c.conf AS imagery_severity, COUNT(*) AS destroyed_buildings, c.lat_e5, c.lon_e5, c.extent_m FROM DEMO.core.Fused d JOIN DEMO.core.Fused c ON d.ref_row = c.row_id WHERE d.kind = 13 AND c.kind = 6 AND c.conf >= 50 AND d.lat_e5 BETWEEN 3595000 AND 3715000 AND d.lon_e5 BETWEEN 3560000 AND 3695000 AND d.t_ms < 1676246400000 AND d.t_end_ms > 1675646254000 GROUP BY c.src_ref ORDER BY destroyed_buildings DESC",
      "why": "501 rows in this Antakya 60 km box, 538 theater-wide. The one place a join is correct, because ref_row is a primary key hop rather than a spatial search. Two independent assessments reaching the same conclusion."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Where do they disagree? Show destruction our imagery did not flag as severe.",
      "sql": "SELECT c.src_ref, c.conf AS imagery_severity, COUNT(*) AS destroyed_buildings, c.lat_e5, c.lon_e5, c.extent_m FROM DEMO.core.Fused d JOIN DEMO.core.Fused c ON d.ref_row = c.row_id WHERE d.kind = 13 AND c.kind = 6 AND c.conf < 50 AND d.lat_e5 BETWEEN 3595000 AND 3715000 AND d.lon_e5 BETWEEN 3560000 AND 3695000 AND d.t_ms < 1676246400000 AND d.t_end_ms > 1675646254000 GROUP BY c.src_ref ORDER BY destroyed_buildings DESC",
      "why": "242 rows in this box, 249 theater-wide. The analytically useful half. Destruction was logged on cells our change detection scored below severe, so this is where the imagery pipeline under-called and where an analyst should look first."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Show only the Copernicus EMS damage records.",
      "sql": "SELECT row_id, lat_e5, lon_e5, conf, payload, src_ref FROM DEMO.core.Fused WHERE kind = 13 AND payload LIKE '%CopernicusEMS%' AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675646254000",
      "why": "739 rows. Assessment provenance is carried in the payload and detail, so a single feed can be isolated without a separate table. The others are cscrs (Istanbul Technical University) and microsoft/BuildingFootprints."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Which hospitals are near logged building destruction?",
      "sql": "SELECT DISTINCT h.row_id, h.lat_e5, h.lon_e5, h.payload, src_ref FROM DEMO.core.Fused h, DEMO.core.Fused d WHERE h.kind = 8 AND h.src_sys = 'OSM' AND h.payload LIKE 'hospital%' AND d.kind = 13 AND ABS(h.lat_e5 - d.lat_e5) < 1800 AND ABS(h.lon_e5 - d.lon_e5) < 2200 AND h.lat_e5 BETWEEN 3580000 AND 3830000 AND h.lon_e5 BETWEEN 3430000 AND 3850000 AND h.t_ms < 1676246400000 AND h.t_end_ms > 1675646254000",
      "why": "25 hospitals. Proximity done as a scaled-integer box on both axes, roughly 2 km, never a great-circle computation on the indexed columns."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Find the part of the video showing rescue workers.",
      "sql": "SELECT row_id, conf, payload, src_ref FROM DEMO.core.Fused WHERE kind = 9 AND ref_row > 0 AND payload LIKE '%rescue%' AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY conf DESC",
      "why": "33 segments. src_ref comes back as a #t=start,end media fragment, so the front end gets a playable offset rather than a file. conf is the mean OpenCLIP score across the frames in that segment."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Show me footage of buildings collapsing.",
      "sql": "SELECT row_id, conf, payload, src_ref FROM DEMO.core.Fused WHERE kind = 9 AND ref_row > 0 AND payload LIKE '%collapse dust%' AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY conf DESC",
      "why": "24 segments, best score 98. Collapse dust and structure fire are deliberately separate labels. A single fire-and-smoke bucket conflated them, because a concrete dust plume reads visually as smoke."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Was there any fire?",
      "sql": "SELECT row_id, conf, payload, src_ref FROM DEMO.core.Fused WHERE kind = 9 AND ref_row > 0 AND payload LIKE '%structure fire%' AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY conf DESC",
      "why": "4 segments, verified by eye as genuine structure fires rather than dust. The low count is the correct answer: this was an earthquake, not a fire event."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "What is in that clip, start to finish?",
      "sql": "SELECT row_id, conf, payload, src_ref FROM DEMO.core.Fused WHERE kind = 9 AND ref_row = :parent_row ORDER BY t_ms",
      "why": "Every labelled segment belonging to one parent clip, in playback order. The inverse of the citation pattern: read ref_row to go up to the clip, filter on ref_row to come back down to its segments."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Find video of rescue workers pulling someone from the rubble.",
      "sql": "SELECT row_id, conf, payload, src_ref FROM DEMO.core.Fused WHERE kind = 9 AND ref_row > 0 AND SEMANTIC('rescue workers pulling someone from rubble') >= 0.24 AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY conf DESC",
      "why": "Free text, not one of the 12 precomputed labels. The gateway embeds the phrase and rewrites the predicate to row_id IN (...). Top hit scored 0.305 and four of the five results carry the rescue operation label."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Is there any footage of orange emergency tents?",
      "sql": "SELECT row_id, conf, payload, src_ref FROM DEMO.core.Fused WHERE kind = 9 AND ref_row > 0 AND SEMANTIC('an orange emergency tent') >= 0.21 AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY conf DESC",
      "why": "Top score 0.223, the uncertain band. The ranking is still correct: four of five hits are shelter segments. This is why the score is surfaced rather than used as a silent filter."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Which vessels met each other, and when?",
      "sql": "SELECT t_ms, t_end_ms, entity_id, rel_id, lat_e5, lon_e5, payload FROM DEMO.core.Fused WHERE kind = 1 AND lat_e5 BETWEEN 3552000 AND 3768000 AND lon_e5 BETWEEN 3466000 AND 3734000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY t_ms",
      "why": "kind 1 is a sustained close-proximity event between two units. entity_id is one MMSI and rel_id the other. t_ms to t_end_ms is how long they held station."
    },
    {
      "persona": "Intel Analyst",
      "nl": "What reports were written, and what is each one based on?",
      "sql": "SELECT r.t_ms, r.payload, r.ref_row, s.kind AS cites_kind, s.payload AS cites FROM DEMO.core.Fused r JOIN DEMO.core.Fused s ON r.ref_row = s.row_id WHERE r.kind = 5 AND r.lat_e5 BETWEEN 3595000 AND 3715000 AND r.lon_e5 BETWEEN 3560000 AND 3695000 AND r.t_ms < 1676246400000 AND r.t_end_ms > 1675468800000 ORDER BY r.t_ms",
      "why": "Every kind 5 report carries ref_row pointing at the row it was derived from, so no report stands without evidence. 27 of 27 cite something. Joining reveals whether a report came from imagery, a seismic solution or a reporting gap."
    },
    {
      "persona": "GeoSpatial Analyst",
      "nl": "Show me only real data, nothing simulated.",
      "sql": "SELECT t_ms, kind, src_sys, lat_e5, lon_e5, payload FROM DEMO.core.Fused WHERE src_sys NOT LIKE '%-SIM' AND lat_e5 BETWEEN 3595000 AND 3715000 AND lon_e5 BETWEEN 3560000 AND 3695000 AND t_ms < 1675905454000 AND t_end_ms > 1675646254000 ORDER BY kind, t_ms",
      "why": "Provenance is a column, so real and simulated separate with one predicate and never need different tables."
    },
    {
      "persona": "Intel Analyst",
      "nl": "Give me the next page of those results.",
      "sql": "SELECT row_id, t_ms, kind, payload FROM DEMO.core.Fused WHERE kind = 12 AND lat_e5 BETWEEN 3580000 AND 3830000 AND lon_e5 BETWEEN 3430000 AND 3850000 AND t_ms < 1676246400000 AND t_end_ms > 1675468800000 ORDER BY row_id LIMIT 100 OFFSET 100",
      "why": "Paging is ordinary SQL. Always ORDER BY a stable column, row_id is safest, then walk OFFSET. A response with truncated true means the row cap was hit and more exist."
    }
  ],
  "antipatterns": [
    {
      "bad": "WHERE t_ms BETWEEN 1675646254000 AND 1675905454000",
      "good": "WHERE t_ms < 1675905454000 AND t_end_ms > 1675646254000",
      "why": "BETWEEN on t_ms tests only where a row STARTS, so it drops every row already under way when the window opens. Measured on Antakya 60 km over T0+72h: interval overlap returns 1869 rows across 10 modalities, BETWEEN returns 1866 across 8. Only 3 rows, but the infra and aircraft layers vanish completely and no error is raised. Rows carrying duration on this table are kind 1 meeting, 2 dark, 3 person, 8 infra and 11 aircraft, plus some of kind 5 report. Kinds 6, 7, 10 and 12 are instants and are unaffected."
    },
    {
      "bad": "WHERE lat_e5 BETWEEN 3597710 AND 3642710",
      "good": "WHERE lat_e5 BETWEEN 3597710 AND 3642710 AND lon_e5 BETWEEN 3588160 AND 3643960 AND t_ms < 1675905454000 AND t_end_ms > 1675646254000",
      "why": "Half a bound is no bound. The gateway rejects any query missing either a time range or a lat/lon range."
    },
    {
      "bad": "WHERE lat_e5 / 100000.0 BETWEEN 36.0 AND 36.5",
      "good": "WHERE lat_e5 BETWEEN 3600000 AND 3650000",
      "why": "Computing on the indexed column defeats the index. Scale the literal, never the column."
    },
    {
      "bad": "WHERE kind = 9 AND payload LIKE '%orange tent%'",
      "good": "WHERE kind = 9 AND SEMANTIC('an orange emergency tent') >= 0.21",
      "why": "payload holds only the 12 precomputed vocabulary labels, so LIKE on any other phrase returns nothing and looks like an empty result rather than a mismatched query. Free text must go through SEMANTIC."
    }
  ]
}