Boards UUIDs

[Thanks to Sel for the work!]

Currently, the backend uses the board slug as a parameter to perform operations on a board. However, once realms are implemented, board names will no longer be unique. Instead, each board should be assigned a uuid. This uuid will be used to uniquely identify the board, regardless of what realm it may be in.

Notes

Database

• the boards table will need an additional column for UUIDs.
  • How to generate uuids?
    • Currently, thread uuids are generated in javascript and passed to the database, and stored as text.
    • However, Ms. Boba is considering upgrading the database to Postgres 13, which allows in-database generation of UUIDs.
    • Note from future Ms. Boba: actually UUIDs should not be generated by the DB as that makes it harder to change id generation format/logic in the future if deemed necessary.
  • For the thread table the uuid is already separate from the primary id
    • There is a standard incrementing int id for threads that is used as index, and then a thread-specific UUID.

Backend

• All SQL queries will need to use this UUID column instead of the name column when searching for tables.
• All API endpoints will need to take a UUID as a parameter, rather than the current name.

Frontend

• The frontend should use the unique combination of realm slug + board slug to fetch the appropriate uuid. This uuid should be used for all queries to the backend.
  • This will almost certainly require a new API endpoint dedicated to retrieving the uuid.

Files to Update

Database

• init/
  • 000_init.sql
  • 100_views.sql
    • thread_details view
      • Added boards.id, boards.string_id as columns
      • Removed board.slug as column
  • 200_settings.sql
• test_db_init/
  • 01_insert.sql
  • 04_muting_insert.sql
  • 11_visibility.sql

Backend

• Types.ts

  • The following types should be modified to use a uuid rather than the slug to identify the parent board.
    • ServerThreadType
    • DbPostType
    • DbThreadType
    • DbActivityThreadType
  • BoardDescription : may need to add a field for uuid
  • DbBoardMetadata : Add a field for the uuid.

• server/

  • admin/
    • queries.ts
      • createBoardsIfNotExist() : needs to be updated to generate and insert a uuid in the query
    • routes.ts
      • /generate/boards POST : calls createBoardsIfNotExist(). May not need to be changed.
  • all-routes.ts
  • boards/
    • queries.ts
      • Functions that take a slug should be changed to use a uuid, and renamed/duplicated if necessary.
        • getBoardBySlug()
        • updateBoardMetadata()
        • getBoardActivityBySlug()
        • markBoardVisit()
        • muteBoard()
        • unmuteBoard()
        • pinBoard()
        • unpinBoard()
        • dismissBoardNotifications()
    • routes.ts
      • Routes that use a board slug should be modified to use the board uuid instead. Their documentation should be updated as necessary.
        • boards/{slug} GET
        • boards/{slug}/metadata/update POST
        • boards/{slug}/visit GET
        • boards/{slug}/mute POST
        • boards/{slug}/mute DELETE
        • boards/{slug}/pin POST
        • boards/{slug}/pin DELETE
        • boards/{slug}/notifications/dismiss POST
        • boards/{slug}/activity/latest GET
    • sql/
      • all-boards.sql
        • might update to include board.string_id in the SELECT statement?
      • board-activity-by-slug.sql
        • add board_string_id into the SELECT statement
        • modify to use board.string_id for the INNER JOIN on thread_details and the WHERE clause.
        • Should be renamed or duplicated into board-activity-by-uuid.sql
      • board-by-slug.sql
        • might update to include board.string_id in the SELECT statement?
        • modify to use board.string_id for the WHERE clause.
        • Also should be renamed or duplicated into board-by-uuid.sql
      • index.ts
        • Query strings that use the board slug as a parameter should be updated to use the uuid instead, and renamed/duplicated as necessary.
          • markBoardVisit
          • deleteSectionCategories
          • deleteSection
          • updateSection
          • createSection
          • muteBoardBySlug
          • unmuteBoardBySlug
          • pinBoardBySlug
          • unpinBoardBySlug
          • dismissNotificationsBySlug
          • updateBoardSettings
    • tests/
      • Tests that use slugs will need to be updated to use uuids instead.
        • activity.test.ts
        • by-slug.test.ts
        • data-all.test.ts
        • metadata.test.ts
        • notifications.test.ts
        • pagination.test.ts
        • rest-api.test.ts
        • restricted.test.ts
    • utils.ts
      • Functions that take a slug should be changed to use a uuid, and renamed/duplicated if necessary.
        • getBoardMetadata()
          • additional note: needs to use string_id for caching rather than slug
        • canAccessBoard()
  • cache.ts
    • Update CacheKeys.BOARD enum comment
  • db-pool.ts
  • index.ts
  • posts/
    • queries.ts
      • The following functions are dependent on the thread_details view and should be updated accordingly.
        • getThreadDetails()
        • postNewContribution()
          • postNewContribution returns the board slug, and this data is used elsewhere (by post/:post_id/contribution, which uses it to call maybeUpdateSubscriptionsOnThreadChange)
      • addNewIdentityToThread() : calls threadsSql.getRoleByStringId() which takes a board slug as a parameter
      • getUserPermissionsForPost() : calls getBoardBySlug() from boards/
    • routes.ts
      • The following endpoint uses the board slug and should be updated accordingly.
        • /:post_id/contribution POST
    • sql/
      • get-thread-details.sql
        • add board.string_id to SELECT
        • remove board.slug from SELECT?
      • index.ts
      • post-by-string-id.sql
        • add board.string_id to SELECT
        • remove board.slug from SELECT?
    • tests/
      • queries.test.ts
      • restricted.test.ts
      • tags.test.ts
    • utils.ts
  • realms/
    • queries.ts
    • routes.ts
    • utils.ts
      • processRealmActivity() : change to use uuid as id rather than slug
  • subscriptions/
    • queries.ts
    • routes.ts
    • sql/
      • index.ts
      • subscription-activity-by-string-id.sql
    • tests/
      • queries.test.ts
      • utils.test.ts
        • calls maybeUpdateSubscriptionOnThreadChange()
    • utils.ts
      • maybeUpdateSubscriptionsOnThreadChange()
        • Note: uses board slug to generate thread URL.
  • threads/
    • queries.ts
      • Functions that take a slug should be changed to use a uuid, and renamed/duplicated if necessary.
        • createThread()
        • getTriggeredWebhooks()
        • moveThread()
      • getUserPermissionsForThread() : calls getBoardBySlug()
    • routes.ts
      • Routes that take a slug should be changed to use a uuid
        • /:id GET
        • /:boardSlug/create POST
          • Note: uses board slug to generate a log message.
          • Note: uses board slug to generate thread URL.
        • /:threadId/move POST
          • slug is in the request body
    • sql/
      • index.ts
        • createThread : uses board slug in subquery WHERE
        • getRoleByStringId : uses board slug in subquery WHERE
        • getThreadDetails : board slug in SELECT statement
        • getTriggeredWebhooks : board slug in WHERE
        • moveThread: board slug in subquery WHERE
      • thread-by-string-id.sql
        • board slug in SELECT
        • board slug in GROUP BY
      • visit-thread-by-string-id.sql
    • tests/
      • activity.test.ts
      • queries.test.ts
        • expect needs to be updated
      • routes.test.ts
        • expect needs to be updated
  • users/
    • queries.ts
    • routes.ts
      • Routes that use a slug should be changed to use a uuid
        • /@me GET : retrieves data via getBoards()
          • This seems to generate a summary - it may need the board slug.
        • /@me/notifications GET
        • /me/feed GET
          • board slug is used in function passed to userActivity.activity.map()
    • sql/
      • index.ts
      • user-feed-activity.sql
        • board slug in SELECT
    • tests/
      • queries.test.ts
      • routes.test.ts

• queries.sql

  • TOP POSTS BY USERS COUNT
    • SELECT uses slug (but this might be intentional. slug = name of board.)
    • GROUP BY uses slug
  • UPDATE BOARD AVATAR
    • WHERE uses slug to reference queerpub board
  • CREATE WEBHOOK SUBSCRIPTION
    • WHERE in first statement's subquery uses slug to reference bobaland board
    • WHERE in third statement's subsubquery uses slug to reference bobaland board
  • ADD NEW BOARD
    • Insertion of the volunteer board does not supply a uuid
  • LOCK BOARD
    • WHERE uses slug to reference volunteers board

• utils/

  • permission-utils.ts
  • queries-utils.ts
  • response-utils.ts
    • The following functions include a slug as part of the return object
      • extractLockedBoardMetadata()
      • processBoardsMetadata()
      • processBoardsNotifications
  • settings.ts
  • test-utils.ts

Notes

• How do we retrieve a board's UUID?

  • All API endpoints returning board data will also return their UUID.
  • To retrieve a board UUID given its slug (and parent realm id), we can use the /realms/{realm_id} endpoint and look for the board with the corresponding slug.
    • Realms are unlikely to grow large enough where this operation will incur significant costs.
  • If only the realm slug is know, the endpoint will be /realms/slug/{realm_slug}.

• How do we deal with log messages without board slug?

  • Log messages should use the board id.

• How do we deal with generating URLs (like thread urls) that use boards slugs?

  • We use the parent_board_id to retrieve the board/realm slug.

Frontend

TBD