Verifying exported data.

A dashboard or channel export is every message in the chosen time range, oldest first, with no cap on the number of rows. It is produced in one of four formats. All of them carry the same facts per message; they differ only in shape.

CSV

UTF-8 with a byte order mark, the column header on the first row, one record per row, no comment rows. Opens correctly in Excel and Numbers.

JSON

A messages array followed by a meta block. The block comes last because the record count and the export proof are only known once every row has been written.

AI-friendly JSON

_meta (a typed field guide, ordering, timezone and the AI disclosure), then messages, then source with the count and the export proof.

JSON Lines

One JSON record per line. The first line is _meta, the last line is _summary with the count and the export proof. Intended for pipelines and tooling.

Per message: a stable id, platform and channel (id, title, username, type), the deep link to the original post, the author (id, name, username, bot flag), the original text and its detected language, every machine translation, the links the text does not spell out (an address hidden behind a word, or a button under the post), whether the message is a reply and to which message, whether it was forwarded and from where, attached media with links, media provenance signals, an AI classification label where one exists, the integrity block described below, every archived prior version, and any keyword match.

Every record has a stable id of the form <channelId>_<messageId>, built from the platform's own identifiers. The same message exported twice, in any format, at any time, carries the same id. Cite it, deduplicate on it, and quote it when you ask TideWater to verify a message.

Timestamps are UTC in the form YYYY-MM-DD HH:MM:SS, whatever the source platform's own encoding. Records are ordered by that timestamp and then by id, so the order is stable across exports.

Each message stored by TideWater carries a hash computed when the message was received. The export reproduces it in the record's integrity block.

integrity.contentHash

HMAC-SHA256 over a canonical string of the message: t1|<platform>|<channelId>|<messageId>|<date ISO>|<editDate ISO or empty>|<senderId or empty>|<text length>|<text>, keyed by a key TideWater holds. algorithm names the scheme (HMAC-SHA256-v1) and keyId names the key, so a hash stays verifiable after the key is rotated.

What it proves

That the text, timestamp, sender, channel and message id in the export are byte-for-byte what TideWater stored when it stamped the hash, and that they have not been altered since, by anyone, including TideWater staff who do not hold the key.

What it does not prove

That the source platform authored those bytes. Telegram and Bluesky do not sign individual messages. The hash binds what TideWater received, not what the platform originally published. A third party cannot recompute the hash without the key; TideWater can, on request, and every such request is logged.

integrity.version

Starts at 1. Each edit observed upstream increments it. The prior state is archived and exported under versions[], each version with its own text, timestamps and hash, so the full history of an edited message is in the file.

integrity.editedAt

The platform's edit time for the current version, or absent.

deletion.seenAt

Present when TideWater observed the message being deleted upstream. The content is retained; the record states when the deletion was seen.

media[].sha256

Where TideWater holds the media bytes in its own cache, the SHA-256 of those bytes is exported next to the link. Media that TideWater only links to on a platform CDN has a link but no byte hash; the platform may change or remove that file at any time.

contentHashStampedAt

Present on messages that were stored before hashing existed and were hashed afterwards by a scheduled sweep. Such a hash attests the bytes as stored at that time, not as received. Treat it as weaker evidence and say so.

Every export file is identified and sealed as a whole, independently of the per-message hashes.

Export id

A UUID issued when the export starts. It is in the X-Export-Id response header, as the last eight characters of the filename, and inside JSON formats as source.export.id (AI-friendly JSON, JSON Lines summary) or meta.exportId (JSON).

Digest

SHA-256 over the records in export order. For each record the input is its integrity.contentHash; for a record without a hash the input is the record's JSON. Inputs are joined with a single newline. The digest and the record count are in the same block as the id.

Signature

Ed25519 over the canonical payload sonar-export-v1|<exportId>|<digestAlgorithm>|<digest>|<recordCount>|<exportedAt>, with the signing key id (default e1). Public keys are published at GET <sonar origin>/api/export-verification; old keys stay published after rotation.

Timestamp

Where a timestamp authority is configured, an RFC 3161 token over the digest, base64-encoded. It proves the digest existed at the time the authority signed it, independently of TideWater.

Audit record

Id, digest, signature, timestamp, record count and the exporting user are written to TideWater's audit log when the export completes. GET /api/export-verification/<exportId> returns that block to the exporting user, which is how a CSV, which has no trailer, is verified after the fact.

The procedure below needs OpenSSL 3 and a POSIX shell. It does not need TideWater, a Sonar account or any TideWater software.

1. STEP 01

   Collect the proof block

   From a JSON, AI-friendly JSON or JSON Lines file, read the export block (source.export, meta.export or the _summary line). For a CSV, or to cross-check any file, ask the exporting user to fetch GET /api/export-verification/<exportId> from their Sonar deployment. You need: id, digestAlgorithm, digest, recordCount, exportedAt, signature, signatureKeyId and, if present, timestampToken.

2. STEP 02

   Fetch the public key

   curl -s https://<sonar origin>/api/export-verification > keys.json
   # pick the PEM whose keyId matches signatureKeyId and save it as pub.pem

3. STEP 03

   Rebuild the canonical payload and verify the signature

   Write the payload exactly, no trailing newline, then verify the raw Ed25519 signature.

   printf 'sonar-export-v1|%s|%s|%s|%s|%s' \
     "$ID" "$DIGEST_ALGORITHM" "$DIGEST" "$RECORD_COUNT" "$EXPORTED_AT" > payload.txt
   printf '%s' "$SIGNATURE_BASE64" | base64 -d > sig.bin
   openssl pkeyutl -verify -pubin -inkey pub.pem -rawin -in payload.txt -sigfile sig.bin
   # prints "Signature Verified Successfully" on success

4. STEP 04

   Recompute the digest from the records

   For every record in file order take integrity.contentHash when present, otherwise the record's own JSON as written in the file; join the inputs with a single newline; SHA-256 the result. It must equal digest. For JSON Lines:

   grep -v '^{"_meta"' export.ndjson | grep -v '^{"_summary"' \
     | jq -r 'if .integrity.contentHash then .integrity.contentHash else (.|tojson) end' \
     | paste -sd '\n' - | tr -d '\r' | head -c -1 \
     | openssl dgst -sha256

   For a CSV, the inputs are the Content Hash column values in row order; a row with an empty hash uses the record as TideWater serialised it to JSON, which is why a JSON format is the better evidential copy. Ask for the JSON Lines export of the same range if you need to recompute a CSV's digest without TideWater's help.

5. STEP 05

   Verify the RFC 3161 timestamp, if present

   printf '%s' "$TIMESTAMP_TOKEN_BASE64" | base64 -d > token.tsr
   openssl ts -verify -digest "$DIGEST" -in token.tsr -CAfile tsa-chain.pem
   # prints "Verification: OK" on success

   tsa-chain.pem is the certificate chain of the timestamp authority named in the token (openssl ts -reply -in token.tsr -text shows it).

6. STEP 06

   Read the result

   Signature verified and digest matches: the file is the one TideWater produced under that export id, and none of its records have been altered, reordered, added or removed since. Timestamp verified: the file existed in that state at the authority's time. Either check failing means the file is not evidential as it stands.

The per-message hash is keyed, so only TideWater can recompute it. Send the export id and the record ids in question to contact@tidewater-intelligence.com. TideWater staff recompute the hash from the stored message, or from the canonical fields you paste, and answer with a written statement per message.

What you send

The export id and, per message, the record id and the integrity.contentHash from your file. Optionally the full canonical fields (platform, channel id, message id, timestamp, edit time, sender id, text) so the check can be run without touching the stored copy.

What you get back

Per message: match or no match, the algorithm and key id used, whether the stored copy is an ingest-time or a backfilled hash, the current version number, and the time of the check. Every verification request is written to TideWater's audit log with who asked and what was answered.

Source authorship

No hash here proves the platform published the text. It proves what TideWater received and stored.

Backfilled hashes

Messages stored before hashing existed carry contentHashStampedAt. Their hash covers the bytes as stored at that later time.

Media on platform CDNs

Linked, not hashed. Only media TideWater cached carries a byte hash.

Channel names

Channel title and username are read at export time. A renamed channel exports under its current name; the channel id does not change.

Reply targets

Recorded for messages received after reply tracking was introduced. Older messages carry the reply flag but not the parent id.

Key rotation

Every hash and signature names its key id. Rotated keys are retained for verification and old public keys stay published. A hash without a key id predates this scheme and was made with the first key.

Deletions

A deletion is recorded only if TideWater was monitoring the channel when it happened.

Exports may contain machine translations and AI-assigned classification labels. Both are marked in the file: every translation carries machineTranslated: true and every model-assigned label carries aiGenerated: true with a plain-language notice. In CSV the same marking is carried by the Translation Source and Classification Source columns. Media provenance signals describe synthetic media detected in monitored content; they are not a statement about anything TideWater generated. Original text is always present next to any translation.