114 lines
3.6 KiB
SQL
114 lines
3.6 KiB
SQL
-- kua-money-trace MVP schema draft
|
|
-- Designed for Postgres. The current MVP uses JSON files, but this schema
|
|
-- defines the service boundary before adding persistence.
|
|
|
|
create table if not exists entities (
|
|
id text primary key,
|
|
name text not null,
|
|
kind text not null check (kind in ('person', 'company', 'vendor', 'bank', 'platform', 'unknown')),
|
|
rut text,
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists financial_accounts (
|
|
id text primary key,
|
|
owner_entity_id text references entities(id),
|
|
institution text not null,
|
|
instrument text not null,
|
|
currency text not null,
|
|
label text,
|
|
account_number_hint text,
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists source_files (
|
|
id text primary key,
|
|
storage_key text not null unique,
|
|
sha256 text not null,
|
|
original_filename text,
|
|
mime_type text,
|
|
size_bytes bigint,
|
|
source_kind text not null,
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists mail_messages (
|
|
id text primary key,
|
|
mailbox text not null,
|
|
message_id text,
|
|
from_addr jsonb,
|
|
to_addrs jsonb,
|
|
subject text,
|
|
date_sent timestamptz,
|
|
raw_file_id text references source_files(id),
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists documents (
|
|
id text primary key,
|
|
kind text not null,
|
|
issuer_name text,
|
|
issuer_rut text,
|
|
receiver_entity_id text references entities(id),
|
|
folio text,
|
|
document_date date,
|
|
currency text not null,
|
|
amount numeric(18, 4) not null,
|
|
source_file_id text references source_files(id),
|
|
extraction_state text not null default 'proposed',
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists financial_movements (
|
|
id text primary key,
|
|
account_id text not null references financial_accounts(id),
|
|
occurred_at timestamptz not null,
|
|
direction text not null check (direction in ('in', 'out')),
|
|
currency text not null,
|
|
amount numeric(18, 4) not null,
|
|
description text,
|
|
counterparty text,
|
|
economic_type text not null default 'unknown',
|
|
beneficiary_entity_id text references entities(id),
|
|
source_file_id text references source_files(id),
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists economic_events (
|
|
id text primary key,
|
|
kind text not null,
|
|
entity_id text references entities(id),
|
|
occurred_at timestamptz,
|
|
currency text not null,
|
|
amount numeric(18, 4) not null,
|
|
description text,
|
|
state text not null default 'proposed',
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists money_links (
|
|
id text primary key,
|
|
from_node text not null,
|
|
to_node text not null,
|
|
link_type text not null,
|
|
currency text not null,
|
|
amount numeric(18, 4) not null,
|
|
method text not null,
|
|
confidence text not null default 'unknown',
|
|
state text not null default 'proposed',
|
|
note text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists idx_money_links_from on money_links(from_node);
|
|
create index if not exists idx_money_links_to on money_links(to_node);
|
|
create index if not exists idx_financial_movements_account_date on financial_movements(account_id, occurred_at);
|
|
create index if not exists idx_documents_receiver_date on documents(receiver_entity_id, document_date);
|