002-create-cache-tables.sql•6 kB
-- Cache tables för Bolagsverket API data
-- Run in Supabase SQL Editor: https://supabase.com/dashboard/project/thjwryuhtwlfxwduyqqd/sql
-- 1. Company details cache (från Bolagsverket API)
CREATE TABLE IF NOT EXISTS public.company_details_cache (
id BIGSERIAL PRIMARY KEY,
organisationsidentitet TEXT UNIQUE NOT NULL,
-- Cached API response
api_response JSONB NOT NULL,
-- Metadata
fetched_at TIMESTAMPTZ DEFAULT NOW(),
cache_expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '30 days'),
fetch_count INTEGER DEFAULT 1,
last_modified TIMESTAMPTZ DEFAULT NOW(),
-- Index på org-id för snabb lookup
CONSTRAINT fk_company FOREIGN KEY (organisationsidentitet)
REFERENCES public.companies(organisationsidentitet) ON DELETE CASCADE
);
CREATE INDEX idx_company_details_orgid ON public.company_details_cache(organisationsidentitet);
CREATE INDEX idx_company_details_expires ON public.company_details_cache(cache_expires_at);
-- 2. Document list cache
CREATE TABLE IF NOT EXISTS public.company_documents_cache (
id BIGSERIAL PRIMARY KEY,
organisationsidentitet TEXT NOT NULL,
-- Cached document list
documents JSONB NOT NULL,
-- Metadata
fetched_at TIMESTAMPTZ DEFAULT NOW(),
cache_expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '7 days'),
fetch_count INTEGER DEFAULT 1,
CONSTRAINT fk_company_docs FOREIGN KEY (organisationsidentitet)
REFERENCES public.companies(organisationsidentitet) ON DELETE CASCADE
);
CREATE INDEX idx_documents_cache_orgid ON public.company_documents_cache(organisationsidentitet);
-- 3. Financial reports (parsed iXBRL data)
CREATE TABLE IF NOT EXISTS public.financial_reports (
id BIGSERIAL PRIMARY KEY,
organisationsidentitet TEXT NOT NULL,
-- Report metadata
report_year INTEGER NOT NULL,
report_type TEXT NOT NULL, -- 'ÅRSREDOVISNING', 'KONCERNREDOVISNING', etc.
filing_date DATE,
-- 78+ financial metrics (structured JSON)
balance_sheet JSONB, -- balansräkning
income_statement JSONB, -- resultaträkning
cash_flow JSONB, -- kassaflödesanalys
key_metrics JSONB, -- nyckeltal
-- File references
source_document_id TEXT, -- Bolagsverket document ID
storage_path TEXT, -- Supabase Storage path to iXBRL/PDF file
-- Metadata
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
-- Unique constraint: one report per company per year
CONSTRAINT unique_report_per_year UNIQUE (organisationsidentitet, report_year, report_type),
CONSTRAINT fk_company_reports FOREIGN KEY (organisationsidentitet)
REFERENCES public.companies(organisationsidentitet) ON DELETE CASCADE
);
CREATE INDEX idx_financial_reports_orgid ON public.financial_reports(organisationsidentitet);
CREATE INDEX idx_financial_reports_year ON public.financial_reports(report_year DESC);
CREATE INDEX idx_financial_reports_storage ON public.financial_reports(storage_path);
-- 4. Board members (styrelse)
CREATE TABLE IF NOT EXISTS public.board_members (
id BIGSERIAL PRIMARY KEY,
organisationsidentitet TEXT NOT NULL,
-- Person details
namn TEXT NOT NULL,
personnummer TEXT, -- Masked for GDPR
roll TEXT NOT NULL, -- 'Styrelseledamot', 'VD', 'Suppleant', etc.
-- Dates
from_date DATE,
to_date DATE,
-- Metadata
source TEXT DEFAULT 'bolagsverket_api',
fetched_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT fk_company_board FOREIGN KEY (organisationsidentitet)
REFERENCES public.companies(organisationsidentitet) ON DELETE CASCADE
);
CREATE INDEX idx_board_members_orgid ON public.board_members(organisationsidentitet);
CREATE INDEX idx_board_members_namn ON public.board_members(namn);
-- 5. API request log (för rate limiting & analytics)
CREATE TABLE IF NOT EXISTS public.api_request_log (
id BIGSERIAL PRIMARY KEY,
-- Request details
endpoint TEXT NOT NULL,
method TEXT NOT NULL,
organisationsidentitet TEXT,
-- Response
status_code INTEGER,
response_time_ms INTEGER,
cache_hit BOOLEAN DEFAULT FALSE,
-- Timestamp
requested_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_api_log_timestamp ON public.api_request_log(requested_at DESC);
CREATE INDEX idx_api_log_endpoint ON public.api_request_log(endpoint);
CREATE INDEX idx_api_log_cache_hit ON public.api_request_log(cache_hit);
-- 6. RLS policies for cache tables
-- Company details cache
ALTER TABLE public.company_details_cache ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Public read cache" ON public.company_details_cache
FOR SELECT
USING (true);
CREATE POLICY "Service write cache" ON public.company_details_cache
FOR ALL
USING (auth.role() = 'service_role');
-- Documents cache
ALTER TABLE public.company_documents_cache ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Public read docs cache" ON public.company_documents_cache
FOR SELECT
USING (true);
CREATE POLICY "Service write docs cache" ON public.company_documents_cache
FOR ALL
USING (auth.role() = 'service_role');
-- Financial reports
ALTER TABLE public.financial_reports ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Public read reports" ON public.financial_reports
FOR SELECT
USING (true);
CREATE POLICY "Service write reports" ON public.financial_reports
FOR ALL
USING (auth.role() = 'service_role');
-- Board members
ALTER TABLE public.board_members ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Public read board" ON public.board_members
FOR SELECT
USING (true);
CREATE POLICY "Service write board" ON public.board_members
FOR ALL
USING (auth.role() = 'service_role');
-- API log (service role only)
ALTER TABLE public.api_request_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Service only api log" ON public.api_request_log
FOR ALL
USING (auth.role() = 'service_role');
-- 7. Create Storage bucket for documents
-- This must be run separately or via Supabase dashboard
-- CREATE STORAGE BUCKET company_documents (
-- PUBLIC = false,
-- FILE_SIZE_LIMIT = 52428800, -- 50MB
-- ALLOWED_MIME_TYPES = ['application/pdf', 'application/xml', 'text/xml']
-- );