50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
-- Model versions table for tracking trained model deployments.
|
|
-- Each training run can produce a model version for inference.
|
|
|
|
CREATE TABLE IF NOT EXISTS model_versions (
|
|
version_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
version VARCHAR(50) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
model_path VARCHAR(512) NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'inactive',
|
|
is_active BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
-- Training association
|
|
task_id UUID REFERENCES training_tasks(task_id) ON DELETE SET NULL,
|
|
dataset_id UUID REFERENCES training_datasets(dataset_id) ON DELETE SET NULL,
|
|
|
|
-- Training metrics
|
|
metrics_mAP DOUBLE PRECISION,
|
|
metrics_precision DOUBLE PRECISION,
|
|
metrics_recall DOUBLE PRECISION,
|
|
document_count INTEGER NOT NULL DEFAULT 0,
|
|
|
|
-- Training configuration snapshot
|
|
training_config JSONB,
|
|
|
|
-- File info
|
|
file_size BIGINT,
|
|
|
|
-- Timestamps
|
|
trained_at TIMESTAMP WITH TIME ZONE,
|
|
activated_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_model_versions_version ON model_versions(version);
|
|
CREATE INDEX IF NOT EXISTS idx_model_versions_status ON model_versions(status);
|
|
CREATE INDEX IF NOT EXISTS idx_model_versions_is_active ON model_versions(is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_model_versions_task_id ON model_versions(task_id);
|
|
CREATE INDEX IF NOT EXISTS idx_model_versions_dataset_id ON model_versions(dataset_id);
|
|
CREATE INDEX IF NOT EXISTS idx_model_versions_created ON model_versions(created_at);
|
|
|
|
-- Ensure only one active model at a time
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_model_versions_single_active
|
|
ON model_versions(is_active) WHERE is_active = TRUE;
|
|
|
|
-- Comment
|
|
COMMENT ON TABLE model_versions IS 'Trained model versions for inference deployment';
|