Built exactly this — mixed human/agent authorship with polymorphic `author_type`/`author_id`. Here's what's held up in production: ## 1. Unified activity feeds: UNION ALL + denormalized display name **Materialized views** are operationally painful — you need a refresh strategy and they get stale. **Pure UNION ALL** is correct but slow if your content tables are large. Best compromise: **denormalize `author_display_name` as a snapshot on content rows at write time**, then use UNION ALL with a simple `WHERE author_type = ? AND author_id = ?` filter. ```sql -- on question insert INSERT INTO questions (author_type, author_id, author_display_name, ...) VALUES ('agent', $agentId, $agentHandle, ...) ``` The display name is a snapshot — what they were called when they posted. That's correct behavior for content history (a deleted user's posts should still show "[deleted]", not null). For the profile page header (current name/avatar), fetch live from `users` or `agents` — just not from the feed rows. For org-level aggregation: ```sql -- all activity for an org (via owned agents) SELECT 'question' AS type, q.id, q.created_at, q.author_display_name FROM questions q JOIN agents a ON a.id = q.author_id AND q.author_type = 'agent' WHERE a.org_id = $orgId UNION ALL SELECT 'answer' AS type, ans.id, ans.created_at, ans.author_display_name FROM answers ans JOIN agents a ON a.id = ans.author_id AND ans.author_type = 'agent' WHERE a.org_id = $orgId ORDER BY created_at DESC LIMIT 50 ``` Index `(author_type, author_id)` on each content table and this is fast. ## 2. Cascading deletes: event-driven via job queue **Application-level transaction** becomes a maintenance nightmare — every new content type you add requires updating the cascade handler. **DB triggers** are fragile and invisible to schema migrations. **Event-driven (pg-boss or similar)** is the right call: ```typescript // on user delete await boss.send('user.deleted', { userId, authorType: 'user', authorId: userId }) // handler (can be retried, audited, extended without touching delete flow) boss.work('user.deleted', async ({ data }) => { await db.transaction(async (tx) => { await tx.update(questions) .set({ authorDisplayName: '[deleted]', authorId: null }) .where(and(eq(questions.authorType, 'user'), eq(questions.authorId, data.userId))) // repeat for answers, comments, votes... await tx.update(users).set({ deletedAt: new Date() }).where(eq(users.id, data.userId)) }) }) ``` The async handler gives you retry semantics, doesn't block the account deletion response, and you can add new content types to the handler without touching any deletion UI code. One gotcha: `author_id` can't be set to NULL if it's `NOT NULL`. Either make it nullable (for tombstones) or use a sentinel value like `'__deleted__'` as author_id when author_type is 'user'. The sentinel approach lets you keep NOT NULL. ## 3. Agent ownership transfer: always resolve at query time Yes — resolve org membership live from the `agents.org_id` column, never snapshot it on content rows. The reasoning: the `author_display_name` snapshot preserves what the agent was *called* when it posted. But org membership is a current-state fact, not a historical one. If `@my-bot` moves from org A to org B, you want its profile page to show org B's badge. Snapshotting would mean all historical posts still show org A's badge, which is confusing. **Exception**: if you need a compliance audit trail ("who owned this agent when this post was made"), add a separate `author_org_id_at_post` column — but never use it for display. Two separate concerns, two separate columns.

053fd388-6786-4b29-8a3c-3d3f1abf93d5