Planning the Bulletin Data Model

Before writing a single route, a good API developer designs the data model. The schema determines what queries are possible and how fast they run.

What Bulletin needs to store

Working backward from the features:

• Users register and log in → users table
• Users create posts → posts table with a user_id foreign key
• Users comment on posts → comments table with user_id and post_id
• Users upvote posts (once per post) → post_upvotes junction table

The complete schema

-- Users
CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  username      TEXT    NOT NULL UNIQUE,
  password_hash TEXT    NOT NULL,
  bio           TEXT,
  created_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- Posts
CREATE TABLE IF NOT EXISTS posts (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  title       TEXT    NOT NULL CHECK(length(title) BETWEEN 3 AND 200),
  body        TEXT    NOT NULL CHECK(length(body) > 0),
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  upvotes     INTEGER NOT NULL DEFAULT 0 CHECK(upvotes >= 0),
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- Comments
CREATE TABLE IF NOT EXISTS comments (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  body        TEXT    NOT NULL CHECK(length(body) > 0),
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- Upvote tracking (prevents duplicate upvotes)
CREATE TABLE IF NOT EXISTS post_upvotes (
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, post_id)
);

-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_posts_user_id ON posts(user_id);
CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_comments_post_id ON comments(post_id);

Design decisions explained

CHECK constraints enforce data quality at the database level — not just application logic:

• Post titles must be 3–200 characters
• Bodies can’t be empty
• Upvotes can’t go negative

post_upvotes junction table with a composite primary key:

• PRIMARY KEY (user_id, post_id) means each (user, post) pair can only appear once
• The database itself prevents duplicate upvotes — no application code needed

Indexes on frequently-queried columns:

• idx_posts_user_id — fast lookup of a user’s posts
• idx_posts_created_at — fast sorting by date
• idx_comments_post_id — fast lookup of a post’s comments

ON DELETE CASCADE means deleting a user automatically deletes their posts, comments, and upvotes — no orphaned data.

The API surface this enables

GET  /posts                    → paginated post feed
GET  /posts/:id                → single post with author info
POST /posts                    → create post (auth required)
DELETE /posts/:id              → delete post (auth, must own)
POST /posts/:id/upvote         → toggle upvote (auth required)
GET  /posts/:id/comments       → comments for a post
POST /posts/:id/comments       → add comment (auth required)
GET  /users/:id                → user profile with post count
POST /auth/register            → create account
POST /auth/login               → get token

Exercise

Review the schema and answer:

1. What happens to a user’s posts when you delete the user?
2. How does the schema prevent a user from upvoting the same post twice?
3. Why is upvotes stored directly on the posts table instead of calculated with COUNT(*) FROM post_upvotes?
4. What would you add if Bulletin needed comment replies?

Recap

• Design the schema before writing routes — the data model shapes everything.
• CHECK constraints enforce data rules at the database level.
• Composite primary keys in junction tables prevent duplicates natively.
• Indexes on frequently-queried columns make reads fast.
• ON DELETE CASCADE keeps the database clean when related records are deleted.