50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
-- ============================================================
|
|
-- Inventory enum types
|
|
-- ============================================================
|
|
|
|
CREATE TYPE app.inventory_request_status AS ENUM ('submitted', 'approved', 'rejected', 'fulfilled', 'cancelled');
|
|
|
|
-- ============================================================
|
|
-- Inventory items
|
|
-- ============================================================
|
|
|
|
CREATE TABLE app.inventory_item (
|
|
item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
unit TEXT NOT NULL DEFAULT 'unit',
|
|
current_quantity NUMERIC NOT NULL DEFAULT 0,
|
|
minimum_quantity NUMERIC NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_inventory_item_active ON app.inventory_item(is_active);
|
|
|
|
-- ============================================================
|
|
-- Inventory requests
|
|
-- ============================================================
|
|
|
|
CREATE TABLE app.inventory_request (
|
|
request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
requested_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
|
|
item_id UUID REFERENCES app.inventory_item(item_id),
|
|
item_name TEXT NOT NULL,
|
|
quantity NUMERIC NOT NULL,
|
|
unit TEXT NOT NULL,
|
|
purpose TEXT,
|
|
status app.inventory_request_status NOT NULL DEFAULT 'submitted',
|
|
reviewed_by_user_id UUID REFERENCES app."user"(user_id),
|
|
reviewed_at TIMESTAMPTZ,
|
|
review_notes TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_inventory_request_status ON app.inventory_request(status);
|
|
CREATE INDEX idx_inventory_request_user ON app.inventory_request(requested_by_user_id);
|
|
|
|
-- Enable audit
|
|
SELECT app.enable_audit('inventory_item', 'item_id');
|
|
SELECT app.enable_audit('inventory_request', 'request_id');
|