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.
178 lines
6.8 KiB
SQL
178 lines
6.8 KiB
SQL
-- Meshkee CMS — website homepage widgets: category/brand groups and sliders
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- brands: user-defined list order
|
|
-- ---------------------------------------------------------------------------
|
|
ALTER TABLE brands
|
|
ADD COLUMN sort_order INTEGER NOT NULL DEFAULT 0;
|
|
|
|
CREATE INDEX idx_brands_business_sort_order
|
|
ON brands (business_id, sort_order);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- website category groups (curated category rows for the storefront)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE website_category_groups (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
business_id BIGINT NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
sort_order INTEGER 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(),
|
|
|
|
CONSTRAINT website_category_groups_business_id_fkey
|
|
FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_website_category_groups_business_id
|
|
ON website_category_groups (business_id);
|
|
|
|
CREATE TRIGGER website_category_groups_set_updated_at
|
|
BEFORE UPDATE ON website_category_groups
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
CREATE TABLE website_category_group_items (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
group_id BIGINT NOT NULL,
|
|
category_id BIGINT NOT NULL,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT website_category_group_items_group_id_fkey
|
|
FOREIGN KEY (group_id) REFERENCES website_category_groups (id) ON DELETE CASCADE,
|
|
CONSTRAINT website_category_group_items_category_id_fkey
|
|
FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE,
|
|
CONSTRAINT website_category_group_items_unique UNIQUE (group_id, category_id)
|
|
);
|
|
|
|
CREATE INDEX idx_website_category_group_items_group_id
|
|
ON website_category_group_items (group_id);
|
|
|
|
CREATE INDEX idx_website_category_group_items_category_id
|
|
ON website_category_group_items (category_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- website brand groups (curated brand rows for the storefront)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE website_brand_groups (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
business_id BIGINT NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
sort_order INTEGER 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(),
|
|
|
|
CONSTRAINT website_brand_groups_business_id_fkey
|
|
FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_website_brand_groups_business_id
|
|
ON website_brand_groups (business_id);
|
|
|
|
CREATE TRIGGER website_brand_groups_set_updated_at
|
|
BEFORE UPDATE ON website_brand_groups
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
CREATE TABLE website_brand_group_items (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
group_id BIGINT NOT NULL,
|
|
brand_id BIGINT NOT NULL,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT website_brand_group_items_group_id_fkey
|
|
FOREIGN KEY (group_id) REFERENCES website_brand_groups (id) ON DELETE CASCADE,
|
|
CONSTRAINT website_brand_group_items_brand_id_fkey
|
|
FOREIGN KEY (brand_id) REFERENCES brands (id) ON DELETE CASCADE,
|
|
CONSTRAINT website_brand_group_items_unique UNIQUE (group_id, brand_id)
|
|
);
|
|
|
|
CREATE INDEX idx_website_brand_group_items_group_id
|
|
ON website_brand_group_items (group_id);
|
|
|
|
CREATE INDEX idx_website_brand_group_items_brand_id
|
|
ON website_brand_group_items (brand_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- website sliders (multiple sliders per business, each with ordered slides)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE website_sliders (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
business_id BIGINT NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
sort_order INTEGER 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(),
|
|
|
|
CONSTRAINT website_sliders_business_id_fkey
|
|
FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_website_sliders_business_id
|
|
ON website_sliders (business_id);
|
|
|
|
CREATE TRIGGER website_sliders_set_updated_at
|
|
BEFORE UPDATE ON website_sliders
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
CREATE TABLE website_slider_slides (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
slider_id BIGINT NOT NULL,
|
|
image_media_id BIGINT NOT NULL,
|
|
title VARCHAR(255),
|
|
link_url VARCHAR(2048),
|
|
sort_order INTEGER 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(),
|
|
|
|
CONSTRAINT website_slider_slides_slider_id_fkey
|
|
FOREIGN KEY (slider_id) REFERENCES website_sliders (id) ON DELETE CASCADE,
|
|
CONSTRAINT website_slider_slides_image_media_id_fkey
|
|
FOREIGN KEY (image_media_id) REFERENCES media (id) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE INDEX idx_website_slider_slides_slider_id
|
|
ON website_slider_slides (slider_id);
|
|
|
|
CREATE INDEX idx_website_slider_slides_image_media_id
|
|
ON website_slider_slides (image_media_id);
|
|
|
|
CREATE TRIGGER website_slider_slides_set_updated_at
|
|
BEFORE UPDATE ON website_slider_slides
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- permissions
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO permissions (name, slug, group_name, description) VALUES
|
|
('View website widgets', 'website.read', 'website', 'View homepage category/brand groups and sliders'),
|
|
('Manage website widgets', 'website.update', 'website', 'Create and edit homepage category/brand groups and sliders')
|
|
ON CONFLICT (slug) DO NOTHING;
|
|
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug LIKE 'website.%'
|
|
WHERE r.slug IN ('business_owner', 'owner', 'admin')
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug IN ('website.read', 'website.update')
|
|
WHERE r.slug = 'editor'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug = 'website.read'
|
|
WHERE r.slug = 'viewer'
|
|
ON CONFLICT DO NOTHING;
|