CockroachDB

Overview

Signals reads conversion and customer data straight from a CockroachDB cluster on a schedule and sends the columns you nominate to your ad platforms.

Reading it directly means there is no file job to maintain and nothing to go stale between runs. Signals reads only; nothing is written back.

CockroachDB speaks the PostgreSQL wire protocol, so the connection details look like a PostgreSQL connection with a different port.

You can point it at a table, or at a query. The query path is what you want when the shape Signals needs does not match anything you already have.

SERIAL on a CockroachDB column means DEFAULT unique_rowid() unless the mode was changed, and the values that function produces are unique without being ordered. The documentation states that they are not guaranteed to increase monotonically: two transactions can take their values in one order and commit in the other, and an insert that rolls back leaves a gap behind it. An incremental read written as WHERE id > 41200 consequently misses a row that committed after the previous run while carrying a value below the mark, and misses it for good, because the next run starts from a mark that has already passed it. Filter a scheduled read on a timestamp your application writes, which is a value you control, rather than on the generated key.

Supported destinations

Destinations supported by CockroachDB

Category	Supported
Offline Conversions	Google Offline Conversions API Google Store Sales LinkedIn Offline Conversions API Meta Offline Conversions API OpenAI Offline Conversions API Snapchat Offline Conversions API TikTok Offline Events API
Lead Conversions	Google Enhanced Conversions for Leads LinkedIn Leads Conversions API Meta Conversions API for CRM Snapchat Leads Conversions API TikTok CRM Integrations
Custom Audience	Google Customer Match Meta Custom Audience Snapchat Custom Audience TikTok Custom Audience

Authentication

In Studio, open Sources, find the Database category, and click the CockroachDB tile.

Field	What it is	Where to find it
Authentication Name	Your own label for this set of credentials.	You choose it. Name it for the database it belongs to.
Username	The database user Signals connects as.	Created in the database by your administrator.
Password	The password for that user.	Set when the user was created.

CockroachDB clusters usually require an encrypted connection. If your cluster enforces certificate authentication rather than a password, confirm with your team that a password-based user is available before starting.

Caution

Create a dedicated read-only user for Datahash rather than reusing an application or administrator account. It keeps the connection working when someone rotates a password, and it stops the connection seeing more than it needs. Enter the password on the Datahash connection screen only.

CREATE ROLE and CREATE USER differ by one default here, and it is the default that decides whether the account can connect:

CREATE ROLE datahash LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE appdb TO datahash;
GRANT USAGE ON SCHEMA public TO datahash;
GRANT SELECT ON TABLE public.conversions TO datahash;

CREATE ROLE sets NOLOGIN. Drop the LOGIN keyword from the first line and you have built a role that holds everything the following three lines grant and is turned away before any of it is consulted. CREATE USER is the same statement without that default, which is why half the examples in circulation use one keyword and half the other.

Read access comes from a table-level grant, which is what the fourth line is. The privileges CockroachDB accepts on a database are ALL, BACKUP, CONNECT, CREATE, DROP, RESTORE and ZONECONFIG, and SELECT is absent from that list, so the database named on the connection screen governs which database the session opens rather than what the session may read. Anyone reaching for a database-wide read grant to save the per-table line is reaching for something that does not exist.

Configuration

Instance Setup

Select the Integration Type, which is the kind of data your records hold. Offline Events is the common choice. Give the instance a name, then choose the table path or the query path. On either path you enter the Database Name, and then either the Table Name or a SQL query.

The integration type decides the columns Signals expects and which destinations the connection can feed, so it is worth settling before anyone models the data. It also decides which file format you get on the next screen.

Port 26257 and the encryption a cluster insists on

26257 is where a node accepts SQL, and the same port carries traffic between nodes; 8080 serves the DB Console. Nothing is listening on 5432, so connection details carried over from a PostgreSQL setup point at a number no CockroachDB cluster answers on. Sharing the wire protocol is what makes that easy to do: the driver is right and the number beside it is not. Ask your Datahash representative which port and host this connection uses, rather than inferring them from an attempt that failed.

A cluster refusing unencrypted sessions declines one before it reaches the credentials, so the transport has to be settled before anyone reads a rejection as an account problem. Ask your Datahash representative how this connection negotiates TLS.

Ranges, leaseholders and a scheduled scan

CockroachDB splits a table into ranges by primary key and spreads those ranges over the nodes, and one replica of each range holds the lease and serves its reads. A scan that puts no restriction on the primary key therefore draws from every node holding part of the table, and it asks the same leaseholders that live traffic is already using.

AS OF SYSTEM TIME follower_read_timestamp() on the end of a query changes where that work lands. The read becomes historical, which lets a replica other than the leaseholder answer it. What you give up is currency, since the rows are a few seconds behind the present, and for a feed that runs on a schedule that is not a cost worth weighing. An exact staleness read can still queue behind write intents left by a long-running transaction, so this eases contention rather than ending it. Follower reads are not available on every CockroachDB deployment either, so run the query against your own cluster once before it goes into the connection. The clause is part of a SELECT, which makes all of this a query-path remedy: the table path writes no query for it to attach to.

Table path

Enter the connection details including the table name, then complete the setup. The name is typed rather than picked from a list, so it has to match the database exactly.

Query path

Enter the connection details without the table name, then write the query. Preview the results before finishing, and read the column names in the output rather than just the row count: a query that runs but returns the wrong names will fail silently once the connection is live.

Manage instance

One instance reads one table or query for one integration type. To read another, add another instance from the Manage existing instance table. To change an existing one, open it from there, click the edit option in the menu to the top right, update the fields and click Finish.

File format and schema

Download the file format from the setup screen and shape the table to it. The column names are what Signals matches on, so a column that has been renamed or cased differently is not recognized and the row it belongs to is dropped rather than reported.

On the query path the same rule applies to the column aliases your query returns. Aliasing to the expected names is the usual way to reconcile data you cannot or should not restructure.

Data & identifiers

Personal identifiers are normalized and hashed before they are sent to a destination.

Best practices

• Use a dedicated read-only user scoped to the data Datahash reads, rather than an application account.
• Prefer the query path with explicit column aliases over pointing at a raw table. It lets your team change the underlying model without breaking the connection.
• Move a scheduled scan off the leaseholders on the query path with a follower read, where the cluster allows one, rather than hunting for a read replica to nominate. Every range answers its reads from a leaseholder by default, so there is no standby copy sitting there to be pointed at, and the table path has no query to carry the clause.
• Ask your Datahash representative whether this connection reaches a load balancer in front of the cluster or a single node. A node taken out for a rolling upgrade stops answering, and a connection aimed at that node alone stops with it.
• Clean the data in the view or query: lowercase email, phone in E.164, no stray whitespace. Hashing a badly formatted value produces a hash that will never match.
• Rotate the password periodically and update the connection afterwards.

Troubleshooting & FAQ

The connection times out rather than reporting bad credentials. That points at reachability rather than authentication. Get the host and the port this connection uses from your Datahash representative first, then confirm that host is exposed, that port is open, and that any firewall or allow list permits the connection.

Credentials are rejected. Check the user can read the data from a client of its own first. Most failures here are permissions rather than typos.

The table name is rejected. It is typed rather than selected, so it has to match exactly.

The query previews correctly but nothing is delivered. The column names it returns do not match the expected schema. Alias them to the names in the downloaded file format.

The attempt is refused with a message about encryption rather than about the account. A cluster that requires TLS closes an unencrypted connection without reaching the credentials, so getting one of these back is evidence the host and the port are already correct. A self-hosted secure node says node is running secure mode, SSL connection required; a CockroachDB Cloud cluster says server requires encryption, which comes from the proxy in front of it rather than from a node. A cluster started with --insecure produces neither, which is how a parameter set that worked all through development fails on its first attempt at production.

It was working and stopped. The password may have been changed or the user disabled. A dedicated service user avoids this.