Skip to main content

CSV Reader Engine (DuckDB Engine)

Overview​

Audience results in this system are stored as CSV files on MinIO/S3, not as database rows, so that very large member lists remain workable. Whenever that data has to be read back — paginating the member list, counting members, or pulling LINE user IDs for a broadcast — this engine does the reading.

The original NestJS service used in-memory DuckDB with httpfs to read CSVs straight from S3. The Go implementation is a rewrite in pure Go with no CGO: it streams the file from object storage, parses the CSV itself (handling quoted fields, embedded newlines, and BOM), and holds the result as an in-memory table cached for 30 seconds per path — while keeping the public surface and output identical to the DuckDB original.

One test endpoint exists, GET /api/duckdb in the duckdbctl module, for debugging whether a given CSV path can be read.

Business Flow​

  1. The Audience Management and Audience Filter modules build an audience and write the result as a CSV into object storage.
  2. When cms-web calls GET /api/audiences/:id/member, the service calls LoadCsvFromMinio(path) if the file is not already cached, then GetPaginatedLineUserIds.
  3. The cache lives for 30 seconds, so subsequent page requests within that window skip the download.
  4. GetTotalLineUserCount answers the total member count.
  5. When the data is no longer needed, the engine calls DestroyTempTable, CleanupExpiredTables, or CleanupAllTables.
  6. Rich menus bound to an audience use the same path to resolve their LINE user list, via processRichMenuAudience in richmenu/create_update.go.
  7. GET /api/duckdb?path=...&page=&limit= is the debug endpoint, wired straight into the Test function.

Behaviours deliberately kept identical to the DuckDB original​

  • Empty CSV values are treated as NULL, so rows with a blank "Line UserID" column are filtered out by an IS NOT NULL condition.
  • GetPaginatedLineUserIds and Test read the first column of each row, following the TypeScript Object.values(r)[0] behaviour, rather than the column literally named "Line UserID".
  • _ensureTableExists, the path used by the test endpoint, does not apply the null filter.
  • All returned values are strings (DuckDB would have inferred numeric types) — an accepted, documented deviation.

Key Files & Functions​

FileRole
internal/duckdb/duckdb.goThe engine itself, reached via Deps.CSVEngine
internal/modules/duckdbctl/controller.goRegisterRoutes registers authed.GET("/duckdb", h.test)
internal/modules/duckdbctl/dto.goQuery params: path is required, page and limit optional
MethodRouteHandlerGuard
GET/api/duckdbh.testThe authed group (global JWT); no policy check

Engine public API: LoadCsvFromMinio, GetPaginatedLineUserIds, GetTotalLineUserCount, DestroyTempTable, ListTables, CleanupTableCache, CleanupExpiredTables, CleanupAllTables, GetCacheStats, Test

Connections to Other Services​

  • Permission — requires a token that passes the global JwtAuth guard (which mandates lineOaId), but no policy check is applied.
  • Storage — reads files from MinIO/S3 through internal/storage.
  • Consumers — Audience Management, Audience Filter, and Rich Menu.
  • Caveat — the cache is in-memory and per-process, so each replica maintains its own copy.