Signal-Desktop/DATABASE_SCHEMA.md

Database Schema

Table: attachment_backup_jobs

CREATE TABLE attachment_backup_jobs (
  mediaName TEXT NOT NULL PRIMARY KEY,
  type TEXT NOT NULL,
  data TEXT NOT NULL,
  receivedAt INTEGER NOT NULL,
  -- job manager fields
  attempts INTEGER NOT NULL,
  active INTEGER NOT NULL,
  retryAfter INTEGER,
  lastAttemptTimestamp INTEGER
) STRICT

Index: attachment_backup_jobs → attachment_backup_jobs_receivedAt

CREATE INDEX attachment_backup_jobs_receivedAt ON attachment_backup_jobs (receivedAt)

Index: attachment_backup_jobs → attachment_backup_jobs_type_receivedAt

CREATE INDEX attachment_backup_jobs_type_receivedAt ON attachment_backup_jobs (type, receivedAt)

Index: attachment_backup_jobs → sqlite_autoindex_attachment_backup_jobs_1

(404: SQL Not Found)

---

Table: attachment_downloads

CREATE TABLE "attachment_downloads" (
  messageId TEXT NOT NULL REFERENCES messages (id) ON DELETE CASCADE,
  attachmentType TEXT NOT NULL,
  attachmentSignature TEXT NOT NULL,
  receivedAt INTEGER NOT NULL,
  sentAt INTEGER NOT NULL,
  contentType TEXT NOT NULL,
  size INTEGER NOT NULL,
  attachmentJson TEXT NOT NULL,
  active INTEGER NOT NULL,
  attempts INTEGER NOT NULL,
  retryAfter INTEGER,
  lastAttemptTimestamp INTEGER,
  source TEXT NOT NULL DEFAULT standard,
  ciphertextSize INTEGER NOT NULL DEFAULT 0,
  originalSource TEXT NOT NULL DEFAULT standard,
  PRIMARY KEY (
    messageId,
    attachmentType,
    attachmentSignature
  )
) STRICT

Index: attachment_downloads → attachment_downloads_active_messageId

CREATE INDEX attachment_downloads_active_messageId ON attachment_downloads (active, messageId)

Index: attachment_downloads → attachment_downloads_active_receivedAt

CREATE INDEX attachment_downloads_active_receivedAt ON attachment_downloads (active, receivedAt)

Index: attachment_downloads → attachment_downloads_active_source_receivedAt

CREATE INDEX attachment_downloads_active_source_receivedAt ON attachment_downloads (
  active,
  source,
  receivedAt
)

Index: attachment_downloads → attachment_downloads_messageId

CREATE INDEX attachment_downloads_messageId ON attachment_downloads (messageId)

Index: attachment_downloads → attachment_downloads_source_ciphertextSize

CREATE INDEX attachment_downloads_source_ciphertextSize ON attachment_downloads (
  source,
  ciphertextSize
)

Index: attachment_downloads → sqlite_autoindex_attachment_downloads_1

(404: SQL Not Found)

Trigger: attachment_downloads → attachment_downloads_backup_job_delete

CREATE TRIGGER attachment_downloads_backup_job_delete AFTER DELETE ON attachment_downloads WHEN OLD.originalSource = 'backup_import' BEGIN
UPDATE attachment_downloads_backup_stats
SET
  completedBytes = completedBytes + OLD.ciphertextSize
WHERE
  id = 0;

END

Trigger: attachment_downloads → attachment_downloads_backup_job_insert

CREATE TRIGGER attachment_downloads_backup_job_insert AFTER INSERT ON attachment_downloads WHEN NEW.originalSource = 'backup_import' BEGIN
UPDATE attachment_downloads_backup_stats
SET
  totalBytes = totalBytes + NEW.ciphertextSize;

END

Trigger: attachment_downloads → attachment_downloads_backup_job_update

CREATE TRIGGER attachment_downloads_backup_job_update AFTER
UPDATE OF ciphertextSize ON attachment_downloads WHEN NEW.originalSource = 'backup_import' BEGIN
UPDATE attachment_downloads_backup_stats
SET
  totalBytes = MAX(
    0,
    totalBytes - OLD.ciphertextSize + NEW.ciphertextSize
  )
WHERE
  id = 0;

END

---

Table: attachment_downloads_backup_stats

CREATE TABLE attachment_downloads_backup_stats (
  id INTEGER PRIMARY KEY CHECK (id = 0),
  totalBytes INTEGER NOT NULL,
  completedBytes INTEGER NOT NULL
) STRICT

---

Table: attachments_protected_from_deletion

CREATE TABLE attachments_protected_from_deletion (
  path TEXT NOT NULL,
  reuseToken TEXT NOT NULL,
  PRIMARY KEY (path, reuseToken)
) STRICT

Index: attachments_protected_from_deletion → attachments_protected_from_deletion_reuseToken

CREATE INDEX attachments_protected_from_deletion_reuseToken ON attachments_protected_from_deletion (reuseToken)

Index: attachments_protected_from_deletion → sqlite_autoindex_attachments_protected_from_deletion_1

(404: SQL Not Found)

---

Table: backup_cdn_object_metadata

CREATE TABLE backup_cdn_object_metadata (
  mediaId TEXT NOT NULL PRIMARY KEY,
  cdnNumber INTEGER NOT NULL,
  sizeOnBackupCdn INTEGER
) STRICT

Index: backup_cdn_object_metadata → sqlite_autoindex_backup_cdn_object_metadata_1

(404: SQL Not Found)

---

Table: badgeImageFiles

CREATE TABLE badgeImageFiles (
  badgeId TEXT REFERENCES badges (id) ON DELETE CASCADE ON UPDATE CASCADE,
  'order' INTEGER NOT NULL,
  url TEXT NOT NULL,
  localPath TEXT,
  theme TEXT NOT NULL
)

---

Table: badges

CREATE TABLE badges (
  id TEXT PRIMARY KEY,
  category TEXT NOT NULL,
  name TEXT NOT NULL,
  descriptionTemplate TEXT NOT NULL
)

Index: badges → sqlite_autoindex_badges_1

(404: SQL Not Found)

---

Table: callLinks

CREATE TABLE callLinks (
  roomId TEXT NOT NULL PRIMARY KEY,
  rootKey BLOB NOT NULL,
  adminKey BLOB,
  name TEXT NOT NULL,
  -- Enum which stores CallLinkRestrictions from ringrtc
  restrictions INTEGER NOT NULL,
  revoked INTEGER NOT NULL,
  expiration INTEGER,
  deleted INTEGER NOT NULL DEFAULT 0,
  storageID TEXT,
  storageVersion INTEGER,
  storageUnknownFields BLOB,
  storageNeedsSync INTEGER NOT NULL DEFAULT 0,
  deletedAt INTEGER
) STRICT

Index: callLinks → callLinks_adminKey

CREATE INDEX callLinks_adminKey ON callLinks (adminKey)

Index: callLinks → callLinks_deleted

CREATE INDEX callLinks_deleted ON callLinks (deleted, roomId)

Index: callLinks → sqlite_autoindex_callLinks_1

(404: SQL Not Found)

---

Table: callsHistory

CREATE TABLE callsHistory (
  callId TEXT PRIMARY KEY,
  peerId TEXT NOT NULL, -- conversation id (legacy) | uuid | groupId | roomId
  ringerId TEXT DEFAULT NULL, -- ringer uuid
  mode TEXT NOT NULL, -- enum "Direct" | "Group"
  type TEXT NOT NULL, -- enum "Audio" | "Video" | "Group"
  direction TEXT NOT NULL, -- enum "Incoming" | "Outgoing
  -- Direct: enum "Pending" | "Missed" | "Accepted" | "Deleted"
  -- Group: enum "GenericGroupCall" | "OutgoingRing" | "Ringing" | "Joined" | "Missed" | "Declined" | "Accepted" | "Deleted"
  status TEXT NOT NULL,
  timestamp INTEGER NOT NULL,
  startedById TEXT DEFAULT NULL,
  endedTimestamp INTEGER DEFAULT NULL,
  UNIQUE (callId, peerId)
  ON CONFLICT FAIL
)

Index: callsHistory → callsHistory_byConversation_order

CREATE INDEX callsHistory_byConversation_order ON callsHistory (
  peerId,
  timestamp DESC,
  callId
)

Index: callsHistory → callsHistory_callAndGroupInfo_optimize

CREATE INDEX callsHistory_callAndGroupInfo_optimize on callsHistory (
  direction,
  peerId,
  timestamp DESC,
  status
)

Index: callsHistory → callsHistory_incoming_missed

CREATE INDEX callsHistory_incoming_missed ON callsHistory (
  callId,
  status,
  direction
)
WHERE
  status IS 'Missed'
  AND direction IS 'Incoming'

Index: callsHistory → callsHistory_order

CREATE INDEX callsHistory_order ON callsHistory (
  timestamp DESC,
  callId,
  peerId
)

Index: callsHistory → sqlite_autoindex_callsHistory_1

(404: SQL Not Found)

Index: callsHistory → sqlite_autoindex_callsHistory_2

(404: SQL Not Found)

---

Table: chatFolders

CREATE TABLE chatFolders (
  id TEXT NOT NULL PRIMARY KEY,
  folderType INTEGER NOT NULL,
  name TEXT NOT NULL,
  position INTEGER NOT NULL,
  showOnlyUnread INTEGER NOT NULL,
  showMutedChats INTEGER NOT NULL,
  includeAllIndividualChats INTEGER NOT NULL,
  includeAllGroupChats INTEGER NOT NULL,
  includedConversationIds TEXT NOT NULL,
  excludedConversationIds TEXT NOT NULL,
  deletedAtTimestampMs INTEGER NOT NULL,
  storageID TEXT,
  storageVersion INTEGER,
  storageUnknownFields BLOB,
  storageNeedsSync INTEGER NOT NULL
) STRICT

Index: chatFolders → chatFolders_by_position

CREATE INDEX chatFolders_by_position on chatFolders (position)

Index: chatFolders → sqlite_autoindex_chatFolders_1

(404: SQL Not Found)

---

Table: conversations

CREATE TABLE conversations (
  id STRING PRIMARY KEY ASC,
  json TEXT,
  active_at INTEGER,
  type STRING,
  members TEXT,
  name TEXT,
  profileName TEXT,
  profileFamilyName TEXT,
  profileFullName TEXT,
  e164 TEXT,
  serviceId TEXT,
  groupId TEXT,
  profileLastFetchedAt INTEGER,
  expireTimerVersion INTEGER NOT NULL DEFAULT 1
)

Index: conversations → conversations_active

CREATE INDEX conversations_active ON conversations (active_at)
WHERE
  active_at IS NOT NULL

Index: conversations → conversations_e164

CREATE INDEX conversations_e164 ON conversations (e164)

Index: conversations → conversations_groupId

CREATE INDEX conversations_groupId ON conversations (groupId)

Index: conversations → conversations_serviceId

CREATE INDEX conversations_serviceId ON conversations (serviceId)

Index: conversations → conversations_type

CREATE INDEX conversations_type ON conversations (type)
WHERE
  type IS NOT NULL

Index: conversations → sqlite_autoindex_conversations_1

(404: SQL Not Found)

---

Table: defunctCallLinks

CREATE TABLE defunctCallLinks (
  roomId TEXT NOT NULL PRIMARY KEY,
  rootKey BLOB NOT NULL,
  adminKey BLOB,
  storageID TEXT,
  storageVersion INTEGER,
  storageUnknownFields BLOB,
  storageNeedsSync INTEGER NOT NULL DEFAULT 0
) STRICT

Index: defunctCallLinks → sqlite_autoindex_defunctCallLinks_1

(404: SQL Not Found)

---

Table: donationReceipts

CREATE TABLE donationReceipts (
  id TEXT NOT NULL PRIMARY KEY,
  currencyType TEXT NOT NULL,
  paymentAmount INTEGER NOT NULL,
  timestamp INTEGER NOT NULL
) STRICT

Index: donationReceipts → donationReceipts_byTimestamp

CREATE INDEX donationReceipts_byTimestamp on donationReceipts (timestamp)

Index: donationReceipts → sqlite_autoindex_donationReceipts_1

(404: SQL Not Found)

---

Table: edited_messages

CREATE TABLE edited_messages (
  messageId STRING REFERENCES messages (id) ON DELETE CASCADE,
  sentAt INTEGER,
  readStatus INTEGER,
  conversationId STRING
)

Index: edited_messages → edited_messages_messageId

CREATE INDEX edited_messages_messageId ON edited_messages (messageId)

Index: edited_messages → edited_messages_sent_at

CREATE INDEX edited_messages_sent_at ON edited_messages (sentAt)

Index: edited_messages → edited_messages_unread

CREATE INDEX edited_messages_unread ON edited_messages (
  readStatus,
  conversationId
)

---

Table: groupCallRingCancellations

CREATE TABLE groupCallRingCancellations (
  ringId INTEGER PRIMARY KEY,
  createdAt INTEGER NOT NULL
)

---

Table: groupSendCombinedEndorsement

CREATE TABLE groupSendCombinedEndorsement (
  groupId TEXT NOT NULL PRIMARY KEY, -- Only one endorsement per group
  expiration INTEGER NOT NULL, -- Unix timestamp in seconds
  endorsement BLOB NOT NULL
) STRICT

Index: groupSendCombinedEndorsement → sqlite_autoindex_groupSendCombinedEndorsement_1

(404: SQL Not Found)

---

Table: groupSendMemberEndorsement

CREATE TABLE groupSendMemberEndorsement (
  groupId TEXT NOT NULL,
  memberAci TEXT NOT NULL,
  expiration INTEGER NOT NULL, -- Unix timestamp in seconds
  endorsement BLOB NOT NULL,
  PRIMARY KEY (groupId, memberAci) -- Only one endorsement per group member
) STRICT

Index: groupSendMemberEndorsement → sqlite_autoindex_groupSendMemberEndorsement_1

(404: SQL Not Found)

---

Table: identityKeys

CREATE TABLE identityKeys (
  id STRING PRIMARY KEY ASC,
  json TEXT
)

Index: identityKeys → sqlite_autoindex_identityKeys_1

(404: SQL Not Found)

---

Table: items

CREATE TABLE items (
  id STRING PRIMARY KEY ASC,
  json TEXT
)

Index: items → sqlite_autoindex_items_1

(404: SQL Not Found)

---

Table: jobs

CREATE TABLE jobs (
  id TEXT PRIMARY KEY,
  queueType TEXT STRING NOT NULL,
  timestamp INTEGER NOT NULL,
  data STRING TEXT
)

Index: jobs → jobs_timestamp

CREATE INDEX jobs_timestamp ON jobs (timestamp)

Index: jobs → sqlite_autoindex_jobs_1

(404: SQL Not Found)

---

Table: key_transparency_account_data

CREATE TABLE key_transparency_account_data (
  aci TEXT NOT NULL PRIMARY KEY,
  data BLOB NOT NULL
) STRICT

Index: key_transparency_account_data → sqlite_autoindex_key_transparency_account_data_1

(404: SQL Not Found)

---

Table: kyberPreKey_triples

CREATE TABLE kyberPreKey_triples (
  id TEXT NOT NULL REFERENCES kyberPreKeys (id) ON DELETE CASCADE,
  signedPreKeyId INTEGER NOT NULL,
  baseKey BLOB NOT NULL,
  UNIQUE (
    id,
    signedPreKeyId,
    baseKey
  )
  ON CONFLICT FAIL
) STRICT

Index: kyberPreKey_triples → sqlite_autoindex_kyberPreKey_triples_1

(404: SQL Not Found)

---

Table: kyberPreKeys

CREATE TABLE kyberPreKeys (
  id STRING PRIMARY KEY NOT NULL,
  json TEXT NOT NULL,
  ourServiceId NUMBER GENERATED ALWAYS AS (
    json_extract(
      json,
      '$.ourServiceId'
    )
  )
)

Index: kyberPreKeys → kyberPreKeys_ourServiceId

CREATE INDEX kyberPreKeys_ourServiceId ON kyberPreKeys (ourServiceId)

Index: kyberPreKeys → sqlite_autoindex_kyberPreKeys_1

(404: SQL Not Found)

---

Table: megaphones

CREATE TABLE megaphones (
  id TEXT NOT NULL PRIMARY KEY,
  desktopMinVersion TEXT,
  priority INTEGER NOT NULL,
  dontShowBeforeEpochMs INTEGER NOT NULL,
  dontShowAfterEpochMs INTEGER NOT NULL,
  showForNumberOfDays INTEGER NOT NULL,
  primaryCtaId TEXT,
  secondaryCtaId TEXT,
  primaryCtaDataJson TEXT,
  secondaryCtaDataJson TEXT,
  conditionalId TEXT,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  primaryCtaText TEXT,
  secondaryCtaText TEXT,
  imagePath TEXT,
  localeFetched TEXT NOT NULL,
  shownAt INTEGER,
  snoozedAt INTEGER,
  snoozeCount INTEGER NOT NULL,
  isFinished INTEGER NOT NULL
) STRICT

Index: megaphones → sqlite_autoindex_megaphones_1

(404: SQL Not Found)

---

Table: mentions

CREATE TABLE mentions (
  messageId REFERENCES messages (id) ON DELETE CASCADE,
  mentionAci STRING,
  start INTEGER,
  length INTEGER
)

Index: mentions → mentions_aci

CREATE INDEX mentions_aci ON mentions (mentionAci)

Index: mentions → mentions_messageId

CREATE INDEX mentions_messageId ON mentions (messageId)

---

Table: message_attachments

CREATE TABLE message_attachments (
  messageId TEXT NOT NULL REFERENCES messages (id) ON DELETE CASCADE,
  -- For editHistoryIndex to be part of the primary key, it cannot be NULL in strict tables.
  -- For that reason, we use a value of -1 to indicate that it is the root message (not in editHistory)
  editHistoryIndex INTEGER NOT NULL,
  attachmentType TEXT NOT NULL, -- 'long-message' | 'quote' | 'attachment' | 'preview' | 'contact' | 'sticker'
  orderInMessage INTEGER NOT NULL,
  conversationId TEXT NOT NULL,
  sentAt INTEGER NOT NULL,
  clientUuid TEXT,
  size INTEGER NOT NULL,
  contentType TEXT NOT NULL,
  path TEXT,
  plaintextHash TEXT,
  localKey TEXT,
  caption TEXT,
  fileName TEXT,
  blurHash TEXT,
  height INTEGER,
  width INTEGER,
  digest TEXT,
  key TEXT,
  downloadPath TEXT,
  version INTEGER,
  incrementalMac TEXT,
  incrementalMacChunkSize INTEGER,
  transitCdnKey TEXT,
  transitCdnNumber INTEGER,
  transitCdnUploadTimestamp INTEGER,
  backupCdnNumber INTEGER,
  thumbnailPath TEXT,
  thumbnailSize INTEGER,
  thumbnailContentType TEXT,
  thumbnailLocalKey TEXT,
  thumbnailVersion INTEGER,
  screenshotPath TEXT,
  screenshotSize INTEGER,
  screenshotContentType TEXT,
  screenshotLocalKey TEXT,
  screenshotVersion INTEGER,
  backupThumbnailPath TEXT,
  backupThumbnailSize INTEGER,
  backupThumbnailContentType TEXT,
  backupThumbnailLocalKey TEXT,
  backupThumbnailVersion INTEGER,
  storyTextAttachmentJson TEXT,
  localBackupPath TEXT,
  flags INTEGER,
  error INTEGER,
  wasTooBig INTEGER,
  isCorrupted INTEGER,
  copiedFromQuotedAttachment INTEGER,
  pending INTEGER,
  backfillError INTEGER,
  messageType TEXT,
  receivedAt INTEGER,
  receivedAtMs INTEGER,
  isViewOnce INTEGER,
  duration REAL,
  PRIMARY KEY (
    messageId,
    editHistoryIndex,
    attachmentType,
    orderInMessage
  )
) STRICT

Index: message_attachments → message_attachments_backupThumbnailPath

CREATE INDEX message_attachments_backupThumbnailPath ON message_attachments (backupThumbnailPath)

Index: message_attachments → message_attachments_getOlderMedia

CREATE INDEX message_attachments_getOlderMedia ON message_attachments (
  conversationId,
  attachmentType,
  receivedAt DESC,
  sentAt DESC
)
WHERE
  editHistoryIndex IS -1
  AND messageType IN (
    'incoming',
    'outgoing'
  )
  AND isViewOnce IS NOT 1

Index: message_attachments → message_attachments_path

CREATE INDEX message_attachments_path ON message_attachments (path)

Index: message_attachments → message_attachments_plaintextHash

CREATE INDEX message_attachments_plaintextHash ON message_attachments (plaintextHash)

Index: message_attachments → message_attachments_screenshotPath

CREATE INDEX message_attachments_screenshotPath ON message_attachments (screenshotPath)

Index: message_attachments → message_attachments_sortBiggerMedia

CREATE INDEX message_attachments_sortBiggerMedia ON message_attachments (
  conversationId,
  attachmentType,
  size DESC,
  receivedAt DESC,
  sentAt DESC
)
WHERE
  editHistoryIndex IS -1
  AND messageType IN (
    'incoming',
    'outgoing'
  )
  AND isViewOnce IS NOT 1

Index: message_attachments → message_attachments_thumbnailPath

CREATE INDEX message_attachments_thumbnailPath ON message_attachments (thumbnailPath)

Index: message_attachments → sqlite_autoindex_message_attachments_1

(404: SQL Not Found)

---

Table: messages

CREATE TABLE messages (
  rowid INTEGER PRIMARY KEY ASC,
  id STRING UNIQUE,
  json TEXT,
  readStatus INTEGER,
  expires_at INTEGER,
  sent_at INTEGER,
  schemaVersion INTEGER,
  conversationId STRING,
  received_at INTEGER,
  hasAttachments INTEGER,
  hasFileAttachments INTEGER,
  hasVisualMediaAttachments INTEGER,
  expireTimer INTEGER,
  expirationStartTimestamp INTEGER,
  type STRING,
  body TEXT,
  messageTimer INTEGER,
  messageTimerStart INTEGER,
  messageTimerExpiresAt INTEGER,
  isErased INTEGER,
  isViewOnce INTEGER,
  sourceServiceId TEXT,
  serverGuid STRING NULL,
  sourceDevice INTEGER,
  storyId STRING,
  isStory INTEGER GENERATED ALWAYS AS (type IS 'story'),
  isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0,
  isTimerChangeFromSync INTEGER GENERATED ALWAYS AS (
    json_extract(
      json,
      '$.expirationTimerUpdate.fromSync'
    ) IS 1
  ),
  seenStatus NUMBER default 0,
  storyDistributionListId STRING,
  expiresAt INT GENERATED ALWAYS AS (
    ifnull(
      expirationStartTimestamp + (expireTimer * 1000),
      9007199254740991
    )
  ),
  isUserInitiatedMessage INTEGER GENERATED ALWAYS AS (
    type IS NULL
    OR type NOT IN (
      'change-number-notification',
      'contact-removed-notification',
      'conversation-merge',
      'group-v1-migration',
      'group-v2-change',
      'keychange',
      'message-history-unsynced',
      'profile-change',
      'story',
      'universal-timer-notification',
      'verified-change'
    )
  ),
  mentionsMe INTEGER NOT NULL DEFAULT 0,
  isGroupLeaveEvent INTEGER GENERATED ALWAYS AS (
    type IS 'group-v2-change'
    AND json_array_length(
      json_extract(
        json,
        '$.groupV2Change.details'
      )
    ) IS 1
    AND json_extract(
      json,
      '$.groupV2Change.details[0].type'
    ) IS 'member-remove'
    AND json_extract(
      json,
      '$.groupV2Change.from'
    ) IS NOT NULL
    AND json_extract(
      json,
      '$.groupV2Change.from'
    ) IS json_extract(
      json,
      '$.groupV2Change.details[0].aci'
    )
  ),
  isGroupLeaveEventFromOther INTEGER GENERATED ALWAYS AS (
    isGroupLeaveEvent IS 1
    AND isChangeCreatedByUs IS 0
  ),
  callId TEXT GENERATED ALWAYS AS (
    json_extract(json, '$.callId')
  ),
  shouldAffectPreview INTEGER GENERATED ALWAYS AS (
    type IS NULL
    OR type NOT IN (
      'change-number-notification',
      'contact-removed-notification',
      'conversation-merge',
      'group-v1-migration',
      'keychange',
      'message-history-unsynced',
      'profile-change',
      'story',
      'universal-timer-notification',
      'verified-change'
    )
    AND NOT (
      type IS 'message-request-response-event'
      AND json_extract(
        json,
        '$.messageRequestResponseEvent'
      ) IN (
        'ACCEPT',
        'BLOCK',
        'UNBLOCK'
      )
    )
  ),
  shouldAffectActivity INTEGER GENERATED ALWAYS AS (
    type IS NULL
    OR type NOT IN (
      'change-number-notification',
      'contact-removed-notification',
      'conversation-merge',
      'group-v1-migration',
      'keychange',
      'message-history-unsynced',
      'profile-change',
      'story',
      'universal-timer-notification',
      'verified-change'
    )
    AND NOT (
      type IS 'message-request-response-event'
      AND json_extract(
        json,
        '$.messageRequestResponseEvent'
      ) IN (
        'ACCEPT',
        'BLOCK',
        'UNBLOCK'
      )
    )
  ),
  isAddressableMessage INTEGER GENERATED ALWAYS AS (
    type IS NULL
    OR type IN (
      'incoming',
      'outgoing'
    )
  ),
  timestamp INTEGER,
  received_at_ms INTEGER,
  unidentifiedDeliveryReceived INTEGER,
  serverTimestamp INTEGER,
  source TEXT,
  isSearchable INT GENERATED ALWAYS AS (
    isViewOnce IS NOT 1
    AND storyId IS NULL
  ) VIRTUAL,
  searchableText TEXT GENERATED ALWAYS AS (
    CASE
      WHEN json -> 'poll' IS NOT NULL THEN json -> 'poll' ->> 'question'
      ELSE body
    END
  ) VIRTUAL,
  hasUnreadPollVotes INTEGER NOT NULL DEFAULT 0,
  hasExpireTimer INTEGER NOT NULL GENERATED ALWAYS AS (
    COALESCE(expireTimer, 0) > 0
  ) VIRTUAL,
  hasPreviews INTEGER NOT NULL GENERATED ALWAYS AS (
    IFNULL(
      json_array_length(json, '$.preview'),
      0
    ) > 0
  ),
  hasContacts INTEGER NOT NULL GENERATED ALWAYS AS (
    IFNULL(
      json_array_length(json, '$.contact'),
      0
    ) > 0
  )
)

Index: messages → expiring_message_by_conversation_and_received_at

CREATE INDEX expiring_message_by_conversation_and_received_at ON messages (
  conversationId,
  storyId,
  expirationStartTimestamp,
  expireTimer,
  received_at
)
WHERE
  isStory IS 0
  AND type IS 'incoming'

Index: messages → message_user_initiated

CREATE INDEX message_user_initiated ON messages (
  conversationId,
  isUserInitiatedMessage
)

Index: messages → messages_activity

CREATE INDEX messages_activity ON messages (
  conversationId,
  shouldAffectActivity,
  isTimerChangeFromSync,
  isGroupLeaveEventFromOther,
  received_at,
  sent_at
)

Index: messages → messages_by_date_addressable

CREATE INDEX messages_by_date_addressable ON messages (
  conversationId,
  isAddressableMessage,
  received_at,
  sent_at
)

Index: messages → messages_by_date_addressable_nondisappearing

CREATE INDEX messages_by_date_addressable_nondisappearing ON messages (
  conversationId,
  isAddressableMessage,
  received_at,
  sent_at
)
WHERE
  expireTimer IS NULL

Index: messages → messages_by_distribution_list

CREATE INDEX messages_by_distribution_list ON messages (
  storyDistributionListId,
  received_at
)
WHERE
  storyDistributionListId IS NOT NULL

Index: messages → messages_by_storyId

CREATE INDEX messages_by_storyId ON messages (storyId)

Index: messages → messages_call

CREATE INDEX messages_call ON messages (
  type,
  conversationId,
  callId,
  sent_at
)
WHERE
  type IS 'call-history'

Index: messages → messages_callHistory_markReadBefore

CREATE INDEX messages_callHistory_markReadBefore ON messages (
  type,
  seenStatus,
  received_at DESC
)
WHERE
  type IS 'call-history'

Index: messages → messages_callHistory_markReadByConversationBefore

CREATE INDEX messages_callHistory_markReadByConversationBefore ON messages (
  type,
  conversationId,
  seenStatus,
  sent_at DESC
)
WHERE
  type IS 'call-history'

Index: messages → messages_callHistory_seenStatus

CREATE INDEX messages_callHistory_seenStatus ON messages (type, seenStatus)
WHERE
  type IS 'call-history'

Index: messages → messages_conversation

CREATE INDEX messages_conversation ON messages (
  conversationId,
  isStory,
  storyId,
  received_at,
  sent_at
)

Index: messages → messages_conversation_no_story_id

CREATE INDEX messages_conversation_no_story_id ON messages (
  conversationId,
  isStory,
  received_at,
  sent_at
)

Index: messages → messages_conversationId_expirationStartTimestamp

CREATE INDEX messages_conversationId_expirationStartTimestamp ON messages (
  conversationId,
  expirationStartTimestamp
)
WHERE
  hasExpireTimer IS 1

Index: messages → messages_expires_at

CREATE INDEX messages_expires_at ON messages (expiresAt)

Index: messages → messages_hasAttachments

CREATE INDEX messages_hasAttachments ON messages (
  conversationId,
  hasAttachments,
  received_at
)
WHERE
  type IS NOT 'story'
  AND storyId IS NULL

Index: messages → messages_hasContacts

CREATE INDEX messages_hasContacts ON messages (
  conversationId,
  received_at DESC,
  sent_at DESC
)
WHERE
  hasContacts IS 1
  AND isViewOnce IS NOT 1
  AND type IN (
    'incoming',
    'outgoing'
  )

Index: messages → messages_hasFileAttachments

CREATE INDEX messages_hasFileAttachments ON messages (
  conversationId,
  hasFileAttachments,
  received_at
)
WHERE
  type IS NOT 'story'
  AND storyId IS NULL

Index: messages → messages_hasPreviews

CREATE INDEX messages_hasPreviews ON messages (
  conversationId,
  received_at DESC,
  sent_at DESC
)
WHERE
  hasPreviews IS 1
  AND isViewOnce IS NOT 1
  AND type IN (
    'incoming',
    'outgoing'
  )

Index: messages → messages_hasVisualMediaAttachments

CREATE INDEX messages_hasVisualMediaAttachments ON messages (
  conversationId,
  isStory,
  storyId,
  hasVisualMediaAttachments,
  received_at,
  sent_at
)
WHERE
  hasVisualMediaAttachments IS 1

Index: messages → messages_id

CREATE INDEX messages_id ON messages (id ASC)

Index: messages → messages_isStory

CREATE INDEX messages_isStory ON messages (
  received_at,
  sent_at
)
WHERE
  isStory = 1

Index: messages → messages_preview

CREATE INDEX messages_preview ON messages (
  conversationId,
  shouldAffectPreview,
  isGroupLeaveEventFromOther,
  received_at,
  sent_at
)

Index: messages → messages_preview_without_story

CREATE INDEX messages_preview_without_story ON messages (
  conversationId,
  shouldAffectPreview,
  isGroupLeaveEventFromOther,
  received_at,
  sent_at
)
WHERE
  storyId IS NULL

Index: messages → messages_receipt

CREATE INDEX messages_receipt ON messages (sent_at)

Index: messages → messages_schemaVersion

CREATE INDEX messages_schemaVersion ON messages (schemaVersion)

Index: messages → messages_searchOrder

CREATE INDEX messages_searchOrder on messages (
  received_at,
  sent_at
)

Index: messages → messages_sourceServiceId

CREATE INDEX messages_sourceServiceId on messages (sourceServiceId)

Index: messages → messages_story_replies

CREATE INDEX messages_story_replies ON messages (
  storyId,
  received_at,
  sent_at
)
WHERE
  isStory IS 0

Index: messages → messages_unexpectedly_missing_expiration_start_timestamp

CREATE INDEX messages_unexpectedly_missing_expiration_start_timestamp ON messages (
  expireTimer,
  expirationStartTimestamp,
  type
)
WHERE
  expireTimer IS NOT NULL
  AND expirationStartTimestamp IS NULL

Index: messages → messages_unread

CREATE INDEX messages_unread ON messages (
  conversationId,
  readStatus,
  isStory,
  storyId,
  received_at,
  sent_at
)
WHERE
  readStatus IS NOT NULL

Index: messages → messages_unread_mentions

CREATE INDEX messages_unread_mentions ON messages (
  conversationId,
  readStatus,
  mentionsMe,
  isStory,
  storyId,
  received_at,
  sent_at
)
WHERE
  readStatus IS NOT NULL

Index: messages → messages_unread_mentions_no_story_id

CREATE INDEX messages_unread_mentions_no_story_id ON messages (
  conversationId,
  readStatus,
  mentionsMe,
  isStory,
  received_at,
  sent_at
)
WHERE
  isStory IS 0
  AND readStatus IS NOT NULL

Index: messages → messages_unread_no_story_id

CREATE INDEX messages_unread_no_story_id ON messages (
  conversationId,
  readStatus,
  isStory,
  received_at,
  sent_at
)
WHERE
  readStatus IS NOT NULL

Index: messages → messages_unread_poll_votes

CREATE INDEX messages_unread_poll_votes ON messages (
  conversationId,
  received_at
)
WHERE
  hasUnreadPollVotes = 1
  AND type IS 'outgoing'

Index: messages → messages_unseen_no_story

CREATE INDEX messages_unseen_no_story ON messages (
  conversationId,
  seenStatus,
  isStory,
  received_at,
  sent_at
)
WHERE
  seenStatus IS NOT NULL

Index: messages → messages_unseen_with_story

CREATE INDEX messages_unseen_with_story ON messages (
  conversationId,
  seenStatus,
  isStory,
  storyId,
  received_at,
  sent_at
)
WHERE
  seenStatus IS NOT NULL

Index: messages → messages_view_once

CREATE INDEX messages_view_once ON messages (isErased)
WHERE
  isViewOnce = 1

Index: messages → sqlite_autoindex_messages_1

(404: SQL Not Found)

Trigger: messages → messages_on_delete

CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN
DELETE FROM messages_fts
WHERE
  rowid = old.rowid;

DELETE FROM sendLogPayloads
WHERE
  id IN (
    SELECT
      payloadId
    FROM
      sendLogMessageIds
    WHERE
      messageId = old.id
  );

DELETE FROM reactions
WHERE
  rowid IN (
    SELECT
      rowid
    FROM
      reactions
    WHERE
      messageId = old.id
  );

DELETE FROM storyReads
WHERE
  storyId = old.storyId;

END

Trigger: messages → messages_on_insert

CREATE TRIGGER messages_on_insert AFTER INSERT ON messages WHEN new.isSearchable IS 1 BEGIN
INSERT INTO
  messages_fts (rowid, body)
VALUES
  (
    new.rowid,
    new.searchableText
  );

END

Trigger: messages → messages_on_insert_insert_mentions

CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages BEGIN
INSERT INTO
  mentions (
    messageId,
    mentionAci,
    start,
    length
  )
SELECT
  messages.id,
  bodyRanges.value ->> 'mentionAci' as mentionAci,
  bodyRanges.value ->> 'start' as start,
  bodyRanges.value ->> 'length' as length
FROM
  messages,
  json_each(
    messages.json ->> 'bodyRanges'
  ) as bodyRanges
WHERE
  bodyRanges.value ->> 'mentionAci' IS NOT NULL
  AND messages.id = new.id;

END

Trigger: messages → messages_on_update

CREATE TRIGGER messages_on_update AFTER
UPDATE ON messages WHEN new.isSearchable IS 1
AND old.searchableText IS NOT new.searchableText BEGIN
UPDATE messages_fts
SET
  body = new.searchableText
WHERE
  rowId = new.rowId;

END

Trigger: messages → messages_on_update_update_mentions

CREATE TRIGGER messages_on_update_update_mentions AFTER
UPDATE ON messages BEGIN
DELETE FROM mentions
WHERE
  messageId = new.id;

INSERT INTO
  mentions (
    messageId,
    mentionAci,
    start,
    length
  )
SELECT
  messages.id,
  bodyRanges.value ->> 'mentionAci' as mentionAci,
  bodyRanges.value ->> 'start' as start,
  bodyRanges.value ->> 'length' as length
FROM
  messages,
  json_each(
    messages.json ->> 'bodyRanges'
  ) as bodyRanges
WHERE
  bodyRanges.value ->> 'mentionAci' IS NOT NULL
  AND messages.id = new.id;

END

Trigger: messages → messages_on_view_once_update

CREATE TRIGGER messages_on_view_once_update AFTER
UPDATE ON messages WHEN new.body IS NOT NULL
AND new.isViewOnce = 1 BEGIN
DELETE FROM messages_fts
WHERE
  rowid = old.rowid;

END

---

Table: messages_fts

CREATE VIRTUAL TABLE messages_fts USING fts5 (
  body,
  tokenize = 'signal_tokenizer'
)

---

Table: messages_fts_config

CREATE TABLE 'messages_fts_config' (k PRIMARY KEY, v) WITHOUT ROWID

Index: messages_fts_config → sqlite_autoindex_messages_fts_config_1

(404: SQL Not Found)

---

Table: messages_fts_content

CREATE TABLE 'messages_fts_content' (
  id INTEGER PRIMARY KEY,
  c0
)

---

Table: messages_fts_data

CREATE TABLE 'messages_fts_data' (
  id INTEGER PRIMARY KEY,
  block BLOB
)

---

Table: messages_fts_docsize

CREATE TABLE 'messages_fts_docsize' (
  id INTEGER PRIMARY KEY,
  sz BLOB
)

---

Table: messages_fts_idx

CREATE TABLE 'messages_fts_idx' (
  segid,
  term,
  pgno,
  PRIMARY KEY (segid, term)
) WITHOUT ROWID

Index: messages_fts_idx → sqlite_autoindex_messages_fts_idx_1

(404: SQL Not Found)

---

Table: notificationProfiles

CREATE TABLE notificationProfiles (
  id TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  emoji TEXT,
  /* A numeric representation of a color, like 0xAARRGGBB */
  color INTEGER NOT NULL,
  createdAtMs INTEGER NOT NULL,
  allowAllCalls INTEGER NOT NULL,
  allowAllMentions INTEGER NOT NULL,
  /* A JSON array of conversationId strings */
  allowedMembersJson TEXT NOT NULL,
  scheduleEnabled INTEGER NOT NULL,
  /* 24-hour clock int, 0000-2359 (e.g., 15, 900, 1130, 2345) */
  scheduleStartTime INTEGER,
  scheduleEndTime INTEGER,
  /* A JSON object with true/false for each of the numbers in the Protobuf enum */
  scheduleDaysEnabledJson TEXT,
  deletedAtTimestampMs INTEGER,
  storageID TEXT,
  storageVersion INTEGER,
  storageUnknownFields BLOB,
  storageNeedsSync INTEGER NOT NULL DEFAULT 0
) STRICT

Index: notificationProfiles → sqlite_autoindex_notificationProfiles_1

(404: SQL Not Found)

---

Table: pinnedMessages

CREATE TABLE pinnedMessages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  conversationId TEXT NOT NULL REFERENCES conversations (id) ON DELETE CASCADE,
  messageId TEXT NOT NULL REFERENCES messages (id) ON DELETE CASCADE,
  pinnedAt INTEGER NOT NULL,
  expiresAt INTEGER,
  UNIQUE (
    conversationId,
    messageId
  )
) STRICT

Index: pinnedMessages → pinnedMessages_byConversation

CREATE INDEX pinnedMessages_byConversation ON pinnedMessages (
  conversationId,
  pinnedAt DESC,
  messageId
)

Index: pinnedMessages → pinnedMessages_byExpiresAt

CREATE INDEX pinnedMessages_byExpiresAt ON pinnedMessages (expiresAt ASC)
WHERE
  expiresAt IS NOT NULL

Index: pinnedMessages → sqlite_autoindex_pinnedMessages_1

(404: SQL Not Found)

---

Table: preKeys

CREATE TABLE preKeys (
  id STRING PRIMARY KEY ASC,
  json TEXT,
  ourServiceId NUMBER GENERATED ALWAYS AS (
    json_extract(
      json,
      '$.ourServiceId'
    )
  )
)

Index: preKeys → preKeys_ourServiceId

CREATE INDEX preKeys_ourServiceId ON preKeys (ourServiceId)

Index: preKeys → sqlite_autoindex_preKeys_1

(404: SQL Not Found)

---

Table: reactions

CREATE TABLE reactions (
  conversationId STRING,
  emoji STRING,
  fromId STRING,
  messageReceivedAt INTEGER,
  targetAuthorAci STRING,
  targetTimestamp INTEGER,
  unread INTEGER,
  messageId STRING,
  timestamp NUMBER
)

Index: reactions → reaction_identifier

CREATE INDEX reaction_identifier ON reactions (
  emoji,
  targetAuthorAci,
  targetTimestamp
)

Index: reactions → reactions_byTimestamp

CREATE INDEX reactions_byTimestamp ON reactions (fromId, timestamp)

Index: reactions → reactions_messageId

CREATE INDEX reactions_messageId ON reactions (messageId)

Index: reactions → reactions_unread

CREATE INDEX reactions_unread ON reactions (
  conversationId,
  unread
)

---

Table: recentEmojis

CREATE TABLE recentEmojis (
  emoji TEXT NOT NULL PRIMARY KEY,
  lastUsedAt INTEGER NOT NULL
) STRICT

Index: recentEmojis → recentEmojis_order

CREATE INDEX recentEmojis_order ON recentEmojis (lastUsedAt DESC)

Index: recentEmojis → sqlite_autoindex_recentEmojis_1

(404: SQL Not Found)

---

Table: recentGifs

CREATE TABLE recentGifs (
  id TEXT NOT NULL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  previewMedia_url TEXT NOT NULL,
  previewMedia_width INTEGER NOT NULL,
  previewMedia_height INTEGER NOT NULL,
  attachmentMedia_url TEXT NOT NULL,
  attachmentMedia_width INTEGER NOT NULL,
  attachmentMedia_height INTEGER NOT NULL,
  lastUsedAt INTEGER NOT NULL
) STRICT

Index: recentGifs → recentGifs_order

CREATE INDEX recentGifs_order ON recentGifs (lastUsedAt DESC)

Index: recentGifs → sqlite_autoindex_recentGifs_1

(404: SQL Not Found)

---

Table: senderKeys

CREATE TABLE senderKeys (
  id TEXT PRIMARY KEY NOT NULL,
  senderId TEXT NOT NULL,
  distributionId TEXT NOT NULL,
  data BLOB NOT NULL,
  lastUpdatedDate NUMBER NOT NULL
)

Index: senderKeys → sqlite_autoindex_senderKeys_1

(404: SQL Not Found)

---

Table: sendLogMessageIds

CREATE TABLE sendLogMessageIds (
  payloadId INTEGER NOT NULL,
  messageId STRING NOT NULL,
  PRIMARY KEY (
    payloadId,
    messageId
  ),
  CONSTRAINT sendLogMessageIdsForeignKey FOREIGN KEY (payloadId) REFERENCES sendLogPayloads (id) ON DELETE CASCADE
)

Index: sendLogMessageIds → sendLogMessageIdsByMessage

CREATE INDEX sendLogMessageIdsByMessage ON sendLogMessageIds (messageId)

Index: sendLogMessageIds → sqlite_autoindex_sendLogMessageIds_1

(404: SQL Not Found)

---

Table: sendLogPayloads

CREATE TABLE sendLogPayloads (
  id INTEGER PRIMARY KEY ASC,
  timestamp INTEGER NOT NULL,
  contentHint INTEGER NOT NULL,
  proto BLOB NOT NULL,
  urgent INTEGER,
  hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL
)

Index: sendLogPayloads → sendLogPayloadsByTimestamp

CREATE INDEX sendLogPayloadsByTimestamp ON sendLogPayloads (timestamp)

---

Table: sendLogRecipients

CREATE TABLE sendLogRecipients (
  payloadId INTEGER NOT NULL,
  recipientServiceId STRING NOT NULL,
  deviceId INTEGER NOT NULL,
  PRIMARY KEY (
    payloadId,
    recipientServiceId,
    deviceId
  ),
  CONSTRAINT sendLogRecipientsForeignKey FOREIGN KEY (payloadId) REFERENCES sendLogPayloads (id) ON DELETE CASCADE
)

Index: sendLogRecipients → sendLogRecipientsByRecipient

CREATE INDEX sendLogRecipientsByRecipient ON sendLogRecipients (
  recipientServiceId,
  deviceId
)

Index: sendLogRecipients → sqlite_autoindex_sendLogRecipients_1

(404: SQL Not Found)

---

Table: sessions

CREATE TABLE sessions (
  id TEXT NOT NULL PRIMARY KEY,
  ourServiceId TEXT NOT NULL,
  serviceId TEXT NOT NULL,
  conversationId TEXT NOT NULL,
  deviceId INTEGER NOT NULL,
  record BLOB NOT NULL
) STRICT

Index: sessions → sqlite_autoindex_sessions_1

(404: SQL Not Found)

---

Table: signedPreKeys

CREATE TABLE signedPreKeys (
  id STRING PRIMARY KEY ASC,
  json TEXT,
  ourServiceId NUMBER GENERATED ALWAYS AS (
    json_extract(
      json,
      '$.ourServiceId'
    )
  )
)

Index: signedPreKeys → signedPreKeys_ourServiceId

CREATE INDEX signedPreKeys_ourServiceId ON signedPreKeys (ourServiceId)

Index: signedPreKeys → sqlite_autoindex_signedPreKeys_1

(404: SQL Not Found)

---

Table: sqlite_sequence

CREATE TABLE sqlite_sequence (name, seq)

---

Table: sqlite_stat1

CREATE TABLE sqlite_stat1 (tbl, idx, stat)

---

Table: sqlite_stat4

CREATE TABLE sqlite_stat4 (
  tbl,
  idx,
  neq,
  nlt,
  ndlt,
  sample
)

---

Table: sticker_packs

CREATE TABLE sticker_packs (
  id TEXT PRIMARY KEY,
  key TEXT NOT NULL,
  author STRING,
  coverStickerId INTEGER,
  createdAt INTEGER,
  downloadAttempts INTEGER,
  installedAt INTEGER,
  lastUsed INTEGER,
  status STRING,
  stickerCount INTEGER,
  title STRING,
  attemptedStatus STRING,
  position INTEGER DEFAULT 0 NOT NULL,
  storageID STRING,
  storageVersion INTEGER,
  storageUnknownFields BLOB,
  storageNeedsSync INTEGER DEFAULT 0 NOT NULL
)

Index: sticker_packs → sqlite_autoindex_sticker_packs_1

(404: SQL Not Found)

Index: sticker_packs → sticker_packs_by_position_and_id

CREATE INDEX sticker_packs_by_position_and_id ON sticker_packs (
  position ASC,
  id ASC
)

---

Table: sticker_references

CREATE TABLE sticker_references (
  messageId STRING,
  packId TEXT,
  stickerId INTEGER NOT NULL DEFAULT -1,
  isUnresolved INTEGER NOT NULL DEFAULT 0,
  CONSTRAINT sticker_references_fk FOREIGN KEY (packId) REFERENCES sticker_packs (id) ON DELETE CASCADE
)

Index: sticker_references → unresolved_sticker_refs

CREATE INDEX unresolved_sticker_refs ON sticker_references (packId, stickerId)
WHERE
  isUnresolved IS 1

---

Table: stickers

CREATE TABLE stickers (
  id INTEGER NOT NULL,
  packId TEXT NOT NULL,
  emoji STRING,
  height INTEGER,
  isCoverOnly INTEGER,
  lastUsed INTEGER,
  path STRING,
  width INTEGER,
  version INTEGER NOT NULL DEFAULT 1,
  localKey TEXT,
  size INTEGER,
  PRIMARY KEY (id, packId),
  CONSTRAINT stickers_fk FOREIGN KEY (packId) REFERENCES sticker_packs (id) ON DELETE CASCADE
)

Index: stickers → sqlite_autoindex_stickers_1

(404: SQL Not Found)

Index: stickers → stickers_recents

CREATE INDEX stickers_recents ON stickers (lastUsed)
WHERE
  lastUsed IS NOT NULL

---

Table: storyDistributionMembers

CREATE TABLE storyDistributionMembers (
  listId STRING NOT NULL REFERENCES storyDistributions (id) ON DELETE CASCADE ON UPDATE CASCADE,
  serviceId STRING NOT NULL,
  PRIMARY KEY (listId, serviceId)
)

Index: storyDistributionMembers → sqlite_autoindex_storyDistributionMembers_1

(404: SQL Not Found)

---

Table: storyDistributions

CREATE TABLE storyDistributions (
  id STRING PRIMARY KEY NOT NULL,
  name TEXT,
  senderKeyInfoJson STRING,
  deletedAtTimestamp INTEGER,
  allowsReplies INTEGER,
  isBlockList INTEGER,
  storageID STRING,
  storageVersion INTEGER,
  storageUnknownFields BLOB,
  storageNeedsSync INTEGER
)

Index: storyDistributions → sqlite_autoindex_storyDistributions_1

(404: SQL Not Found)

---

Table: storyReads

CREATE TABLE storyReads (
  authorId STRING NOT NULL,
  conversationId STRING NOT NULL,
  storyId STRING NOT NULL,
  storyReadDate NUMBER NOT NULL,
  PRIMARY KEY (authorId, storyId)
)

Index: storyReads → sqlite_autoindex_storyReads_1

(404: SQL Not Found)

Index: storyReads → storyReads_data

CREATE INDEX storyReads_data ON storyReads (
  storyReadDate,
  authorId,
  conversationId
)

Index: storyReads → storyReads_storyId

CREATE INDEX storyReads_storyId ON storyReads (storyId)

---

Table: syncTasks

CREATE TABLE syncTasks (
  id TEXT PRIMARY KEY NOT NULL,
  attempts INTEGER NOT NULL,
  createdAt INTEGER NOT NULL,
  data TEXT NOT NULL,
  envelopeId TEXT NOT NULL,
  sentAt INTEGER NOT NULL,
  type TEXT NOT NULL
) STRICT

Index: syncTasks → sqlite_autoindex_syncTasks_1

(404: SQL Not Found)

Index: syncTasks → syncTasks_delete

CREATE INDEX syncTasks_delete ON syncTasks (attempts DESC)

Index: syncTasks → syncTasks_type

CREATE INDEX syncTasks_type ON syncTasks (type)

---

Table: uninstalled_sticker_packs

CREATE TABLE uninstalled_sticker_packs (
  id STRING NOT NULL PRIMARY KEY,
  uninstalledAt NUMBER NOT NULL,
  storageID STRING,
  storageVersion NUMBER,
  storageUnknownFields BLOB,
  storageNeedsSync INTEGER NOT NULL
)

Index: uninstalled_sticker_packs → sqlite_autoindex_uninstalled_sticker_packs_1

(404: SQL Not Found)

---

Table: unprocessed

CREATE TABLE unprocessed (
  id TEXT NOT NULL PRIMARY KEY ASC,
  type INTEGER NOT NULL,
  timestamp INTEGER NOT NULL,
  attempts INTEGER NOT NULL,
  receivedAtCounter INTEGER NOT NULL,
  urgent INTEGER NOT NULL,
  story INTEGER NOT NULL,
  serverGuid TEXT NOT NULL,
  serverTimestamp INTEGER NOT NULL,
  isEncrypted INTEGER NOT NULL,
  content BLOB NOT NULL,
  messageAgeSec INTEGER NOT NULL,
  destinationServiceId TEXT NOT NULL,
  -- Not present for 1:1 messages and not sealed messages
  groupId TEXT,
  -- Not present for sealed envelopes
  reportingToken BLOB,
  source TEXT,
  sourceServiceId TEXT,
  updatedPni TEXT,
  sourceDevice INTEGER,
  receivedAtDate INTEGER DEFAULT 0 NOT NULL
) STRICT

Index: unprocessed → sqlite_autoindex_unprocessed_1

(404: SQL Not Found)

Index: unprocessed → unprocessed_byReceivedAtCounter

CREATE INDEX unprocessed_byReceivedAtCounter ON unprocessed (receivedAtCounter)

Index: unprocessed → unprocessed_byReceivedAtDate

CREATE INDEX unprocessed_byReceivedAtDate ON unprocessed (receivedAtDate)

---