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.
80 lines
3.8 KiB
SQL
80 lines
3.8 KiB
SQL
-- Business team: owners can add staff with limited per-business roles
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- business_users: add role_id for staff permissions (owners use is_owner=true)
|
|
-- ---------------------------------------------------------------------------
|
|
ALTER TABLE business_users
|
|
ADD COLUMN IF NOT EXISTS role_id BIGINT,
|
|
ADD COLUMN IF NOT EXISTS invited_by BIGINT,
|
|
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
|
|
|
|
ALTER TABLE business_users DROP CONSTRAINT IF EXISTS business_users_role_id_fkey;
|
|
ALTER TABLE business_users
|
|
ADD CONSTRAINT business_users_role_id_fkey
|
|
FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE RESTRICT;
|
|
|
|
ALTER TABLE business_users DROP CONSTRAINT IF EXISTS business_users_invited_by_fkey;
|
|
ALTER TABLE business_users
|
|
ADD CONSTRAINT business_users_invited_by_fkey
|
|
FOREIGN KEY (invited_by) REFERENCES users (id) ON DELETE SET NULL;
|
|
|
|
ALTER TABLE business_users DROP CONSTRAINT IF EXISTS business_users_member_role_check;
|
|
ALTER TABLE business_users
|
|
ADD CONSTRAINT business_users_member_role_check CHECK (
|
|
(is_owner = TRUE AND role_id IS NULL)
|
|
OR (is_owner = FALSE AND role_id IS NOT NULL)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_business_users_role_id ON business_users (role_id);
|
|
|
|
DROP TRIGGER IF EXISTS business_users_set_updated_at ON business_users;
|
|
CREATE TRIGGER business_users_set_updated_at
|
|
BEFORE UPDATE ON business_users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- business_staff global role (dashboard access for invited team members)
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO roles (name, slug, description, is_system) VALUES
|
|
('Business Staff', 'business_staff', 'Team member on a business with limited permissions', TRUE)
|
|
ON CONFLICT (slug) DO NOTHING;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- team management permissions (for business owners)
|
|
-- ---------------------------------------------------------------------------
|
|
INSERT INTO permissions (name, slug, group_name, description) VALUES
|
|
('View business team', 'business.team.read', 'business_team', 'View team members of a business'),
|
|
('Invite business team', 'business.team.invite', 'business_team', 'Add team members to a business'),
|
|
('Update business team', 'business.team.update', 'business_team', 'Change team member roles'),
|
|
('Remove business team', 'business.team.remove', 'business_team', 'Remove team members from a business')
|
|
ON CONFLICT (slug) DO NOTHING;
|
|
|
|
-- business_owner gets team management permissions
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug LIKE 'business.team.%'
|
|
WHERE r.slug = 'business_owner'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- admin staff role: almost full business access + team read (not invite/remove owners)
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.slug IN (
|
|
'business.read', 'business.update',
|
|
'domains.read',
|
|
'products.read', 'products.create', 'products.update', 'products.delete', 'products.publish',
|
|
'blogs.read', 'blogs.create', 'blogs.update', 'blogs.delete', 'blogs.publish',
|
|
'portfolios.read', 'portfolios.create', 'portfolios.update', 'portfolios.delete', 'portfolios.publish',
|
|
'media.read', 'media.create', 'media.update', 'media.delete',
|
|
'categories.read', 'categories.create', 'categories.update', 'categories.delete',
|
|
'business.team.read'
|
|
)
|
|
WHERE r.slug = 'admin'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- editor & viewer already seeded in 002 — ensure business_staff has no extra perms
|
|
-- business_staff global role: no permissions (permissions come from business_users.role_id)
|