Add API for working with "annotated collections"

[rudokemper]

Feature Request

• We introduce a new persistent virtual data structure in GC Explorer: annotated collection, stored in the guardianconnector metastore database (where views config is also stored).
• This introduces a curation layer in the GC Explorer architecture to allow the user to group and annotate disparate data entries into coherent datasets.
• Annotated collections do not mutate source data. Rather, they are read-only, virtual groupings of data and any associated annotations (for example, “dataset name”, “creator(s)”, “source tools”).

More here: https://docs.google.com/document/d/1XWYOaNrZrSPe5Lrt6hgKA7DXwNZHhKFbCkpAtKwLdFM/edit?tab=t.0#heading=h.b1rqq05uqi6z

More forthcoming. Placeholder for now.

User Stories & Acceptance Criteria

UI Sketch (or Screenshot)

Implementation Plan

(may be added later, before work begins)

[conservationtimothy]

@rudokemper Riffing here on design, we will talk more:

-- Add to guardianconnector metastore database

-- Main incidents table
CREATE TABLE incidents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    description TEXT,
    incident_type TEXT, -- "gold_mining", "selective_logging", etc.
    status TEXT DEFAULT 'suspected', -- "suspected", "validated", "resolved"
    responsible_party TEXT,
    impact_description TEXT,
    supporting_evidence JSONB, -- Store file paths, URLs, etc.
    created_by TEXT NOT NULL, -- Auth0 user ID
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT false -- Currently active incident
);

-- Link incidents to specific data entries from various tables
CREATE TABLE incident_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    incident_id UUID REFERENCES incidents(id) ON DELETE CASCADE,
    source_table TEXT NOT NULL, -- "alerts_data", "mapeo_data", etc.
    source_id TEXT NOT NULL, -- The actual row ID from the source table
    source_data JSONB, -- Snapshot of the source data at time of creation
    added_by TEXT NOT NULL, -- Auth0 user ID
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    notes TEXT, -- Optional notes about why this entry was included
    UNIQUE(incident_id, source_table, source_id)
);

-- Indexes for performance
CREATE INDEX idx_incidents_created_by ON incidents(created_by);
CREATE INDEX idx_incidents_status ON incidents(status);
CREATE INDEX idx_incidents_active ON incidents(is_active);
CREATE INDEX idx_incident_entries_incident_id ON incident_entries(incident_id);
CREATE INDEX idx_incident_entries_source ON incident_entries(source_table, source_id);

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_incidents_updated_at 
    BEFORE UPDATE ON incidents 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

// Create an incident
POST /api/incidents
Body: {
  name: string,
  description?: string,
  incident_type?: string,
  status?: string,
  responsible_party?: string,
  impact_description?: string,
  supporting_evidence?: object,
  entries: Array<{
    source_table: string,
    source_id: string,
    notes?: string
  }>
}
Response: { incident: Incident }
// Update an incident
PUT /api/incidents/{incident_id}
Body: {
  name?: string,
  description?: string,
  incident_type?: string,
  status?: string,
  responsible_party?: string,
  impact_description?: string,
  supporting_evidence?: object
}
Response: { incident: Incident }
//Add entries to incident
POST /api/incidents/{incident_id}/entries
Body: {
  entries: Array<{
    source_table: string,
    source_id: string,
    notes?: string
  }>
}
Response: { entries: IncidentEntry[] }

It could work like:

POST /api/incidents
{
  "name": "Illegal road construction near Wapichan territory",
  "description": "Suspected illegal logging activity with new road construction",
  "incident_type": "selective_logging",
  "status": "suspected",
  "responsible_party": "Unknown - under investigation",
  "impact_description": "New road construction observed in protected area",
  "supporting_evidence": {
    "photos": ["/uploads/road_construction_2025_07_30.jpg"],
    "documents": ["/uploads/land_use_permit_check.pdf"]
  },
  "entries": [
    {"source_table": "alerts_data", "source_id": "alert_123", "notes": "Deforestation alert"},
    {"source_table": "alerts_data", "source_id": "alert_124", "notes": "Road construction alert"},
    {"source_table": "alerts_data", "source_id": "alert_125", "notes": "Follow-up alert"},
    {"source_table": "mapeo_data", "source_id": "mapeo_456", "notes": "Community observation"},
    {"source_table": "mapeo_data", "source_id": "mapeo_457", "notes": "Field verification"},
    {"source_table": "infrastructure_data", "source_id": "road_789", "notes": "New road feature"}
  ]
}

[conservationtimothy]

More riffing:

-- Add to guardianconnector metastore database

-- Parent table for all annotated collections
CREATE TABLE annotated_collections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    description TEXT,
    collection_type TEXT NOT NULL, -- "incident", "wildlife_sighting", "research_plot", etc.
    status TEXT DEFAULT 'active', -- "active", "archived", "draft"
    created_by TEXT NOT NULL, -- Auth0 user ID
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT false, -- Currently active collection
    metadata JSONB DEFAULT '{}' -- Type-specific metadata
);

-- Type-specific tables that extend the base collection
CREATE TABLE incidents (
    collection_id UUID PRIMARY KEY REFERENCES annotated_collections(id) ON DELETE CASCADE,
    incident_type TEXT, -- "gold_mining", "selective_logging", etc.
    responsible_party TEXT,
    impact_description TEXT,
    supporting_evidence JSONB -- Store file paths, URLs, etc.
);

CREATE TABLE wildlife_sightings (
    collection_id UUID PRIMARY KEY REFERENCES annotated_collections(id) ON DELETE CASCADE,
    species TEXT,
    observation_type TEXT, -- "camera_trap", "direct_observation", "track", etc.
    behavior_notes TEXT,
    habitat_type TEXT,
    weather_conditions TEXT,
    observer_confidence TEXT -- "high", "medium", "low"
);

-- Future collection types can be added here:
-- CREATE TABLE research_plots (...);
-- CREATE TABLE community_surveys (...);

-- Link collections to specific data entries from various tables
CREATE TABLE collection_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    collection_id UUID REFERENCES annotated_collections(id) ON DELETE CASCADE,
    source_table TEXT NOT NULL, -- "alerts_data", "mapeo_data", "camera_trap_data", etc.
    source_id TEXT NOT NULL, -- The actual row ID from the source table
    source_data JSONB, -- Snapshot of the source data at time of creation
    added_by TEXT NOT NULL, -- Auth0 user ID
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    notes TEXT, -- Optional notes about why this entry was included
    UNIQUE(collection_id, source_table, source_id)
);

-- Indexes for performance
CREATE INDEX idx_annotated_collections_type ON annotated_collections(collection_type);
CREATE INDEX idx_annotated_collections_created_by ON annotated_collections(created_by);
CREATE INDEX idx_annotated_collections_status ON annotated_collections(status);
CREATE INDEX idx_annotated_collections_active ON annotated_collections(is_active);
CREATE INDEX idx_collection_entries_collection_id ON collection_entries(collection_id);
CREATE INDEX idx_collection_entries_source ON collection_entries(source_table, source_id);

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_annotated_collections_updated_at 
    BEFORE UPDATE ON annotated_collections 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

could be used like:

// Create an incident collection
POST /api/collections
{
  "name": "Illegal road construction near Wapichan territory",
  "description": "Suspected illegal logging activity with new road construction",
  "collection_type": "incident",
  "status": "active",
  "type_specific_data": {
    "incident_type": "selective_logging",
    "responsible_party": "Unknown - under investigation",
    "impact_description": "New road construction observed in protected area",
    "supporting_evidence": {
      "photos": ["/uploads/road_construction_2025_07_30.jpg"],
      "documents": ["/uploads/land_use_permit_check.pdf"]
    }
  },
  "entries": [
    {"source_table": "alerts_data", "source_id": "alert_123", "notes": "Deforestation alert"},
    {"source_table": "mapeo_data", "source_id": "mapeo_456", "notes": "Community observation"}
  ]
}