Mental Model for Database Developers#

  ObjectType ────────►  ObjectType
       │                    ▲
       │ LinkType           │
       ▼                    │
  ObjectType ─────────►  LinkType  ────►  ObjectType
 
  EventType ──────►  ObjectType   (event references a thing)
  ObjectType ──────►  EventType   (thing was involved in event)

-- Postgres
CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT UNIQUE NOT NULL,
  phone       TEXT,
  country     TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE logins (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  ip          INET,
  user_agent  TEXT,
  occurred_at TIMESTAMPTZ NOT NULL
);
 
CREATE INDEX logins_user_idx ON logins(user_id, occurred_at DESC);

# RelataDB — schema-as-code, declarative
[User]
kind = "ObjectType"
properties =
  email    = { type = "Identity", canonical = "Email", unique = true }
  phone    = { type = "Identity", canonical = "Msisdn" }
  country  = { type = "String" }
cluster_key = "email"
 
[Login]
kind = "EventType"
properties =
  user_id    = { type = "Identity", link_to = "User" }
  ip         = { type = "Identity", canonical = "IPv4" }
  user_agent = { type = "String" }
cluster_key = "(user_id, occurred_at)"
 
[UserLoggedIn]
kind = "LinkType"
from = "User"
to   = "Login"

-- Postgres
SELECT u.email, COUNT(*) AS login_count
FROM users u
JOIN logins l ON l.user_id = u.id
WHERE l.occurred_at >= now() - INTERVAL '7 days'
GROUP BY u.email
ORDER BY login_count DESC
LIMIT 10;

-- RelataDB — same shape, plus audit by default
PURPOSE 'analytics'
SELECT u.email, COUNT(*) AS login_count
FROM User u
JOIN Login l ON l.user_id = u.email            -- identity-typed join
WHERE l.occurred_at >= now() - INTERVAL '7 days'
GROUP BY u.email
ORDER BY login_count DESC
LIMIT 10;

// MongoDB
db.users.insertOne({
  _id: ObjectId("..."),
  email: "alice@example.com",
  phone: "+1 415-555-0100",
  profile: { bio: "...", avatar_url: "..." },
  addresses: [
    { kind: "home", line1: "...", city: "...", country: "US" }
  ],
  tags: ["vip", "founder"]
});
 
db.events.insertOne({
  type: "page_view",
  user_id: ObjectId("..."),
  url: "/pricing",
  ts: new Date()
});

# RelataDB
[User]
kind = "ObjectType"
properties =
  email    = { type = "Identity", canonical = "Email", unique = true }
  phone    = { type = "Identity", canonical = "Msisdn" }
  bio      = { type = "String", indexed = "bm25" }
  avatar   = { type = "BlobRef" }
 
[Address]
kind = "ObjectType"
properties =
  user_email = { type = "Identity", canonical = "Email", link_to = "User" }
  kind       = { type = "String" }
  line1      = { type = "String" }
  city       = { type = "String" }
  country    = { type = "String" }
 
[UserTag]
kind = "ObjectType"
properties =
  user_email = { type = "Identity", canonical = "Email", link_to = "User" }
  tag        = { type = "String" }
 
[PageView]
kind = "EventType"
properties =
  user_email = { type = "Identity", canonical = "Email", link_to = "User" }
  url        = { type = "String" }

-- What was Alice's email on 1 March 2024?
PURPOSE 'audit' SELECT email FROM User
WHERE email = 'alice@example.com'
AS OF '2024-03-01T00:00:00Z';
 
-- What did we *believe* on 1 March 2024 (system-time)?
PURPOSE 'audit' SELECT email FROM User
AS OF SYSTEM TIME '2024-03-01T00:00:00Z'
WHERE email = 'alice@example.com';

-- Are these two identifiers the same person?
SELECT * FROM SAME_IDENTITY('+971501234567', 'alice@example.com');
 
-- Walk every path between two entities, up to 4 hops
SELECT * FROM PATHS_BETWEEN('alice@example.com', 'bob@example.com', MAX_HOPS => 4);
 
-- All phone numbers, emails, IBANs known to be linked to this entity
SELECT * FROM IDENTITY_CLUSTER('alice@example.com');

-- Postgres
CREATE TABLE tickets (
  id          BIGSERIAL PRIMARY KEY,
  subject     TEXT NOT NULL,
  status      TEXT NOT NULL CHECK (status IN ('open', 'pending', 'closed')),
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  assignee_id BIGINT REFERENCES agents(id),
  priority    TEXT NOT NULL DEFAULT 'normal',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE ticket_comments (
  id          BIGSERIAL PRIMARY KEY,
  ticket_id   BIGINT NOT NULL REFERENCES tickets(id),
  author_id   BIGINT NOT NULL,
  body        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE ticket_attachments (
  id          BIGSERIAL PRIMARY KEY,
  ticket_id   BIGINT NOT NULL REFERENCES tickets(id),
  filename    TEXT NOT NULL,
  bytes       BYTEA,
  sha256      BYTEA
);

# Ontology (schema-as-code, versioned in git)
 
[Customer]
kind = "ObjectType"
properties =
  email      = { type = "Identity", canonical = "Email", unique = true }
  phone      = { type = "Identity", canonical = "Msisdn" }
  name       = { type = "String" }
 
[Agent]
kind = "ObjectType"
properties =
  email      = { type = "Identity", canonical = "Email", unique = true }
  team       = { type = "String" }
 
[Ticket]
kind = "ObjectType"
properties =
  subject    = { type = "String", indexed = "bm25" }
  status     = { type = "String" }
  priority   = { type = "String" }
state_machine = { field = "status",
                  transitions = [
                    { from = "open",    to = "pending" },
                    { from = "pending", to = "closed" },
                    { from = "closed",  to = "open"    }
                  ] }
 
[Comment]
kind = "EventType"
properties =
  ticket_id  = { type = "Identity", link_to = "Ticket" }
  author_id  = { type = "Identity", link_to = "Agent" }
  body       = { type = "String" }
 
[Attachment]
kind = "ObjectType"
properties =
  ticket_id  = { type = "Identity", link_to = "Ticket" }
  filename   = { type = "String" }
  sha256     = { type = "Identity", canonical = "Hash_SHA256" }
  bytes      = { type = "BlobRef" }
 
[OpenedTicket]
kind = "LinkType"
from = "Customer"
to   = "Ticket"
 
[AssignedTicket]
kind = "LinkType"
from = "Agent"
to   = "Ticket"

-- Open tickets for a customer, hybrid-ranked
PURPOSE 'support'
SELECT t.id, t.subject, t.status, t.priority, t.valid_from
FROM Ticket t
JOIN OpenedTicket ot ON ot.to = t.id
WHERE ot.from = RESOLVE_IDENTITY('alice@example.com')
  AND t.status = 'open'
  AND MATCH(t.subject, 'refund OR billing')
ORDER BY rank DESC
LIMIT 25;
 
-- The full audit trail for one ticket — what changed, when, by whom
PURPOSE 'audit'
SELECT * FROM Ticket
WHERE id = 'ticket-123'
ORDER BY system_from DESC
WITH PROVENANCE;
 
-- "All comments and attachments, ever, for tickets involving this email"
PURPOSE 'support'
SELECT c.*, a.filename
FROM Comment c
LEFT JOIN Attachment a ON a.ticket_id = c.ticket_id
WHERE c.ticket_id IN (
  SELECT id FROM Ticket WHERE customer_email = 'alice@example.com'
)
ORDER BY c.occurred_at DESC;

  Postgres        →  RelataDB
  ─────────          ──────────
  CREATE TABLE   →  [Type] declaration in TOML or Rust
  BIGSERIAL      →  server-minted ObjectId
  TEXT           →  String
  TEXT (email)   →  Identity (canonical = "Email")
  TIMESTAMPTZ    →  i64 ns UTC (valid_from / system_from)
  FOREIGN KEY    →  LinkType, or Identity property with link_to
  CHECK          →  state_machine declaration
  CREATE INDEX   →  indexed = "bm25" | "hnsw" | "range"
  audit_log tbl  →  automatic PROV-O provenance per row
  RLS policy     →  cell-level ACL compiled into scan
 
  MongoDB         →  RelataDB
  ─────────          ──────────
  collection     →  ObjectType / EventType
  _id            →  server-minted ObjectId
  field          →  typed property
  embed          →  typed nested property  OR  separate ObjectType + LinkType
  array          →  separate ObjectType with link_to
  $lookup        →  LinkType traversal  OR  IDENTITY_CLUSTER
  text index     →  indexed = "bm25" on a property