# lala850307-glitch/fin-intel-query-core architecture

> Architecture diagram of the GitHub repository lala850307-glitch/fin-intel-query-core, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-05-21). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/lala850307-glitch/fin-intel-query-core
- Repository: https://github.com/lala850307-glitch/fin-intel-query-core

## Overview

##### This repo is a multi-surface product, not a single app.
It contains four major subsystems: a **web application**, an **offline desktop package**, a **data ingestion / ETL pipeline**, and a **search/AI enrichment toolkit**. The architecture is centered on a shared company dataset that is built offline and then served through either PostgreSQL or SQLite depending on deployment mode.

##### Web runtime: FastAPI backend + React frontend.
The live product uses `app.py` as the Python backend entrypoint and `frontend/` as a Vite + React + TypeScript SPA. The frontend holds the landing page, search UI, company detail pages, admin pages, and API registration flows. The backend likely exposes the search, detail, tracking, update, and sync endpoints described in the README, with Uvicorn as the serving runtime.

##### Search is the core application boundary.
Search is not a single query path; it combines multiple strategies: exact match, fuzzy match, pinyin, zhuyin-to-Chinese conversion, alias matching, and semantic/vector search. The repo has dedicated search implementations under `search/` for online/PostgreSQL scenarios and `exe/search_company_sqlite.py` for offline SQLite mode. This means the search layer is split by deployment target but shares the same conceptual scoring model.

##### The dataset is assembled through a multi-step ETL pipeline.
Business data is built from scripts in `scripts/` and `clean_scripts/`, with a documented sequence: base company list, GCIS enrichment, alias generation, financials, pricing, and flagship table assembly. The pipeline can run step-by-step or via `run_pipeline.py`, with `run_daily.sh` and `run_quarterly.sh` orchestrating scheduled execution. The produced “flagship” table appears to be the main denormalized search/index dataset consumed by the app.

##### There are specialized crawler and enrichment sub-pipelines.
`langchain/finance_report/` and `langchain/langchain/pipeline/` indicate separate ingestion flows for news, IR reports, sentiment, summarization, and CSV export. These outputs feed the intelligence layer of the product rather than the base company database. The repo also includes embedding generation artifacts under `Embedding/`, suggesting offline preprocessing for vector search or similarity tasks.

##### Semantic search depends on external model infrastructure.
The README references **Ollama** with `bge-m3:latest` and `pgvector`. That means semantic retrieval is not just an application feature; it is a dependency on a local or server-side model runtime and a vector-capable database schema. In the offline package, this dependency is deliberately excluded, which keeps the packaged app self-contained.

##### User tracking and API products are a separate service layer.
Beyond company search, the backend also manages Google OAuth login, anonymous/session tracking, API key issuance, and usage logging. The data model described in the README (`users`, `user_events`, `tab_durations`, `api_applications`, `api_usage_logs`, `app_download_logs`) indicates a second domain focused on product analytics and paid/controlled API access rather than search itself.

##### The API exposure layer is versioned and metered.
The public API is under `/api/v1` and supports search, company detail, related companies, and popular queries. API keys are generated server-side, expire after 48 hours, and every call is logged with query, latency, and IP. This is a clear boundary between the public API product and the internal search implementation.

##### Offline desktop packaging is a first-class distribution path.
The `exe/` directory contains PyInstaller specs, a bundle launcher, SQLite export tooling, and platform-specific build assets. The desktop app starts a local backend, opens a native window via pywebview, auto-selects a free port, and uses local database snapshots. That means the “app” is actually a bundled client-server system running on the user’s machine.

##### The offline app has its own update/sync control plane.
At startup it checks GitHub Releases for a new executable and checks the server for a newer data snapshot. The README describes background download/sync plus front-end polling for update readiness. This creates two asynchronous control flows: one for **binary updates** and one for **data freshness**, both independent of the search UI.

##### Automation and deployment are externalized.
ETL is not cron-driven inside the repo; it is triggered by an external **OpenClaw** scheduler over SSH. Telegram notifications are used for step-level progress and failure reporting. CI/CD is handled by GitHub Actions release workflows that build Mac and Windows artifacts from tagged releases.

##### Frontend structure is organized by user journey and admin concerns.
Under `frontend/src/pages/`, the app is split into Landing, Home, Search, CompanyDetail, Team, ApiRegister, and a substantial Admin console. The Company Detail page is further divided into tabs for board, branch, financial, news, related companies, and stock data, reflecting the backend’s denormalized company-centric model.

External company/financial/market sources are ingested by the scripts, normalized and enriched, optionally vectorized, and then written to PostgreSQL or SQLite. The search backend reads from that store and serves both the web frontend and the public API, while the desktop build uses the same pipeline output exported into a local SQLite snapshot.

Main entry points: [`frontend/src/App.tsx`](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/App.tsx) (Frontend), [`app.py`](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/app.py) (Backend), [`scripts/06_build_flagship.py`](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/scripts/06_build_flagship.py) (ETL scripts), [`exe/search_company_sqlite.py`](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/search_company_sqlite.py) (SQLite search)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_web["Web app"]
  node_app_py["Backend<br/>FastAPI server<br/>[app.py]"]
  node_frontend_app["Frontend<br/>React SPA<br/>[App.tsx]"]
  node_search_ui["Search UI<br/>page<br/>[index.tsx]"]
  node_company_detail["Company detail<br/>page<br/>[index.tsx]"]
end

subgraph group_desktop["Desktop app"]
  node_sqlite_package[("SQLite snapshot<br/>offline datastore<br/>[search_data.db]")]
  node_desktop_bundle["Desktop bundle<br/>PyInstaller app<br/>[app_bundle.py]"]
  node_sqlite_search["SQLite search<br/>offline search"]
  node_export_sqlite["SQLite export<br/>packaging step"]
end

subgraph group_pipeline["ETL pipeline"]
  node_etl_scripts["ETL scripts<br/>batch pipeline"]
  node_clean_pipeline["Clean pipeline<br/>data prep"]
  node_daily_quarterly["Schedulers<br/>orchestration<br/>[run_pipeline.py]"]
end

subgraph group_search["Search and AI"]
  node_online_search["Online search<br/>search engine"]
  node_fallback_search["Search fallback<br/>search engine"]
  node_semantic_assets["Semantic assets<br/>embedding prep"]
  node_ollama_model{{"Ollama model<br/>external model runtime"}}
end

subgraph group_analytics["Tracking and API ops"]
  node_admin_console["Admin console<br/>ops UI<br/>[index.tsx]"]
  node_api_register["API register<br/>public form<br/>[index.tsx]"]
  node_track_api["Tracking API<br/>client telemetry<br/>[track.ts]"]
end

subgraph group_data["Data stores"]
  node_flagship_data[("Flagship data<br/>denormalized dataset")]
  node_raw_sources[("Raw sources<br/>input data<br/>[factory.csv]")]
  node_backend_store[("PostgreSQL store<br/>online datastore")]
end

node_frontend_app -->|"routes to"| node_search_ui
node_frontend_app -->|"routes to"| node_company_detail
node_frontend_app -->|"routes to"| node_admin_console
node_frontend_app -->|"routes to"| node_api_register
node_frontend_app -->|"reports"| node_track_api
node_app_py -->|"serves"| node_frontend_app
node_app_py -->|"queries"| node_online_search
node_app_py -->|"reads/writes"| node_backend_store
node_online_search -->|"reads"| node_backend_store
node_online_search -->|"uses"| node_semantic_assets
node_semantic_assets -->|"depends on"| node_ollama_model
node_fallback_search -->|"reads"| node_backend_store
node_etl_scripts -->|"ingests"| node_raw_sources
node_clean_pipeline -->|"feeds"| node_etl_scripts
node_etl_scripts -->|"produces"| node_flagship_data
node_daily_quarterly -->|"orchestrates"| node_etl_scripts
node_export_sqlite -->|"exports from"| node_flagship_data
node_export_sqlite -->|"writes"| node_sqlite_package
node_desktop_bundle -->|"launches"| node_sqlite_search
node_sqlite_search -->|"reads"| node_sqlite_package
node_sqlite_search -.->|"mirrors"| node_flagship_data
node_app_py -->|"logs"| node_track_api
node_app_py -->|"issues keys"| node_api_register

click node_app_py "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/app.py"
click node_frontend_app "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/App.tsx"
click node_search_ui "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/pages/Search/index.tsx"
click node_company_detail "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/pages/CompanyDetail/index.tsx"
click node_admin_console "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/pages/Admin/index.tsx"
click node_api_register "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/pages/ApiRegister/index.tsx"
click node_track_api "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/api/track.ts"
click node_etl_scripts "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/scripts/06_build_flagship.py"
click node_clean_pipeline "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/clean_scripts/clean_06_flagship.py"
click node_daily_quarterly "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/run_pipeline.py"
click node_flagship_data "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/data/flagship/06_taiwan_company_flagship.csv"
click node_raw_sources "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/data/raw/factory.csv"
click node_sqlite_package "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/search_data.db"
click node_desktop_bundle "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/app_bundle.py"
click node_sqlite_search "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/search_company_sqlite.py"
click node_export_sqlite "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/export_to_sqlite.py"
click node_online_search "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/search/search_company_completwithAI_sql_only.py"
click node_fallback_search "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/search/search_company_completwithAI_sql_fallback.py"
click node_semantic_assets "https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/search/build_company_alias_ollama_db.py"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_app_py,node_frontend_app,node_search_ui,node_company_detail toneBlue
class node_sqlite_package,node_desktop_bundle,node_sqlite_search,node_export_sqlite toneAmber
class node_etl_scripts,node_clean_pipeline,node_daily_quarterly toneMint
class node_online_search,node_fallback_search,node_semantic_assets,node_ollama_model toneRose
class node_admin_console,node_api_register,node_track_api toneIndigo
class node_flagship_data,node_raw_sources,node_backend_store toneTeal
```

## Components

### Web app

FastAPI backend, React SPA, and public API surface

- [Backend](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/app.py): FastAPI server · Main Python entrypoint for the live web/backend runtime · `app.py`
- [Frontend](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/App.tsx): React SPA · Vite + React TypeScript single-page application · `frontend/src/App.tsx`
- [Search UI](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/pages/Search/index.tsx): page · Search, landing, and company browsing experience · `frontend/src/pages/Search/index.tsx`
- [Company detail](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/pages/CompanyDetail/index.tsx): page · Company profile view with board, branch, financial, news, related, and stock tabs · `frontend/src/pages/CompanyDetail/index.tsx`

### Desktop app

Packaged offline client-server runtime with local SQLite

- [SQLite snapshot](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/search_data.db): offline datastore · Embedded database shipped with the desktop build · `exe/search_data.db`
- [Desktop bundle](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/app_bundle.py): PyInstaller app · Packaged local client-server launcher with webview shell · `exe/app_bundle.py`
- [SQLite search](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/search_company_sqlite.py): offline search · Local search implementation for the desktop package · `exe/search_company_sqlite.py`
- [SQLite export](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/exe/export_to_sqlite.py): packaging step · Exports the built dataset into the embedded desktop database · `exe/export_to_sqlite.py`

### ETL pipeline

Batch enrichment and flagship dataset assembly

- [ETL scripts](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/scripts/06_build_flagship.py): batch pipeline · Stepwise enrichment scripts for base list, GCIS, aliases, financials, pricing, and flagship build · `scripts/06_build_flagship.py`
- [Clean pipeline](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/clean_scripts/clean_06_flagship.py): data prep · Normalization and intermediate cleanup stages used by the ETL flow · `clean_scripts/clean_06_flagship.py`
- [Schedulers](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/run_pipeline.py): orchestration · Shell orchestration for daily and quarterly execution · `run_pipeline.py`

### Search and AI

Deployment-specific search engines and semantic enrichment

- [Online search](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/search/search_company_completwithAI_sql_only.py): search engine · Primary search implementation for the live PostgreSQL-backed product · `search/search_company_completwithAI_sql_only.py`
- [Search fallback](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/search/search_company_completwithAI_sql_fallback.py): search engine · Alternate SQL-backed search path with reduced AI dependency · `search/search_company_completwithAI_sql_fallback.py`
- [Semantic assets](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/search/build_company_alias_ollama_db.py): embedding prep · Alias expansion, zhuyin/pinyin handling, and embedding artifacts for semantic retrieval · `search/build_company_alias_ollama_db.py`
- **Ollama model**: external model runtime · Local or server-side embedding model used for semantic search

### Tracking and API ops

User analytics, API keys, and usage metering

- [Admin console](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/pages/Admin/index.tsx): ops UI · Operational dashboards for users, API, downloads, analytics, and settings · `frontend/src/pages/Admin/index.tsx`
- [API register](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/pages/ApiRegister/index.tsx): public form · Frontend flow for API application and key issuance · `frontend/src/pages/ApiRegister/index.tsx`
- [Tracking API](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/frontend/src/api/track.ts): client telemetry · Frontend-side event and usage reporting · `frontend/src/api/track.ts`

### Data stores

Shared source, intermediate, and published datasets

- [Flagship data](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/data/flagship/06_taiwan_company_flagship.csv): denormalized dataset · Published company-centric CSV used by search and product views · `data/flagship/06_taiwan_company_flagship.csv`
- [Raw sources](https://github.com/lala850307-glitch/fin-intel-query-core/blob/main/data/raw/factory.csv): input data · Imported upstream source files used by ETL and enrichment · `data/raw/factory.csv`
- **PostgreSQL store**: online datastore · Primary live database with vector and product-analytics tables

## Connections

- Frontend → Search UI: routes to · App shell renders the search journey
- Frontend → Company detail: routes to · App shell renders company-centric detail pages
- Frontend → Admin console: routes to · App shell renders operational dashboards
- Frontend → API register: routes to · App shell renders API signup flow
- Frontend → Tracking API: reports · Client events and dwell-time telemetry are sent from the UI
- Backend → Frontend: serves · Backend hosts the web application and API consumers
- Backend → Online search: queries · Live backend delegates search requests to the online engine
- Backend → PostgreSQL store: reads/writes · Backend persists users, usage, and searchable company data
- Online search → PostgreSQL store: reads · Primary search executes against PostgreSQL-backed tables and vectors
- Online search → Semantic assets: uses · Search relies on alias expansion, pinyin/zhuyin normalization, and embeddings
- Semantic assets → Ollama model: depends on · Embedding and similarity flows rely on external model runtime
- Search fallback → PostgreSQL store: reads · Reduced-dependency search path still queries the main online store
- ETL scripts → Raw sources: ingests · Batch pipeline starts from raw source files
- Clean pipeline → ETL scripts: feeds · Cleanup stages prepare enriched inputs for the main ETL build
- ETL scripts → Flagship data: produces · Pipeline assembles the denormalized flagship company dataset
- Schedulers → ETL scripts: orchestrates · Scheduled runs trigger the staged enrichment pipeline
- SQLite export → Flagship data: exports from · Desktop packaging copies the built dataset into SQLite form
- SQLite export → SQLite snapshot: writes · Creates the embedded snapshot shipped with the offline app
- Desktop bundle → SQLite search: launches · Packaged app starts the local search backend
- SQLite search → SQLite snapshot: reads · Offline search runs entirely against the embedded database
- SQLite search → Flagship data: mirrors · Offline mode is derived from the same flagship dataset lineage
- Backend → Tracking API: logs · Backend consumes usage and telemetry events for analytics
- Backend → API register: issues keys · Backend manages API applications and credentials
