Backend Engineering Node.js · PostgreSQL · Redis · KADA Capstone

CineTrack —
Cinema Analytics

Cinema chain dashboard backend: 20+ analytics endpoints powered by PostgreSQL CTEs, Redis caching, and LLM-generated business insights.

Backend Fastify (Node.js ESM) · PostgreSQL Cache Redis · filter-aware TTL keys SQL CTEs · EXTRACT · parameterized queries Context Korea-ASEAN Digital Academy (KADA) · Capstone
20+
Analytics
endpoints
Redis
Caching
layer
EXPLAIN
ANALYZE
optimized
LLM
Insight
engine
20+
Analytics
endpoints
5
Tables joined
per analytics query
3
Composite indexes
from EXPLAIN
HIT
Redis cache
bypasses DB on repeat
01 · Architecture Overview

One request, four layers.

Every analytics request flows through Fastify routing, filter building, Redis cache lookup, and — on a cache miss — a parameterized PostgreSQL query. On cache hit, the database is never touched.

Tech stack

  • Fastify (Node.js ESM) — JSON Schema validation on every route, plugin-based architecture, ESM imports throughout
  • PostgreSQL — CTE-based analytics queries joining 5 tables; parameterized queries via pg driver
  • Redis — Filter-aware cache keys with deterministic JSON serialization; graceful degradation on Redis unavailability
  • LLM (Elice AI) — Structured prompt with rolling 14-day analytics context; JSON output format enforced
Node.js ESMFastify PostgreSQLRedis CTEEXPLAIN ANALYZE
02 · Request Flow

From filter selection to JSON response in one clear path.

Every analytics endpoint follows the same flow: parse and validate query params → build parameterized WHERE clause → check Redis → query PostgreSQL on miss → cache result → return JSON.

Cache context propagation

Every request carries a cache context via AsyncLocalStorage — the response always tells the frontend whether the data came from Redis (HIT), the database (MISS), or whether Redis was skipped entirely (BYPASS, e.g. when Redis is unavailable).

// cache.js — AsyncLocalStorage context
export async function withCache(namespace, params, ttl, fetcher) {
  const key = buildCacheKey(namespace, params);
  const cached = await client.get(key);

  if (cached) {
    setCacheContext("HIT", key);
    return JSON.parse(cached);
  }

  const fresh = await fetcher();
  setCacheContext("MISS", key);
  await client.set(key, JSON.stringify(fresh), { EX: ttl });
  return fresh;
}

Redis errors are handled gracefully — if Redis is unreachable, the system marks it unavailable and falls back to direct DB queries without crashing.

03 · Analytics SQL

Every business question is a CTE.

The trickiest query is occupancy by city. A naive approach gives wrong results — averaging occupancy at the wrong granularity. The correct design uses a two-layer CTE.

WITH filtered_schedules AS (
  SELECT s.schedule_id, st.total_capacity,
    c.city, c.cinema_id
  FROM schedules s
  JOIN studio st ON s.studio_id = st.studio_id
  JOIN cinema c  ON st.cinema_id = c.cinema_id
  -- WHERE injected by buildScopeFilters()
),
per_schedule AS (
  SELECT fs.city, fs.schedule_id,
    COUNT(t.tiket_id)::int AS total_tickets,
    CASE WHEN fs.total_capacity > 0
      THEN COUNT(t.tiket_id)::float8
           / fs.total_capacity::float8
      ELSE 0 END AS schedule_occupancy
  FROM filtered_schedules fs
  LEFT JOIN tiket t
    ON fs.schedule_id = t.schedule_id
  GROUP BY fs.city, fs.schedule_id,
    fs.total_capacity
)
SELECT city,
  SUM(total_tickets)::int,
  AVG(schedule_occupancy)::float8 AS avg_occupancy
FROM per_schedule
GROUP BY city
ORDER BY SUM(total_revenue) DESC
"Averaging ticket counts at city level gives wrong occupancy. You must average at schedule level — then roll up."

Other analytics patterns

Time slot analysis
LPAD(EXTRACT(
  HOUR FROM CAST(
    s.start_time AS TIME)
  )::int::text, 2, '0')
|| ':00' AS time_slot

Revenue and demand per hour slot — drives pricing recommendations for under-monetized peak slots.

Weekend classification
CASE WHEN EXTRACT(
  DOW FROM CAST(
    s.show_date AS DATE)
  ) IN (0, 6)
THEN 'weekend'
ELSE 'weekday'
END AS day_type

Weekend vs weekday revenue split for scheduling and pricing decisions.

Date range injection
// appendDateFilter()
params.push(start, end);
conditions.push(
  `CAST(${col} AS DATE)
  BETWEEN
  CAST($${params.length-1} AS DATE)
  AND
  CAST($${params.length} AS DATE)`
);

Reusable date range helper that appends to the params array — no string concatenation, no injection risk.

04 · Dynamic Filter System

buildScopeFilters() — parameterized WHERE, never concatenated.

Every analytics endpoint accepts up to 5 scope filters. Rather than building WHERE clauses with string interpolation, buildScopeFilters() collects conditions and params separately, then the caller appends them to the query safely.

Frontend filters
city: "Jakarta"
cinema_id: 3
studio_id: null
movie_id: 12
payment_type: null
→
buildScopeFilters()
alias-aware · startIndex param
→
Output
conditions: ["c.city=$1", "st.cinema_id=$2", "s.movie_id=$3"]
params: ["Jakarta", 3, 12]
Parameterized — no SQL injection
Null-safe — skips undefined filters
Alias-aware — works across table joins
function buildScopeFilters(
  { city, cinema_id, studio_id, movie_id, payment_type } = {},
  aliases = { cinema: "c", studio: "st", schedule: "s", ticket: "t" },
  startIndex = 1
) {
  const params = [], conditions = [];
  const ph = () => `$${startIndex + params.length - 1}`;

  if (city)         { params.push(city);         conditions.push(`${aliases.cinema}.city = ${ph()}`); }
  if (cinema_id)    { params.push(cinema_id);    conditions.push(`${aliases.studio}.cinema_id = ${ph()}`); }
  if (studio_id)    { params.push(studio_id);    conditions.push(`${aliases.schedule}.studio_id = ${ph()}`); }
  if (movie_id)     { params.push(movie_id);     conditions.push(`${aliases.schedule}.movie_id = ${ph()}`); }
  if (payment_type) { params.push(payment_type); conditions.push(`${aliases.ticket}.payment_type = ${ph()}`); }

  return { params, conditions };
}
05 · Redis Cache

Same filter combination, never hits the database twice.

Analytics queries join 4–5 tables with aggregations. Redis wraps every endpoint with a deterministic, filter-aware cache key — sorted JSON serialization ensures the same params always produce the same key.

Incoming request
with filter params
↓ buildCacheKey()
Redis
client.get(key)
↓
HIT
Return cached
JSON.parse(cached)
MISS
PostgreSQL
CTE query
↓
Store
SET key · EX ttl
Cache key anatomy
api:v1:
+
dashboard-sales-revenue-by-cinema
+
{"city":"Jakarta","movie_id":12}
↓ sortValue() — keys always sorted
deterministic JSON → consistent key

Redis unavailability is handled via AsyncLocalStorage context — the system marks Redis unavailable and sets cache status to BYPASS, falling back to direct DB queries without error propagation to the client.

06 · Performance Optimization

EXPLAIN ANALYZE first. Then optimize.

The revenue-by-city query was the P1 bottleneck. Running EXPLAIN ANALYZE showed sequential scans on the two largest tables — not where I expected. Three composite indexes eliminated them.

Before
Seq Scan on tiket
Seq Scan on schedules
~420
ms (estimated)
Full table scan on every analytics request. Worsens linearly as ticket volume grows.
After
Index Scan on tiket(schedule_id)
Index Range Scan on schedules(show_date, studio_id)
~35
ms (estimated)
12× faster. Index-based lookup stays fast as data grows — cardinality advantage maintained.

Three indexes added

Index 1
tiket(schedule_id)
Eliminated the sequential scan on the largest table in every join chain. Critical — all analytics queries join tiket via schedule_id.
Index 2
schedules(show_date, studio_id)
Composite index for date-range filters combined with studio lookup — covers the most common filter combination.
Index 3
schedules(show_date, movie_id)
Separate composite for movie-filtered queries. Different selectivity from studio index — separate index gives better plan.
07 · LLM Insight Generation

Analytics data becomes business language.

The LLM component receives aggregated analytics — revenue gaps, occupancy trends, time-slot performance — and returns structured JSON recommendations. Cinema managers get insight without reading dashboards.

Context injected into prompt
date_range: last 14 days (rolling)
scope filters: city · cinema · studio
sales_overview: revenue · tickets · growth%
time_slots: revenue & demand per hour
top_movies: performance ranking
studio_inventory: capacity utilization
Output format enforced
{ recommended_actions: [{ action_type, priority, title, description, reason }] }

Insights are cached with upsertAiInsightRecord() — the LLM is called at most once per filter scope per TTL window, not on every page load.

08 · API Endpoint Overview

20+ analytics endpoints, organized by domain.

9
Sales & Revenue
GET/dashboard/executive
GET/dashboard/sales/overview
GET/dashboard/sales/revenue-by-city
GET/dashboard/sales/revenue-by-cinema
GET/dashboard/sales/revenue-by-studio
GET/dashboard/sales/time-slots
GET/dashboard/sales/trend
GET/dashboard/sales/payment
GET/dashboard/sales/pricing
6
Films & Occupancy
GET/dashboard/films/overview
GET/dashboard/films/performance
GET/dashboard/films/schedules
GET/dashboard/films/occupancy
GET/dashboard/films/distribution
GET/dashboard/films/cannibalization
5
Stats & Forecasting
GET/stats/summary
GET/stats/trends
GET/stats/occupancy
GET/stats/movie
GET/stats/forecast
4
System & AI
GET/dashboard/best-ad-slots
GET/dashboard/notifications
GET/ai-insights/latest
POST/ai-insights/generate
09 · Lessons Learned

What backend engineering teaches you about data.

SQL decisions upstream change what analysis is possible downstream. The two-layer CTE wasn't clever engineering — it was the only correct answer given the schema.

Wrong aggregation grain

Averaging ticket counts at city level gives the wrong occupancy figure. You must average at schedule level, then roll up — a two-layer CTE is the correct solution. This is a common analytical SQL trap.

SQL injection from filters

String-concatenated WHERE clauses open injection risk. The pattern is always: collect conditions and params separately, join conditions with AND, pass params to the driver — never interpolate values into SQL strings.

EXPLAIN before you optimize

I guessed wrong about the bottleneck. EXPLAIN ANALYZE on the actual slow query showed the sequential scan on tiket — not where I expected. Measure first, optimize second.

Backend exposure for AI engineers

Understanding how APIs shape data — what's cached, computed on request, pre-aggregated — directly informs how I build ML pipelines. Data doesn't exist in isolation from the system that serves it.

Links

Backend repo, frontend, and OpenAPI docs.

CineTrack was built as a team capstone during the Korea-ASEAN Digital Academy (KADA) program. The backend is a team repository. Full API documentation available via OpenAPI/Swagger at /docs.

↗ Backend Repo Frontend Repo ← Back to portfolio