codeseed
Sign in
Explore Projects
SQLintermediatewebAI generated

Blog Platform with Tags, Comments, and Full-Text Search

Model a blogging platform with posts, authors, tags, and threaded comments, then implement full-text search over post content and queries for the most-discussed posts. Learners practice many-to-many tagging, self-referencing comment threads, and PostgreSQL full-text search.

Estimate
~10.5h
Steps
6
Completed by
0
Proposed by
codeseed.app

PostgreSQL · pgAdmin · tsvector

Sign in to claim this project

Project roadmap

  1. 01

    Design the schema

    ~2h

    Create posts, authors, tags, a post_tags junction table, and a self-referencing comments table.

  2. 02

    Insert sample data

    ~1.5h

    Add posts with tags and threaded comment replies.

  3. 03

    Set up full-text search

    ~2h

    Add a tsvector column and a GIN index over post title and body.

  4. 04

    Write a ranked search query

    ~1.5h

    Write a full-text search query ranked by relevance using ts_rank.

  5. 05

    Render comment threads

    ~2h

    Write a recursive CTE that reconstructs threaded comments in reply order.

  6. 06

    Report most-commented posts per tag

    ~1.5h

    Write a query ranking posts by comment count grouped by tag.

Resources

Ready to build this?

Get a GitHub repo and start building. Your AI reviewer checks each step as you go.

~10.5h · 6 steps

Tech stack

PostgreSQLpgAdmintsvector