references/schema.md — Desktop Chat Reader

# ChatStorage.sqlite schema reference

This reference documents the schema assumptions, joins, and deliberately ignored
fields used by `scripts/whatsapp_read.py`. It is an operational reference for the
CLI, not a claim to describe every table or column in every WhatsApp Desktop
version.

## Contents

- [Time representation](#time-representation)
- [Tables and columns](#tables-and-columns)
- [Message types](#message-types)
- [Sender resolution](#sender-resolution)
- [Query behavior and gotchas](#query-behavior-and-gotchas)
- [Read-only and schema checks](#read-only-and-schema-checks)

## Time representation

`ZWAMESSAGE.ZMESSAGEDATE` uses Core Data epoch seconds: seconds since
2001-01-01 00:00:00 UTC. Convert it to a Unix timestamp by adding
`CORE_DATA_EPOCH = 978307200`.

Worked example: a stored value of `0` converts to
`0 + 978307200 = 978307200`, which is 2001-01-01 00:00:00 UTC. The CLI uses
the same offset for date filtering and for rendering message times.

## Tables and columns

`USE` means the CLI reads the column or relies on it for a join, filter, sort,
display value, or diagnostic interpretation. `DO-NOT-USE` marks a field the
script explicitly rejects for its apparent purpose.

### ZWAMESSAGE

One row per message.

| Column | Status | Use or reason |
| --- | --- | --- |
| `Z_PK` | USE | Message primary key. Counted when listing chats and joined to `ZWAMEDIAITEM.ZMESSAGE`. |
| `ZCHATSESSION` | USE | Foreign key to `ZWACHATSESSION.Z_PK`; scopes messages to a chat. |
| `ZMESSAGEDATE` | USE | Core Data epoch seconds. Used for chronological ordering, date cutoffs, and displayed timestamps. |
| `ZISFROMME` | USE | `1` identifies a message sent by the account owner. |
| `ZMESSAGETYPE` | USE | Integer mapped through `MESSAGE_TYPES`; unlisted values render as `type:<n>`. |
| `ZTEXT` | USE | Message body. It can be `NULL` for media and system rows, so callers must not assume text exists. |
| `ZGROUPMEMBER` | USE | Foreign key to `ZWAGROUPMEMBER.Z_PK`; identifies the sender of an inbound group message. |
| `ZPUSHNAME` | DO-NOT-USE | Prohibited sender field: `ZWAMESSAGE.ZPUSHNAME` can contain a base64 protobuf blob rather than a usable name. |
| `ZGROUPEVENTTYPE` | observed, not read by the CLI | Holds an event code associated with message type `10` in observed schemas. |
| `ZVCARDNAME` | observed, not read by the CLI | Diagnostic payload noted for unsettled type `14`; observed to hold a group JID. |
| `ZVCARDSTRING` | observed, not read by the CLI | Diagnostic payload noted for unsettled type `14`; observed to hold a member JID. |

### ZWACHATSESSION

One row per chat session.

| Column | Status | Use or reason |
| --- | --- | --- |
| `Z_PK` | USE | Session primary key, referenced by `ZWAMESSAGE.ZCHATSESSION`. |
| `ZCONTACTJID` | USE | Chat JID and primary lookup key. `@g.us` denotes a group; `@s.whatsapp.net` and `@lid` denote individual chats, with `@lid` representing a multi-device linked identity. |
| `ZPARTNERNAME` | USE | Chat display name. Also supplies the sender name for inbound messages in a 1:1 chat. |
| `ZSESSIONTYPE` | USE | Filters broadcast and status containers from `chats` unless `--include-status` is requested. |

### ZWAGROUPMEMBER

Maps the message-side group-member foreign key to a participant JID.

| Column | Status | Use or reason |
| --- | --- | --- |
| `Z_PK` | USE | Group-member primary key, referenced by `ZWAMESSAGE.ZGROUPMEMBER`. |
| `ZMEMBERJID` | USE | Clean participant JID. This is the reliable group-sender identity field and the key used for push-name lookup. |
| `ZCONTACTNAME` | DO-NOT-USE | Usually empty and otherwise a base64 protobuf blob, not a usable name. |
| `ZFIRSTNAME` | DO-NOT-USE | Has the same protobuf-data problem as `ZCONTACTNAME`; never use it as a fallback name. |

### ZWAPROFILEPUSHNAME

The JID-to-display-name directory used for group sender resolution and by the
`contacts` command.

| Column | Status | Use or reason |
| --- | --- | --- |
| `ZJID` | USE | Lookup key matched to `ZWAGROUPMEMBER.ZMEMBERJID`. |
| `ZPUSHNAME` | USE | Human-readable display name for the matching JID. This is distinct from the unusable `ZWAMESSAGE.ZPUSHNAME` field. |

The `contacts` command lists this local JID-to-push-name directory, not the
user's address book. It may contain unsaved group participants and omit saved
contacts. The directory is the CLI's source for group push names. If no
matching or nonblank profile push name is available, the CLI falls back to the
raw member JID.

### ZWAMEDIAITEM

Media metadata joined to a message.

| Column | Status | Use or reason |
| --- | --- | --- |
| `ZMESSAGE` | USE | Foreign key to `ZWAMESSAGE.Z_PK`. |
| `ZTITLE` | USE | Caption or title. For links it may hold the page title; for documents it may hold the human caption. |
| `ZMEDIALOCALPATH` | USE | Local media path. When text and title are absent, the CLI may display its basename as context. |

## Message types

The CLI maps the following integer values. These labels describe the script's
current rendering behavior; any unlisted value is rendered as `type:<n>` rather
than silently assigned a guessed meaning.

| Integer | Label |
| ---: | --- |
| 0 | `text` |
| 1 | `image` |
| 2 | `video` |
| 3 | `voice` |
| 4 | `contact` |
| 5 | `location` |
| 6 | `group-event` |
| 7 | `link` |
| 8 | `document` |
| 10 | `group-event` |
| 11 | `gif` |
| 12 | `media` |
| 13 | `gif` |
| 14 | `group-ref` |
| 15 | `sticker` |
| 27 | `media` |
| 28 | `poll` |
| 43 | `reaction` |
| 46 | `media` |
| 54 | `notice` |
| 57 | `notice` |
| 59 | `e2e-notice` |
| 60 | `notice` |
| 66 | `notice` |
| 75 | `notice` |

Type `14`/`15` semantics are **UNSETTLED**. For type `14`, the examined payload
typically has empty `ZTEXT`, with a group JID in `ZVCARDNAME` and a member JID in
`ZVCARDSTRING`; it is not a contact card, because that is type `4`. Some forensic
interpretations identify type `14` as a Delete-for-Everyone retraction marker,
while other research points to type `15`, whose examined rows had empty text.
The script therefore keeps type `14` descriptive as `group-ref` and does not
claim a settled semantic meaning.

## Sender resolution

Resolve the sender in this order:

1. If `ZWAMESSAGE.ZISFROMME = 1`, return `ME`.
2. If the chat JID does not end in `@g.us`, return
   `ZWACHATSESSION.ZPARTNERNAME` for the 1:1 partner. If that is blank, the
   implementation can fall back to the joined member JID and then `?`.
3. For a group, follow `ZWAMESSAGE.ZGROUPMEMBER` to
   `ZWAGROUPMEMBER.Z_PK`, read `ZWAGROUPMEMBER.ZMEMBERJID`, then match that JID
   to `ZWAPROFILEPUSHNAME.ZJID`.
4. If the profile row has a nonblank `ZWAPROFILEPUSHNAME.ZPUSHNAME`, use it;
   otherwise return the raw `ZMEMBERJID`, or `?` only when neither is present.

Never re-derive a name from `ZWAMESSAGE.ZPUSHNAME`,
`ZWAGROUPMEMBER.ZCONTACTNAME`, or `ZWAGROUPMEMBER.ZFIRSTNAME`: those fields can
contain base64 protobuf data instead of names.

## Query behavior and gotchas

- `ZCONTACTJID` is not unique. `chats` groups sessions by JID. For `read`, the
  resolver chooses the session with the most messages, using the lowest session
  primary key on ties.
- A name fragment can match several different JIDs. In that case the resolver
  reports the ambiguity, lists candidates, and requires an exact JID instead
  of guessing. Chat lookup by name is a literal substring match.
- `ZTEXT` can be `NULL` for media and system rows. For `read`, the CLI builds a
  bracketed type placeholder and may add `ZTITLE` or the local-path basename.
- Each message appears once. Message results are ordered by message date, then
  primary key; `read` renders them chronologically.
- `chats --search`, the `search` term, `contacts --search`, and chat lookup by
  name are literal substring matches. `%` and `_` match literally. Search is
  an escaped `ZWAMESSAGE.ZTEXT LIKE ?` scan; there is no FTS index.
- `at` and `last_message_at` are local ISO-8601 timestamps with seconds and a
  UTC offset.
- WhatsApp Desktop may be writing while a read begins. SQLite can then report
  `SQLITE_BUSY` or a locked database; the CLI surfaces that error rather than
  returning an empty result.
- `chats` excludes `ZSESSIONTYPE` values `2` (broadcast container) and `3`
  (status bucket) by default. It also excludes JIDs ending in `.status`.
  `--include-status` disables those default filters.
- A link row can keep its URL in `ZTEXT` and page title in `ZTITLE`. A document
  can keep its filename in `ZTEXT` and its human caption in `ZTITLE`; the CLI
  renders both when they differ.

## Read-only and schema checks

The CLI reads the selected database and adjacent `-wal` and `-shm` files when
present through read-only, no-follow descriptors; symlinked source files are
refused. It writes only a private temporary copy under the system temp folder:
a 0700 directory with 0600 files, deleted when the command ends, including on
errors, Ctrl-C, SIGTERM and SIGHUP. A forced kill (SIGKILL) or power loss can leave the copy in the system temp folder. The copy needs free space up to the combined database-file size (maximum 1 GiB). Signal exits are 143 for SIGTERM and 129 for SIGHUP. SQLite opens the copy with `mode=ro` so committed
WAL-only rows remain visible; temporary SQL storage stays in memory. Source
files are never written or checkpointed. Size and modification-time changes
during copying cause a whole-snapshot retry, up to three attempts. If writes
keep changing the sources, close WhatsApp Desktop and retry; the CLI refuses
the unstable snapshot rather than reading it. This metadata check is not an
atomic snapshot guarantee against concurrent writers.

Immediately after opening, the CLI queries `sqlite_master` and checks
`REQUIRED_TABLES = {"ZWAMESSAGE", "ZWACHATSESSION", "ZWAGROUPMEMBER",
"ZWAPROFILEPUSHNAME", "ZWAMEDIAITEM"}`. If any of these five tables is
missing, it stops with a schema-drift error instead of trusting incompatible
results. Query failures caused by locked/busy state are also reported
explicitly.

By Zero Width Lab (zerowidthlab.com)

License: MIT (read it).

Back to the listing