mirror of
https://git.meshkee.com/Meshkee/backend.git
synced 2026-08-11 22:30:59 +04:30
Ali Reza
bb59d5e9ba
NestJS backend with Prisma, Docker Compose for Postgres/Redis, and deploy docs for the production VM.
64 lines
2.6 KiB
SQL
64 lines
2.6 KiB
SQL
-- Meshkee CMS — saved operator shopping cards (draft orders)
|
|
|
|
CREATE TABLE shopping_cards (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
business_id BIGINT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
subtotal NUMERIC(12, 2) NOT NULL DEFAULT 0,
|
|
total NUMERIC(12, 2) NOT NULL DEFAULT 0,
|
|
created_by BIGINT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT shopping_cards_business_id_fkey
|
|
FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
|
|
CONSTRAINT shopping_cards_user_id_fkey
|
|
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
|
|
CONSTRAINT shopping_cards_created_by_fkey
|
|
FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
|
|
CONSTRAINT shopping_cards_subtotal_non_negative CHECK (subtotal >= 0),
|
|
CONSTRAINT shopping_cards_total_non_negative CHECK (total >= 0)
|
|
);
|
|
|
|
CREATE INDEX idx_shopping_cards_business_created
|
|
ON shopping_cards (business_id, created_at DESC);
|
|
|
|
CREATE INDEX idx_shopping_cards_business_user
|
|
ON shopping_cards (business_id, user_id, created_at DESC);
|
|
|
|
CREATE TRIGGER shopping_cards_set_updated_at
|
|
BEFORE UPDATE ON shopping_cards
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
CREATE TABLE shopping_card_items (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
shopping_card_id BIGINT NOT NULL,
|
|
store_item_variant_id BIGINT,
|
|
product_id BIGINT NOT NULL,
|
|
product_title VARCHAR(255) NOT NULL,
|
|
variant_sku VARCHAR(100),
|
|
unit_price NUMERIC(12, 2) NOT NULL,
|
|
compare_at_price NUMERIC(12, 2),
|
|
quantity INT NOT NULL,
|
|
line_total NUMERIC(12, 2) NOT NULL,
|
|
selections_snapshot JSONB NOT NULL DEFAULT '[]',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT shopping_card_items_card_id_fkey
|
|
FOREIGN KEY (shopping_card_id) REFERENCES shopping_cards (id) ON DELETE CASCADE,
|
|
CONSTRAINT shopping_card_items_variant_id_fkey
|
|
FOREIGN KEY (store_item_variant_id) REFERENCES store_item_variants (id) ON DELETE SET NULL,
|
|
CONSTRAINT shopping_card_items_product_id_fkey
|
|
FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT,
|
|
CONSTRAINT shopping_card_items_quantity_positive CHECK (quantity > 0),
|
|
CONSTRAINT shopping_card_items_unit_price_non_negative CHECK (unit_price >= 0),
|
|
CONSTRAINT shopping_card_items_line_total_non_negative CHECK (line_total >= 0)
|
|
);
|
|
|
|
CREATE INDEX idx_shopping_card_items_card_id
|
|
ON shopping_card_items (shopping_card_id);
|
|
|
|
CREATE INDEX idx_shopping_card_items_variant_id
|
|
ON shopping_card_items (store_item_variant_id);
|