CompanyIQ MCP Server

-- ============================================= -- Authentication & User Management -- ============================================= -- Users table for web authentication CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY, username VARCHAR(255) UNIQUE NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_login TIMESTAMP, is_active BOOLEAN DEFAULT true ); -- API keys table (per-user) CREATE TABLE IF NOT EXISTS api_keys ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, api_key VARCHAR(64) UNIQUE NOT NULL, name VARCHAR(255) DEFAULT 'Default Key', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_used TIMESTAMP, expires_at TIMESTAMP, is_active BOOLEAN DEFAULT true ); -- Sessions table for express-session with PostgreSQL store CREATE TABLE IF NOT EXISTS sessions ( sid VARCHAR(255) PRIMARY KEY, sess JSONB NOT NULL, expire TIMESTAMP NOT NULL ); -- User indexes CREATE INDEX IF NOT EXISTS idx_users_username ON users(username); CREATE INDEX IF NOT EXISTS idx_users_email ON users(email); CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id); CREATE INDEX IF NOT EXISTS idx_api_keys_key ON api_keys(api_key); CREATE INDEX IF NOT EXISTS idx_sessions_expire ON sessions(expire); -- ============================================= -- Company Data -- ============================================= -- Companies master table CREATE TABLE IF NOT EXISTS companies ( org_nr TEXT PRIMARY KEY, name TEXT NOT NULL, organization_form TEXT, organization_form_code TEXT, nace_code TEXT, nace_description TEXT, employees_count INTEGER, established_date TEXT, -- Address information business_address TEXT, business_postcode TEXT, business_city TEXT, business_municipality TEXT, business_municipality_number TEXT, postal_address TEXT, postal_postcode TEXT, postal_city TEXT, -- Status flags in_mva_register BOOLEAN, in_foretaksregister BOOLEAN, in_frivillighetsregister BOOLEAN, in_stiftelsesregister BOOLEAN, bankrupt BOOLEAN, under_liquidation BOOLEAN, under_forced_liquidation BOOLEAN, -- Metadata last_updated TEXT, fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, data_quality_score INTEGER DEFAULT 50 ); -- Sub-units (underenheter) CREATE TABLE IF NOT EXISTS subunits ( org_nr TEXT PRIMARY KEY, parent_org_nr TEXT NOT NULL, name TEXT NOT NULL, nace_code TEXT, employees_count INTEGER, business_address TEXT, municipality TEXT, established_date TEXT, fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (parent_org_nr) REFERENCES companies(org_nr) ); -- Board members and leadership (roller) CREATE TABLE IF NOT EXISTS board_members ( id SERIAL PRIMARY KEY, company_org_nr TEXT NOT NULL, person_id TEXT, name TEXT, role_type TEXT NOT NULL, role_description TEXT, appointed_date TEXT, birth_date TEXT, is_active BOOLEAN DEFAULT true, fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_org_nr) REFERENCES companies(org_nr) ); -- Company ownership structure CREATE TABLE IF NOT EXISTS ownership ( id SERIAL PRIMARY KEY, company_org_nr TEXT NOT NULL, owner_org_nr TEXT, owner_name TEXT, owner_type TEXT, -- person, company, foreign ownership_percentage REAL, shares_count INTEGER, fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_org_nr) REFERENCES companies(org_nr) ); -- Historical company changes (for tracking trends) CREATE TABLE IF NOT EXISTS company_history ( id SERIAL PRIMARY KEY, org_nr TEXT NOT NULL, change_type TEXT NOT NULL, -- name_change, address_change, board_change, etc old_value TEXT, new_value TEXT, change_date TEXT, recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (org_nr) REFERENCES companies(org_nr) ); -- Financial snapshots (for growth analysis) CREATE TABLE IF NOT EXISTS financial_snapshots ( id SERIAL PRIMARY KEY, org_nr TEXT NOT NULL, year INTEGER NOT NULL, revenue BIGINT, profit BIGINT, assets BIGINT, equity BIGINT, employees INTEGER, source TEXT DEFAULT 'estimated', fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (org_nr) REFERENCES companies(org_nr), UNIQUE(org_nr, year) ); -- Market/Industry analysis cache CREATE TABLE IF NOT EXISTS market_analysis ( id SERIAL PRIMARY KEY, nace_code TEXT NOT NULL, analysis_type TEXT NOT NULL, -- growth, consolidation, competition region TEXT, data JSONB NOT NULL, valid_until TIMESTAMP NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- SSB statistics cache (enhanced with filtering support) CREATE TABLE IF NOT EXISTS ssb_cache ( id SERIAL PRIMARY KEY, table_id TEXT NOT NULL, table_name TEXT, category TEXT, filters_hash TEXT, nace_code TEXT, region TEXT, year TEXT, data JSONB NOT NULL, metadata JSONB, time_series JSONB, trend_analysis JSONB, fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, valid_until TIMESTAMP NOT NULL, UNIQUE(table_id, filters_hash) ); -- Company risk scores CREATE TABLE IF NOT EXISTS risk_scores ( org_nr TEXT PRIMARY KEY, overall_score INTEGER, bankruptcy_risk INTEGER, growth_score INTEGER, stability_score INTEGER, financial_health_score INTEGER, calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, factors JSONB, FOREIGN KEY (org_nr) REFERENCES companies(org_nr) ); -- Indexes for performance CREATE INDEX IF NOT EXISTS idx_companies_nace ON companies(nace_code); CREATE INDEX IF NOT EXISTS idx_companies_municipality ON companies(business_municipality_number); CREATE INDEX IF NOT EXISTS idx_companies_employees ON companies(employees_count); CREATE INDEX IF NOT EXISTS idx_companies_status ON companies(bankrupt, under_liquidation); CREATE INDEX IF NOT EXISTS idx_subunits_parent ON subunits(parent_org_nr); CREATE INDEX IF NOT EXISTS idx_board_company ON board_members(company_org_nr); CREATE INDEX IF NOT EXISTS idx_board_person ON board_members(person_id); CREATE INDEX IF NOT EXISTS idx_ownership_company ON ownership(company_org_nr); CREATE INDEX IF NOT EXISTS idx_ownership_owner ON ownership(owner_org_nr); CREATE INDEX IF NOT EXISTS idx_history_company ON company_history(org_nr); CREATE INDEX IF NOT EXISTS idx_financial_company_year ON financial_snapshots(org_nr, year); CREATE INDEX IF NOT EXISTS idx_market_nace ON market_analysis(nace_code); CREATE INDEX IF NOT EXISTS idx_ssb_cache_table ON ssb_cache(table_id); CREATE INDEX IF NOT EXISTS idx_ssb_cache_filters ON ssb_cache(table_id, filters_hash); CREATE INDEX IF NOT EXISTS idx_ssb_cache_nace ON ssb_cache(nace_code); CREATE INDEX IF NOT EXISTS idx_ssb_cache_valid ON ssb_cache(valid_until);