Clean up reactions and comments when articles or gists are deleted (polymorphic resource_id / resource_type)

[kingRayhan]

Problem

reactions and comments reference content via polymorphic fields (resource_id, resource_type), not foreign keys to articles or gists. Deleting an article or gist does not remove related rows in those tables, so orphans can remain.

Only user_id → users is set to cascade on delete; parent resources are not.

Context

• There are no PostgreSQL triggers in this repo today for this behavior; cleanup would be new work (app-layer and/or migrations).
• gist_files can still cascade from gists via normal FKs; this issue is about polymorphic children only.

Goal

Ensure that when an article or gist is deleted, all associated reactions and comments (and nested comment threads if applicable) are removed consistently, without leaving orphaned rows.

Options (pick one direction in implementation)

1. Application layer: In the same transaction as the article/gist delete, explicitly delete matching reactions and comments (and any related rows if required by the data model).
2. Database layer: Add PostgreSQL AFTER DELETE triggers (or other DDL) to delete matching polymorphic rows; keep behavior in migrations and document it.

Trade-offs: app-layer keeps logic in TypeScript and is easier to test in existing flows; DB triggers enforce consistency even if deletes happen outside the app (e.g. admin SQL).

Acceptance criteria

• Deleting an article removes all reactions and comments where resource_id / resource_type refer to that article (and handles nested comments per product rules).
• Deleting a gist does the same for gist-scoped reactions/comments.
• Behavior is documented briefly (code comment or internal note) so future deletes do not bypass cleanup.
• No regressions to existing publish/delete flows; errors roll back the transaction.

References

• Schema: src/backend/persistence/schemas.ts
• Delete flows: article/gist server actions under src/backend/services/