DEV Community: Tommy

World Cup Data Pipeline

Tommy — Fri, 17 Jul 2026 06:44:17 +0000

How I Built a CSV-to-Postgres Data Pipeline for World Cup Player Stats

The Problem

With excited of watching the World Cup for the past month, I wanted to my own project that would show the stats of all players that have played in the tournament.

One of the major issues that I ran into was that the CSV file I had wasn't really one dataset. It flattened together two different kinds of records. Outfield players have fields like goals, assists, and yellow or red cards. Goalkeepers have saves, clean sheets, and goals conceded instead. Representing both in a single table would mean filling half the columns with meaningless NULL values depending on the player's position.

I wanted something closer to a real data pipeline than a spreadsheet import. The goal was to read the raw CSV, validate it, transform each row into the shape it actually belonged to, store it in a normalized Postgres schema, and expose it through an API instead of treating the CSV as the application's source of truth.

The project deliberately focused on the unglamorous middle of backend engineering: ingesting data, transforming it correctly, storing it efficiently, and serving it reliably.

Schema Design Decisions

The first design decision happened during the transform step. Instead of forcing every record into the same schema, the pipeline checks position === "GK" and routes each row into either a player shape or a goalkeeper shape. Each shape lands in its own normalized Postgres table, which avoids maintaining a table full of columns that only apply to half the records.

The next challenge came from Postgres itself. It has a hard limit of 65,535 bound parameters per query, which means a single giant INSERT eventually stops working as the dataset grows. Rather than inserting everything at once, the pipeline chunks writes into batches: 200 players or 30 goalkeepers per INSERT. Each batch generates its own $1..$N placeholder sequence, keeping every query comfortably below the parameter limit regardless of how large the CSV becomes.

I also wanted the pipeline to tolerate partial failures instead of treating every error as fatal. Player and goalkeeper batches execute through Promise.allSettled, so a failed goalkeeper batch doesn't prevent successful player batches from committing. Within each batch, the pipeline continues attempting later chunks even if an earlier one fails, then reports exactly how many rows were inserted compared to how many were attempted.

That design became even more important once I considered running the pipeline multiple times against refreshed data. After verifying that (name, country) produced zero collisions across all 1,247 rows, I added a unique constraint through a Knex migration and changed inserts to ON CONFLICT (name, country) DO UPDATE. Re-running the pipeline now updates existing rows instead of creating duplicates.

API Architecture

The serving layer is intentionally small. Everything flows through a single Express 5 endpoint: GET /players.

Requests first pass through rate limiting, which allows 100 requests every 15 minutes. After that, the endpoint uses a cache-aside strategy backed by Redis with a one-hour TTL. The first request reads from Postgres and populates Redis. Every subsequent request during that hour reads directly from the cache instead of hitting the database again.

The part that required the most thought wasn't caching itself—it was making sure the cache couldn't become a single point of failure.

try {
  // Redis cache operations
} catch (error) {
  // Fall back to Postgres
}

Redis operations are isolated inside their own try/catch, separate from the Postgres query. If Redis becomes unavailable, the endpoint doesn't return a 500 error. Instead, it skips the cache, serves data directly from Postgres, logs the Redis failure, and continues responding normally. Losing the cache should never mean losing the API.

That same philosophy of graceful degradation shows up elsewhere in the project. The ingestion pipeline is designed to continue processing remaining chunks after individual failures, and the API is designed to continue serving data when an infrastructure component disappears. Both decisions favor availability over an all-or-nothing approach.

Keeping the serving layer intentionally small also helped keep responsibilities clear. The pipeline owns reading, validating, transforming, and storing the data. The API owns exposing that data efficiently, with Redis acting as a performance optimization instead of becoming a dependency the application cannot function without.

The Debugging Rabbit Hole

The bug that taught me the most wasn't dramatic. Nothing crashed. Nothing printed an error.

After refactoring row-by-row inserts into batch inserts, I built the values array, generated the placeholder groups using the correct offset math (base = index * columns.length), and saw success messages in the console. The pipeline reported that player data had been stored successfully, and there were no visible failures.

Except the database hadn't changed.

I compared row counts before and after running the entire pipeline, and they were identical. The SQL was built correctly. The values array was correct. The placeholder math was correct.

The problem was almost embarrassingly simple: after constructing the query, I never actually called pool.query(). The function finished immediately after building the .map(). Nothing threw an exception because nothing that could have thrown was ever executed.

The lesson that stuck is that logs only prove your function returned—they don't prove your data changed.

If you're writing a data pipeline, verify the state of the database itself. Check row counts. Run a SELECT. Trust what the database confirms, not what your console prints.

Outcome

The finished project is deployed on Render, backed by Supabase Postgres and a Redis cache. It includes a test suite of 19 Jest and Supertest tests running against an isolated Postgres test schema so production data is never touched. The suite covers real constraint failures, per-chunk failure behavior, and the ON CONFLICT upsert path.

The pipeline is now operational. Running it against a refreshed CSV updates existing records instead of inserting duplicates, making repeated imports a normal workflow rather than something to avoid.

You can try the live project here:

This became Portfolio Project #2 on my self-taught software engineering roadmap. The goal wasn't to build another CRUD application—it was to build something with a completely different shape: ingest, transform, chunked storage, caching, validation, and graceful degradation working together as a data pipeline. That difference is exactly what satisfies the Week 16 milestone of shipping two backend projects that tell different technical stories.

More than anything else, this project reinforced one idea I'll carry into every future backend system: when software writes data, the database—not the logs—is the ground truth.

Twitter Bookmark Organizer

Tommy — Sun, 28 Jun 2026 19:01:14 +0000

How I Built a Secure REST API to Organize My Twitter Bookmarks

The Problem

My Twitter bookmark list was a graveyard. Hundreds of saved tweets — digital art, career advice, ai news — with no way to find anything. Twitter's native bookmarks have zero organization. I needed a way to save a URL, tag it, and pull it back up later. Currently, Twitter does have a folder system, but it is locked behind a pay wall.

So I built one. A backend REST API with exactly three features: store a bookmark, tag it, and filter by tag. No Chrome extension, no AI auto-tagging, no slick UI. Just a backend I could actually ship.

Schema Design Decisions

The database has two tables: users and bookmarks. The decisions behind them were deliberate.

On the users table, username is UNIQUE NOT NULL enforced at the database level — not just in the app. App-level checks can have race conditions (two requests land at the same millisecond; both pass the check; both try to insert). A UNIQUE constraint at the DB level makes that a hard stop, not a maybe.

On the bookmarks table, user_id is a foreign key with ON DELETE CASCADE. Every bookmark is owned by exactly one user. If that user is deleted, their bookmarks go with them — automatically, without needing a separate cleanup query. No orphaned rows left rotting in the database.

I also separated my two database tools intentionally. Knex handles migrations — versioned, rollbackable schema changes. pg.Pool handles all runtime queries. They look interchangeable at first glance, but they're not. Knex is for evolving the schema; the pool is for talking to it day-to-day. Mixing them causes subtle bugs that are hard to trace.

API Architecture

Five routes: POST /register, POST /login, POST /storeBookmark, GET /filterBookmarks, and GET /bookmarks. The auth and bookmark endpoints are kept separate on purpose — different middleware applies to each.

Protected routes follow a consistent middleware chain:

rateLimiter → authenticateUser → validateBookmark → handler

The order is intentional. Rate limiting runs first because it's the cheapest thing to reject — no DB call, no token verification, just check the IP and bail. Auth runs next. Validation runs last, right before the handler that does real work.

Every catch block in every route calls next(error) — never res.json(). This ensures all errors, no matter where they originate, flow through a single centralized error handler. That handler uses a custom AppError class hierarchy (AuthError, ValidationError, ConflictError) to map errors to the right HTTP status. And critically: any unrecognized 500-level error gets its internal message stripped. Clients only ever see "Internal Server Error" — never a stack trace, never a SQL query, never a file path.

The Debugging Rabbit Hole

I spent hours staring at ECONNREFUSED errors on my database connection. I checked my Supabase credentials. I regenerated the connection string. I restarted the server repeatedly. The credentials looked right in my .env file. I would go back and forth from one project to the current one just see if I typed something wrong. Nothing worked.

The fix was one line I had forgotten: dotenv.config().

dotenv reads your .env file and loads the values into process.env. But if you never call it, process.env.PG_CONNECTION_STRING is undefined when the app starts — and PostgreSQL tries to connect to nothing. The error looks exactly like a database problem. It isn't. It's an environment problem masquerading as one.

The lesson I took: when you see ECONNREFUSED, log process.env.PG_CONNECTION_STRING before you touch anything else. If it's undefined, stop — the bug isn't in your database config, it's upstream. Also, dotenv.config() must be the very first call in your entry file. Any module loaded before it that reads process.env will get undefined, even if you add the call later.

Outcome

The app is a working REST API where authenticated users can store, tag, and retrieve bookmarks. Under the hood it ships six independent security layers: parameterized SQL queries (injection prevention), async bcrypt hashing (password security), stateless JWT auth with 15-minute expiry, two-tier rate limiting (stricter on auth routes to blunt brute-force attempts), input validation with express-validator, and BOLA prevention — every bookmark query is scoped to req.user.userId so no user can read another's data.

It also has a test suite: integration tests with Jest and Supertest against a real database, plus a mock-based suite that simulates database crashes to verify the error handler works without needing a real failure.

This project covered Weeks 9–12 of my SWE roadmap: building servers, designing database schemas, layering in security, and proving correctness with tests. More importantly, it taught me that the bugs that cost the most time are rarely in the code you're focused on — they're in the invisible infrastructure surrounding it.