thread_subscriptions

0 rows where room_id = "!LoaBXycKruHTVYwHMK:matrix.jamesst.one"

✎ View and edit SQL

This data as json

0 records

CREATE TABLE thread_subscriptions (
  stream_id INTEGER NOT NULL PRIMARY KEY,
  instance_name TEXT NOT NULL,

  room_id TEXT NOT NULL,
  event_id TEXT NOT NULL,
  user_id TEXT NOT NULL,

  subscribed BOOLEAN NOT NULL,
  automatic BOOLEAN NOT NULL, unsubscribed_at_stream_ordering BIGINT, unsubscribed_at_topological_ordering BIGINT,

  CONSTRAINT thread_subscriptions_fk_users
    FOREIGN KEY (user_id)
    REFERENCES users(name),

  CONSTRAINT thread_subscriptions_fk_rooms
    FOREIGN KEY (room_id)
    -- When we delete a room, we should already have deleted all the events in that room
    -- and so there shouldn't be any subscriptions left in that room.
    -- So the `ON DELETE CASCADE` should be optional, but included anyway for good measure.
    REFERENCES rooms(room_id) ON DELETE CASCADE,

  CONSTRAINT thread_subscriptions_fk_events
    FOREIGN KEY (event_id)
    REFERENCES events(event_id) ON DELETE CASCADE,

  -- This order provides a useful index for:
  -- 1. foreign key constraint on (room_id)
  -- 2. foreign key constraint on (room_id, event_id)
  -- 3. finding the user's settings for a specific thread (as well as enforcing uniqueness)
  UNIQUE (room_id, event_id, user_id)
);
CREATE INDEX thread_subscriptions_user_room ON thread_subscriptions (user_id, room_id);
CREATE INDEX thread_subscriptions_by_user ON thread_subscriptions (user_id, stream_id);
CREATE INDEX thread_subscriptions_by_event ON thread_subscriptions (event_id);