60 lines
2.2 KiB
SQL
60 lines
2.2 KiB
SQL
-- =========================================================
|
|
-- TABLERO CDC - HISTORIAL DE ACTIVIDAD POR PROYECTO
|
|
-- Ejecutar en Supabase SQL Editor antes de desplegar esta versión.
|
|
--
|
|
-- Crea una tabla pequeña para registrar comentarios y movimientos
|
|
-- dentro de cada proyecto, sin tocar el Google Sheet ni n8n.
|
|
-- =========================================================
|
|
|
|
create table if not exists public.tablero_cdc_project_activity (
|
|
id uuid primary key default gen_random_uuid(),
|
|
project_id uuid not null references public.tablero_cdc_projects(id) on delete cascade,
|
|
actor_id uuid null,
|
|
actor_email text null,
|
|
actor_name text null,
|
|
activity_type text not null default 'note',
|
|
title text not null,
|
|
description text null,
|
|
extra_data jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists tablero_cdc_project_activity_project_created_idx
|
|
on public.tablero_cdc_project_activity(project_id, created_at desc);
|
|
|
|
alter table public.tablero_cdc_project_activity enable row level security;
|
|
|
|
drop policy if exists "tablero_cdc_project_activity_select_authenticated" on public.tablero_cdc_project_activity;
|
|
drop policy if exists "tablero_cdc_project_activity_insert_authenticated" on public.tablero_cdc_project_activity;
|
|
|
|
create policy "tablero_cdc_project_activity_select_authenticated"
|
|
on public.tablero_cdc_project_activity
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
create policy "tablero_cdc_project_activity_insert_authenticated"
|
|
on public.tablero_cdc_project_activity
|
|
for insert
|
|
to authenticated
|
|
with check (auth.uid() is not null);
|
|
|
|
grant select, insert on public.tablero_cdc_project_activity to authenticated;
|
|
|
|
-- Realtime opcional para futuras mejoras. No es obligatorio para que la app funcione.
|
|
do $$
|
|
begin
|
|
if not exists (
|
|
select 1
|
|
from pg_publication_tables
|
|
where pubname = 'supabase_realtime'
|
|
and schemaname = 'public'
|
|
and tablename = 'tablero_cdc_project_activity'
|
|
) then
|
|
alter publication supabase_realtime add table public.tablero_cdc_project_activity;
|
|
end if;
|
|
end $$;
|
|
|
|
-- Validación opcional después de crear la tabla:
|
|
-- select count(*) from public.tablero_cdc_project_activity;
|