Blog App — Developer Documentation

Overview

This internal documentation page contains the HTML docs plus the full OpenAPI spec, SQL migrations for MySQL, and a starter repo scaffold for backend (Node/Express) and frontend (React + Tailwind/Bootstrap).

Quickstart

Clone the repository (see scaffold section).
Run DB migrations from migrations/ against a MySQL server with utf8mb4 charset.
Configure environment variables in backend: .env (DB, S3 keys, JWT secret, Redis).
Start backend: npm run dev. Start frontend: npm start in client dir.

OpenAPI (Swagger) — `openapi.yaml`

The full OpenAPI YAML is included below. Import into Swagger UI or Postman for interactive docs.


# See openapi.yaml in this file for the full spec (import to Swagger UI)

DB Migrations

Use the SQL DDL provided in the migrations/ block. Run each file in order. Ensure `utf8mb4` charset.


-- See migrations.sql block below

Repository Scaffold & Boilerplate

Recommended repo layout and essential starter files. Expand with tests, CI, and Dockerfiles.


See 'Repo scaffold' block below for full tree & starter code snippets.

Contacts

For questions about the spec, migration ordering, or deployment, contact the product owner or lead developer listed in the project management tool.

Appendix — Raw files (OpenAPI, Migrations, Scaffold)

openapi.yaml


# OPENAPI YAML START
openapi: 3.0.1
info:
  title: Blog App API
  version: '1.0.0'
  description: API for Blog Web Application
servers:
  - url: https://api.example.com/api/v1
paths:
  /auth/register:
    post:
      tags: [Auth]
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                name:
                  type: string
                email:
                  type: string
                password:
                  type: string
      responses:
        '200':
          description: Registered
  /auth/login:
    post:
      tags: [Auth]
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                email: { type: string }
                password: { type: string }
      responses:
        '200':
          description: Logged in
  /auth/refresh:
    post:
      tags: [Auth]
      responses: { '200': { description: 'Refreshed' } }
  /users/me:
    get:
      tags: [Users]
      security:
        - bearerAuth: []
      responses: { '200': { description: 'User profile' } }
  /posts:
    get:
      tags: [Posts]
      parameters:
        - in: query
          name: page
          schema: { type: integer }
        - in: query
          name: limit
          schema: { type: integer }
      responses: { '200': { description: 'List posts' } }
    post:
      tags: [Posts]
      security: [ { bearerAuth: [] } ]
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                title: { type: string }
                slug: { type: string }
                content: { type: string }
                language: { type: string }
                status: { type: string }
      responses: { '201': { description: 'Created' } }
  /posts/{id}:
    get:
      tags: [Posts]
      parameters:
        - in: path
          name: id
          required: true
          schema: { type: integer }
      responses: { '200': { description: 'Single post' } }
    put:
      tags: [Posts]
      security: [ { bearerAuth: [] } ]
      parameters:
        - in: path
          name: id
          required: true
          schema: { type: integer }
      requestBody:
        content:
          application/json:
            schema:
              type: object
      responses: { '200': { description: 'Updated' } }
    delete:
      tags: [Posts]
      security: [ { bearerAuth: [] } ]
      responses: { '204': { description: 'Deleted' } }
components:
  securitySchemes:
    bearerAuth:
      type: http
      scheme: bearer
      bearerFormat: JWT
# OPENAPI YAML END

migrations.sql


-- MIGRATIONS START
-- Charset and engine defaults
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

-- roles
CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- users
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role_id INT NOT NULL DEFAULT 4,
  name VARCHAR(150),
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255),
  avatar_url VARCHAR(512),
  bio TEXT,
  is_verified TINYINT(1) DEFAULT 0,
  rewards_balance DECIMAL(10,2) DEFAULT 0.00,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- posts
CREATE TABLE IF NOT EXISTS posts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  author_id INT NOT NULL,
  post_type VARCHAR(50) NOT NULL DEFAULT 'article',
  status ENUM('draft','pending','scheduled','published','archived') NOT NULL DEFAULT 'draft',
  slug VARCHAR(255) NOT NULL,
  language CHAR(8) DEFAULT 'en',
  featured TINYINT(1) DEFAULT 0,
  featured_image_id BIGINT DEFAULT NULL,
  excerpt TEXT,
  seo_title VARCHAR(255),
  seo_description VARCHAR(512),
  published_at DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  views BIGINT DEFAULT 0,
  allow_comments TINYINT(1) DEFAULT 1,
  CONSTRAINT ux_posts_slug_lang UNIQUE (slug, language),
  INDEX idx_posts_status_published (status, published_at),
  INDEX idx_posts_author (author_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- post_translations
CREATE TABLE IF NOT EXISTS post_translations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  post_id BIGINT NOT NULL,
  language CHAR(8) NOT NULL,
  title VARCHAR(255),
  content LONGTEXT,
  table_of_contents TEXT,
  meta_json JSON,
  UNIQUE KEY uq_post_lang (post_id, language),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- categories
CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(255) NOT NULL UNIQUE,
  parent_id INT DEFAULT NULL,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS category_translations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  language CHAR(8) NOT NULL,
  title VARCHAR(255),
  description TEXT,
  UNIQUE KEY uq_cat_lang (category_id, language),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- post_categories
CREATE TABLE IF NOT EXISTS post_categories (
  post_id BIGINT NOT NULL,
  category_id INT NOT NULL,
  PRIMARY KEY (post_id, category_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- tags
CREATE TABLE IF NOT EXISTS tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  slug VARCHAR(150) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS post_tags (
  post_id BIGINT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- media
CREATE TABLE IF NOT EXISTS media (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  s3_key VARCHAR(1024) NOT NULL,
  url VARCHAR(1024) NOT NULL,
  mime VARCHAR(100),
  size BIGINT,
  width INT,
  height INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- post_images
CREATE TABLE IF NOT EXISTS post_images (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  post_id BIGINT NOT NULL,
  media_id BIGINT NOT NULL,
  caption VARCHAR(512),
  sort_order INT DEFAULT 0,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- post_files
CREATE TABLE IF NOT EXISTS post_files (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  post_id BIGINT NOT NULL,
  media_id BIGINT NOT NULL,
  label VARCHAR(255),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- comments
CREATE TABLE IF NOT EXISTS comments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  post_id BIGINT NOT NULL,
  user_id INT DEFAULT NULL,
  parent_id BIGINT DEFAULT NULL,
  content TEXT NOT NULL,
  status ENUM('approved','pending','spam','deleted') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE SET NULL,
  INDEX idx_comments_post_status (post_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- reactions
CREATE TABLE IF NOT EXISTS reactions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  post_id BIGINT NOT NULL,
  user_id INT NOT NULL,
  emoji VARCHAR(50) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reaction (post_id, user_id, emoji),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- reading_list
CREATE TABLE IF NOT EXISTS reading_list (
  user_id INT NOT NULL,
  post_id BIGINT NOT NULL,
  added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, post_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- follows
CREATE TABLE IF NOT EXISTS follows (
  follower_id INT NOT NULL,
  following_id INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, following_id),
  FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- polls
CREATE TABLE IF NOT EXISTS polls (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  post_id BIGINT DEFAULT NULL,
  created_by INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS poll_options (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  poll_id BIGINT NOT NULL,
  label VARCHAR(255),
  sort_order INT DEFAULT 0,
  FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS poll_votes (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  poll_id BIGINT NOT NULL,
  option_id BIGINT NOT NULL,
  user_id INT DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE,
  FOREIGN KEY (option_id) REFERENCES poll_options(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- subscribers
CREATE TABLE IF NOT EXISTS subscribers (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(150),
  is_confirmed TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- settings
CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(191) PRIMARY KEY,
  `value` TEXT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS=1;
-- MIGRATIONS END

Repo scaffold & boilerplate


# Project root
README.md
backend/
  package.json
  src/
    server.js
    routes/
      auth.js
      posts.js
    controllers/
      authController.js
      postsController.js
    models/
      index.js  -- knex or sequelize setup
    services/
      s3.js
      ai.js
    config/
      default.js
  migrations/  <-- use the migrations.sql earlier or split by files
  .env.example

client/
  package.json
  src/
    App.jsx
    index.jsx
    pages/
      Home.jsx
      Post.jsx
    components/
      Header.jsx
      Footer.jsx
      PostCard.jsx
  tailwind.config.js

# backend/package.json (snippet)
{
  "name": "blog-backend",
  "version": "1.0.0",
  "main": "src/server.js",
  "scripts": {
    "dev": "nodemon src/server.js",
    "start": "node src/server.js",
    "migrate": "mysql -u $DB_USER -p$DB_PASS $DB_NAME < migrations.sql"
  },
  "dependencies": {
    "express": "^4.18.2",
    "mysql2": "^3.3.0",
    "jsonwebtoken": "^9.0.0",
    "bcrypt": "^5.1.0",
    "aws-sdk": "^2.1363.0",
    "helmet": "^7.0.0",
    "cors": "^2.8.5",
    "i18next": "^23.0.0",
    "bullmq": "^1.81.0",
    "redis": "^4.6.5"
  }
}

# backend/src/server.js (minimal)
const express = require('express');
const helmet = require('helmet');
const cors = require('cors');
const authRoutes = require('./routes/auth');
const postsRoutes = require('./routes/posts');
require('dotenv').config();

const app = express();
app.use(helmet());
app.use(cors());
app.use(express.json({limit: '10mb'}));

app.use('/api/v1/auth', authRoutes);
app.use('/api/v1/posts', postsRoutes);

app.get('/', (req, res) => res.send('Blog API'));

const PORT = process.env.PORT || 4000;
app.listen(PORT, () => console.log(`Server running on ${PORT}`));

# client/package.json (snippet)
{
  "name": "blog-client",
  "private": true,
  "dependencies": {
    "react": "^18.2.0",
    "react-dom": "^18.2.0",
    "axios": "^1.4.0",
    "react-router-dom": "^6.14.2",
    "tailwindcss": "^3.5.0",
    "bootstrap": "^5.3.2"
  },
  "scripts": {
    "start": "vite"
  }
}

# client/src/App.jsx (minimal)
import React from 'react';
import { BrowserRouter, Routes, Route } from 'react-router-dom';
import Home from './pages/Home';
import Post from './pages/Post';

export default function App(){
  return (
    
      
        } />
        } />
      
    
  );
}

# END of scaffold block

Contents