Comments and activity

The comments block adds threaded comments to any record in a tenant (projects, tickets, invoices) and an activity feed. Mentions notify the mentioned members through the notifications block, and every comment writes a comment.* event to the outbox.

better-supabase sql add comments   # adds tenant and access as well

Only the author edits a comment. Deleting is a soft delete: the row stays as a placeholder so replies keep their parent, and its body and mentions are cleared.

SubjectsCopy Anchor Link

Without options, a comment can name any subject_type and subject_id. Map each subject type to its table so the policies also require that the caller can read the subject row (the subject table's own policies apply) and, optionally, hold a permission:

export default defineConfig({
  sql: {
    modules: {
      comments: {
        options: {
          subjects: {
            project: { table: "projects", permission: "projects.read" },
            ticket: {
              table: "support.tickets",
              id: "ticket_id",
              tenant: "team_id",
            },
          },
        },
      },
    },
  },
});

id defaults to id and tenant to organization_id. A subject type not in the map is refused. maxBodyLength (default 10000) caps the body.

permissions gives a subject type its own keys per action, in place of the module's comments.read, comments.create and comments.moderate, so comments on deals follow the deal permissions while other subjects keep the defaults:

subjects: {
  deal: {
    table: "deals",
    permissions: { read: "deals.read", create: "deals.comment", moderate: "deals.manage" },
  },
},

read decides who sees the thread, create who may comment, and moderate who may edit or delete other people's comments; authors always edit their own.

cascade: true on a subject writes an after delete trigger on its table that deletes the subject's comments, which stands in for a foreign key, since subject_id is text. The subject table must exist before the module file.

Rich textCopy Anchor Link

A comment can carry a document (any JSON, such as a block editor's document) next to body, which stays its plain text for search, summaries and notifications. createComments({ mentionsOf }) reads the mentioned user ids from the comment when a call passes no mentions, for editors that store mentions as nodes instead of @[Name](<user id>) markup:

const comments = createComments({
  transport,
  mentionsOf: ({ document }) =>
    mentionNodes(document).map((node) => node.userId),
});

await comments.create({
  organizationId,
  subjectType,
  subjectId,
  body,
  document,
});
await comments.edit(id, { body, document: null }); // null removes the document

An edit without document keeps the current one. With the jsonb-schemas module, options.documentSchema (a JSON Schema object) adds a pg_jsonschema check on the column.

Copying a threadCopy Anchor Link

When the app duplicates a record (a quote into an invoice, say), comments.copy(organizationId, { type, id }, { type, id }) copies the thread to the new subject with its authors, times and replies, and returns the number of comments copied. copy_comments is for the service role only: call it from the code that duplicates the record, after that code checked the caller may read both. It works over sqlTransport and over rpcTransport with a service-role client; PostgREST sessions on Supabase load pg-safeupdate, which refuses a delete or update without a where clause, and every statement the SQL modules run has one. The function creates no temporary table, so supabase db lint checks it like any other.

CommentsCopy Anchor Link

import { createComments, sqlTransport } from "better-supabase/blocks/comments";

const comments = createComments({
  transport: sqlTransport(postgres.asUser(claims)),
});

await comments
  .create({
    organizationId,
    subjectType: "project",
    subjectId: projectId,
    body: "Ready for review @[Ada](8c5a3d3e-...)",
  })
  .orThrow();

const thread = await comments
  .list(organizationId, "project", projectId)
  .orThrow();

create and edit read the mentions from the body when you pass none: mentionsIn(body) returns the user ids written as @[Name](<user id>), the markup most mention inputs produce. The module drops the author and repeats, and notifies only mentioned members who hold comments.read. list returns the thread oldest first; cursor (an instant) polls for new comments, and offset with limit pages by number. counts(organizationId, subjectType, subjectIds) returns how many comments each subject has that the caller can read (comment_counts), deleted ones left out and 0 for a subject without any, for a counter on each row of a list. edit returns not_found for a comment the caller can't see, and forbidden with the hint COMMENT_NOT_AUTHOR for someone else's comment.

With the notifications module installed, each new mention sends a comment.mentioned notification with the subject, the first 140 characters of the body as its summary and the commenter as the actor. Three more keys of options.subjects.<type> shape it, each SQL on the subject's row {row}:

subjects: {
  task: {
    table: "public.tasks",
    idType: "uuid",
    label: "{row}.title",
    path: "'/tasks/' || {row}.id",
    readableBy: "not {row}.private or {row}.owner_id = {user}",
  },
},

label fills the notification's subject_label and path its action_path, which an adopted events table may require. A mentioned member is notified only when they hold the subject type's read key (permissions.read, else comments.read) and its permission in the tenant, and readableBy holds for them ({user} is their id). The others stay in the comment's mentions but get nothing.

Group mentionsCopy Anchor Link

options.mentionGroups expands a mention of a group, such as @team, into its members. It names a table with one row per member of a group, the group and member columns, and optionally a tenant column that must match the comment's tenant:

comments: {
  options: {
    mentionGroups: {
      table: "public.team_members",
      group: "team_id",
      member: "user_id",
      tenant: "organization_id",
    },
  },
},

The app writes the group id into mentions like a user id, for example as @[Support](<team id>). The trigger adds the members of every mentioned group to the mentioned users, leaves out the author, and then applies the read check above to each of them, so a group member who can't read the subject gets nothing. On an edit, only members who were not already reached through the old mentions are notified, and comment.mentioned carries the expanded user ids in mentionIds. Both columns hold uuids, and the comment's mentions keep the group id as written.

options.notify: false sends no notification at all, for an app that sends its own mention notifications. The outbox still gets comment.mentioned, so that app can send from the event.

EventsCopy Anchor Link

With the outbox installed, the module writes these events with the tenant as the partition key and comments/<id> as the subject:

Activity feedCopy Anchor Link

activitySink is an outbox sink that writes events into activity_entries. Run it as a consumer with a service-role transport:

import { activitySink, sqlTransport } from "better-supabase/blocks/comments";

await outbox.relay(
  "activity",
  activitySink({
    transport: sqlTransport(postgres.admin),
    types: ["comment.*", "organization.member_added", "billing.*"],
    describe: (event, type) =>
      type === "comment.created" ? { summary: "commented" } : {},
  }),
);

Events without a tenant are skipped, and the event id is unique, so a replayed batch writes nothing twice. The actor is the event's actorId, authorId or userId, and the subject its subjectType and subjectId (or the CloudEvents subject). describe can set the summary, actor, subject and data, or return null to skip an event.

comments.history(organizationId, subject?, { before, limit }) reads the feed through list_activity, which runs as the caller: the tenant's entries newest first, or one subject's timeline when you pass { type, id }. It works over rpcTransport too, without exposing the module schema.

const timeline = await comments
  .history(organizationId, { type: "quote", id: quoteId }, { limit: 20 })
  .orThrow();

Or read the feed with activityListQuery, a cursor-paged list query filtered by type, actor and subject, newest first. Generate types for the better_supabase schema to have the table in your models:

import { activityListQuery } from "better-supabase/blocks/comments";

export const activity = activityListQuery(betterSupabase, "activityEntries", {
  pageSize: 30,
});