21 lines
738 B
SQL
21 lines
738 B
SQL
-- Tenant DB schema v4.6 — support tables for inventory UI
|
|
|
|
-- Product categories (hierarchical, optional)
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
parent_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_categories_parent ON categories(parent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_categories_active ON categories(is_active);
|
|
|
|
-- Tier-based discount configuration for price tiers 2 and 3
|
|
CREATE TABLE IF NOT EXISTS tier_discounts (
|
|
tier_id INTEGER PRIMARY KEY,
|
|
tier_name VARCHAR(50) NOT NULL,
|
|
discount_pct NUMERIC(5,2) DEFAULT 0
|
|
);
|