vec.trace

Compose vector distributions out of your SQL queries, then find the entities that do not behave like anybody else.

vec.trace turns the tables of your PostgreSQL databases into one vector per entity — a customer, a device, a merchant — and runs outlier detection on the result. You describe what a behaviour is with ordinary SQL; vec.trace takes care of joining the descriptions, normalising them, choosing the right detector, scoring every entity and explaining why the anomalous ones are anomalous.

Everything you build (connections, blocks, distributions, tuning profiles, runs and their per-entity results) is persisted per user in a PostgreSQL service database, so an analysis is a reproducible object, not a notebook you lose.

source DB ──query──> block ──┐
source DB ──query──> block ──┼──join on entity id──> distribution ──> run ──> outliers + why
source DB ──query──> block ──┘                                   (profile = tuning)

Two frontends share one core library: a command line (vec.cli) and a Streamlit application (vec.app). Neither contains domain logic.

---

The concepts in one table

Concept	What it is
Connection	a source PostgreSQL database, reachable through a read-only role; its password is encrypted at rest
Block	one SELECT + the column that holds the entity id + a map that renames the numeric result columns into components
Distribution	blocks joined on the entity id (inner, left or outer): a matrix of n entities × d components
Profile	a saved set of algorithm parameters (scaling, imputation, PCA, contamination, detector…), stored partially so it keeps inheriting the defaults
Run	one detection execution, with the effective parameters snapshotted and the per-entity score, percentile, flag and explanation

How an outlier is found

The workflow follows a fixed sequence, and every step reports what it decided:

1. Prepare — drop constant components, impute the gaps left by a left/outer join, scale with RobustScaler (median / IQR), rescaling by the standard deviation the components whose IQR is zero (sentinel values, sparse counters).
2. Reduce — above max_dim (50) components, project with PCA. The target is the number of components whose eigenvalue crosses the Marchenko-Pastur noise edge, and the projection is skipped when the components are uncorrelated: truncating a flat spectrum only mixes signal into noise.
3. Test the shape — is the distribution unimodal or multimodal? A Gaussian-mixture BIC comparison on a low-dimensional projection plus a KDE peak count, both run on a trimmed sample so that the outliers themselves do not masquerade as a second population.
4. Detect — ECOD for a unimodal distribution, IsolationForest for a multimodal one (a partitioning detector also replaces ECOD when the matrix handed to the detector is still very wide, because an additive marginal score is diluted by the number of components).
5. Interpret — the percentile of every score, the flag from the contamination threshold, and for the most anomalous entities a median-counterfactual attribution: each component is reset to its median, the fitted model scores the entity again, and the score drop is that component's share of the anomaly.

Steps 2 and 4 deviate from the literal rule in the original specification for measured reasons, and both are one parameter away from the literal behaviour (pca_adaptive = false, high_dim_algorithm = null). The numbers behind that choice are in docs/ALGORITHMS.md.

---

Quick start with Docker

docker compose up -d --build

That starts PostgreSQL (service database and an e-commerce demo dataset) and the UI on http://localhost:8501. No .env needed: every variable has a default. The first account you create from the sign-in page becomes the administrator.

The demo database contains 3,012 customers with 12 planted anomalous accounts, and three ready-to-paste block queries. A detection run over them puts 10 of the 12 in the top 20, in under a second:

distribution 'customer-behaviour'
  entities         3012
  components       40
  components used  39
  modality         distribution judged multimodal by combined test
                   [gmm=multimodal, kde=multimodal]: best mixture k=4
  algorithm        IsolationForest (iforest)
  outliers         13 of 3012 (0.4%)

why '900001' (rank 4, percentile 99.9, score 0.6456):
  1. 'orders_total' = 360, at the 100.0th percentile of the distribution,
     robust z = +32.36, median = 4, restoring the median lowers the score
     by 0.010 (21% of the anomaly)
  2. 'tickets_total' = 6, at the 99.9th percentile, robust z = +6.00, ...
  3. 'events_total' = 420, at the 99.9th percentile, robust z = +4.15, ...

Ports, credentials, the demo schema and the queries: docker/README.md.

Quick start from source

python -m venv .venv && .venv\Scripts\activate     # source .venv/bin/activate
pip install -e ".[dev]"
copy .env.example .env                             # cp .env.example .env

vec.cli db init
vec.cli user create alice --password -             # the first user is the admin
vec.cli auth login --username alice

vec.cli conn add shop --host localhost --port 5433 --database vectrace_demo \
                     --username vec_reader --password - --search-path shop
vec.cli block create orders --connection shop --query-file orders.sql \
                     --entity-id customer_id --map orders_90d --map revenue --verify
vec.cli block create web --connection shop --query-file web.sql \
                     --entity-id customer_id --map events_total --map night_event_share
vec.cli dist create customer-behaviour --blocks orders,web --join left

vec.cli detect run customer-behaviour --top 15
vec.cli detect explain <run-id> 900001
vec.app                                            # the same workflow in the browser

---

Features

• Blocks as first-class objects — a query, its entity id and its component mapping are persisted, versioned by name and reusable across distributions.
• Composition by join — inner, left and outer on the entity id, with per-component missing-value reporting and five imputation strategies.
• A detector chosen from the data, not from a config file: unimodal → ECOD, multimodal → IsolationForest, with an explainable modality verdict (BIC and KDE evidence stored in the run).
• Adaptive dimensionality reduction driven by random matrix theory instead of a blind truncation.
• Explanations, not just scores — percentile, rank, robust z, empirical tail percentile and a model-aware counterfactual attribution expressed in the original components (never in principal components).
• Reproducible runs — every run stores its effective parameters, its notes, its timings and its per-entity result; re-running a distribution months later shows exactly what was used.
• Multi-user and safe — PBKDF2 password hashing, revocable session tokens, Fernet-encrypted source credentials, per-user ownership, read-only SQL validation and read-only server transactions with a statement timeout.
• Two frontends, one core — a scriptable CLI with --json/--csv output and meaningful exit codes, and a Streamlit app with an interactive block builder.
• Portable — a docker compose up away, and the test-suite runs on SQLite for the core and on PostgreSQL for the connector.

Documentation

Document	Contents
docs/USER_MANUAL.md	concepts, installation, the UI page by page, the full CLI reference, a worked example, how to read a result, tuning recipes, troubleshooting
docs/ALGORITHMS.md	the mathematics: robust scaling, imputation, PCA and the Marchenko-Pastur edge, the modality tests, ECOD and IsolationForest, thresholds, the counterfactual attribution, complexity, the parameter reference and the limitations
docs/SOFTWARE.md	architecture, module map, the service database schema, the settings cascade, the security model, the CLI and the app internals, configuration, packaging, extension points
docker/README.md	the compose stack, the two databases and their roles, the demo dataset, the 12 planted anomalies, three example block queries
spec.txt	the original (Italian) requirements this project was built from

Project layout

src/vec/
  trace/                 the core library — no UI knowledge
    config.py            settings and every algorithm parameter
    models.py            the service database schema (SQLAlchemy 2.0)
    db.py repository.py  engines, sessions, per-user persistence
    auth.py security.py  users, tokens, password hashing, credential encryption
    connectors/          read-only SQL validation + the PostgreSQL connector
    blocks.py            query -> entity id + numeric components
    distributions.py     join the blocks into the matrix
    pipeline/            preprocessing, reduction, modality, detectors,
                         explanation, engine
    service.py           the VecTrace facade used by both frontends
  cli/                   vec.cli: argparse commands, output formatting
  app/                   vec.app: Streamlit entry point + 8 pages
tests/                   248 tests: units on SQLite, pages via AppTest,
                         integration on PostgreSQL
docker/                  compose init scripts, demo dataset, deployment notes

Development

pip install -e ".[dev]"
pytest                                     # 234 tests, no external service needed
set VECTRACE_TEST_SERVICE_DSN=postgresql+psycopg://vectrace:vectrace@localhost:5433/vectrace
pytest                                     # + the 14 PostgreSQL integration tests
ruff check src tests

Module	Tests	Covers
test_pipeline.py	78	preparation, modality, reduction, ECOD/IsolationForest, thresholds, explanations, end-to-end detection
test_core_units.py	57	configuration, parameter validation, security, SQL safety, block materialisation
test_service_sqlite.py	37	persistence, ownership, the settings cascade, the run lifecycle
test_cli.py	35	argument handling, exit codes, the whole command surface
test_distributions.py	14	join semantics and composition errors
test_postgres_integration.py	14	the real connector, the read-only transaction, a run on PostgreSQL
test_app.py	13	every Streamlit page, headlessly, via streamlit.testing.v1.AppTest

The integration suite creates and drops its own schema, so it is safe to run against the demo database.

Security notes

• Source queries are validated (one statement, read-only keyword) and executed inside SET LOCAL transaction_read_only = on with a statement timeout. Register your connections with a role that can only SELECT.
• Source passwords are encrypted with VECTRACE_ENCRYPTION_KEY before being stored. Set that variable explicitly in any shared deployment; changing it later makes the stored passwords unreadable.
• vec.trace reports statistical outliers. A flagged entity is unusual, not proven guilty — the explanation exists so a human can decide.

Status

0.1.0 — the core workflow (composition + outlier detection), both frontends, the container deployment and the demo dataset are complete and tested. Foreseen by the design and not implemented yet: SPARQL and HTTP-API sources, operations on distributions other than outlier detection, incremental materialisation, schema migrations.

License

Apache License 2.0 — see LICENSE.