实验报告：Hexo 评论系统

English README：Click Here

0.系统概述

为 Hexo 博客或者任意博客提供独立后台的评论、回复、点赞系统。前端以 comments.js 注入任意主题，后端使用 FastAPI + SQL Server 负责身份验证、评论树管理和管理员面板。
本实验以hexo为例，但实际上comments.js只是一个js模块，可以被植入到任何网页的任意部位。
项目地址：billma007/comments_mysql
主要特性：
- 用户注册/登录、评论/回复树形渲染、点赞开关；
- /admin 后台支持评论审核、软删除与用户浏览；
- REST API + CORS 设计，方便 Hexo 或其他静态站点调用；
- init_db.py 一键初始化数据库结构与默认管理员。

1. 环境

Windows 11 桌面环境。
Python 3.12.10（通过 pip install -r requirements.txt 安装 FastAPI、uvicorn、pyodbc、Jinja2 等包）。
本地 SQL Server（Developer / Express），使用 Microsoft ODBC Driver 17 与 Python 通讯。

2. 配置过程

（创建conda环境，然后） pip install -r requirements.txt
安装 ODBC Driver 17 并在 Control Panel -> ODBC Data Sources 中确认驱动名称与 pyodbc.drivers() 输出一致。
打开sql server 配置管理器，选择“SQL server网络配置”，找到“%数据库名字%的协议”，找到TCP/IP协议，在“协议”界面中将两个判断全部改为“是”，然后在“IP地址”标签页中拉到最下面“IPAII”，在TCP端口中填写和下面SQLSERVER_PORT相同的端口，一般为1433。确认后重启sql服务。

在 PowerShell 里设置连接环境变量：

setx SQLSERVER_DRIVER "{ODBC Driver 17 for SQL Server}"
setx SQLSERVER_SERVER "localhost"
setx SQLSERVER_PORT "1433"
setx SQLSERVER_DATABASE "HexoComments"
setx SQLSERVER_USERNAME "sa"
setx SQLSERVER_PASSWORD "YourStrong!Passw0rd"
setx SQLSERVER_ENCRYPT "no"
setx SQLSERVER_TRUST_CERT "yes"

运行 py init_db.py，脚本自动连接 master，创建目标数据库及 users / comments / comment_likes 表，并注入默认管理员。
使用 uvicorn backend.main:app --reload --host 0.0.0.0 --port 8000 启动服务，将 Hexo 模板中的评论脚本指向 http://服务器:8000/static/comments.js。

（可选）将nodejs下载并加入环境变量。在一个文件夹下进行如下指令：

1 2	npm install -g hexo-cli hexo init

然后将以下js：

<div id="hexo-comments-root" data-post-id="<%= page.permalink %>"></div>
<script>
  window.HEX0_COMMENTS_CONFIG = {
    apiBase: 'http://localhost:8000',
    postId: '<%= page.permalink %>'
  };
</script>
<script src="http://localhost:8000/static/comments.js"></script>

添加到文章内容渲染结束的位置。每个主题不同。
以默认的landscape为例子，在 themes/landscape/layout/_partial/article.ejs中的

1	<%- page.content %>

后。
额外地，如果您需要仅在文章页面开启评论，可以添加一个判断：

<% if (page.layout === 'post') { %>
  <div id="hexo-comments-root" data-post-id="<%= page.permalink %>"></div>
  <script>
    window.HEX0_COMMENTS_CONFIG = {
      apiBase: 'http://localhost:8000',
      postId: '<%= page.permalink %>'
    };
  </script>
  <script src="http://localhost:8000/static/comments.js"></script>
<% } %>

如果你的IP和port在上面启动服务时进行过更改，则需要对应改变。

编写文章。随后打开blog根目录的terminal，输入：
1
2
3
hexo clean
hexo generate
hexo server
就可以在http://localhost:4000 启动博客，并且在本地运行comment.js
如果按照上述配置，comments.js会在http://localhost:8000/static/comments.js 中。admin后台则在 http://localhost:8000/admin

3. 大体结构

backend/api：FastAPI 路由层（users/comments/admin）。
backend/services：业务逻辑与权限判定。
backend/db：数据库访问封装（pyodbc）。
backend/templates：管理后台 admin.html。
backend/static：Hexo 嵌入脚本 comments.js。
backend/config.py：集中配置。
backend/main.py：FastAPI 启动入口与静态/模板挂载。
init_db.py：数据库初始化脚本；requirements.txt：依赖清单。
Hexo 前端只负责加载 JS 并调用 REST，Python 控制全部逻辑，SQL Server 仅保存数据。

4. 重要运算函数与通信函数

数据库通信封装：backend/db/database.py 通过 _build_connection_string() 组合连接串，get_connection() 统一管理事务；上层只需调用 execute/fetch_one/fetch_all 即可与 SQL Server 交互，这些函数是整个系统的 I/O 通道。
会话管理：backend/services/auth_service.py 内的 SessionManager.issue_token() 为登录用户签发内存 token，require_session()/require_admin_session() 则在 API 层被 FastAPI 依赖调用，用于校验权限。
评论树构建：backend/services/comment_service.py 的 _build_tree() 将 _fetch_rows() 得到的平面数据重建为嵌套结构，并附加 like_count、liked_by_viewer，供前端直接渲染；toggle_like() 负责在 comment_likes 中增删记录。
前端交互：backend/static/comments.js 中的 loadComments()、handleAuth()、handleLike()、handleReply() 采用 fetch 向 FastAPI 发送 JSON 请求，使用 localStorage 保存 token，实现登录、发表评论、回复和点赞。

5. 直接发送 SQL 指令的函数

user_service
- get_user_by_username
  - 语句：SELECT id, username, role, created_at, password FROM users WHERE username = ?
  - 作用：按用户名读取完整用户记录，用于登录校验与注册查重。
- create_user
  - 语句 1：SELECT id FROM users WHERE username = ?
  - 语句 2：INSERT INTO users (username, password, role) VALUES (?, ?, ?)
  - 作用：先检测用户名是否存在，再插入新账号。
- list_all_users
  - 语句：SELECT id, username, role, created_at FROM users ORDER BY created_at DESC
  - 作用：后台管理员查看全部用户列表。
comment_service
- _fetch_rows
  - 语句：SELECT c.id, c.post_id, c.user_id, u.username, c.content, c.created_at, c.is_deleted, c.parent_comment_id, ISNULL(l.like_count, 0) AS like_count FROM comments c INNER JOIN users u ON u.id = c.user_id LEFT JOIN (SELECT comment_id, COUNT(*) AS like_count FROM comment_likes GROUP BY comment_id) l ON l.comment_id = c.id WHERE ... ORDER BY c.created_at DESC
  - 作用：获取评论树所需的平面数据，包含作者、内容、状态、点赞数。
- _fetch_liked_ids
  - 语句：SELECT comment_id FROM comment_likes WHERE user_id = ? AND comment_id IN (...)
  - 作用：一次性查询当前用户已点赞的评论，用于前端“已点赞”标记。
- add_comment
  - 语句 1：SELECT id, post_id, is_deleted FROM comments WHERE id = ?
  - 语句 2：INSERT INTO comments (post_id, user_id, content, parent_comment_id) VALUES (?, ?, ?, ?)
  - 语句 3：SELECT TOP 1 c.id, c.post_id, c.user_id, u.username, c.content, c.created_at, c.is_deleted, c.parent_comment_id, 0 AS like_count FROM comments c INNER JOIN users u ON u.id = c.user_id WHERE c.user_id = ? ORDER BY c.created_at DESC
  - 作用：验证父评论、写入新内容并取回最新记录。
- soft_delete_comment
  - 语句：UPDATE comments SET is_deleted = 1 WHERE id = ?
  - 作用：在数据库中将评论标记为删除。
- toggle_like
  - 语句 1：SELECT id, is_deleted FROM comments WHERE id = ?
  - 语句 2：SELECT id FROM comment_likes WHERE comment_id = ? AND user_id = ?
  - 语句 3：INSERT INTO comment_likes (comment_id, user_id) VALUES (?, ?)
  - 语句 4：DELETE FROM comment_likes WHERE id = ?
  - 语句 5：SELECT COUNT(*) AS cnt FROM comment_likes WHERE comment_id = ?
  - 作用：判断评论是否有效、切换点赞状态，并返回当前点赞数。

6. 数据处理流程示例

场景 1：页面加载显示现有评论

浏览器打开文章后，comments.js 会：
- 从 localStorage 读 token；
- 构造 GET /api/comments?post_id=<slug> 请求，若有 token 则加上 Authorization: Bearer ...；
- 调用 fetch() 并等待 JSON 响应。

FastAPI comments_router.list_comments() 收到请求：

通过 require_session()（若带 token）解析用户 ID；

调用 comment_service._fetch_rows()，该函数利用 database.fetch_all() 发送带参数的 SQL：

SELECT c.id, c.post_id, c.user_id, u.username, c.content, c.created_at,
       c.is_deleted, c.parent_comment_id,
       ISNULL(l.like_count, 0) AS like_count
FROM comments c
INNER JOIN users u ON u.id = c.user_id
LEFT JOIN (
    SELECT comment_id, COUNT(*) AS like_count
    FROM comment_likes
    GROUP BY comment_id
) l ON l.comment_id = c.id
WHERE c.post_id = ? AND (c.is_deleted = 0 OR ? = 1)
ORDER BY c.created_at ASC;

pyodbc 将参数（post_id、是否管理员）绑定后发给 SQL Server，得到多行结果：

1
2
3

id=1 user1 comment1 like=2 parent=null
id=2 user2 comment2 like=0 parent=1
id=3 user1 comment3 like=1 parent=2

若用户已登录，_fetch_liked_ids() 还会发送 SELECT comment_id FROM comment_likes WHERE user_id = ? AND comment_id IN (?,?,?) 以判断“已赞”。

comment_service._build_tree() 在 Python 内部把平面结果拼成树，结构示例：

[
  {
    "id":1,"username":"user1","content":"comment1","like_count":2,
    "liked_by_viewer":false,
    "replies":[
      {
        "id":2,"username":"user2","content":"comment2","like_count":0,
        "liked_by_viewer":false,
        "replies":[
          {
            "id":3,"username":"user1","content":"comment3","like_count":1,
            "liked_by_viewer":true,"replies":[]
          }
        ]
      }
    ]
  }
]

FastAPI 将 JSON 返回给前端，renderComments() 递归生成 DOM，效果即示例层级。

场景 2：用户发表评论

用户在输入框填写内容点击“发布”：
- JS 取出 token、内容、post_id，可选 parent_comment_id；
- 调用 fetch('POST /api/comments', {body: JSON.stringify({...})})。

comments_router.create_comment()：

借助 require_session() 确认用户；

调用 comment_service.add_comment()，其内部 SQL 通信流程：

若 parent_comment_id 不为空，先执行
1
2
3
SELECT id, post_id, is_deleted
FROM comments
WHERE id = ?;
确保父评论属于同一文章且未删除。

通过 database.execute() 发送插入语句（pyodbc 用参数避免 SQL 注入）：

1 2	INSERT INTO comments (post_id, user_id, content, parent_comment_id) VALUES (?, ?, ?, ?);

用 database.fetch_one() 执行

SELECT TOP 1 c.id, c.post_id, c.user_id, u.username, c.content, c.created_at,
       c.is_deleted, c.parent_comment_id, 0 AS like_count
FROM comments c
INNER JOIN users u ON u.id = c.user_id
WHERE c.user_id = ?
ORDER BY c.created_at DESC;

读取刚插入的完整记录。

返回 JSON（含 id/username/content/like_count=0），浏览器将其插入评论列表，并选择性重新拉取整棵树保证排序一致。

场景 3：点赞与取消点赞
1. 点击“点赞”后前端调用 POST /api/comments/{comment_id}/like，请求头带 token。
2. comment_service.toggle_like() 的 SQL 往返：
  - SELECT id, is_deleted FROM comments WHERE id = ? 确认评论存在且未删除；
  - SELECT id FROM comment_likes WHERE comment_id = ? AND user_id = ? 判断当前状态；
  - 若无记录：执行 INSERT INTO comment_likes (comment_id, user_id) VALUES (?, ?)；
  - 若已点赞：执行 DELETE FROM comment_likes WHERE id = ?；
  - 最后 SELECT COUNT(*) AS cnt FROM comment_likes WHERE comment_id = ? 统计最新点赞数。
3. 服务端把 {"like_count":<n>,"liked":true/false} 返回，前端更新按钮和数字，同时在本地 state 中同步。
场景 4：管理员删除评论（示例 3）
1. 管理后台 /admin 中点击“删除”按钮会调用 DELETE /api/comments/{id} 并附带管理员 token。
2. comment_service.soft_delete_comment() 先 SELECT id FROM comments WHERE id = ? 校验，再执行
  1
  2
  3
  UPDATE comments
  SET is_deleted = 1
  WHERE id = ?;
  通过 pyodbc 提交事务。
3. 删除后，前端重新请求 GET /api/comments，因为 _fetch_rows() 的 WHERE 子句会过滤 is_deleted = 1，页面立刻看不到该评论，仅管理员在后台可见。

7.API overview

Endpoint	Method	Purpose
`/api/users/register`	POST	Register normal user
`/api/users/login`	POST	Login, receive token
`/api/comments`	GET	Public comments for a post
`/api/comments`	POST	Add comment (needs token)
`/api/admin/comments`	GET	Admin moderation feed
`/api/admin/delete_comment`	POST	Soft delete
`/api/admin/create`	POST	Create new admin

8. 其他必要信息

安全性取舍：为降低复杂度，密码以明文保存、token 存内存，没有 HTTPS/JWT/CSRF；后续可在 service 层增加哈希、在 API 层引入更严格认证。
测试方法：手动流程为：运行 py init_db.py → uvicorn backend.main:app --reload ... → 访问 /admin 以默认管理员登录 → 在 Hexo 页面使用 comments.js 测试注册/登录/评论/点赞。若需要自动化，可编写 pytest + httpx 调用 API 验证响应。
扩展方向：数据库层已支持 comment replies 与 likes，后续可拓展更多表（如举报、附件）。只需在 init_db.py 增加 ALTER 脚本并在 service 层调用数据库封装即可。

9.如果您需要在云端部署

建议python server和sql server在同一服务器
sql server建议和python进行本地通信。特殊地，在激活python server时，需要改成云端的公网IP，并修改port
在hexo这边进行js植入的时候，也需要将js中的IP地址改成公网IP
建议进行通信加密

English Version:

0. System Overview

Provides an independent backend for comments, threaded replies, and likes that can be embedded into any Hexo (or other static) blog. The front end injects comments.js into any theme; the backend uses FastAPI + SQL Server for authentication, comment tree management, and the admin panel.
Hexo is used as the reference blog engine, but comments.js is just a standalone script and can be mounted on any web page.
Repository: billma007/comments_mysql
Key Features:
- User registration/login, tree-style rendering for comments and replies, like toggle;
- /admin console for moderation, soft delete, and user browsing;
- REST API with CORS, so any static site can call it;
- init_db.py bootstraps schema and seeds a default admin account.

1. Environment

Windows 11 desktop.
Python 3.12.10 (install FastAPI, uvicorn, pyodbc, Jinja2, etc., via pip install -r requirements.txt).
Local SQL Server (Developer/Express) communicating with Python through Microsoft ODBC Driver 17.

2. Configuration Steps

(Optionally create a conda env, then) run pip install -r requirements.txt.
Install ODBC Driver 17 and ensure the driver name matches pyodbc.drivers() in Control Panel → ODBC Data Sources.
Open SQL Server Configuration Manager → SQL Server Network Configuration, pick the target instance, enable TCP/IP, then in the IP Addresses tab scroll to IPAll and set the TCP port to match SQLSERVER_PORT (1433 by default). Restart the SQL Server service afterwards.

Set connection environment variables in PowerShell:

setx SQLSERVER_DRIVER "{ODBC Driver 17 for SQL Server}"
setx SQLSERVER_SERVER "localhost"
setx SQLSERVER_PORT "1433"
setx SQLSERVER_DATABASE "HexoComments"
setx SQLSERVER_USERNAME "sa"
setx SQLSERVER_PASSWORD "YourStrong!Passw0rd"
setx SQLSERVER_ENCRYPT "no"
setx SQLSERVER_TRUST_CERT "yes"

Run py init_db.py. The script connects to master, creates the HexoComments database and the users / comments / comment_likes tables, and seeds the default admin.
Launch the backend with uvicorn backend.main:app --reload --host 0.0.0.0 --port 8000, and point your Hexo template to http://<server>:8000/static/comments.js.

(Optional) Install Node.js, add it to PATH, then inside any folder run:

1 2	npm install -g hexo-cli hexo init

Embed the following snippet at the end of every article template:

<div id="hexo-comments-root" data-post-id="<%= page.permalink %>"></div>
<script>
  window.HEX0_COMMENTS_CONFIG = {
    apiBase: 'http://localhost:8000',
    postId: '<%= page.permalink %>'
  };
</script>
<script src="http://localhost:8000/static/comments.js"></script>

For the default landscape theme, insert it right after <%- page.content %> in themes/landscape/layout/_partial/article.ejs.

To only enable comments for posts, wrap the block with:

<% if (page.layout === 'post') { %>
  <div id="hexo-comments-root" data-post-id="<%= page.permalink %>"></div>
  <script>
    window.HEX0_COMMENTS_CONFIG = {
      apiBase: 'http://localhost:8000',
      postId: '<%= page.permalink %>'
    };
  </script>
  <script src="http://localhost:8000/static/comments.js"></script>
<% } %>

Adjust the host/port if the backend is not running on localhost:8000.

Write blog posts, then in the Hexo root run:
1
2
3
hexo clean
hexo generate
hexo server
This starts the blog at http://localhost:4000 and loads comments.js locally.
With the above setup, comments.js is served from http://localhost:8000/static/comments.js, and the admin console lives at http://localhost:8000/admin.

3. Project Structure

backend/api: FastAPI routes (users, comments, admin).
backend/services: Business logic and permission checks.
backend/db: Database access helpers built on pyodbc.
backend/templates: Admin HTML (Jinja2).
backend/static: comments.js for Hexo injection.
backend/config.py: Centralized settings.
backend/main.py: FastAPI entry point plus static/template mounting.
init_db.py: Database initializer; requirements.txt: dependency list.
Hexo front end only loads JS and calls REST; Python handles all logic; SQL Server simply stores data.

4. Core Computation & Communication Functions

Database wrapper: backend/db/database.py builds the connection string via _build_connection_string() and exposes get_connection() plus execute / fetch_one / fetch_all. Higher layers only talk to SQL Server through these helpers.
Session management: backend/services/auth_service.py issues in-memory tokens with SessionManager.issue_token(). require_session() and require_admin_session() are FastAPI dependencies that enforce authentication and roles.
Comment tree assembly: backend/services/comment_service.py reconstructs nested replies in _build_tree() using the flat rows from _fetch_rows(), attaching like_count and liked_by_viewer. toggle_like() manipulates the comment_likes table.
Front-end interactions: backend/static/comments.js contains loadComments(), handleAuth(), handleLike(), and handleReply(), which call FastAPI endpoints via fetch, manage tokens in localStorage, and update the DOM.

5. Functions That Send Raw SQL

user_service:
- get_user_by_username
  - SQL: SELECT id, username, role, created_at, password FROM users WHERE username = ?
  - Use case: fetch full user info for login or duplicate checks.
- create_user
  - SQL 1: SELECT id FROM users WHERE username = ?
  - SQL 2: INSERT INTO users (username, password, role) VALUES (?, ?, ?)
  - Use case: ensure uniqueness, then insert the account.
- list_all_users
  - SQL: SELECT id, username, role, created_at FROM users ORDER BY created_at DESC
  - Use case: admin view of all users.
comment_service:
- _fetch_rows
  - SQL: SELECT c.id, c.post_id, c.user_id, u.username, c.content, c.created_at, c.is_deleted, c.parent_comment_id, ISNULL(l.like_count, 0) AS like_count FROM comments c INNER JOIN users u ON u.id = c.user_id LEFT JOIN (SELECT comment_id, COUNT(*) AS like_count FROM comment_likes GROUP BY comment_id) l ON l.comment_id = c.id WHERE ... ORDER BY c.created_at DESC
  - Use case: retrieve flat data for building the comment tree.
- _fetch_liked_ids
  - SQL: SELECT comment_id FROM comment_likes WHERE user_id = ? AND comment_id IN (...)
  - Use case: determine which comments the current user already liked.
- add_comment
  - SQL 1: SELECT id, post_id, is_deleted FROM comments WHERE id = ?
  - SQL 2: INSERT INTO comments (post_id, user_id, content, parent_comment_id) VALUES (?, ?, ?, ?)
  - SQL 3: SELECT TOP 1 c.id, c.post_id, c.user_id, u.username, c.content, c.created_at, c.is_deleted, c.parent_comment_id, 0 AS like_count FROM comments c INNER JOIN users u ON u.id = c.user_id WHERE c.user_id = ? ORDER BY c.created_at DESC
  - Use case: validate parent, insert the new comment, and return the latest record.
- soft_delete_comment
  - SQL: UPDATE comments SET is_deleted = 1 WHERE id = ?
  - Use case: mark a comment as deleted.
- toggle_like
  - SQL 1: SELECT id, is_deleted FROM comments WHERE id = ?
  - SQL 2: SELECT id FROM comment_likes WHERE comment_id = ? AND user_id = ?
  - SQL 3: INSERT INTO comment_likes (comment_id, user_id) VALUES (?, ?)
  - SQL 4: DELETE FROM comment_likes WHERE id = ?
  - SQL 5: SELECT COUNT(*) AS cnt FROM comment_likes WHERE comment_id = ?
  - Use case: verify comment state, toggle like entry, then return the current like count.

6. Data Processing Scenarios

Scenario 1: Loading existing comments

When a blog post loads, comments.js:
- Reads the token from localStorage;
- Issues GET /api/comments?post_id=<slug> (adds Authorization header if a token exists);
- Waits for the JSON response.
comments_router.list_comments():
- Resolves the user via require_session() (if a token is provided);
- Calls comment_service._fetch_rows() which executes the parameterized SQL shown earlier through database.fetch_all() and pyodbc;
- If the user is logged in, *_fetch_liked_ids() runs SELECT comment_id FROM comment_likes WHERE user_id = ? AND comment_id IN (?,?,?) to determine liked comments.

_build_tree() transforms flat rows into nested JSON, e.g.

[
  {
    "id": 1,
    "username": "user1",
    "content": "comment1",
    "like_count": 2,
    "liked_by_viewer": false,
    "replies": [
      {
        "id": 2,
        "username": "user2",
        "content": "comment2",
        "like_count": 0,
        "liked_by_viewer": false,
        "replies": [
          {
            "id": 3,
            "username": "user1",
            "content": "comment3",
            "like_count": 1,
            "liked_by_viewer": true,
            "replies": []
          }
        ]
      }
    ]
  }
]

FastAPI returns the JSON, and renderComments() recursively renders DOM nodes to match the hierarchy.

Scenario 2: Posting a comment
1. The user fills the textarea and clicks Post:
  - JS collects token, content, post_id, and optional parent_comment_id;
  - Sends POST /api/comments with a JSON payload.
2. comments_router.create_comment():
  - Uses require_session() to confirm the user;
  - Invokes comment_service.add_comment() which:
    - If replying, executes SELECT id, post_id, is_deleted FROM comments WHERE id = ? to validate the parent;
    - Inserts via INSERT INTO comments (post_id, user_id, content, parent_comment_id) VALUES (?, ?, ?, ?) with pyodbc parameter binding;
    - Reads back the newest row with SELECT TOP 1 ... WHERE c.user_id = ? ORDER BY c.created_at DESC.
3. The response contains the new comment (id / username / content / like_count = 0). The browser prepends it to the list and optionally refetches the entire tree.
Scenario 3: Toggling likes
1. Clicking the like button triggers POST /api/comments/{comment_id}/like with the token header.
2. comment_service.toggle_like() runs the SQL chain:
  - SELECT id, is_deleted FROM comments WHERE id = ? to ensure the comment is valid;
  - SELECT id FROM comment_likes WHERE comment_id = ? AND user_id = ? to detect current state;
  - Insert or delete from comment_likes accordingly;
  - SELECT COUNT(*) AS cnt FROM comment_likes WHERE comment_id = ? for the latest tally.
3. The API responds with { "like_count": <n>, "liked": true/false }, and the front end updates the button label/count.
Scenario 4: Admin deletion
1. In /admin, clicking Delete calls DELETE /api/comments/{id} with an admin token.
2. comment_service.soft_delete_comment() verifies with SELECT id FROM comments WHERE id = ?, then runs UPDATE comments SET is_deleted = 1 WHERE id = ? and commits.
3. After deletion, the front end reloads GET /api/comments; because _fetch_rows() filters is_deleted = 1, the removed comment disappears for regular users while remaining visible to admins in their list.

7. API Overview

Endpoint	Method	Purpose
`/api/users/register`	POST	Register a normal user
`/api/users/login`	POST	Log in and receive a token
`/api/comments`	GET	Fetch public comments per post
`/api/comments`	POST	Publish comment (requires auth)
`/api/admin/comments`	GET	Moderation feed for admins
`/api/admin/delete_comment`	POST	Soft delete a comment
`/api/admin/create`	POST	Create another admin user

8. Additional Notes

Security trade-offs: To keep the sample simple, passwords are stored in plain text, tokens sit in memory, and there is no HTTPS/JWT/CSRF. Add hashing and stronger auth layers before production use.
Testing flow: py init_db.py → uvicorn backend.main:app --reload ... → open /admin with the default admin → use the blog page and comments.js to test registration/login/comment/like. Automated testing can be built with pytest + httpx against the APIs.
Future extensions: The schema already supports replies and likes; you can add more tables (reports, attachments, etc.) by extending init_db.py and adding service methods that reuse the database helpers.

9. Cloud Deployment Tips

Prefer running the Python service and SQL Server on the same VM.
Keep the Python ↔ SQL connection on the private network; if exposing FastAPI, bind it to the VM’s public IP/port.
Update the injected JS snippet with the public API base instead of localhost.
Open the required ports (FastAPI + SQL) on your firewall/security group and enforce TLS/strong passwords before exposing the service.