docs/usage-analytics/architecture.md
Rama main · réplica de lectura del repositorio original.
# Internal usage analytics architecture
This is an engineering reference for the dedicated analytics project: how events
become queryable Parquet, how the backend obtains access, and which security
boundaries are implemented. It describes the connector, credential provider and
project-provisioning stack, not a production-ready customer rollout.
The intended product is a backend-managed metadata project, separate from an
organization's business models. Users explore fixed dimensions and metrics using
Lightdash's normal Explore/query flow; this is not just a set of hardcoded report
queries. It does not require dbt or a MotherDuck account.
For current role requirements, project-list visibility and support guidance, see
[managed analytics project access](access.md). A standard project type does not
remove the analytics-specific organization-management check.
For the automatic import on project creation and the Settings action for
existing projects, see [historical backfill](history-backfill.md). It recovers
retained query history and AI usage into their event datasets, plus legacy
views, app, conversation, tool and export facts in a separate Historical Activity
Explore. It does not reconstruct every past event or historical People summaries.
## Current implementation versus rollout intent
| Area | Implemented | Still required for production |
| ---------------------- | ------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------- |
| Project | New projects use `DEFAULT` with `provisioning_source=analytics`; older previews retain their type | Non-admin readers need explicit managed Analytics and project permissions |
| Entry point | Organization analytics settings and session-authenticated, org-admin-only endpoints | No configurable connector setup UI |
| Enablement | Validated commercial license and usage storage; enabled by default | Controlled live verification before customer rollout |
| Source org | Persisted, authorized project org; legacy local overrides ignored | Live shared-instance isolation verification |
| Storage authentication | Existing writer credentials retained in backend; signed GET URLs passed to DuckDB | Verify effective IAM; read-only hardening is deferred (PROD-11103) |
| Models | Backend-owned Query Events, AI Usage, Data App Events and Export Events explores | Additional event coverage and named-entity lookups |
| Current names | Daily per-org charts, dashboards, users and agents snapshots with optional ID joins | Other entity lookups; production-scale database load verification |
Historical tickets and handover proposals may describe different designs. They
are not evidence that production rollout or final role restrictions are complete.
See [current dimensions](dimensions.md) and [export events](exports.md) for the
implemented enrichment and event coverage.
## End-to-end data flow
```text
Backend event projections
→ buffered writer: gzip JSONL in events/raw/ (org / stream / date)
→ scheduled compactor: typed Parquet in events/compacted/ (same partitions)
Postgres charts, dashboards and organization members
→ same daily job: paged reads → temporary JSONL → Parquet
→ overwrite one current object per organization and dimension
Retained Postgres query history, AI usage ledger and allowlisted activity records
→ one background backfill → history=<stream>/history.parquet
→ immediately queryable; no additional nightly compaction required
Signed-in org admin
→ create-or-get endpoint → fixed system explores stored in the app database
→ Explore query → backend authorization and org-scoped file discovery
→ exact-file signed GET URLs + optional org-scoped cached bytes
→ private query files or remote reads → isolated in-memory DuckDB → query results
```
### Write path and partitions
The [event registry](../../packages/backend/src/analytics/eventStream/registry.ts)
allowlists projected events and defines each stream's compacted schema. The
[buffered writer](../../packages/backend/src/analytics/eventStream/BufferedEventStreamWriter.ts)
groups records by org, stream and UTC date, and writes independent gzip JSONL
objects. Writers do not append to a shared Parquet object.
```text
events/raw/org_id=<org-uuid>/stream=<stream>/dt=<YYYY-MM-DD>/<writer>-<uuid>.jsonl.gz
events/compacted/org_id=<org-uuid>/stream=<stream>/dt=<YYYY-MM-DD>/part-<hash>.parquet
```
Partitions are object-key prefixes grouping related data, not separate databases
or access-control policies. Org partitioning keeps each output part within one
org; stream/date partitions keep schemas and time ranges organized. The read
layer lists one org prefix, then selects supported streams across all retained
dates. Date filters belong to Explore queries, not a fixed source window. It does
not list the whole bucket and filter orgs later. Only compacted Parquet is read;
today's uncompacted raw events are not yet available through this reader.
The [compactor](../../packages/backend/src/analytics/eventStream/UsageEventsCompactor.ts)
processes closed date partitions (before today UTC), converts the exact listed
raw objects into typed, Zstandard-compressed Parquet, then deletes those raw
objects only after successful conversion. Unknown streams are skipped. Part
names are deterministic for the input-key set; late-arriving files can produce
additional parts. There is not necessarily one file per day or one file per org.
With Google OAuth storage, a token can expire during a long partition query.
Compaction retries an HTTP 401 once in a new query session using the same inputs
and output key. If that retry fails, raw inputs and the checkpoint remain for
the next run. This does not extend the token lifetime for a single query.
Compaction reduces the small-file overhead of independent writers and produces
a columnar format suitable for analytics. The reader only sees compacted output,
so capture is not immediately visible in Explore. Event delivery does not have
an exactly-once guarantee.
Embedded dashboard fetches reuse `content_views`. After a successful authorized
fetch, `embed_dashboard.viewed` records an interaction ID and the explicit JWT
`user.externalId`, when supplied. The usage-only metadata is not added to
RudderStack. Preview traffic is excluded, the registered-user ID remains null,
and neither the token issuer nor a generated fallback identity counts as an
external viewer. Arbitrary user attributes are not captured.
The Embed reach model deduplicates interaction IDs and counts external IDs
within each organization/project. Refetches are separate loads; tile queries
are not loads. This is observed backend usage, not confirmed browser rendering,
configured embed dependencies, tenant attribution or invoice reconciliation.
Existing partitions without the new nullable column remain readable; capture
starts after deployment and requires no backfill. New loads appear after daily
compaction, and **Sync content** installs the Explore and Embed dashboard.
### Current dimension snapshots
After event compaction, the same `compactUsageEvents` job refreshes charts,
dashboards and users from Postgres, including on days with no new events. Each
organization selected for processing has separate files in the shared bucket.
Nightly runs require a managed Analytics project; regular projects alone do not
qualify. Explicit organization-scoped processing retains its existing scope:
```text
events/compacted/org_id=<org-uuid>/dim=charts/charts.parquet
events/compacted/org_id=<org-uuid>/dim=dashboards/dashboards.parquet
events/compacted/org_id=<org-uuid>/dim=users/users.parquet
events/compacted/org_id=<org-uuid>/dim=agents/agents.parquet
```
Every successful refresh overwrites these keys, even when the source is unchanged.
There is no checksum comparison, missing-ID reconciliation or historical snapshot
series. Names reflect the latest successful refresh, including for older events.
The exporter filters Postgres rows by org before writing; the reader lists and
signs only that org's files. Org isolation does not depend on a query-time SQL
filter over a shared all-org file.
The exporter processes organizations and dimensions sequentially, reads 1,000-row
keyset pages, stages data on disk, converts with one DuckDB thread and a 256 MB
engine limit, and streams uploads with one 8 MB part in flight. See
[dimension export and failure behavior](dimensions.md#export-and-failure-behavior)
for resource limits, retries and operational caveats. Reader resource settings
below apply to interactive queries, not this background export.
Scheduled processing includes only organizations with a managed Analytics project
(`provisioning_source = 'analytics'); deployment availability still applies. Regular,
preview and playground projects alone do not qualify. Raw event capture continues
for other organizations, but their events are not processed by scheduled runs.
Creating Analytics still queues explicit organization-scoped initial processing
and makes the organization eligible for future nightly runs. Explicit recovery
runs remain available. Initial and nightly processing prepare the last seven
closed UTC days first, then enqueue a low-priority history pass after one minute
when older work remains. Project setup also checks previously compacted history.
History processing includes all retained closed raw partitions, including events
captured before the Analytics project existed. It uses the same compactor and
serial queue, so recent processing and history cannot compact the same inputs
concurrently. Today's events remain excluded until the next UTC day.
Recent processing retains its 5,000-partition work budget. History batches
process up to 100 raw partitions and 100 changed People summaries, then schedule
a continuation while deferred work is making progress. Successful raw inputs are
removed and unchanged People summaries are skipped, so subsequent batches resume
from storage without a new database cursor. Failed or unsupported partitions do
not cause an endless continuation loop; nightly processing revisits retained
work. Nightly processing also revives exhausted history jobs, including failed
People summaries whose raw inputs were already compacted. Runs with no deferred
history or failed jobs avoid rescanning old summaries;
organization-scoped setup and recovery still perform a history pass. Settings
counts older pending partitions and polls between batches while
keeping Explore available. The retained-database import is separate and
supplements, rather than replaces, captured event history.
### Project creation and models
[`ProjectService.ensureAnalyticsProject`](../../packages/backend/src/services/ProjectService/ProjectService.ts)
backs `POST /api/v1/org/analytics-project`:
1. Check the session's organization, org-management permission and deployment availability.
No org, bucket, path or credential is accepted in the request.
2. Verify signed-Parquet access before creating a project. Model compilation is
in memory, but the endpoint includes this storage round trip.
3. Acquire the per-org advisory lock and look for `provisioning_source=analytics`.
4. Reuse that project or create an internal DuckDB analytics connection, then
save the compiled system explores. Repeated calls refresh models without
deleting saved content and can repair a previous model-save failure.
5. Return `{ projectUuid, url, created }`. Redirect to the returned URL.
The lock serializes concurrent lookup/create requests across API pods. Identity
comes from the internal marker and org, not the display name or slug. The normal
[`generateUniqueProjectSlug`](../../packages/backend/src/utils/SlugUtils.ts)
allocator derives `lightdash-analytics` from `Lightdash analytics`, then tries
`lightdash-analytics-1`, `-2`, etc. within the org. Reused projects keep their slug
and UUID; a conflicting ordinary project is never repurposed.
[`createAnalyticsExplores`](../../packages/backend/src/services/ProjectService/analyticsProject/createAnalyticsExplores.ts)
derives dimensions from the compacted schemas and metrics from
[`systemStreamMetrics`](../../packages/backend/src/analytics/systemExplores/systemStreamMetrics.ts).
It explicitly includes `query_events`, `ai_usage`, `data_app_events` and
`export_events`; registering another writer stream does not expose another
explore automatically. Query Events joins Charts, Dashboards and Users; AI Usage
joins Users and Agents; event models can join Users. Content inventory reads only
the current `lightdash_content` snapshot, without user or event joins. These optional many-to-one
LEFT joins match organization and
entity ID, preserving events whose lookup is missing. Compiled models
refer to table names, not signed URLs. SQL and aggregations are generated from
the user's Explore selections over these fixed models.
## Credentials and trust boundaries
There are three distinct forms of access; none should be confused with the others:
| Layer | Credential or authority | Scope |
| ------------------------ | ---------------------------------------------------------------------------- | -------------------------------------------------------------- |
| User → Lightdash | Authenticated session, org authorization and deployment availability | Access to that org's analytics project |
| Backend → object storage | Server-owned usage-events access key/secret, currently reused from ingestion | Potentially broad bucket access, including writes; not per-org |
| DuckDB → Parquet | Short-lived signed GET URLs generated by the backend | Exact selected objects, HTTP method and expiry |
The current tested cloud path uses GCS's S3-compatible interface and HMAC
credentials with the AWS S3 SDK. It does not run a cloud CLI, obtain a developer
OAuth token, provision an identity or require a MotherDuck token.
### Configuration and lifetime
[`parseUsageEventsS3Config`](../../packages/backend/src/config/parseConfig.ts)
reads `USAGE_EVENTS_S3_ENDPOINT`, `USAGE_EVENTS_S3_BUCKET`,
`USAGE_EVENTS_S3_REGION`, `USAGE_EVENTS_S3_ACCESS_KEY` and
`USAGE_EVENTS_S3_SECRET_KEY`. Each falls back to its base `S3_*` setting; a valid
base storage configuration is still required. These are server configuration,
not user-editable project credentials. Use the endpoint override to read GCS
without redirecting ordinary local MinIO storage. This override applies to the
usage-events writer/compactor configuration too, not only the reader.
[`S3AnalyticsSource`](../../packages/backend/src/services/ProjectService/analyticsProject/S3AnalyticsSource.ts)
lists `events/compacted/org_id=<validated-org>/`, validates returned keys and
selects only supported Parquet paths across all retained dates. It bounds pagination
(1,000 objects/page, at most 100 pages) and selected files (10,000), and rejects
incomplete/malformed listings or an empty manifest. It signs exact
`GetObject` requests for 900 seconds and destroys the per-resolution SDK client.
The backend still retains its configured credentials; destroying the client
does not erase that process's configuration or revoke credentials.
Each new DuckDB session resolves a fresh manifest. Buffered files need no
signature unless private staging fails. Remote files reuse a bounded process-local
signature cache, keyed by a SHA-256 digest of organization, storage configuration,
resolved credentials and exact object key/ETag/size. Concurrent requests share
pending signatures. Reuse ends two minutes before URL or credential expiry;
failed signing is never cached. Unversioned objects are signed on each resolution. Credential rotation must
refresh backend configuration through the deployment's normal secret/restart
mechanism; this is not an automatic rotation implementation.
### What reaches DuckDB
The signed manifest contains table names, an expected prefix and exact URLs.
It contains no bucket access key/secret and no bearer token. A signed URL is
itself a bearer capability: anyone possessing it may read that object while it
is valid. Never place one in logs, API responses, tickets or persisted models.
The internal Parquet path in
[`DuckdbWarehouseClient`](../../packages/warehouses/src/warehouseClients/DuckdbWarehouseClient.ts):
- Creates isolated in-memory query instances (256 MB DuckDB memory limit, 32
threads to overlap remote footer and column reads). Explicit caller resource
limits override these internal-reader defaults; other DuckDB defaults are unchanged.
- Requires HTTPS for remote storage; HTTP is restricted to loopback test endpoints.
- Validates canonical paths against the trusted scope; disallows arbitrary
globbing/path substitution and mixing signed URLs with broad storage secrets.
- Sets exact `allowed_paths` and disables general external access and disk spilling.
- Enables HTTP metadata, Parquet metadata and external-file caching only inside
the private query instance. The instance is closed on success or failure;
its engine caches are not reused by another request. The separate backend
byte and signature caches described below can serve later authorized queries
for the same organization.
- Builds temporary views over `read_parquet` for those exact objects only, plus
validated typed empty views for dimension snapshots not yet published.
- Restricts user SQL and blocks catalog access that could disclose view SQL;
disables profiling and sanitizes native query errors.
Schema binding retains `union_by_name=true` so older files can lack newer columns.
Caching prevents repeated remote metadata reads during binding, validation and
execution. This does not remove all-history discovery, skip old files or change
the 10,000-selected-file cap (unrelated tables no longer consume it). Large histories still require further scaling work (PROD-11111);
these settings do not guarantee a latency ceiling for arbitrary data volumes.
Each concurrent query has its own thread budget; deployment-wide and per-org
concurrency limits still need validation before customer rollout.
The query-local cache lifetime does not mean Lightdash has no persisted query results.
The normal result/history paths still exist and need authorization. Analytics
checks cover project access, result/history retrieval, CSV/XLSX export generation
and protected download retrieval. Scheduled deliveries can intentionally share
signed export links; see [downloads and scheduled deliveries](access.md#downloads-and-scheduled-deliveries).
Deleting the project does not revoke previously issued signed delivery links
or returned data.
### Small Parquet cache and temporary-file lifecycle
[`AnalyticsFileCache`](../../packages/backend/src/services/ProjectService/analyticsProject/AnalyticsFileCache.ts)
caches compressed Parquet bytes in the query-serving Node process. It is separate
from DuckDB's per-query caches and Lightdash's query-result cache. Object storage
remains the source of truth; this does not change capture or nightly compaction.
1. Authorize the analytics project and resolve its persisted organization as
usual. List current objects on every query; never reuse the manifest.
2. Reuse or download eligible bytes. Cache identity includes storage endpoint,
bucket, authorized org prefix, storage configuration/resolved credentials,
object key, ETag and size. Downloads use
`If-Match` and validate the returned ETag and exact length. Changed objects
get new entries; deleted objects are absent from the new manifest and cannot
be selected from the cache. Concurrent requests for the same identity share
one download; different organizations do not share entries or downloads.
3. After validating the manifest URLs, write cached bytes to a new private
`lightdash-analytics-query-*` directory under `os.tmpdir()` on the query-serving
host/container. These are compressed Parquet copies, not exported query
results. Files use owner-only permissions (`0600`) in a private directory.
A table uses local files only when all its files are available within the
staging budget. Only tables that can fit the budget are downloaded. Buffered
objects use canonical unsigned URLs internally; these never reach DuckDB. If
staging fails, the provider lazily signs those exact objects with a fresh SDK
client. DuckDB validates their scope and unchanged object paths before using
the whole table remotely, preserving the signed-only engine boundary.
4. Give that query's isolated DuckDB instance an exact-file allowlist, never a
directory or shared-cache allowlist. Arbitrary external access remains disabled.
Another organization's cached or staged files are not in that allowlist.
5. In session teardown (`finally`), close DuckDB and remove the query directory
recursively. Normal query/bootstrap errors also run teardown. Later queries
create new directories even when reusing the same in-memory bytes.
| Resource | Limit / lifetime |
| ---------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- |
| Reusable memory cache | 64 MiB of compressed bytes and 16,384 entries **per process**, shared capacity across orgs but separately keyed entries |
| Eligible file | At most 1 MiB, with a positive known size and ETag |
| Cache downloads | At most 16 in flight per process; excess requests queue within reserved byte/entry limits; 10-second active-request timeout |
| Per-query buffers and staged files | At most 32 MiB each; concurrent queries have separate staging directories |
| Memory eviction | Least recently used completed entries are evicted when capacity is needed; no time-based TTL; process exit clears memory |
| Reusable signatures | At most 16,384 entries / 32 MiB per process, including pending entries; 16 active signatures, bounded queue, LRU eviction; process exit clears cache |
| Disk cleanup | At session teardown after success or error; no scheduled cleanup interval |
The byte budget is not a process RSS limit: in-flight queries can still reference
buffers evicted from the cache, and DuckDB, JavaScript and concurrent staging add
resource use. Each replica/process warms independently. Cache misses, download
failures, missing metadata, budget exhaustion or staging failures fall back to
signed remote reads; there is no stale-data fallback. Tables that stage locally
make zero signing calls, including on a cold cache. Listing remains fresh and
large/unbufferable cold tables still need per-file signatures, so this does not
guarantee a latency ceiling for arbitrary histories.
`Analytics source prepared` logs listing, download, signing and total preparation
times, selected/buffered/signed file counts, buffer bytes and signature cache hits.
`DuckDB Parquet source prepared` adds source resolution and private staging times.
The lazy staging fallback has its own signing timing. These logs contain no object
keys, signed URLs, credentials or query results.
**Crash limitation:** SIGKILL, OOM or host failure can prevent `finally` from
running; a teardown error can also prevent directory removal. There is no startup
or periodic sweeper. A process restart clears memory but does not guarantee removal
of orphaned files from the host/container temporary filesystem. Their retention
depends on that filesystem's lifecycle/cleanup. Do not bulk-delete these directories
while queries are active; operational cleanup must first establish that no live
query owns them. Disabling the feature prevents new authorized reads but does not
immediately purge previously cached bytes. Eviction is not secure erasure.
### Org isolation: implemented boundary and remaining risk
[`analyticsProjectClient`](../../packages/backend/src/services/ProjectService/analyticsProject/analyticsProjectClient.ts)
reads files for the persisted project's org after service authorization checks.
Legacy local org/source-org overrides are ignored in all environments. Production
execution uses validated commercial registration and configured usage storage.
See [live testing](live-testing.md) for rollout and remaining trust boundaries.
The prefix filter is a backend authorization boundary, not bucket IAM. Storage
signatures enforce the exact object/method capability passed to DuckDB, but the
trusted signer can still access or sign other objects using its broader keys.
A compromised signer/backend is outside the protection provided by those URLs.
Tests for path tampering do not prove arbitrary SQL or backend compromise is safe.
Read-only credentials reduce the signer's write/delete privilege exposure. They
do not, on their own, prevent reads across org prefixes. The deferred identity
design is a separate deployment-level reader identity plus reviewed app-side org
binding, not a service account per app org. Creating another key on the writer's
identity does not narrow permissions. See PROD-11103 for infrastructure guidance;
no Terraform/IAM change or read-only cutover is included in this stack.
## Operations, verification and rollout
- Follow [local testing](local-testing.md) for deployment availability, persisted-org
binding and the browser-console provisioning request. Keep capture
disabled for a read-only local test; never copy another worktree's DB settings.
- Follow [credential verification](credentials.md) for real-bucket read-only
smoke tests and disposable MinIO tests of signatures, expiry and denied writes.
- Missing data: check capture, compaction success, source org, Explore date
filters, retention and stream schemas. No supported event files means no data is
available, even if dimension snapshots exist; partially missing
streams and schema evolution still need rollout design.
- Missing names: check dimension refresh logs and the current per-org objects.
Click **Sync content** to refresh an existing project's models and managed
sample dashboards, or re-run the provisioning endpoint. See
[dimension rollout](dimensions.md#read-path-and-rollout).
- Access errors: check endpoint, bucket and configured key permissions without
printing keys, SDK request details or signed URLs. A new query can recover from
URL expiry; it cannot fix revoked or incorrectly scoped source credentials.
- Provisioning latency includes storage access. Large org histories can hit the
listing/file caps; freshness, caching, pagination scale and long-running query
behavior require further work before production (PROD-11111). Caps fail rather
than silently exposing only part of the history. Historical availability is
limited by retained files, not their age; one-year workloads remain unbenchmarked.
- Before customer rollout: verify production authorization, role restrictions,
effective IAM, and query/cached-result surfaces. Dedicated read-only credentials
are optional deferred hardening (PROD-11103). Hidden UI and folder
separation are not substitutes for access control.
The read path has been exercised against real GCS with native DuckDB and via the
local API for the original Query Events and AI Usage explores; local UI provisioning was manually confirmed.
Dimension exports and joins have been exercised against isolated Postgres and MinIO.
Focused tests cover reuse, access denial and credential boundaries. This is not
a production multi-tenant security audit or a claim that every warehouse provider
and failure mode has been validated.
## Related work
- [PROD-11059](https://linear.app/lightdash/issue/PROD-11059): implementation/triage.
- [PROD-8603](https://linear.app/lightdash/issue/PROD-8603): pipeline architecture.
- [PROD-8678](https://linear.app/lightdash/issue/PROD-8678): current content and user dimensions.
- [PROD-11103](https://linear.app/lightdash/issue/PROD-11103): deferred read-only credentials.
- Stack: [connector + flag](https://github.com/lightdash/lightdash/pull/28909) →
[credentials](https://github.com/lightdash/lightdash/pull/28916) →
[project endpoint](https://github.com/lightdash/lightdash/pull/28849) → documentation.