xfy
449a545886
P0 blockers: - Fix migration numbering conflict and duplicate indexes - Change comments.post_id FK to ON DELETE CASCADE - Restrict public post detail endpoint to published posts only - Fix rate-limiting IP extraction and fallback to ConnectInfo - Harden HTML sanitizer: deny unknown URL schemes, restrict data URIs - Remove session token from login response body - Enforce image pixel/dimension limits on upload and serving P1 high-risk: - Validate uploads by magic bytes and decode GIF/WebP - Add pagination/rate-limiting to search, tag posts, and comments - Make first-admin registration and slug uniqueness check atomic - HTML-escape comment author fields - Improve HTML minify cache key and skip admin/error responses - Add mobile navigation menu P2 accessibility/quality: - Associate form labels with inputs - Key PostDetail article by slug to re-init scripts on navigation - Improve image viewer keyboard accessibility - Make theme toggle SSR-friendly and add aria-label - Invalidate slug 404 cache on create and pending count on new comment - Deduplicate tags case-insensitively P3 cleanup: - Remove unused tower-http dependency, expand make clean - Configure DB pool timeouts and verified recycling - Run background cleanup tasks immediately on startup - Use SHA-256 for stable disk cache keys - Log DB errors with Display instead of Debug - Update README migration instructions All tests pass (321), clippy clean, dx check clean.
58 lines
2.0 KiB
PL/PgSQL
58 lines
2.0 KiB
PL/PgSQL
CREATE TABLE IF NOT EXISTS comments (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
parent_id BIGINT REFERENCES comments(id) ON DELETE SET NULL,
|
|
depth INT NOT NULL DEFAULT 0,
|
|
author_name VARCHAR(50) NOT NULL,
|
|
author_email VARCHAR(255) NOT NULL,
|
|
author_url VARCHAR(500),
|
|
content_md TEXT NOT NULL,
|
|
content_html TEXT,
|
|
content_hash VARCHAR(64),
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
ip_address VARCHAR(45),
|
|
user_agent VARCHAR(500),
|
|
approved_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
|
|
CONSTRAINT comments_status_check
|
|
CHECK (status IN ('pending', 'approved', 'spam', 'trash')),
|
|
CONSTRAINT comments_depth_check
|
|
CHECK (depth >= 0 AND depth <= 20),
|
|
CONSTRAINT comments_content_not_empty
|
|
CHECK (length(trim(content_md)) >= 1),
|
|
CONSTRAINT comments_name_not_empty
|
|
CHECK (length(trim(author_name)) >= 1)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comments_post_approved
|
|
ON comments(post_id, created_at) WHERE status = 'approved' AND deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comments_top_level
|
|
ON comments(post_id, created_at)
|
|
WHERE parent_id IS NULL AND status = 'approved' AND deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comments_pending
|
|
ON comments(created_at DESC) WHERE status = 'pending' AND deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comments_admin_list
|
|
ON comments(status, created_at DESC) WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comments_parent
|
|
ON comments(parent_id) WHERE parent_id IS NOT NULL;
|
|
|
|
CREATE OR REPLACE FUNCTION update_comments_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_comments_updated_at
|
|
BEFORE UPDATE ON comments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_comments_updated_at();
|