Report Engine

A configuration-driven reporting service built with Go, PostgreSQL, Chi, CSV, and XLSX exporters.

Problem statement

Business reporting often grows into a collection of report-specific SQL queries, API handlers, filters, and export implementations. That approach duplicates logic, couples releases to individual reports, and makes authorization, validation, observability, pagination, and export behavior difficult to keep consistent.

This project provides a generic reporting engine in which a report is described by a template rather than hard-coded application logic. A template declares:

• The base PostgreSQL table and alias.
• Allowed joins.
• Selectable output columns and labels.
• Allowed filter operators per column.
• Sortable and groupable fields.
• Default sorting and grouping.
• The maximum interactive page size.

Clients can generate paginated JSON and export the same result snapshot as CSV or XLSX. Runtime values are parameterized, while identifiers and expressions come only from validated templates.

Approach

The implementation follows a ports-and-adapters style architecture:

• internal/domain/report contains report models, errors, and interfaces.
• internal/usecase/report coordinates template persistence, generation, caching, and export.
• internal/querybuilder validates templates and requests and builds parameterized PostgreSQL queries.
• internal/repository/postgres implements template and report-data repositories.
• internal/cache stores generated snapshots and serialized exports.
• internal/exporter contains format-specific serialization strategies.
• internal/delivery/http exposes the service through Chi HTTP handlers.
• cmd/server composes the concrete dependencies and starts the server.

The main workflow is:

Template → validate and persist

Generate → load template version → validate request → find cached snapshot
         → on miss, execute complete query and cache rows
         → return run_id plus the requested page

Export   → load snapshot by run_id → find cached serialized format
         → on miss, serialize and cache → return file

API

Create or update a template:

POST /v1/report-templates

Generate a paginated report:

POST /v1/reports/{templateID}/generate
Content-Type: application/json

{
  "columns": ["customer_name", "order_status", "total_amount"],
  "filters": [
    {"field": "order_status", "operator": "in", "value": ["PAID", "SHIPPED"]}
  ],
  "sort": [{"field": "customer_name", "direction": "ASC"}],
  "group_by": ["customer_name", "order_status"],
  "page": 1,
  "page_size": 100
}

The response includes run_id and template_version. Export uses that immutable run snapshot:

POST /v1/reports/{templateID}/export
Content-Type: application/json

{
  "run_id": "<run_id-from-generate>",
  "format": "xlsx"
}

Supported formats are csv, xlsx, and the excel alias.

Design patterns

Error handling: Decorator pattern

ErrorHandlingDecorator wraps the core ReportService. Expected domain errors, such as an unknown template, invalid request, or unsupported format, retain their identity so the HTTP layer can map them to stable 400 or 404 responses. Unexpected infrastructure and serialization failures receive operation context and become 500 responses without leaking internal details.

This keeps error policy outside the business orchestration and avoids repeating wrapping and classification logic in every service method.

Logging: Decorator pattern

LoggingDecorator independently wraps the same service interface and records structured slog events for template creation, generation, and export. Logs include relevant fields such as:

• Operation and template ID.
• Template version.
• Duration and result size.
• Page information.
• Generation cache-hit status.
• Export format and output byte count.

HTTP middleware adds request IDs, method, path, response status, response size, panic recovery, and request duration. Because logging is implemented as a decorator, the core service remains unaware of logging infrastructure and is straightforward to unit test.

Export: Strategy pattern

Each export format implements the domain ExportStrategy interface:

type ExportStrategy interface {
    ContentType() string
    FileExtension() string
    Export(result Result) ([]byte, error)
}

exporter.Context selects a registered strategy from the normalized requested format. CSV and XLSX therefore share the same orchestration without conditional format logic in the report service. A new format can be added by implementing the interface and registering it with the export context.

Persistence and query construction

Repository interfaces isolate PostgreSQL access from use cases. The query builder acts as a specialized Builder: it maps client-facing column keys to template-owned SQL expressions, validates joins and operations, and binds filter values through PostgreSQL placeholders.

Performance optimizations

The service uses two bounded, TTL-based in-memory cache layers:

1. Result snapshot cache — stores the complete result for a run_id.
2. Serialized export cache — stores the first generated file for each run_id + format combination.

Generation cache keys include the template ID, template version, selected columns, filters, sorting, and grouping. Pagination is intentionally excluded. Consequently, repeated requests and requests for different pages of the same report reuse one query result. A generation cache hit returns the same run_id and slices the requested page in memory.

Export never reruns the report query. The first format request serializes the cached snapshot; later requests for that format return the cached bytes. Requesting CSV and XLSX for the same run serializes each format once while sharing the result snapshot.

Current cache characteristics:

• Entries expire after 30 minutes.
• Result snapshots are limited to 100 entries.
• Serialized exports are limited to 300 entries.
• Entries are process-local and disappear on restart.
• Multiple application instances do not share cache state.

Observed local E2E response improvement

The Docker-backed E2E test confirms that repeated requests take the cache paths. In one local run using the deterministic two-row fixture:

Operation	First request	Cached request	Observed change
Report generation service time	11.9 ms	0.7 ms	approximately 94% lower
CSV export service time	57 µs	4 µs	serialization skipped

The repeated generation log reported cache_hit=true, reused the same run_id, and did not execute the report query again. The repeated CSV export was byte-identical to the first response. These values validate the optimization path but are not a production benchmark; actual latency depends on query complexity, row count, database load, hardware, network overhead, and export size.

For larger production workloads, replace the in-memory adapter with Redis or object storage, add byte-based limits, and stream or asynchronously generate very large exports. PostgreSQL indexes should support configured joins and common filters, and expensive aggregate reports may benefit from materialized views.

Adding a new template

Posting a previously unseen template ID performs the following actions:

1. The query builder validates the template before persistence.
2. PostgreSQL inserts the template at version 1.
3. No report query runs during template creation.
4. The first generation request executes the configured query and creates a version-1 snapshot.
5. Later equivalent generation and export requests reuse their respective caches.

Templates may reference application-owned tables or views in the configured PostgreSQL database. The customer/order schema supplied under docker/postgres exists only for local and E2E testing.

Updating an existing template

Posting an existing template ID performs an atomic PostgreSQL upsert and increments its version. Existing cached runs are not overwritten or invalidated:

• An existing run_id remains bound to its original template version and rows, so an export stays consistent with what the user viewed.
• A new generation loads the latest template version. Because the version participates in the cache key, it cannot reuse a snapshot created by an older template definition.
• The latest query runs once, creates a new snapshot and run_id, and subsequent equivalent requests reuse it.
• Exporting the new run_id serializes the latest query result; exporting an older run_id serializes the older snapshot until it expires.

This behavior provides snapshot consistency while allowing template definitions to evolve safely.

Running locally

With Go and PostgreSQL:

export DATABASE_URL='postgres://postgres:postgres@localhost:5432/report_engine?sslmode=disable'
psql "$DATABASE_URL" -f migrations/001_create_report_templates.sql
go run ./cmd/server

With Docker Compose:

docker-compose up -d --build
curl http://localhost:8080/healthz

Docker Compose loads deterministic customer/order fixtures for local demonstration. Templates are created through the HTTP API; examples/customer_order_summary_template.sql is an optional manual example.

Testing

Run unit tests:

go test ./...

Run the Docker-backed E2E test:

./scripts/e2e.sh

The E2E flow creates and updates a versioned template, verifies that the updated query result is used, exercises repeated generation and export cache paths, validates CSV/XLSX contents, and writes evidence to artifacts/e2e/e2e.log.

Additional design details are available in docs/design.md.