silo/migrations/001_initial.sql

-- Silo Database Schema
-- Migration: 001_initial
-- Date: 2026-01

BEGIN;

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- For fuzzy text search

--------------------------------------------------------------------------------
-- Part Numbering Schemas
--------------------------------------------------------------------------------

CREATE TABLE schemas (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT UNIQUE NOT NULL,
    version INTEGER NOT NULL DEFAULT 1,
    description TEXT,
    definition JSONB NOT NULL,  -- Parsed YAML schema
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_schemas_name ON schemas(name);

--------------------------------------------------------------------------------
-- Items (Core Entity)
--------------------------------------------------------------------------------

CREATE TABLE items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    part_number TEXT UNIQUE NOT NULL,
    schema_id UUID REFERENCES schemas(id),
    item_type TEXT NOT NULL,  -- 'part', 'assembly', 'drawing', etc.
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    archived_at TIMESTAMPTZ,  -- Soft delete
    current_revision INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_items_part_number ON items(part_number);
CREATE INDEX idx_items_schema ON items(schema_id);
CREATE INDEX idx_items_type ON items(item_type);
CREATE INDEX idx_items_description_trgm ON items USING gin(description gin_trgm_ops);

--------------------------------------------------------------------------------
-- Revisions (Append-Only History)
--------------------------------------------------------------------------------

CREATE TABLE revisions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    revision_number INTEGER NOT NULL,
    properties JSONB NOT NULL DEFAULT '{}',
    file_key TEXT,           -- MinIO object key
    file_version TEXT,       -- MinIO version ID
    file_checksum TEXT,      -- SHA256 of file
    file_size BIGINT,        -- File size in bytes
    thumbnail_key TEXT,      -- MinIO key for thumbnail
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by TEXT,         -- User identifier
    comment TEXT,
    UNIQUE(item_id, revision_number)
);

CREATE INDEX idx_revisions_item ON revisions(item_id);
CREATE INDEX idx_revisions_item_rev ON revisions(item_id, revision_number DESC);

--------------------------------------------------------------------------------
-- Relationships (BOM Structure)
--------------------------------------------------------------------------------

CREATE TYPE relationship_type AS ENUM ('component', 'alternate', 'reference');

CREATE TABLE relationships (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    parent_item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    child_item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    rel_type relationship_type NOT NULL DEFAULT 'component',
    quantity DECIMAL(12, 4),
    unit TEXT,               -- Unit of measure
    reference_designators TEXT[],  -- e.g., ARRAY['R1', 'R2', 'R3']
    child_revision INTEGER,  -- NULL means "latest"
    metadata JSONB,          -- Assembly-specific relationship properties
    parent_revision_id UUID REFERENCES revisions(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT no_self_reference CHECK (parent_item_id != child_item_id)
);

CREATE INDEX idx_relationships_parent ON relationships(parent_item_id);
CREATE INDEX idx_relationships_child ON relationships(child_item_id);
CREATE INDEX idx_relationships_type ON relationships(rel_type);

--------------------------------------------------------------------------------
-- Locations (Physical Inventory Hierarchy)
--------------------------------------------------------------------------------

CREATE TABLE locations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    path TEXT UNIQUE NOT NULL,  -- e.g., 'lab/shelf-a/bin-3'
    name TEXT NOT NULL,
    parent_id UUID REFERENCES locations(id),
    location_type TEXT NOT NULL,
    depth INTEGER NOT NULL DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_locations_path ON locations(path);
CREATE INDEX idx_locations_parent ON locations(parent_id);
CREATE INDEX idx_locations_type ON locations(location_type);

--------------------------------------------------------------------------------
-- Inventory (Item Quantities at Locations)
--------------------------------------------------------------------------------

CREATE TABLE inventory (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    location_id UUID NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
    quantity DECIMAL(12, 4) NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(item_id, location_id)
);

CREATE INDEX idx_inventory_item ON inventory(item_id);
CREATE INDEX idx_inventory_location ON inventory(location_id);

--------------------------------------------------------------------------------
-- Sequence Counters (Part Number Generation)
--------------------------------------------------------------------------------

CREATE TABLE sequences (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    schema_id UUID NOT NULL REFERENCES schemas(id) ON DELETE CASCADE,
    scope TEXT NOT NULL,  -- Scope key, e.g., 'PROTO-AS'
    current_value INTEGER NOT NULL DEFAULT 0,
    UNIQUE(schema_id, scope)
);

CREATE INDEX idx_sequences_schema_scope ON sequences(schema_id, scope);

--------------------------------------------------------------------------------
-- Functions
--------------------------------------------------------------------------------

-- Get next sequence value atomically
CREATE OR REPLACE FUNCTION next_sequence_value(
    p_schema_id UUID,
    p_scope TEXT
) RETURNS INTEGER AS $$
DECLARE
    v_next INTEGER;
BEGIN
    INSERT INTO sequences (schema_id, scope, current_value)
    VALUES (p_schema_id, p_scope, 1)
    ON CONFLICT (schema_id, scope) DO UPDATE
    SET current_value = sequences.current_value + 1
    RETURNING current_value INTO v_next;
    
    RETURN v_next;
END;
$$ LANGUAGE plpgsql;

-- Update item's current revision
CREATE OR REPLACE FUNCTION update_item_revision()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE items 
    SET current_revision = NEW.revision_number,
        updated_at = now()
    WHERE id = NEW.item_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_item_revision
AFTER INSERT ON revisions
FOR EACH ROW EXECUTE FUNCTION update_item_revision();

--------------------------------------------------------------------------------
-- Views
--------------------------------------------------------------------------------

-- Current item state (latest revision)
CREATE VIEW items_current AS
SELECT 
    i.id,
    i.part_number,
    i.item_type,
    i.description,
    i.schema_id,
    i.current_revision,
    i.created_at,
    i.updated_at,
    r.properties,
    r.file_key,
    r.file_version,
    r.thumbnail_key,
    r.created_by AS last_modified_by,
    r.comment AS last_revision_comment
FROM items i
LEFT JOIN revisions r ON r.item_id = i.id AND r.revision_number = i.current_revision
WHERE i.archived_at IS NULL;

-- BOM explosion (single level)
CREATE VIEW bom_single_level AS
SELECT
    rel.parent_item_id,
    parent.part_number AS parent_part_number,
    rel.child_item_id,
    child.part_number AS child_part_number,
    child.description AS child_description,
    rel.rel_type,
    rel.quantity,
    rel.unit,
    rel.reference_designators,
    rel.child_revision,
    COALESCE(rel.child_revision, child.current_revision) AS effective_revision
FROM relationships rel
JOIN items parent ON parent.id = rel.parent_item_id
JOIN items child ON child.id = rel.child_item_id
WHERE parent.archived_at IS NULL AND child.archived_at IS NULL;

COMMIT;