324 lines
14 KiB
PL/PgSQL
324 lines
14 KiB
PL/PgSQL
-- PostgreSQL Schema for Chicken Farm Management System
|
|
-- This file is automatically executed on database initialization
|
|
|
|
-- =============================================================================
|
|
-- CORE TABLES
|
|
-- =============================================================================
|
|
|
|
-- Cycles table
|
|
CREATE TABLE IF NOT EXISTS cycles (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
total_days INTEGER NOT NULL,
|
|
current_day INTEGER NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE,
|
|
chick_in_weight INTEGER,
|
|
doc_in_count INTEGER,
|
|
status VARCHAR(50) NOT NULL CHECK(status IN ('Completed', 'Active', 'Upcoming')),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Kandangs table (chicken houses for mortality/population tracking)
|
|
CREATE TABLE IF NOT EXISTS kandangs (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
cage_uuid VARCHAR(36) UNIQUE, -- External cage UUID for scales API integration
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Kandang-Cycles junction table (doc_in_count per kandang per cycle)
|
|
CREATE TABLE IF NOT EXISTS kandang_cycles (
|
|
id SERIAL PRIMARY KEY,
|
|
kandang_id INTEGER NOT NULL,
|
|
cycle_id VARCHAR(255) NOT NULL,
|
|
doc_in_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (kandang_id) REFERENCES kandangs(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (cycle_id) REFERENCES cycles(id) ON DELETE CASCADE,
|
|
UNIQUE(kandang_id, cycle_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_kandang_cycles_kandang ON kandang_cycles(kandang_id);
|
|
CREATE INDEX IF NOT EXISTS idx_kandang_cycles_cycle ON kandang_cycles(cycle_id);
|
|
|
|
-- Mortality records table
|
|
CREATE TABLE IF NOT EXISTS mortality_records (
|
|
id SERIAL PRIMARY KEY,
|
|
cycle_id VARCHAR(255) NOT NULL,
|
|
day INTEGER NOT NULL,
|
|
mortality_count INTEGER NOT NULL DEFAULT 0,
|
|
chicken_count INTEGER,
|
|
population INTEGER,
|
|
kandang_id INTEGER REFERENCES kandangs(id) ON DELETE SET NULL,
|
|
is_edited BOOLEAN DEFAULT FALSE,
|
|
afkir INTEGER DEFAULT 0,
|
|
panen INTEGER DEFAULT 0,
|
|
berat_panen NUMERIC(10,2) DEFAULT 0,
|
|
keterangan TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (cycle_id) REFERENCES cycles(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Unique index for mortality records (supports per-kandang tracking)
|
|
-- Wrapped in DO block: on existing DBs, kandang_id may not exist yet (added by migration 002)
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'mortality_records' AND column_name = 'kandang_id'
|
|
) THEN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_mortality_cycle_day_kandang') THEN
|
|
CREATE UNIQUE INDEX idx_mortality_cycle_day_kandang
|
|
ON mortality_records(cycle_id, day, COALESCE(kandang_id, -1));
|
|
END IF;
|
|
ELSE
|
|
-- Fallback: ensure old unique constraint exists for pre-migration state
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'mortality_records_cycle_id_day_key'
|
|
) AND NOT EXISTS (
|
|
SELECT 1 FROM pg_indexes WHERE indexname = 'idx_mortality_cycle_day_kandang'
|
|
) THEN
|
|
ALTER TABLE mortality_records ADD CONSTRAINT mortality_records_cycle_id_day_key UNIQUE (cycle_id, day);
|
|
END IF;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_mortality_cycle_id ON mortality_records(cycle_id);
|
|
CREATE INDEX IF NOT EXISTS idx_mortality_day ON mortality_records(day);
|
|
CREATE INDEX IF NOT EXISTS idx_cycles_status ON cycles(status);
|
|
CREATE INDEX IF NOT EXISTS idx_cycles_start_date ON cycles(start_date);
|
|
|
|
-- kandang_id index (only if column exists - added by migration 002 on existing DBs)
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'mortality_records' AND column_name = 'kandang_id'
|
|
) THEN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_mortality_kandang_id') THEN
|
|
CREATE INDEX idx_mortality_kandang_id ON mortality_records(kandang_id);
|
|
END IF;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
-- =============================================================================
|
|
-- CHICKEN COUNTING API TABLES
|
|
-- =============================================================================
|
|
|
|
-- API Keys for authentication
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
key_hash VARCHAR(255) NOT NULL UNIQUE,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
created_by VARCHAR(255),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
last_used_at TIMESTAMP WITH TIME ZONE,
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|
rate_limit INTEGER DEFAULT 1000,
|
|
allowed_ips TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(key_hash);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_active ON api_keys(is_active);
|
|
|
|
-- Location hierarchy: Locations (top level)
|
|
CREATE TABLE IF NOT EXISTS locations (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
address TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Location hierarchy: Coops (buildings within locations)
|
|
CREATE TABLE IF NOT EXISTS coops (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
location_id VARCHAR(255) NOT NULL,
|
|
capacity INTEGER,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_coops_location ON coops(location_id);
|
|
|
|
-- Location hierarchy: Floors (levels within coops)
|
|
CREATE TABLE IF NOT EXISTS floors (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
coop_id VARCHAR(255) NOT NULL,
|
|
floor_number INTEGER,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (coop_id) REFERENCES coops(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_floors_coop ON floors(coop_id);
|
|
|
|
-- Location hierarchy: Cameras (AI/CV cameras on floors)
|
|
CREATE TABLE IF NOT EXISTS cameras (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
camera_type VARCHAR(50) NOT NULL CHECK(camera_type IN ('STANDARD', 'INFRARED', 'THERMAL', 'THREE_D')) DEFAULT 'STANDARD',
|
|
rtsp_url TEXT,
|
|
floor_id VARCHAR(255),
|
|
model VARCHAR(255),
|
|
status VARCHAR(50) NOT NULL CHECK(status IN ('online', 'offline', 'error')) DEFAULT 'offline',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (floor_id) REFERENCES floors(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_cameras_floor ON cameras(floor_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cameras_status ON cameras(status);
|
|
|
|
-- Chicken count records from AI cameras
|
|
CREATE TABLE IF NOT EXISTS chicken_count_records (
|
|
id SERIAL PRIMARY KEY,
|
|
camera_id VARCHAR(255) NOT NULL,
|
|
cycle_id VARCHAR(255) NOT NULL,
|
|
day INTEGER NOT NULL,
|
|
count INTEGER NOT NULL CHECK(count >= 0),
|
|
confidence_score REAL CHECK(confidence_score >= 0 AND confidence_score <= 1),
|
|
processing_status VARCHAR(50) NOT NULL CHECK(processing_status IN ('success', 'processing', 'failed')) DEFAULT 'success',
|
|
source_type VARCHAR(50) NOT NULL CHECK(source_type IN ('ai_camera', 'manual', 'estimated')) DEFAULT 'ai_camera',
|
|
image_url TEXT,
|
|
recorded_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
is_verified BOOLEAN DEFAULT FALSE,
|
|
verified_by VARCHAR(255),
|
|
verified_at TIMESTAMP WITH TIME ZONE,
|
|
notes TEXT,
|
|
metadata JSONB,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (camera_id) REFERENCES cameras(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (cycle_id) REFERENCES cycles(id) ON DELETE CASCADE,
|
|
UNIQUE(camera_id, cycle_id, day, recorded_at)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_count_camera ON chicken_count_records(camera_id);
|
|
CREATE INDEX IF NOT EXISTS idx_count_cycle ON chicken_count_records(cycle_id);
|
|
CREATE INDEX IF NOT EXISTS idx_count_day ON chicken_count_records(day);
|
|
CREATE INDEX IF NOT EXISTS idx_count_recorded_at ON chicken_count_records(recorded_at);
|
|
CREATE INDEX IF NOT EXISTS idx_count_cycle_day ON chicken_count_records(cycle_id, day);
|
|
CREATE INDEX IF NOT EXISTS idx_count_status ON chicken_count_records(processing_status);
|
|
CREATE INDEX IF NOT EXISTS idx_count_verified ON chicken_count_records(is_verified);
|
|
CREATE INDEX IF NOT EXISTS idx_count_metadata ON chicken_count_records USING GIN(metadata);
|
|
|
|
-- Daily aggregated counts for performance
|
|
CREATE TABLE IF NOT EXISTS daily_aggregated_counts (
|
|
id SERIAL PRIMARY KEY,
|
|
cycle_id VARCHAR(255) NOT NULL,
|
|
day INTEGER NOT NULL,
|
|
total_count INTEGER NOT NULL,
|
|
average_confidence REAL,
|
|
camera_count INTEGER,
|
|
recorded_date DATE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (cycle_id) REFERENCES cycles(id) ON DELETE CASCADE,
|
|
UNIQUE(cycle_id, day, recorded_date)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_daily_agg_cycle ON daily_aggregated_counts(cycle_id);
|
|
CREATE INDEX IF NOT EXISTS idx_daily_agg_day ON daily_aggregated_counts(day);
|
|
CREATE INDEX IF NOT EXISTS idx_daily_agg_date ON daily_aggregated_counts(recorded_date);
|
|
|
|
-- =============================================================================
|
|
-- FEED SACK COLUMN CONFIGURATION
|
|
-- =============================================================================
|
|
|
|
-- Single-row table storing the column config as JSONB
|
|
CREATE TABLE IF NOT EXISTS feed_sack_column_config (
|
|
id SERIAL PRIMARY KEY,
|
|
config JSONB NOT NULL DEFAULT '[]',
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- FUNCTIONS AND TRIGGERS
|
|
-- =============================================================================
|
|
|
|
-- Function to automatically update the updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Apply update_updated_at trigger to all tables with updated_at column
|
|
DROP TRIGGER IF EXISTS update_cycles_updated_at ON cycles;
|
|
CREATE TRIGGER update_cycles_updated_at BEFORE UPDATE ON cycles
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_mortality_records_updated_at ON mortality_records;
|
|
CREATE TRIGGER update_mortality_records_updated_at BEFORE UPDATE ON mortality_records
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_kandangs_updated_at ON kandangs;
|
|
CREATE TRIGGER update_kandangs_updated_at BEFORE UPDATE ON kandangs
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_kandang_cycles_updated_at ON kandang_cycles;
|
|
CREATE TRIGGER update_kandang_cycles_updated_at BEFORE UPDATE ON kandang_cycles
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_locations_updated_at ON locations;
|
|
CREATE TRIGGER update_locations_updated_at BEFORE UPDATE ON locations
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_coops_updated_at ON coops;
|
|
CREATE TRIGGER update_coops_updated_at BEFORE UPDATE ON coops
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_floors_updated_at ON floors;
|
|
CREATE TRIGGER update_floors_updated_at BEFORE UPDATE ON floors
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_cameras_updated_at ON cameras;
|
|
CREATE TRIGGER update_cameras_updated_at BEFORE UPDATE ON cameras
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_chicken_count_records_updated_at ON chicken_count_records;
|
|
CREATE TRIGGER update_chicken_count_records_updated_at BEFORE UPDATE ON chicken_count_records
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_daily_aggregated_counts_updated_at ON daily_aggregated_counts;
|
|
CREATE TRIGGER update_daily_aggregated_counts_updated_at BEFORE UPDATE ON daily_aggregated_counts
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- =============================================================================
|
|
-- AUDIT LOG TABLE
|
|
-- =============================================================================
|
|
|
|
-- Audit logs table for tracking all data changes
|
|
-- Note: Timestamps are stored in UTC (TIMESTAMP WITH TIME ZONE)
|
|
-- Frontend converts to WIB (UTC+7) for display
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
table_name VARCHAR(50) NOT NULL,
|
|
record_id VARCHAR(50) NOT NULL,
|
|
action VARCHAR(20) NOT NULL CHECK(action IN ('CREATE', 'UPDATE', 'DELETE')),
|
|
old_values JSONB,
|
|
new_values JSONB,
|
|
changed_fields TEXT[],
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- Stored in UTC
|
|
);
|
|
|
|
-- Indexes for efficient querying
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_table_record ON audit_logs(table_name, record_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_table_created ON audit_logs(table_name, created_at DESC);
|