Bug: DELETE /posts/:id returns success but post remains .. deeper analysis

[approximated-intelligence]

Description

DELETE /api/v1/posts/:id returns {"success":true,"message":"Post deleted"} but the post is not deleted.

Steps to Reproduce

1. Create a post via POST /api/v1/posts
2. Delete via DELETE /api/v1/posts/:id
3. Observe success response
4. GET /api/v1/posts/:id — post still exists

Expected Behavior

Post is deleted (or soft-deleted) and no longer retrievable.

Actual Behavior

Returns success, post unchanged.

Root Cause

PostService.delete() lacks safeguards:

await queryOne('DELETE FROM posts WHERE id = $1', [postId]);

1. No result verification — doesn't check rowCount
2. Schema mismatch — schema has is_deleted BOOLEAN for soft deletes, code does hard delete
3. RLS interaction — if DELETE policy is missing (see issue: Security: Missing RLS DELETE policy on posts table (Supabase) #73), query succeeds with zero rows affected

Proposed Fix

Two options:

Option A: Fix hard delete (minimal)

const result = await query(
  'DELETE FROM posts WHERE id = $1 RETURNING id',
  [postId]
);

if (result.rowCount === 0) {
  throw new Error('Failed to delete post');
}

Pros: Minimal change
Cons: Loses audit trail, cascades comments, is_deleted column unused

Option B: Implement soft delete

const result = await queryOne(
  'UPDATE posts SET is_deleted = true, updated_at = NOW() WHERE id = $1 RETURNING id',
  [postId]
);

if (!result) {
  throw new Error('Failed to delete post');
}

Also requires adding WHERE is_deleted = false to:

• findById()
• getFeed()
• getPersonalizedFeed()
• Same for CommentService.js

Pros: Uses schema as designed, preserves audit trail, works with UPDATE RLS policy
Cons: More code changes

Additional Context

An ORM with soft-delete middleware (Prisma, Drizzle) would prevent this bug class entirely. The codebase already has hand-rolled query helpers — might be worth the migration.

[approximated-intelligence]

should the hard delete option (A) be implemented, then the Supabase RLS has to be configured correctly to make it work, see issue: #73