atBB Boards Hierarchy Design#

Issue: ATB-23 - Add categoryUri column to posts schema Date: 2026-02-13 Status: Approved

Context#

The original ATB-23 scope was to add categoryUri to posts for category filtering. During design exploration, we discovered a fundamental mismatch between atBB's current 2-level hierarchy and traditional BB forum structure (phpBB, SMF).

Traditional BB forums use:

Forum (instance) → Categories (groupings) → Boards (postable areas) → Topics → Replies

atBB currently has:

Forum (instance) → Categories (postable areas) → Topics → Replies

The current categories table is serving the role of "boards" (postable areas), not groupings. This restructuring aligns atBB with traditional forum UX expectations.

Decision#

Restructure atBB to use the traditional 3-level hierarchy by introducing boards as the postable areas, with categories becoming non-postable groupings.

No migration needed: There are no published records in production, only local development data.

Data Model#

New Hierarchy#

space.atbb.forum.forum      # Forum instance (singleton, Forum DID)
  └─ space.atbb.forum.category   # Category groupings (non-postable, Forum DID)
      └─ space.atbb.forum.board      # Boards (postable areas, Forum DID)
          └─ space.atbb.post             # Topics + replies (User DID)

Database Schema Changes#

1. Keep categories table (semantic shift to groupings):

• No structural changes
• Now represents non-postable category groupings

2. Create new boards table:

export const boards = pgTable("boards", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  did: text("did").notNull(),
  rkey: text("rkey").notNull(),
  cid: text("cid").notNull(),
  name: text("name").notNull(),
  description: text("description"),
  slug: text("slug"),
  sortOrder: integer("sort_order"),
  categoryId: bigint("category_id", { mode: "bigint" }).references(() => categories.id),
  categoryUri: text("category_uri").notNull(), // For out-of-order indexing
  createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
  indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
}, (table) => [
  uniqueIndex("boards_did_rkey_idx").on(table.did, table.rkey),
  index("boards_category_id_idx").on(table.categoryId),
]);

3. Update posts table:

export const posts = pgTable("posts", {
  // ... existing fields ...
  forumUri: text("forum_uri"),      // KEEP - forum instance reference (for client flexibility)
  boardUri: text("board_uri"),      // NEW - board reference
  boardId: bigint("board_id", { mode: "bigint" }).references(() => boards.id), // NEW
  rootPostId: bigint("root_post_id", { mode: "bigint" }).references((): any => posts.id),
  parentPostId: bigint("parent_post_id", { mode: "bigint" }).references((): any => posts.id),
  // ... rest of fields ...
}, (table) => [
  // ... existing indexes ...
  index("posts_board_id_idx").on(table.boardId),
  index("posts_board_uri_idx").on(table.boardUri),
]);

Rationale for keeping forumUri:

• Maintains flexibility for clients with different UX models
• Some clients may want flat view (forums → topics) without board hierarchy
• Storage is cheap, redundancy aids AT Proto interoperability

Lexicon Changes#

New Lexicon: space.atbb.forum.board#

lexicon: 1
id: space.atbb.forum.board
defs:
  main:
    type: record
    description: A board (subforum) within a category. Owned by Forum DID.
    key: tid
    record:
      type: object
      required: [name, category, createdAt]
      properties:
        name:
          type: string
          maxLength: 300
          maxGraphemes: 100
          description: Display name of the board.
        description:
          type: string
          maxLength: 3000
          maxGraphemes: 300
          description: A short description for the board.
        slug:
          type: string
          maxLength: 100
          description: URL-friendly identifier.
        sortOrder:
          type: integer
          minimum: 0
          description: Numeric sort position. Lower values appear first.
        category:
          type: ref
          ref: "#categoryRef"
        createdAt:
          type: string
          format: datetime
  categoryRef:
    type: object
    required: [category]
    properties:
      category:
        type: ref
        ref: com.atproto.repo.strongRef

Updated Lexicon: space.atbb.post#

Add board reference (keep existing forum reference):

properties:
  text: ...
  forum:          # KEEP - existing forum reference
    type: ref
    ref: "#forumRef"
  board:          # NEW - board reference
    type: ref
    ref: "#boardRef"
  reply: ...
  createdAt: ...

defs:
  # Existing forumRef stays unchanged
  forumRef:
    type: object
    required: [forum]
    properties:
      forum:
        type: ref
        ref: com.atproto.repo.strongRef

  # New boardRef
  boardRef:
    type: object
    required: [board]
    properties:
      board:
        type: ref
        ref: com.atproto.repo.strongRef

API Endpoints#

New Endpoints#

Boards:

GET  /api/boards              # List all boards (with category grouping)
GET  /api/boards/:id          # Get board details
GET  /api/boards/:id/topics   # List topics in a board (paginated by page number)

Categories (enhanced):

GET  /api/categories          # Existing - list all categories
GET  /api/categories/:id/boards  # NEW - list boards in a category

Updated Endpoints#

Topics:

POST /api/topics
  Body: { text: string, boardUri: string }
  - boardUri is REQUIRED
  - forumUri is always the configured singleton (not accepted as input)
  - Validates board exists before writing to PDS
  - Writes both forum and board refs to PDS

GET /api/topics/:id
  - No changes

Endpoint Details#

GET /api/boards/:id/topics:

• Returns topics (posts with NULL root) in the board
• Paginated using page numbers (traditional BB forum style)
• Sorted by last reply time (descending)
• Includes metadata: reply count, last post time, author info

GET /api/categories/:id/boards:

• Returns boards in a category
• Sorted by sortOrder
• Includes board metadata + topic count (e.g., "25 topics, 143 posts")

GET /api/boards:

• Returns all boards grouped by category
• Includes category metadata and board counts

Indexer Changes#

New Board Config#

private boardConfig: CollectionConfig<Board.Record> = {
  name: "Board",
  table: boards,
  deleteStrategy: "hard",
  toInsertValues: async (event, record, tx) => {
    const categoryId = await this.getCategoryIdByUri(record.category.category.uri, tx);

    if (!categoryId) {
      console.warn(`[CREATE] Board: Category not found for URI ${record.category.category.uri}`);
      return null;
    }

    return {
      did: event.did,
      rkey: event.commit.rkey,
      cid: event.commit.cid,
      name: record.name,
      description: record.description ?? null,
      slug: record.slug ?? null,
      sortOrder: record.sortOrder ?? null,
      categoryId,
      categoryUri: record.category.category.uri,
      createdAt: new Date(record.createdAt),
      indexedAt: new Date(),
    };
  },
  toUpdateValues: async (event, record, tx) => {
    const categoryId = await this.getCategoryIdByUri(record.category.category.uri, tx);

    return {
      cid: event.commit.cid,
      name: record.name,
      description: record.description ?? null,
      slug: record.slug ?? null,
      sortOrder: record.sortOrder ?? null,
      categoryId,
      categoryUri: record.category.category.uri,
      indexedAt: new Date(),
    };
  },
};

Updated Post Config#

private postConfig: CollectionConfig<Post.Record> = {
  // ... existing config ...
  toInsertValues: async (event, record, tx) => {
    // Look up board for the post
    let boardId: bigint | null = null;
    if (record.board) {
      boardId = await this.getBoardIdByUri(record.board.board.uri, tx);
    }

    // Look up parent/root for replies (existing logic)
    let rootId: bigint | null = null;
    let parentId: bigint | null = null;
    if (Post.isReplyRef(record.reply)) {
      rootId = await this.getPostIdByUri(record.reply.root.uri, tx);
      parentId = await this.getPostIdByUri(record.reply.parent.uri, tx);
    }

    return {
      did: event.did,
      rkey: event.commit.rkey,
      cid: event.commit.cid,
      text: record.text,
      forumUri: record.forum?.forum.uri ?? null,  // KEEP
      boardUri: record.board?.board.uri ?? null,  // NEW
      boardId,                                     // NEW
      rootPostId: rootId,
      rootUri: record.reply?.root.uri ?? null,
      parentPostId: parentId,
      parentUri: record.reply?.parent.uri ?? null,
      createdAt: new Date(record.createdAt),
      indexedAt: new Date(),
    };
  },
  toUpdateValues: async (event, record) => ({
    cid: event.commit.cid,
    text: record.text,
    forumUri: record.forum?.forum.uri ?? null,  // KEEP
    boardUri: record.board?.board.uri ?? null,  // NEW
    indexedAt: new Date(),
  }),
};

New Helper Methods#

private async getBoardIdByUri(uri: string, tx: DbOrTransaction): Promise<bigint | null> {
  const { did, rkey } = parseAtUri(uri);
  const [result] = await tx
    .select({ id: boards.id })
    .from(boards)
    .where(and(eq(boards.did, did), eq(boards.rkey, rkey)))
    .limit(1);
  return result?.id ?? null;
}

private async getCategoryIdByUri(uri: string, tx: DbOrTransaction): Promise<bigint | null> {
  const { did, rkey } = parseAtUri(uri);
  const [result] = await tx
    .select({ id: categories.id })
    .from(categories)
    .where(and(eq(categories.did, did), eq(categories.rkey, rkey)))
    .limit(1);
  return result?.id ?? null;
}

FirehoseService Registration#

.register({
  collection: "space.atbb.forum.board",
  onCreate: this.createWrappedHandler("handleBoardCreate"),
  onUpdate: this.createWrappedHandler("handleBoardUpdate"),
  onDelete: this.createWrappedHandler("handleBoardDelete"),
})

Write-Path Implementation#

Updated POST /api/topics#

.post("/", requireAuth(ctx), async (c) => {
  const user = c.get("user")!;

  let body: any;
  try {
    body = await c.req.json();
  } catch {
    return c.json({ error: "Invalid JSON in request body" }, 400);
  }

  const { text, boardUri } = body;

  // Validate text
  const validation = validatePostText(text);
  if (!validation.valid) {
    return c.json({ error: validation.error }, 400);
  }

  // Validate boardUri is required
  if (typeof boardUri !== "string" || !boardUri.trim()) {
    return c.json({ error: "boardUri is required" }, 400);
  }

  try {
    // Always use the configured singleton forum
    const forumUri = `at://${ctx.config.forumDid}/space.atbb.forum.forum/self`;

    // Look up forum to get CID
    const forum = await getForumByUri(ctx.db, forumUri);
    if (!forum) {
      return c.json({ error: "Forum not found" }, 404);
    }

    // Look up board to get CID
    const board = await getBoardByUri(ctx.db, boardUri);
    if (!board) {
      return c.json({ error: "Board not found" }, 404);
    }

    // Generate TID for rkey
    const rkey = TID.nextStr();

    // Write to user's PDS
    const result = await user.agent.com.atproto.repo.putRecord({
      repo: user.did,
      collection: "space.atbb.post",
      rkey,
      record: {
        $type: "space.atbb.post",
        text: validation.trimmed!,
        forum: {
          forum: { uri: forumUri, cid: forum.cid },
        },
        board: {
          board: { uri: boardUri, cid: board.cid },
        },
        createdAt: new Date().toISOString(),
      },
    });

    return c.json({ uri: result.data.uri, cid: result.data.cid, rkey }, 201);
  } catch (error) {
    // ... existing error handling ...
  }
});

New Helper Function#

export async function getBoardByUri(
  db: Database,
  uri: string
): Promise<{ cid: string } | null> {
  const { did, rkey } = parseAtUri(uri);
  const [result] = await db
    .select({ cid: boards.cid })
    .from(boards)
    .where(and(eq(boards.did, did), eq(boards.rkey, rkey)))
    .limit(1);
  return result ?? null;
}

Database Migrations#

Migration 1: Create boards table

CREATE TABLE boards (
  id BIGSERIAL PRIMARY KEY,
  did TEXT NOT NULL,
  rkey TEXT NOT NULL,
  cid TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  slug TEXT,
  sort_order INTEGER,
  category_id BIGINT REFERENCES categories(id),
  category_uri TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  indexed_at TIMESTAMPTZ NOT NULL
);

CREATE UNIQUE INDEX boards_did_rkey_idx ON boards(did, rkey);
CREATE INDEX boards_category_id_idx ON boards(category_id);

Migration 2: Update posts table

ALTER TABLE posts ADD COLUMN board_uri TEXT;
ALTER TABLE posts ADD COLUMN board_id BIGINT REFERENCES boards(id);

CREATE INDEX posts_board_id_idx ON posts(board_id);
CREATE INDEX posts_board_uri_idx ON posts(board_uri);

Testing Strategy#

Test Coverage#

Lexicon:

• ✅ space.atbb.forum.board generates correct TypeScript types
• ✅ Post type includes both forum and board refs

Indexer:

• ✅ Board create/update/delete events
• ✅ Posts with board references get indexed correctly
• ✅ Handles missing board gracefully (logs warning, skips insert)
• ✅ Category references resolve correctly

API Endpoints:

• ✅ GET /api/boards returns all boards grouped by category
• ✅ GET /api/boards/:id returns board details
• ✅ GET /api/boards/:id/topics returns paginated topics with metadata (reply count, last post time, author)
• ✅ GET /api/categories/:id/boards returns boards in category with topic counts
• ✅ POST /api/topics requires boardUri, validates board exists
• ✅ POST /api/topics writes both forum and board refs to PDS

Error Cases:

• ✅ Invalid boardUri format (400)
• ✅ Board not found (404)
• ✅ Missing boardUri in POST request (400)
• ✅ Malformed JSON in POST request (400)

Integration:

• ✅ End-to-end: Create board → Create topic → View in board → Reply to topic
• ✅ Pagination works correctly with page numbers
• ✅ Topic counts update correctly

Implementation Phases#

Phase 1: Lexicon & Schema

1. Create space.atbb.forum.board lexicon
2. Update space.atbb.post lexicon with board reference
3. Regenerate TypeScript types
4. Create database migration for boards table
5. Create database migration for posts table updates

Phase 2: Indexer

1. Add board config to Indexer
2. Update post config to extract boardUri
3. Add helper methods (getBoardIdByUri, getCategoryIdByUri)
4. Register board handlers in FirehoseService
5. Write indexer tests

Phase 3: API - Boards

1. Create GET /api/boards endpoint
2. Create GET /api/boards/:id endpoint
3. Create GET /api/boards/:id/topics endpoint with pagination
4. Create GET /api/categories/:id/boards endpoint
5. Write API tests

Phase 4: API - Topics

1. Update POST /api/topics to require boardUri
2. Add getBoardByUri helper
3. Update request validation
4. Update PDS write to include board reference
5. Write API tests

Phase 5: Bruno Collections

1. Add board endpoints to Bruno collection
2. Update topic creation request with boardUri
3. Document all request/response formats
4. Add error case examples

Success Criteria#

• ✅ Boards can be created and indexed from firehose
• ✅ Topics require board assignment
• ✅ API returns proper hierarchy: categories → boards → topics
• ✅ Pagination works with page numbers
• ✅ Topic metadata (reply count, last post) displays correctly
• ✅ All tests pass
• ✅ Bruno collections updated and tested
• ✅ Documentation updated (plan doc, Linear issue)

Related Issues#

• ATB-23: Original issue (scope expanded)
• ATB-11: Where missing categoryUri was first identified
• Future: Setup wizard to create initial forum/category/board structure