step2.md

Messaging Data Modeling ℹ️ For technical support, please contact us via email or LinkedIn.

⬅️ Back Step 2 of 7 Next ➡️

Create tables

✅ Create table folders_by_user:

CREATE TABLE IF NOT EXISTS folders_by_user (
  username TEXT,
  label TEXT,
  color TEXT,
  PRIMARY KEY ((username),label)
);

✅ Create table unread_email_stats:

CREATE TABLE IF NOT EXISTS unread_email_stats (
  username TEXT,
  label TEXT,
  num_unread COUNTER,
  PRIMARY KEY ((username),label)
);

✅ Create table emails_by_user_folder:

CREATE TABLE IF NOT EXISTS emails_by_user_folder (
  username TEXT,
  label TEXT,
  id TIMEUUID,
  "from" TEXT,
  subject TEXT,
  is_read BOOLEAN,
  PRIMARY KEY ((username,label),id)
) WITH CLUSTERING ORDER BY (id DESC);

✅ Create table emails:

CREATE TABLE IF NOT EXISTS emails (
  id TIMEUUID,
  "to" LIST<TEXT>,
  "from" TEXT,
  subject TEXT,
  body TEXT,
  attachments MAP<TEXT,INT>,
  PRIMARY KEY ((id))
);

✅ Create table attachments:

CREATE TABLE IF NOT EXISTS attachments (
  email_id TIMEUUID,
  filename TEXT,
  chunk_number INT,
  type TEXT,
  value BLOB,
  PRIMARY KEY ((email_id,filename,chunk_number))
);

✅ Verify that the five tables have been created:

DESCRIBE TABLES;

⬅️ Back Next ➡️