30 lines
1.6 KiB
SQL
30 lines
1.6 KiB
SQL
-- Migration: Create item_classification table
|
|
-- This table stores classification data for items in the grocery_list table
|
|
-- Each row in grocery_list can have ONE corresponding classification row
|
|
|
|
CREATE TABLE IF NOT EXISTS item_classification (
|
|
id INTEGER PRIMARY KEY REFERENCES grocery_list(id) ON DELETE CASCADE,
|
|
item_type VARCHAR(50) NOT NULL,
|
|
item_group VARCHAR(100) NOT NULL,
|
|
zone VARCHAR(100),
|
|
confidence DECIMAL(3,2) DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),
|
|
source VARCHAR(20) DEFAULT 'user' CHECK (source IN ('user', 'ml', 'default')),
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Index for faster lookups by type
|
|
CREATE INDEX IF NOT EXISTS idx_item_classification_type ON item_classification(item_type);
|
|
|
|
-- Index for zone-based queries
|
|
CREATE INDEX IF NOT EXISTS idx_item_classification_zone ON item_classification(zone);
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE item_classification IS 'Stores classification metadata for grocery list items';
|
|
COMMENT ON COLUMN item_classification.id IS 'Foreign key to grocery_list.id (one-to-one relationship)';
|
|
COMMENT ON COLUMN item_classification.item_type IS 'High-level category (produce, meat, dairy, etc.)';
|
|
COMMENT ON COLUMN item_classification.item_group IS 'Subcategory within item_type (filtered by type)';
|
|
COMMENT ON COLUMN item_classification.zone IS 'Store zone/location (optional)';
|
|
COMMENT ON COLUMN item_classification.confidence IS 'Confidence score 0-1 (1.0 for user-provided, lower for ML-predicted)';
|
|
COMMENT ON COLUMN item_classification.source IS 'Source of classification: user, ml, or default';
|