455 lines
18 KiB
PL/PgSQL
455 lines
18 KiB
PL/PgSQL
-- ==========================================================================
|
|
-- Agencia PSI — Migracao: tabelas de Documentos & Arquivos
|
|
-- ==========================================================================
|
|
-- Criado por: Leonardo Nohama
|
|
-- Data: 2026-03-29 · Sao Carlos/SP — Brasil
|
|
--
|
|
-- Proposito:
|
|
-- Modulo completo de documentos do paciente.
|
|
-- Tabelas: documents, document_access_logs, document_signatures,
|
|
-- document_share_links.
|
|
--
|
|
-- Relacionamentos:
|
|
-- documents.patient_id → patients(id)
|
|
-- documents.owner_id → auth.users(id)
|
|
-- documents.tenant_id → tenants(id)
|
|
-- documents.agenda_evento_id → agenda_eventos(id) (opcional)
|
|
-- document_access_logs.documento_id → documents(id)
|
|
-- document_signatures.documento_id → documents(id)
|
|
-- document_share_links.documento_id → documents(id)
|
|
--
|
|
-- RLS: owner_id = auth.uid() para documents, signatures e share_links.
|
|
-- access_logs: somente INSERT (imutavel) + SELECT por tenant.
|
|
-- ==========================================================================
|
|
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- 1. Tabela principal: documents
|
|
-- --------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS public.documents (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
|
|
-- Contexto de acesso
|
|
owner_id uuid NOT NULL,
|
|
tenant_id uuid NOT NULL,
|
|
|
|
-- Vinculo com paciente
|
|
patient_id uuid NOT NULL REFERENCES public.patients(id) ON DELETE CASCADE,
|
|
|
|
-- Arquivo no Storage
|
|
bucket_path text NOT NULL,
|
|
storage_bucket text NOT NULL DEFAULT 'documents',
|
|
nome_original text NOT NULL,
|
|
mime_type text,
|
|
tamanho_bytes bigint,
|
|
|
|
-- Classificacao
|
|
tipo_documento text NOT NULL DEFAULT 'outro',
|
|
-- laudo | receita | exame | termo_assinado | relatorio_externo
|
|
-- identidade | convenio | declaracao | atestado | recibo | outro
|
|
categoria text,
|
|
descricao text,
|
|
tags text[] DEFAULT '{}',
|
|
|
|
-- Vinculo opcional com sessao/nota
|
|
agenda_evento_id uuid REFERENCES public.agenda_eventos(id) ON DELETE SET NULL,
|
|
session_note_id uuid,
|
|
|
|
-- Visibilidade & controle de acesso
|
|
visibilidade text NOT NULL DEFAULT 'privado',
|
|
-- privado | compartilhado_supervisor | compartilhado_portal
|
|
compartilhado_portal boolean DEFAULT false NOT NULL,
|
|
compartilhado_supervisor boolean DEFAULT false NOT NULL,
|
|
compartilhado_em timestamptz,
|
|
expira_compartilhamento timestamptz,
|
|
|
|
-- Upload pelo paciente (portal)
|
|
enviado_pelo_paciente boolean DEFAULT false NOT NULL,
|
|
status_revisao text DEFAULT 'aprovado',
|
|
-- pendente | aprovado | rejeitado
|
|
revisado_por uuid,
|
|
revisado_em timestamptz,
|
|
|
|
-- Quem fez upload
|
|
uploaded_by uuid NOT NULL,
|
|
uploaded_at timestamptz DEFAULT now() NOT NULL,
|
|
|
|
-- Soft delete com retencao (LGPD / CFP)
|
|
deleted_at timestamptz,
|
|
deleted_by uuid,
|
|
retencao_ate timestamptz,
|
|
|
|
-- Controle
|
|
created_at timestamptz DEFAULT now(),
|
|
updated_at timestamptz DEFAULT now(),
|
|
|
|
CONSTRAINT documents_pkey PRIMARY KEY (id),
|
|
|
|
-- Validacoes
|
|
CONSTRAINT documents_tipo_check CHECK (
|
|
tipo_documento = ANY (ARRAY[
|
|
'laudo', 'receita', 'exame', 'termo_assinado', 'relatorio_externo',
|
|
'identidade', 'convenio', 'declaracao', 'atestado', 'recibo', 'outro'
|
|
])
|
|
),
|
|
CONSTRAINT documents_visibilidade_check CHECK (
|
|
visibilidade = ANY (ARRAY['privado', 'compartilhado_supervisor', 'compartilhado_portal'])
|
|
),
|
|
CONSTRAINT documents_status_revisao_check CHECK (
|
|
status_revisao = ANY (ARRAY['pendente', 'aprovado', 'rejeitado'])
|
|
)
|
|
);
|
|
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- 2. Indices — documents
|
|
-- --------------------------------------------------------------------------
|
|
CREATE INDEX IF NOT EXISTS docs_patient_idx
|
|
ON public.documents USING btree (patient_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS docs_owner_idx
|
|
ON public.documents USING btree (owner_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS docs_tenant_idx
|
|
ON public.documents USING btree (tenant_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS docs_tipo_idx
|
|
ON public.documents USING btree (patient_id, tipo_documento);
|
|
|
|
CREATE INDEX IF NOT EXISTS docs_tags_idx
|
|
ON public.documents USING gin (tags);
|
|
|
|
CREATE INDEX IF NOT EXISTS docs_uploaded_at_idx
|
|
ON public.documents USING btree (patient_id, uploaded_at DESC);
|
|
|
|
-- Excluir soft-deleted da listagem padrao
|
|
CREATE INDEX IF NOT EXISTS docs_active_idx
|
|
ON public.documents USING btree (patient_id, uploaded_at DESC)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
-- Busca textual no nome do arquivo
|
|
CREATE INDEX IF NOT EXISTS docs_nome_trgm_idx
|
|
ON public.documents USING gin (nome_original gin_trgm_ops);
|
|
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- 3. Trigger updated_at — documents
|
|
-- --------------------------------------------------------------------------
|
|
CREATE TRIGGER trg_documents_updated_at
|
|
BEFORE UPDATE ON public.documents
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.set_updated_at();
|
|
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- 4. Trigger: registrar na patient_timeline ao adicionar documento
|
|
-- --------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION public.fn_documents_timeline_insert()
|
|
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$
|
|
BEGIN
|
|
INSERT INTO public.patient_timeline (
|
|
patient_id, tenant_id, evento_tipo,
|
|
titulo, descricao, icone_cor,
|
|
link_ref_tipo, link_ref_id,
|
|
gerado_por, ocorrido_em
|
|
) VALUES (
|
|
NEW.patient_id,
|
|
NEW.tenant_id,
|
|
'documento_adicionado',
|
|
'Documento adicionado: ' || COALESCE(NEW.nome_original, 'arquivo'),
|
|
'Tipo: ' || COALESCE(NEW.tipo_documento, 'outro'),
|
|
'blue',
|
|
'documento',
|
|
NEW.id,
|
|
NEW.uploaded_by,
|
|
NEW.uploaded_at
|
|
);
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE TRIGGER trg_documents_timeline_insert
|
|
AFTER INSERT ON public.documents
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.fn_documents_timeline_insert();
|
|
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- 5. RLS — documents
|
|
-- --------------------------------------------------------------------------
|
|
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "documents: owner full access"
|
|
ON public.documents
|
|
USING (owner_id = auth.uid())
|
|
WITH CHECK (owner_id = auth.uid());
|
|
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- 6. Comentarios — documents
|
|
-- --------------------------------------------------------------------------
|
|
COMMENT ON TABLE public.documents IS 'Documentos e arquivos vinculados a pacientes. Armazenados no Supabase Storage.';
|
|
COMMENT ON COLUMN public.documents.owner_id IS 'Terapeuta dono do documento (auth.uid()).';
|
|
COMMENT ON COLUMN public.documents.tenant_id IS 'Tenant do terapeuta.';
|
|
COMMENT ON COLUMN public.documents.patient_id IS 'Paciente ao qual o documento pertence.';
|
|
COMMENT ON COLUMN public.documents.bucket_path IS 'Caminho do arquivo no Supabase Storage bucket.';
|
|
COMMENT ON COLUMN public.documents.storage_bucket IS 'Nome do bucket no Storage. Default: documents.';
|
|
COMMENT ON COLUMN public.documents.nome_original IS 'Nome original do arquivo enviado.';
|
|
COMMENT ON COLUMN public.documents.mime_type IS 'MIME type do arquivo. Ex: application/pdf, image/jpeg.';
|
|
COMMENT ON COLUMN public.documents.tamanho_bytes IS 'Tamanho do arquivo em bytes.';
|
|
COMMENT ON COLUMN public.documents.tipo_documento IS 'Tipo: laudo|receita|exame|termo_assinado|relatorio_externo|identidade|convenio|declaracao|atestado|recibo|outro.';
|
|
COMMENT ON COLUMN public.documents.categoria IS 'Categoria livre para organizacao adicional.';
|
|
COMMENT ON COLUMN public.documents.tags IS 'Tags livres para busca e filtro. Array de text.';
|
|
COMMENT ON COLUMN public.documents.visibilidade IS 'privado|compartilhado_supervisor|compartilhado_portal.';
|
|
COMMENT ON COLUMN public.documents.compartilhado_portal IS 'true = visivel para o paciente no portal.';
|
|
COMMENT ON COLUMN public.documents.compartilhado_supervisor IS 'true = visivel para o supervisor.';
|
|
COMMENT ON COLUMN public.documents.enviado_pelo_paciente IS 'true = upload feito pelo paciente via portal.';
|
|
COMMENT ON COLUMN public.documents.status_revisao IS 'pendente|aprovado|rejeitado — para uploads do paciente.';
|
|
COMMENT ON COLUMN public.documents.deleted_at IS 'Soft delete: data da exclusao. NULL = ativo.';
|
|
COMMENT ON COLUMN public.documents.retencao_ate IS 'LGPD/CFP: arquivo retido ate esta data mesmo apos soft delete.';
|
|
|
|
|
|
-- ==========================================================================
|
|
-- 7. Tabela: document_access_logs (imutavel — auditoria)
|
|
-- ==========================================================================
|
|
CREATE TABLE IF NOT EXISTS public.document_access_logs (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
|
|
documento_id uuid NOT NULL REFERENCES public.documents(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL,
|
|
|
|
-- Acao realizada
|
|
acao text NOT NULL,
|
|
-- visualizou | baixou | imprimiu | compartilhou | assinou
|
|
user_id uuid,
|
|
ip inet,
|
|
user_agent text,
|
|
|
|
acessado_em timestamptz DEFAULT now() NOT NULL,
|
|
|
|
CONSTRAINT document_access_logs_pkey PRIMARY KEY (id),
|
|
|
|
CONSTRAINT dal_acao_check CHECK (
|
|
acao = ANY (ARRAY['visualizou', 'baixou', 'imprimiu', 'compartilhou', 'assinou'])
|
|
)
|
|
);
|
|
|
|
-- Indices
|
|
CREATE INDEX IF NOT EXISTS dal_documento_idx
|
|
ON public.document_access_logs USING btree (documento_id, acessado_em DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS dal_tenant_idx
|
|
ON public.document_access_logs USING btree (tenant_id, acessado_em DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS dal_user_idx
|
|
ON public.document_access_logs USING btree (user_id, acessado_em DESC);
|
|
|
|
-- RLS — somente INSERT (imutavel) + SELECT
|
|
ALTER TABLE public.document_access_logs ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "dal: tenant members can insert"
|
|
ON public.document_access_logs
|
|
FOR INSERT
|
|
WITH CHECK (true);
|
|
|
|
CREATE POLICY "dal: tenant members can select"
|
|
ON public.document_access_logs
|
|
FOR SELECT
|
|
USING (tenant_id IN (
|
|
SELECT tm.tenant_id FROM public.tenant_members tm
|
|
WHERE tm.user_id = auth.uid() AND tm.status = 'active'
|
|
));
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE public.document_access_logs IS 'Log imutavel de acessos a documentos. Conformidade CFP e LGPD. Sem UPDATE/DELETE.';
|
|
COMMENT ON COLUMN public.document_access_logs.acao IS 'visualizou|baixou|imprimiu|compartilhou|assinou.';
|
|
|
|
|
|
-- ==========================================================================
|
|
-- 8. Tabela: document_signatures (assinatura eletronica)
|
|
-- ==========================================================================
|
|
CREATE TABLE IF NOT EXISTS public.document_signatures (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
|
|
documento_id uuid NOT NULL REFERENCES public.documents(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL,
|
|
|
|
-- Signatario
|
|
signatario_tipo text NOT NULL,
|
|
-- paciente | responsavel_legal | terapeuta
|
|
signatario_id uuid,
|
|
signatario_nome text,
|
|
signatario_email text,
|
|
|
|
-- Ordem e status
|
|
ordem smallint DEFAULT 1 NOT NULL,
|
|
status text NOT NULL DEFAULT 'pendente',
|
|
-- pendente | enviado | assinado | recusado | expirado
|
|
|
|
-- Dados da assinatura (preenchidos ao assinar)
|
|
ip inet,
|
|
user_agent text,
|
|
assinado_em timestamptz,
|
|
hash_documento text,
|
|
|
|
-- Controle
|
|
criado_em timestamptz DEFAULT now(),
|
|
atualizado_em timestamptz DEFAULT now(),
|
|
|
|
CONSTRAINT document_signatures_pkey PRIMARY KEY (id),
|
|
|
|
CONSTRAINT ds_signatario_tipo_check CHECK (
|
|
signatario_tipo = ANY (ARRAY['paciente', 'responsavel_legal', 'terapeuta'])
|
|
),
|
|
CONSTRAINT ds_status_check CHECK (
|
|
status = ANY (ARRAY['pendente', 'enviado', 'assinado', 'recusado', 'expirado'])
|
|
)
|
|
);
|
|
|
|
-- Indices
|
|
CREATE INDEX IF NOT EXISTS ds_documento_idx
|
|
ON public.document_signatures USING btree (documento_id, ordem);
|
|
|
|
CREATE INDEX IF NOT EXISTS ds_tenant_idx
|
|
ON public.document_signatures USING btree (tenant_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS ds_status_idx
|
|
ON public.document_signatures USING btree (documento_id, status);
|
|
|
|
-- Trigger updated_at
|
|
CREATE TRIGGER trg_ds_updated_at
|
|
BEFORE UPDATE ON public.document_signatures
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.set_updated_at();
|
|
|
|
-- Trigger: ao assinar, registrar na patient_timeline
|
|
CREATE OR REPLACE FUNCTION public.fn_document_signature_timeline()
|
|
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$
|
|
DECLARE
|
|
v_patient_id uuid;
|
|
v_tenant_id uuid;
|
|
v_doc_nome text;
|
|
BEGIN
|
|
IF NEW.status = 'assinado' AND (OLD.status IS NULL OR OLD.status <> 'assinado') THEN
|
|
SELECT d.patient_id, d.tenant_id, d.nome_original
|
|
INTO v_patient_id, v_tenant_id, v_doc_nome
|
|
FROM public.documents d
|
|
WHERE d.id = NEW.documento_id;
|
|
|
|
IF v_patient_id IS NOT NULL THEN
|
|
INSERT INTO public.patient_timeline (
|
|
patient_id, tenant_id, evento_tipo,
|
|
titulo, descricao, icone_cor,
|
|
link_ref_tipo, link_ref_id,
|
|
gerado_por, ocorrido_em
|
|
) VALUES (
|
|
v_patient_id,
|
|
v_tenant_id,
|
|
'documento_assinado',
|
|
'Documento assinado: ' || COALESCE(v_doc_nome, 'documento'),
|
|
'Assinado por ' || COALESCE(NEW.signatario_nome, NEW.signatario_tipo),
|
|
'green',
|
|
'documento',
|
|
NEW.documento_id,
|
|
NEW.signatario_id,
|
|
NEW.assinado_em
|
|
);
|
|
END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE TRIGGER trg_ds_timeline
|
|
AFTER UPDATE ON public.document_signatures
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.fn_document_signature_timeline();
|
|
|
|
-- RLS
|
|
ALTER TABLE public.document_signatures ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "ds: tenant members access"
|
|
ON public.document_signatures
|
|
USING (tenant_id IN (
|
|
SELECT tm.tenant_id FROM public.tenant_members tm
|
|
WHERE tm.user_id = auth.uid() AND tm.status = 'active'
|
|
))
|
|
WITH CHECK (tenant_id IN (
|
|
SELECT tm.tenant_id FROM public.tenant_members tm
|
|
WHERE tm.user_id = auth.uid() AND tm.status = 'active'
|
|
));
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE public.document_signatures IS 'Assinaturas eletronicas de documentos. Cada signatario tem seu registro.';
|
|
COMMENT ON COLUMN public.document_signatures.signatario_tipo IS 'paciente|responsavel_legal|terapeuta.';
|
|
COMMENT ON COLUMN public.document_signatures.status IS 'pendente|enviado|assinado|recusado|expirado.';
|
|
COMMENT ON COLUMN public.document_signatures.hash_documento IS 'Hash SHA-256 do documento no momento da assinatura. Garante integridade.';
|
|
COMMENT ON COLUMN public.document_signatures.ip IS 'IP do signatario no momento da assinatura.';
|
|
|
|
|
|
-- ==========================================================================
|
|
-- 9. Tabela: document_share_links (links temporarios)
|
|
-- ==========================================================================
|
|
CREATE TABLE IF NOT EXISTS public.document_share_links (
|
|
id uuid DEFAULT gen_random_uuid() NOT NULL,
|
|
|
|
documento_id uuid NOT NULL REFERENCES public.documents(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL,
|
|
|
|
-- Token unico para o link
|
|
token text NOT NULL DEFAULT encode(gen_random_bytes(32), 'hex'),
|
|
|
|
-- Limites
|
|
expira_em timestamptz NOT NULL,
|
|
usos_max smallint DEFAULT 5 NOT NULL,
|
|
usos smallint DEFAULT 0 NOT NULL,
|
|
|
|
-- Quem criou
|
|
criado_por uuid NOT NULL,
|
|
criado_em timestamptz DEFAULT now(),
|
|
|
|
-- Controle
|
|
ativo boolean DEFAULT true NOT NULL,
|
|
|
|
CONSTRAINT document_share_links_pkey PRIMARY KEY (id),
|
|
CONSTRAINT dsl_token_unique UNIQUE (token)
|
|
);
|
|
|
|
-- Indices
|
|
CREATE INDEX IF NOT EXISTS dsl_documento_idx
|
|
ON public.document_share_links USING btree (documento_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS dsl_token_idx
|
|
ON public.document_share_links USING btree (token)
|
|
WHERE ativo = true;
|
|
|
|
CREATE INDEX IF NOT EXISTS dsl_expira_idx
|
|
ON public.document_share_links USING btree (expira_em)
|
|
WHERE ativo = true;
|
|
|
|
-- RLS
|
|
ALTER TABLE public.document_share_links ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "dsl: creator full access"
|
|
ON public.document_share_links
|
|
USING (criado_por = auth.uid())
|
|
WITH CHECK (criado_por = auth.uid());
|
|
|
|
-- Politica publica de leitura por token (para acesso externo sem login)
|
|
CREATE POLICY "dsl: public read by token"
|
|
ON public.document_share_links
|
|
FOR SELECT
|
|
USING (ativo = true AND expira_em > now() AND usos < usos_max);
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE public.document_share_links IS 'Links temporarios assinados para compartilhar documento com profissional externo.';
|
|
COMMENT ON COLUMN public.document_share_links.token IS 'Token unico gerado automaticamente (32 bytes hex).';
|
|
COMMENT ON COLUMN public.document_share_links.expira_em IS 'Data/hora de expiracao do link.';
|
|
COMMENT ON COLUMN public.document_share_links.usos_max IS 'Numero maximo de acessos permitidos.';
|
|
COMMENT ON COLUMN public.document_share_links.usos IS 'Numero de vezes que o link ja foi acessado.';
|
|
|
|
|
|
-- ==========================================================================
|
|
-- FIM DA MIGRACAO 005
|
|
-- ==========================================================================
|