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.
84 lines
2.9 KiB
SQL
84 lines
2.9 KiB
SQL
-- Meshkee CMS — polymorphic comments (product, blog, portfolio)
|
|
|
|
CREATE TABLE comments (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
business_id BIGINT NOT NULL,
|
|
entity_type media_entity_type NOT NULL,
|
|
entity_id BIGINT NOT NULL,
|
|
author_name VARCHAR(255) NOT NULL,
|
|
author_email VARCHAR(255),
|
|
text TEXT NOT NULL,
|
|
is_approved BOOLEAN NOT NULL DEFAULT FALSE,
|
|
approved_at TIMESTAMPTZ,
|
|
approved_by BIGINT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT comments_business_id_fkey
|
|
FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
|
|
CONSTRAINT comments_approved_by_fkey
|
|
FOREIGN KEY (approved_by) REFERENCES users (id) ON DELETE SET NULL,
|
|
CONSTRAINT comments_text_nonempty CHECK (char_length(trim(text)) > 0),
|
|
CONSTRAINT comments_author_name_nonempty CHECK (char_length(trim(author_name)) > 0)
|
|
);
|
|
|
|
CREATE INDEX idx_comments_business_approval
|
|
ON comments (business_id, is_approved, created_at DESC);
|
|
|
|
CREATE INDEX idx_comments_entity
|
|
ON comments (business_id, entity_type, entity_id, is_approved, created_at DESC);
|
|
|
|
CREATE TRIGGER comments_set_updated_at
|
|
BEFORE UPDATE ON comments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- permissions
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO permissions (name, slug, group_name, description) VALUES
|
|
('View comments', 'comments.read', 'comments', 'View comments on business content'),
|
|
('Approve comments', 'comments.approve', 'comments', 'Approve or reject comments'),
|
|
('Delete comments', 'comments.delete', 'comments', 'Delete comments')
|
|
ON CONFLICT (slug) DO NOTHING;
|
|
|
|
-- business_owner
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug LIKE 'comments.%'
|
|
WHERE r.slug = 'business_owner'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- owner (legacy global role)
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug LIKE 'comments.%'
|
|
WHERE r.slug = 'owner'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- admin
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug IN ('comments.read', 'comments.approve', 'comments.delete')
|
|
WHERE r.slug = 'admin'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- editor: read + approve (moderate), no delete
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug IN ('comments.read', 'comments.approve')
|
|
WHERE r.slug = 'editor'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- viewer: read only
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug = 'comments.read'
|
|
WHERE r.slug = 'viewer'
|
|
ON CONFLICT DO NOTHING;
|