Database Schema Designer

-- Schema generated by DB Schema Designer
-- Dialect: PostgreSQL
-- Generated: 2026-08-04T11:46:58.166Z

CREATE TABLE IF NOT EXISTS "users" (
  "id" SERIAL PRIMARY KEY,
  "username" VARCHAR(100) NOT NULL UNIQUE,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS "posts" (
  "id" SERIAL PRIMARY KEY,
  "user_id" INTEGER NOT NULL,
  "title" VARCHAR(255) NOT NULL,
  "body" TEXT,
  "published" BOOLEAN DEFAULT false,
  FOREIGN KEY ("user_id") REFERENCES "users"("id")
);

CREATE TABLE IF NOT EXISTS "tags" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(50) NOT NULL UNIQUE
);

-- Junction table for N-N: posts <-> tags
CREATE TABLE IF NOT EXISTS "posts_tags" (
  "posts_id" INTEGER NOT NULL,
  "tags_id" INTEGER NOT NULL,
  PRIMARY KEY ("posts_id", "tags_id"),
  FOREIGN KEY ("posts_id") REFERENCES "posts"("id"),
  FOREIGN KEY ("tags_id") REFERENCES "tags"("id")
);