19 lines
929 B
SQL
19 lines
929 B
SQL
-- Soft-delete (admin moderation) for posts and comments. Additive: the column
|
|
-- is nullable and defaults to NULL, and every read query now filters
|
|
-- `deleted_at IS NULL` — a no-op for existing rows (all NULL), so nothing in
|
|
-- the live app changes until an admin actually deletes something. A soft-
|
|
-- deleted row disappears from all reads but stays in the table, recoverable
|
|
-- via the admin "restore" action.
|
|
ALTER TABLE posts
|
|
ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
|
|
|
|
ALTER TABLE post_comments
|
|
ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
|
|
|
|
-- Partial indexes keep the hot "not deleted" reads fast (they match the
|
|
-- deleted_at IS NULL predicate now on every posts/comments query).
|
|
CREATE INDEX IF NOT EXISTS posts_not_deleted_idx
|
|
ON posts (created_at DESC) WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS post_comments_not_deleted_idx
|
|
ON post_comments (post_id) WHERE deleted_at IS NULL;
|