Spaces:
Sleeping
Sleeping
File size: 9,516 Bytes
a8e67fc 6bc4034 1361870 6bc4034 1361870 a8e67fc 1361870 e32f628 a8e67fc | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 | -- ============================================================
-- RBAC Migration — adds workspaces, users, channels, and
-- per-role / per-user permission tables.
--
-- Safe to run multiple times (all statements are idempotent).
-- Run AFTER the base schema.sql.
-- ============================================================
-- ── Workspaces (company-level isolation) ────────────────────
create table if not exists workspaces (
id uuid primary key default gen_random_uuid(),
slug text not null unique,
name text not null,
created_at timestamptz not null default now()
);
-- Seed the default workspace that matches existing data
insert into workspaces (id, slug, name)
values ('00000000-0000-0000-0000-000000000001', 'default', 'Default Workspace')
on conflict (slug) do nothing;
-- ── Users (replaces hardcoded credentials) ──────────────────
create table if not exists users (
id uuid primary key default gen_random_uuid(),
workspace_id uuid not null references workspaces(id) on delete cascade,
email text not null,
name text not null,
-- bcrypt hash; null = SSO-only account
password_hash text,
-- engineer | manager | admin | org_admin
role text not null default 'engineer',
is_new_hire boolean not null default false,
-- auto-clear new_hire flag after this date (null = never auto-clear)
new_hire_until date,
is_active boolean not null default true,
invited_by uuid references users(id),
created_at timestamptz not null default now(),
last_active_at timestamptz,
unique(workspace_id, email)
);
create index if not exists users_workspace_email_idx on users (workspace_id, email);
create index if not exists users_role_idx on users (role);
-- ── Teams table ──────────────────────────────────────────────
-- Create teams with team_id PK if it doesn't exist at all.
create table if not exists teams (
team_id text primary key,
cag_snapshot text,
snapshot_at timestamptz,
created_at timestamptz not null default now()
);
-- If teams existed before with a different PK name, add team_id as a column.
do $$ begin
if not exists (
select 1 from information_schema.columns
where table_schema = 'public'
and table_name = 'teams'
and column_name = 'team_id'
) then
alter table teams add column team_id text;
create unique index if not exists teams_team_id_idx on teams (team_id);
end if;
end $$;
-- Extend teams table with RBAC columns.
alter table teams add column if not exists workspace_id uuid references workspaces(id) on delete cascade;
alter table teams add column if not exists name text;
alter table teams add column if not exists slug text;
-- Ensure the default team row exists.
insert into teams (team_id, workspace_id, name, slug)
values ('default', '00000000-0000-0000-0000-000000000001', 'Engineering', 'engineering')
on conflict (team_id) do update
set workspace_id = excluded.workspace_id,
name = excluded.name,
slug = excluded.slug;
-- ── User → Team memberships ──────────────────────────────────
create table if not exists user_teams (
user_id uuid not null references users(id) on delete cascade,
team_id text not null references teams(team_id) on delete cascade,
-- member | lead
role text not null default 'member',
primary key (user_id, team_id)
);
-- ── Channels (scoped data sources within a workspace/team) ───
-- Examples: "Backend Confluence", "HR Payroll Docs", "Public GitHub"
create table if not exists channels (
id uuid primary key default gen_random_uuid(),
workspace_id uuid not null references workspaces(id) on delete cascade,
-- null = workspace-wide channel (not team-specific)
team_id text references teams(team_id) on delete set null,
name text not null,
source_type text, -- github | confluence | notion | slack | file | url | null
-- public | internal | confidential | restricted
sensitivity text not null default 'internal',
created_at timestamptz not null default now()
);
create index if not exists channels_workspace_idx on channels (workspace_id);
create index if not exists channels_team_idx on channels (team_id);
-- Seed the default channel that covers all existing 'default' team_id data
insert into channels (id, workspace_id, team_id, name, source_type, sensitivity)
values (
'00000000-0000-0000-0000-000000000002',
'00000000-0000-0000-0000-000000000001',
'default',
'General',
null,
'internal'
)
on conflict do nothing;
-- ── Role-level channel access defaults ───────────────────────
-- Defines which roles can access a channel without an explicit override.
create table if not exists channel_role_grants (
channel_id uuid not null references channels(id) on delete cascade,
role text not null,
primary key (channel_id, role)
);
-- All roles can access the default general channel
insert into channel_role_grants (channel_id, role) values
('00000000-0000-0000-0000-000000000002', 'engineer'),
('00000000-0000-0000-0000-000000000002', 'manager'),
('00000000-0000-0000-0000-000000000002', 'admin'),
('00000000-0000-0000-0000-000000000002', 'org_admin')
on conflict do nothing;
-- ── Per-user channel permission overrides ────────────────────
-- Grants or revokes a specific user's access to a channel,
-- overriding their role's default grant.
create table if not exists user_channel_permissions (
user_id uuid not null references users(id) on delete cascade,
channel_id uuid not null references channels(id) on delete cascade,
can_read boolean not null default true,
granted_by uuid references users(id),
granted_at timestamptz not null default now(),
primary key (user_id, channel_id)
);
-- ── Add channel_id to existing document tables ───────────────
alter table documents add column if not exists channel_id uuid references channels(id) on delete set null;
alter table chunks add column if not exists channel_id uuid references channels(id) on delete set null;
create index if not exists documents_channel_id_idx on documents (channel_id);
create index if not exists chunks_channel_id_idx on chunks (channel_id);
-- Backfill: existing rows with team_id='default' map to the default channel
update documents
set channel_id = '00000000-0000-0000-0000-000000000002'
where team_id = 'default' and channel_id is null;
update chunks
set channel_id = '00000000-0000-0000-0000-000000000002'
where team_id = 'default' and channel_id is null;
-- ── Audit log ────────────────────────────────────────────────
-- Append-only record of permission changes and sensitive admin actions.
create table if not exists rbac_audit_log (
id uuid primary key default gen_random_uuid(),
actor_id uuid references users(id),
action text not null, -- grant_channel | revoke_channel | change_role | invite_user | deactivate_user
target_type text not null, -- user | channel | team
target_id text not null,
metadata jsonb not null default '{}',
created_at timestamptz not null default now()
);
create index if not exists rbac_audit_log_actor_idx on rbac_audit_log (actor_id);
create index if not exists rbac_audit_log_target_idx on rbac_audit_log (target_type, target_id);
create index if not exists rbac_audit_log_time_idx on rbac_audit_log (created_at desc);
-- ── RLS policies ─────────────────────────────────────────────
alter table users enable row level security;
alter table channels enable row level security;
alter table user_channel_permissions enable row level security;
alter table rbac_audit_log enable row level security;
-- Service role bypasses RLS; these cover anon/authenticated
-- Users can only see their own record (service role sees all)
create policy "users: own record only"
on users for select
using (id::text = current_setting('app.user_id', true));
-- Channels visible only if user has a role grant or explicit permission
create policy "channels: visible to granted users"
on channels for select
using (
id in (
select crg.channel_id
from channel_role_grants crg
where crg.role = current_setting('app.role', true)
)
or
id in (
select ucp.channel_id
from user_channel_permissions ucp
where ucp.user_id::text = current_setting('app.user_id', true)
and ucp.can_read = true
)
);
|