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.