nala/Investidor-V2
1
0
Fork 0
Code Issues Pull Requests Actions 9 Packages Projects Releases Wiki Activity
Files
main
Investidor-V2/migrations/001_mne_schema.sql
T
nala 3dc7c5c649
tests / ci (push) Canceled after 0s
Details
commit 30/9
2026-09-30 09:05:43 +02:00

116 lines
35 KiB
PL/PgSQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- Adaptado para PostgreSQL 17 por autorização do utilizador. Executar isoladamente e interromper no primeiro erro.
-- UP MIGRATION: PostgreSQL 17 + PostGIS — esquema mne
BEGIN;
DO $pg17$
BEGIN
IF current_setting('server_version_num')::integer < 170000 OR current_setting('server_version_num')::integer >= 180000 THEN
RAISE EXCEPTION 'Este script requer PostgreSQL 17.x; versão detectada: %', current_setting('server_version');
END IF;
END
$pg17$;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE SCHEMA IF NOT EXISTS mne;
SET LOCAL search_path = mne, public;
COMMENT ON SCHEMA mne IS 'Mercado de Negócios Existentes — PostgreSQL 17/PostGIS';
DO $postgis$
BEGIN
IF postgis_lib_version() IS NULL THEN
RAISE EXCEPTION 'PostGIS não está disponível';
END IF;
END
$postgis$;
CREATE TABLE data_classifications (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), code varchar(30) NOT NULL UNIQUE, name varchar(100) NOT NULL, rank smallint NOT NULL UNIQUE CHECK (rank>=0), public_allowed boolean NOT NULL DEFAULT false, encryption_required boolean NOT NULL DEFAULT false, default_retention_days integer CHECK (default_retention_days>0));
CREATE TABLE business_modalities (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), code varchar(50) NOT NULL UNIQUE, name_pt varchar(150) NOT NULL, name_en varchar(150), requires_equity boolean NOT NULL DEFAULT false, requires_asset boolean NOT NULL DEFAULT false, active boolean NOT NULL DEFAULT true);
CREATE TABLE workflow_states (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), entity_type varchar(50) NOT NULL, code varchar(40) NOT NULL, name varchar(100) NOT NULL, sequence smallint NOT NULL, terminal boolean NOT NULL DEFAULT false, public_visible boolean NOT NULL DEFAULT false, UNIQUE(entity_type,code), UNIQUE(entity_type,sequence));
CREATE TABLE organizations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), legal_name varchar(255) NOT NULL, trade_name varchar(255), registration_number varchar(100), tax_number varchar(50), country_code char(2) NOT NULL, organization_type varchar(50) NOT NULL, verification_status varchar(30) NOT NULL DEFAULT 'pending', created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), deleted_at timestamptz, version integer NOT NULL DEFAULT 1);
CREATE UNIQUE INDEX uq_organizations_registration ON organizations(country_code,registration_number) WHERE registration_number IS NOT NULL AND deleted_at IS NULL;
CREATE TABLE parties (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), party_type varchar(20) NOT NULL CHECK (party_type IN ('person','organization')), organization_id uuid REFERENCES organizations(id) ON UPDATE CASCADE ON DELETE RESTRICT, user_id uuid, display_name varchar(255) NOT NULL, verification_status varchar(30) NOT NULL DEFAULT 'pending', master_external_id varchar(150) UNIQUE, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), deleted_at timestamptz, version integer NOT NULL DEFAULT 1, CHECK ((party_type='organization' AND organization_id IS NOT NULL AND user_id IS NULL) OR (party_type='person' AND user_id IS NOT NULL AND organization_id IS NULL)));
CREATE TABLE party_roles (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), party_id uuid NOT NULL REFERENCES parties(id) ON UPDATE CASCADE ON DELETE RESTRICT, role_code varchar(50) NOT NULL, valid_from date NOT NULL DEFAULT CURRENT_DATE, valid_until date, status varchar(20) NOT NULL DEFAULT 'active', UNIQUE(party_id,role_code,valid_from), CHECK(valid_until IS NULL OR valid_until>=valid_from));
CREATE TABLE issuing_authorities (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), code varchar(50) NOT NULL, official_name varchar(255) NOT NULL, jurisdiction varchar(100) NOT NULL, country_code char(2) NOT NULL, authority_type varchar(50) NOT NULL, active boolean NOT NULL DEFAULT true, UNIQUE(code,jurisdiction));
CREATE TABLE source_records (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), source_type varchar(50) NOT NULL, title varchar(255) NOT NULL, authority_id uuid REFERENCES issuing_authorities(id) ON UPDATE CASCADE ON DELETE RESTRICT, reference varchar(255) NOT NULL, source_date date, verification_url text, checksum char(64), UNIQUE(source_type,reference));
CREATE TABLE existing_businesses (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), legacy_code varchar(100), public_code varchar(40) NOT NULL UNIQUE, modality_id uuid NOT NULL REFERENCES business_modalities(id) ON UPDATE CASCADE ON DELETE RESTRICT, title_public varchar(255) NOT NULL, description_private text NOT NULL, sector_id uuid NOT NULL, asking_value numeric(19,4) CHECK(asking_value>=0), currency_code char(3), available_equity_pct numeric(5,2) CHECK(available_equity_pct BETWEEN 0 AND 100), maturity varchar(30) NOT NULL, status_id uuid NOT NULL REFERENCES workflow_states(id) ON UPDATE CASCADE ON DELETE RESTRICT, confidentiality_id uuid NOT NULL REFERENCES data_classifications(id) ON UPDATE CASCADE ON DELETE RESTRICT, valid_from date NOT NULL, valid_until date, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), deleted_at timestamptz, version integer NOT NULL DEFAULT 1, CHECK((asking_value IS NULL AND currency_code IS NULL) OR (asking_value IS NOT NULL AND currency_code IS NOT NULL)), CHECK(valid_until IS NULL OR valid_until>=valid_from));
CREATE INDEX ix_businesses_filters ON existing_businesses(status_id,modality_id,sector_id,valid_until) WHERE deleted_at IS NULL;
CREATE TABLE business_owners (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON UPDATE CASCADE ON DELETE RESTRICT, owner_party_id uuid NOT NULL REFERENCES parties(id) ON UPDATE CASCADE ON DELETE RESTRICT, ownership_type varchar(40) NOT NULL, ownership_pct numeric(5,2) CHECK(ownership_pct BETWEEN 0 AND 100), declared boolean NOT NULL DEFAULT true, verified boolean NOT NULL DEFAULT false, valid_from date NOT NULL, valid_until date, UNIQUE(business_id,owner_party_id,valid_from), CHECK(valid_until IS NULL OR valid_until>=valid_from));
CREATE TABLE business_status_history (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON UPDATE CASCADE ON DELETE RESTRICT, from_status_id uuid REFERENCES workflow_states(id) ON UPDATE CASCADE ON DELETE RESTRICT, to_status_id uuid NOT NULL REFERENCES workflow_states(id) ON UPDATE CASCADE ON DELETE RESTRICT, reason_code varchar(60) NOT NULL, changed_by uuid NOT NULL, changed_at timestamptz NOT NULL DEFAULT now(), correlation_id uuid NOT NULL);
CREATE INDEX ix_business_status_history ON business_status_history(business_id,changed_at DESC);
CREATE TABLE publication_authorizations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON UPDATE CASCADE ON DELETE RESTRICT, requested_by uuid NOT NULL, first_approved_by uuid, second_approved_by uuid, scope jsonb NOT NULL DEFAULT '{}'::jsonb, approved_at timestamptz, expires_at timestamptz, revoked_at timestamptz, CHECK(first_approved_by IS NULL OR first_approved_by<>requested_by), CHECK(second_approved_by IS NULL OR second_approved_by<>requested_by), CHECK(first_approved_by IS NULL OR second_approved_by IS NULL OR first_approved_by<>second_approved_by));
CREATE UNIQUE INDEX uq_publication_active ON publication_authorizations(business_id) WHERE approved_at IS NOT NULL AND revoked_at IS NULL;
CREATE TABLE mandates (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), principal_party_id uuid NOT NULL REFERENCES parties(id) ON UPDATE CASCADE ON DELETE RESTRICT, representative_party_id uuid NOT NULL REFERENCES parties(id) ON UPDATE CASCADE ON DELETE RESTRICT, scope text NOT NULL, issued_at date NOT NULL, expires_at date, verification_status varchar(30) NOT NULL DEFAULT 'pending', document_id uuid, CHECK(principal_party_id<>representative_party_id), CHECK(expires_at IS NULL OR expires_at>=issued_at));
CREATE TABLE locations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), country_code char(2) NOT NULL, province_code varchar(20), district_code varchar(20), locality varchar(150), geom geometry(MultiPolygon,4326) NOT NULL, precision_level varchar(20) NOT NULL, classification_id uuid NOT NULL REFERENCES data_classifications(id) ON UPDATE CASCADE ON DELETE RESTRICT, CHECK(ST_SRID(geom)=4326 AND ST_IsValid(geom) AND NOT ST_IsEmpty(geom)));
CREATE INDEX ix_locations_geom ON locations USING GIST(geom);
CREATE TABLE business_locations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON UPDATE CASCADE ON DELETE CASCADE, location_id uuid NOT NULL REFERENCES locations(id) ON UPDATE CASCADE ON DELETE RESTRICT, location_role varchar(30) NOT NULL, is_public boolean NOT NULL DEFAULT false, valid_from date NOT NULL, valid_until date, UNIQUE(business_id,location_id,location_role,valid_from), CHECK(valid_until IS NULL OR valid_until>=valid_from));
CREATE TABLE business_ecosystem_links (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON UPDATE CASCADE ON DELETE CASCADE, ecosystem_type varchar(30) NOT NULL, external_id varchar(150) NOT NULL, relation_type varchar(40) NOT NULL, source_system varchar(80) NOT NULL, verified_at timestamptz, UNIQUE(business_id,ecosystem_type,external_id));
CREATE TABLE external_identifiers (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), entity_type varchar(50) NOT NULL, entity_id uuid NOT NULL, system_code varchar(80) NOT NULL, external_id varchar(180) NOT NULL, valid_from timestamptz NOT NULL DEFAULT now(), valid_until timestamptz, UNIQUE(system_code,external_id), CHECK(valid_until IS NULL OR valid_until>=valid_from));
CREATE INDEX ix_external_identifiers_entity ON external_identifiers(entity_type,entity_id);
-- P1 — documentos, activos, direitos e capital
CREATE TABLE documents (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), storage_key varchar(500) NOT NULL, document_type varchar(60) NOT NULL, classification_id uuid NOT NULL REFERENCES data_classifications(id) ON DELETE RESTRICT, checksum char(64) NOT NULL, mime_type varchar(120) NOT NULL, size_bytes bigint NOT NULL CHECK(size_bytes>0), malware_scan_status varchar(30) NOT NULL DEFAULT 'pending', retention_policy_id uuid, owner_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX ix_documents_checksum ON documents(checksum);
ALTER TABLE mandates ADD CONSTRAINT fk_mandates_document FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE RESTRICT;
CREATE TABLE document_versions (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), document_id uuid NOT NULL REFERENCES documents(id) ON DELETE RESTRICT, version_no integer NOT NULL CHECK(version_no>0), storage_key varchar(500) NOT NULL, checksum char(64) NOT NULL, size_bytes bigint NOT NULL CHECK(size_bytes>0), created_by uuid NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(document_id,version_no));
CREATE TABLE business_documents (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE CASCADE, document_id uuid NOT NULL REFERENCES documents(id) ON DELETE RESTRICT, document_role varchar(50) NOT NULL, required boolean NOT NULL DEFAULT false, publishable boolean NOT NULL DEFAULT false, valid_from date NOT NULL, valid_until date, UNIQUE(business_id,document_id,document_role), CHECK(valid_until IS NULL OR valid_until>=valid_from));
CREATE TABLE document_access_policies (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), document_id uuid NOT NULL REFERENCES documents(id) ON DELETE RESTRICT, principal_type varchar(30) NOT NULL, principal_id uuid, permission varchar(20) NOT NULL CHECK(permission IN ('view','download','manage')), valid_from timestamptz NOT NULL, valid_until timestamptz, granted_by uuid NOT NULL, CHECK(valid_until IS NULL OR valid_until>=valid_from));
CREATE TABLE assets (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, asset_type varchar(40) NOT NULL, public_name varchar(255) NOT NULL, private_description text, declared_owner_party_id uuid REFERENCES parties(id) ON DELETE RESTRICT, verification_status varchar(30) NOT NULL DEFAULT 'pending', estimated_value numeric(19,4) CHECK(estimated_value>=0), currency_code char(3), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), deleted_at timestamptz, version integer NOT NULL DEFAULT 1, CHECK((estimated_value IS NULL AND currency_code IS NULL) OR (estimated_value IS NOT NULL AND currency_code IS NOT NULL)));
CREATE INDEX ix_assets_business ON assets(business_id,asset_type) WHERE deleted_at IS NULL;
CREATE TABLE real_estates (asset_id uuid PRIMARY KEY REFERENCES assets(id) ON DELETE CASCADE, property_type varchar(50) NOT NULL, area_value numeric(18,4) NOT NULL CHECK(area_value>0), area_unit varchar(20) NOT NULL, occupancy_status varchar(30) NOT NULL, use_status varchar(30) NOT NULL, conservation_status varchar(30) NOT NULL, geometry geometry(MultiPolygon,4326) NOT NULL, geometry_precision varchar(20) NOT NULL, CHECK(ST_SRID(geometry)=4326 AND ST_IsValid(geometry) AND NOT ST_IsEmpty(geometry)));
CREATE INDEX ix_real_estates_geometry ON real_estates USING GIST(geometry);
CREATE TABLE real_estate_units (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), real_estate_asset_id uuid NOT NULL REFERENCES real_estates(asset_id) ON DELETE CASCADE, unit_code varchar(60) NOT NULL, unit_type varchar(40) NOT NULL, floor_label varchar(30), gross_area numeric(18,4) CHECK(gross_area>0), area_unit varchar(20), occupancy_status varchar(30) NOT NULL, UNIQUE(real_estate_asset_id,unit_code));
CREATE TABLE improvements (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), real_estate_asset_id uuid NOT NULL REFERENCES real_estates(asset_id) ON DELETE RESTRICT, improvement_type varchar(50) NOT NULL, description text NOT NULL, completion_date date, declared_value numeric(19,4) CHECK(declared_value>=0), currency_code char(3), ownership_status varchar(30) NOT NULL, CHECK((declared_value IS NULL AND currency_code IS NULL) OR (declared_value IS NOT NULL AND currency_code IS NOT NULL)));
CREATE TABLE agricultural_investments (asset_id uuid PRIMARY KEY REFERENCES assets(id) ON DELETE CASCADE, activity_type varchar(60) NOT NULL, crop_or_livestock varchar(120), cultivated_area numeric(18,4) CHECK(cultivated_area>0), area_unit varchar(20), irrigation boolean NOT NULL DEFAULT false, operational_status varchar(30) NOT NULL, seasonality jsonb);
CREATE TABLE land_rights (asset_id uuid PRIMARY KEY REFERENCES assets(id) ON DELETE RESTRICT, right_type varchar(50) NOT NULL, document_reference varchar(150) NOT NULL, issuing_authority_id uuid NOT NULL REFERENCES issuing_authorities(id) ON DELETE RESTRICT, authorized_purpose text NOT NULL, issued_at date NOT NULL, expires_at date, transfer_status varchar(30) NOT NULL, verification_status varchar(30) NOT NULL DEFAULT 'pending', UNIQUE(document_reference,issuing_authority_id), CHECK(expires_at IS NULL OR expires_at>=issued_at));
CREATE TABLE licences (asset_id uuid PRIMARY KEY REFERENCES assets(id) ON DELETE RESTRICT, licence_type varchar(60) NOT NULL, licence_number_masked varchar(100) NOT NULL, licence_number_hash varchar(128) NOT NULL UNIQUE, issuing_authority_id uuid NOT NULL REFERENCES issuing_authorities(id) ON DELETE RESTRICT, purpose text NOT NULL, issued_at date NOT NULL, expires_at date, renewal_status varchar(30) NOT NULL, transfer_status varchar(30) NOT NULL, verification_status varchar(30) NOT NULL DEFAULT 'pending', CHECK(expires_at IS NULL OR expires_at>=issued_at));
CREATE TABLE ownership_records (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), asset_id uuid NOT NULL REFERENCES assets(id) ON DELETE RESTRICT, owner_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, ownership_type varchar(40) NOT NULL, share_pct numeric(5,2) CHECK(share_pct BETWEEN 0 AND 100), declared_at date NOT NULL, verified_at timestamptz, source_document_id uuid REFERENCES documents(id) ON DELETE RESTRICT);
CREATE INDEX ix_ownership_asset ON ownership_records(asset_id,declared_at DESC);
CREATE TABLE encumbrances (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), asset_id uuid NOT NULL REFERENCES assets(id) ON DELETE RESTRICT, encumbrance_type varchar(50) NOT NULL, holder_party_id uuid REFERENCES parties(id) ON DELETE RESTRICT, amount numeric(19,4) CHECK(amount>=0), currency_code char(3), start_date date, end_date date, status varchar(30) NOT NULL, source_document_id uuid REFERENCES documents(id) ON DELETE RESTRICT, CHECK((amount IS NULL AND currency_code IS NULL) OR (amount IS NOT NULL AND currency_code IS NOT NULL)), CHECK(end_date IS NULL OR start_date IS NULL OR end_date>=start_date));
CREATE TABLE restrictions (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), asset_id uuid NOT NULL REFERENCES assets(id) ON DELETE RESTRICT, restriction_type varchar(50) NOT NULL, legal_basis text, description text NOT NULL, starts_at date, ends_at date, status varchar(30) NOT NULL, source_id uuid REFERENCES source_records(id) ON DELETE RESTRICT, CHECK(ends_at IS NULL OR starts_at IS NULL OR ends_at>=starts_at));
CREATE TABLE transfer_conditions (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), asset_id uuid NOT NULL REFERENCES assets(id) ON DELETE RESTRICT, condition_type varchar(50) NOT NULL, description text NOT NULL, required_authority_id uuid REFERENCES issuing_authorities(id) ON DELETE RESTRICT, approval_status varchar(30) NOT NULL, fulfilled_at timestamptz, CHECK(approval_status<>'fulfilled' OR fulfilled_at IS NOT NULL));
CREATE TABLE capital_requests (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, request_type varchar(40) NOT NULL, amount_min numeric(19,4) CHECK(amount_min>=0), amount_max numeric(19,4) CHECK(amount_max>=0), currency_code char(3) NOT NULL, use_of_funds text NOT NULL, target_date date, status varchar(30) NOT NULL, CHECK(amount_min IS NULL OR amount_max IS NULL OR amount_min<=amount_max));
CREATE TABLE equity_offers (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, equity_pct numeric(5,2) NOT NULL CHECK(equity_pct>0 AND equity_pct<=100), share_class varchar(40), indicative_value numeric(19,4) CHECK(indicative_value>=0), currency_code char(3), valid_until date, status varchar(30) NOT NULL, CHECK((indicative_value IS NULL AND currency_code IS NULL) OR (indicative_value IS NOT NULL AND currency_code IS NOT NULL)));
CREATE TABLE valuations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, asset_id uuid REFERENCES assets(id) ON DELETE RESTRICT, valuation_type varchar(40) NOT NULL, amount numeric(19,4) NOT NULL CHECK(amount>=0), currency_code char(3) NOT NULL, valuation_date date NOT NULL, valuer_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, methodology varchar(100) NOT NULL, source_document_id uuid REFERENCES documents(id) ON DELETE RESTRICT, status varchar(30) NOT NULL);
CREATE TABLE business_values (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, value_type varchar(30) NOT NULL, amount numeric(19,4) NOT NULL CHECK(amount>=0), currency_code char(3) NOT NULL, effective_at timestamptz NOT NULL, source_type varchar(40) NOT NULL, source_id uuid, confirmed boolean NOT NULL DEFAULT false);
CREATE INDEX ix_business_values_latest ON business_values(business_id,value_type,effective_at DESC);
-- P2 — verificação, interesse e diligência
CREATE TABLE verification_records (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), subject_type varchar(50) NOT NULL, subject_id uuid NOT NULL, verification_type varchar(50) NOT NULL, status varchar(30) NOT NULL, source_id uuid NOT NULL REFERENCES source_records(id) ON DELETE RESTRICT, verified_by uuid NOT NULL, verified_at timestamptz NOT NULL, expires_at timestamptz, notes_classification_id uuid NOT NULL REFERENCES data_classifications(id) ON DELETE RESTRICT);
CREATE INDEX ix_verification_subject ON verification_records(subject_type,subject_id);
CREATE TABLE audit_events (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), actor_id uuid, action varchar(100) NOT NULL, subject_type varchar(50) NOT NULL, subject_id uuid NOT NULL, occurred_at timestamptz NOT NULL DEFAULT now(), correlation_id uuid NOT NULL, ip_hash varchar(128), metadata_redacted jsonb NOT NULL DEFAULT '{}'::jsonb);
CREATE INDEX ix_audit_subject ON audit_events(subject_type,subject_id,occurred_at DESC);
CREATE TABLE investor_profiles (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), party_id uuid NOT NULL UNIQUE REFERENCES parties(id) ON DELETE RESTRICT, investor_type varchar(40) NOT NULL, ticket_min numeric(19,4), ticket_max numeric(19,4), currency_code char(3), sectors jsonb NOT NULL DEFAULT '[]'::jsonb, geographies jsonb NOT NULL DEFAULT '[]'::jsonb, horizon_months integer CHECK(horizon_months>0), qualification_status varchar(30) NOT NULL, CHECK(ticket_min IS NULL OR ticket_min>=0), CHECK(ticket_max IS NULL OR ticket_max>=0), CHECK((ticket_min IS NULL AND ticket_max IS NULL) OR currency_code IS NOT NULL), CHECK(ticket_min IS NULL OR ticket_max IS NULL OR ticket_min<=ticket_max));
CREATE TABLE consent_records (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, purpose_code varchar(60) NOT NULL, legal_basis varchar(60) NOT NULL, granted boolean NOT NULL, granted_at timestamptz NOT NULL, expires_at timestamptz, withdrawn_at timestamptz, evidence_document_id uuid REFERENCES documents(id) ON DELETE RESTRICT);
CREATE UNIQUE INDEX uq_consent_active ON consent_records(party_id,purpose_code) WHERE granted=true AND withdrawn_at IS NULL;
CREATE TABLE interests (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, investor_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, modality_id uuid NOT NULL REFERENCES business_modalities(id) ON DELETE RESTRICT, source varchar(50) NOT NULL, qualification_status varchar(30) NOT NULL, crm_external_id varchar(150), consent_id uuid NOT NULL REFERENCES consent_records(id) ON DELETE RESTRICT, dedupe_period date NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(investor_party_id,business_id,modality_id,dedupe_period));
CREATE TABLE match_results (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), interest_id uuid NOT NULL REFERENCES interests(id) ON DELETE RESTRICT, business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, score numeric(6,3) NOT NULL CHECK(score BETWEEN 0 AND 100), reasons jsonb NOT NULL, model_version varchar(80) NOT NULL, reviewed_by uuid, reviewed_at timestamptz, decision varchar(30) NOT NULL);
CREATE TABLE crm_links (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), local_entity_type varchar(50) NOT NULL, local_entity_id uuid NOT NULL, crm_system varchar(50) NOT NULL, crm_entity_type varchar(50) NOT NULL, crm_external_id varchar(180) NOT NULL, sync_status varchar(30) NOT NULL, last_synced_at timestamptz, UNIQUE(crm_system,crm_entity_type,crm_external_id));
CREATE TABLE notifications (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), recipient_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, channel varchar(20) NOT NULL, template_code varchar(80) NOT NULL, payload_redacted jsonb NOT NULL, status varchar(30) NOT NULL, scheduled_at timestamptz NOT NULL, sent_at timestamptz, failure_code varchar(80));
CREATE TABLE access_requests (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, requester_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, request_type varchar(40) NOT NULL, purpose text NOT NULL, status varchar(30) NOT NULL, reviewed_by uuid, reviewed_at timestamptz, expires_at timestamptz);
CREATE UNIQUE INDEX uq_access_request_open ON access_requests(business_id,requester_party_id,request_type) WHERE status IN ('submitted','under_review','approved');
CREATE TABLE ndas (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), access_request_id uuid NOT NULL UNIQUE REFERENCES access_requests(id) ON DELETE RESTRICT, template_version varchar(40) NOT NULL, owner_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, requester_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, signed_at timestamptz, expires_at timestamptz, document_id uuid NOT NULL REFERENCES documents(id) ON DELETE RESTRICT, status varchar(30) NOT NULL, CHECK(status<>'active' OR signed_at IS NOT NULL));
CREATE TABLE data_rooms (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, name varchar(150) NOT NULL, classification_id uuid NOT NULL REFERENCES data_classifications(id) ON DELETE RESTRICT, status varchar(30) NOT NULL, opened_at timestamptz, closed_at timestamptz, owner_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT);
CREATE TABLE data_room_members (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), data_room_id uuid NOT NULL REFERENCES data_rooms(id) ON DELETE CASCADE, party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, role_code varchar(40) NOT NULL, can_view boolean NOT NULL DEFAULT true, can_download boolean NOT NULL DEFAULT false, valid_from timestamptz NOT NULL, valid_until timestamptz, revoked_at timestamptz, UNIQUE(data_room_id,party_id,valid_from));
CREATE TABLE due_diligence_checklists (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, checklist_type varchar(40) NOT NULL, template_version varchar(30) NOT NULL, status varchar(30) NOT NULL, owner_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, started_at timestamptz NOT NULL, completed_at timestamptz, UNIQUE(business_id,checklist_type,template_version));
CREATE TABLE due_diligence_items (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), checklist_id uuid NOT NULL REFERENCES due_diligence_checklists(id) ON DELETE CASCADE, code varchar(60) NOT NULL, title varchar(255) NOT NULL, requirement text NOT NULL, status varchar(30) NOT NULL, responsible_party_id uuid REFERENCES parties(id) ON DELETE RESTRICT, due_at timestamptz, evidence_document_id uuid REFERENCES documents(id) ON DELETE RESTRICT, UNIQUE(checklist_id,code));
CREATE TABLE information_requests (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, checklist_item_id uuid REFERENCES due_diligence_items(id) ON DELETE RESTRICT, requested_by uuid NOT NULL, assigned_to uuid NOT NULL, question text NOT NULL, response text, status varchar(30) NOT NULL, due_at timestamptz, responded_at timestamptz, CHECK(status<>'responded' OR (response IS NOT NULL AND responded_at IS NOT NULL)));
-- P3–P4 — negociação, monetização e operação
CREATE TABLE negotiations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, interest_id uuid NOT NULL REFERENCES interests(id) ON DELETE RESTRICT, stage varchar(40) NOT NULL, owner_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, opened_at timestamptz NOT NULL DEFAULT now(), closed_at timestamptz, outcome varchar(40));
CREATE UNIQUE INDEX uq_negotiation_open ON negotiations(business_id,interest_id) WHERE closed_at IS NULL;
CREATE TABLE proposals (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), negotiation_id uuid NOT NULL REFERENCES negotiations(id) ON DELETE RESTRICT, proposal_no integer NOT NULL CHECK(proposal_no>0), proposal_type varchar(40) NOT NULL, amount numeric(19,4), currency_code char(3), equity_pct numeric(5,2) CHECK(equity_pct BETWEEN 0 AND 100), terms jsonb NOT NULL, binding boolean NOT NULL DEFAULT false, submitted_at timestamptz NOT NULL, expires_at timestamptz, status varchar(30) NOT NULL, UNIQUE(negotiation_id,proposal_no), CHECK((amount IS NULL)=(currency_code IS NULL)), CHECK(equity_pct IS NULL OR equity_pct>0));
CREATE TABLE negotiation_events (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), negotiation_id uuid NOT NULL REFERENCES negotiations(id) ON DELETE RESTRICT, event_type varchar(50) NOT NULL, occurred_at timestamptz NOT NULL DEFAULT now(), actor_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, summary text NOT NULL, payload_redacted jsonb NOT NULL DEFAULT '{}'::jsonb, correlation_id uuid NOT NULL);
CREATE INDEX ix_negotiation_events ON negotiation_events(negotiation_id,occurred_at);
CREATE TABLE conditions_precedent (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), negotiation_id uuid NOT NULL REFERENCES negotiations(id) ON DELETE RESTRICT, code varchar(60) NOT NULL, description text NOT NULL, responsible_party_id uuid REFERENCES parties(id) ON DELETE RESTRICT, due_at date, status varchar(30) NOT NULL, satisfied_at timestamptz, evidence_document_id uuid REFERENCES documents(id) ON DELETE RESTRICT, UNIQUE(negotiation_id,code));
CREATE TABLE advisors (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), negotiation_id uuid NOT NULL REFERENCES negotiations(id) ON DELETE RESTRICT, party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, advisor_role varchar(50) NOT NULL, appointed_at date NOT NULL, ended_at date, conflict_status varchar(30) NOT NULL, UNIQUE(negotiation_id,party_id,advisor_role,appointed_at));
CREATE TABLE transaction_closures (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), negotiation_id uuid NOT NULL UNIQUE REFERENCES negotiations(id) ON DELETE RESTRICT, outcome varchar(40) NOT NULL, confirmed_value numeric(19,4), currency_code char(3), closed_at timestamptz NOT NULL, approved_by uuid NOT NULL, closure_document_id uuid REFERENCES documents(id) ON DELETE RESTRICT, CHECK((confirmed_value IS NULL)=(currency_code IS NULL)), CHECK(confirmed_value IS NULL OR confirmed_value>=0));
CREATE TABLE commercial_contracts (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE RESTRICT, client_party_id uuid NOT NULL REFERENCES parties(id) ON DELETE RESTRICT, contract_number varchar(80) NOT NULL, version_no integer NOT NULL CHECK(version_no>0), starts_at date NOT NULL, ends_at date, status varchar(30) NOT NULL, document_id uuid NOT NULL REFERENCES documents(id) ON DELETE RESTRICT, UNIQUE(contract_number,version_no));
CREATE TABLE commission_rules (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), contract_id uuid NOT NULL REFERENCES commercial_contracts(id) ON DELETE RESTRICT, rule_version integer NOT NULL, basis_type varchar(40) NOT NULL, rate_pct numeric(7,4), fixed_amount numeric(19,4), currency_code char(3), min_amount numeric(19,4), max_amount numeric(19,4), effective_from date NOT NULL, effective_until date, UNIQUE(contract_id,rule_version), CHECK(fixed_amount IS NULL OR fixed_amount>=0), CHECK(min_amount IS NULL OR min_amount>=0), CHECK(max_amount IS NULL OR max_amount>=0), CHECK(rate_pct IS NULL OR rate_pct BETWEEN 0 AND 100), CHECK(effective_until IS NULL OR effective_until>=effective_from), CHECK((fixed_amount IS NULL AND min_amount IS NULL AND max_amount IS NULL) OR currency_code IS NOT NULL));
CREATE TABLE billable_events (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), contract_id uuid NOT NULL REFERENCES commercial_contracts(id) ON DELETE RESTRICT, negotiation_id uuid REFERENCES negotiations(id) ON DELETE RESTRICT, event_type varchar(50) NOT NULL, occurred_at timestamptz NOT NULL, base_amount numeric(19,4) NOT NULL CHECK(base_amount>=0), currency_code char(3) NOT NULL, verified_by uuid NOT NULL, verified_at timestamptz NOT NULL, status varchar(30) NOT NULL);
CREATE TABLE invoices (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), billable_event_id uuid NOT NULL REFERENCES billable_events(id) ON DELETE RESTRICT, invoice_number varchar(80) NOT NULL UNIQUE, issued_at date NOT NULL, due_at date NOT NULL, subtotal numeric(19,4) NOT NULL, tax_amount numeric(19,4) NOT NULL, total_amount numeric(19,4) NOT NULL, currency_code char(3) NOT NULL, status varchar(30) NOT NULL, external_id varchar(150), CHECK(subtotal>=0), CHECK(tax_amount>=0), CHECK(total_amount>=0), CHECK(due_at>=issued_at), CHECK(total_amount=subtotal+tax_amount));
CREATE TABLE payments (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), invoice_id uuid NOT NULL REFERENCES invoices(id) ON DELETE RESTRICT, payment_reference varchar(120) NOT NULL UNIQUE, amount numeric(19,4) NOT NULL CHECK(amount>0), currency_code char(3) NOT NULL, paid_at timestamptz NOT NULL, status varchar(30) NOT NULL, provider_external_id varchar(150));
CREATE TABLE reconciliation_records (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), entity_type varchar(40) NOT NULL, entity_id uuid NOT NULL, expected_amount numeric(19,4) NOT NULL, actual_amount numeric(19,4) NOT NULL, currency_code char(3) NOT NULL, reconciled_at timestamptz NOT NULL, reconciled_by uuid NOT NULL, status varchar(30) NOT NULL, difference_reason text);
CREATE TABLE favorites (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), party_id uuid NOT NULL REFERENCES parties(id) ON DELETE CASCADE, business_id uuid NOT NULL REFERENCES existing_businesses(id) ON DELETE CASCADE, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(party_id,business_id));
CREATE TABLE saved_searches (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), party_id uuid NOT NULL REFERENCES parties(id) ON DELETE CASCADE, name varchar(120) NOT NULL, filters jsonb NOT NULL, query_version integer NOT NULL DEFAULT 1, active boolean NOT NULL DEFAULT true, UNIQUE(party_id,name));
CREATE TABLE alerts (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), saved_search_id uuid NOT NULL REFERENCES saved_searches(id) ON DELETE CASCADE, frequency varchar(20) NOT NULL, channel varchar(20) NOT NULL, last_run_at timestamptz, next_run_at timestamptz, active boolean NOT NULL DEFAULT true);
CREATE TABLE migration_batches (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), source_system varchar(80) NOT NULL, batch_reference varchar(120) NOT NULL, started_at timestamptz NOT NULL, completed_at timestamptz, status varchar(30) NOT NULL, total_rows integer NOT NULL CHECK(total_rows>=0), accepted_rows integer NOT NULL CHECK(accepted_rows>=0), rejected_rows integer NOT NULL CHECK(rejected_rows>=0), checksum char(64) NOT NULL, UNIQUE(source_system,batch_reference), CHECK(total_rows=accepted_rows+rejected_rows));
CREATE TABLE migration_rejections (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), batch_id uuid NOT NULL REFERENCES migration_batches(id) ON DELETE CASCADE, source_row_ref varchar(150) NOT NULL, error_code varchar(80) NOT NULL, error_details jsonb NOT NULL, payload_redacted jsonb NOT NULL, resolved_at timestamptz, resolved_by uuid, UNIQUE(batch_id,source_row_ref,error_code));
CREATE TABLE feature_flags (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), "key" varchar(100) NOT NULL, description text NOT NULL, enabled boolean NOT NULL DEFAULT false, rollout_pct numeric(5,2) NOT NULL DEFAULT 0 CHECK(rollout_pct BETWEEN 0 AND 100), conditions jsonb NOT NULL DEFAULT '{}'::jsonb, environment varchar(30) NOT NULL, changed_by uuid NOT NULL, changed_at timestamptz NOT NULL DEFAULT now(), UNIQUE("key",environment));
CREATE TABLE integration_outbox (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), aggregate_type varchar(50) NOT NULL, aggregate_id uuid NOT NULL, event_type varchar(80) NOT NULL, payload jsonb NOT NULL, occurred_at timestamptz NOT NULL DEFAULT now(), available_at timestamptz NOT NULL DEFAULT now(), attempts integer NOT NULL DEFAULT 0 CHECK(attempts>=0), processed_at timestamptz, last_error_redacted text);
CREATE INDEX ix_outbox_pending ON integration_outbox(available_at) WHERE processed_at IS NULL;
COMMIT;
Reference in New Issue View Git Blame Copy Permalink