Database connector
The database connector mirrors your Routy data into your own database: clicks, conversions, page views and, if your account manager turns them on, the reports your affiliate programs send us land in tables you own, so you can query them with your own tools alongside everything else you hold.
This screen is where you point us at that database, see which tables we fill, pause the writing, and check what we have actually written. The figures on it are the subject of most of this page, because a row count on its own cannot tell you whether the mirror is keeping up.
Watch this video on its own page →
Transcript
The database connector copies your Routy data into a database you own, so you can query it with your own tools. It isn't part of every plan. If Settings shows Unlock Database Connector, like this, your plan doesn't include it. If you manage billing, Upgrade plan shows the plans that do. If you don't, it tells you who to ask.
Even on a plan that includes it, not everyone can use it. Only your account admins can set up the connector, or change it. Everyone else on the team sees this, and should ask an admin.
As an account admin, with nothing set up yet, you start here. Click Set Up Connector.
Routy connects to a PostgreSQL database. Enter your server's address, and the port. The standard PostgreSQL port is filled in for you. Then the database, and the schema the tables go in. And a database user that Routy can write with.
Turn on SSL, so the connection to your database is encrypted. Before saving, click Test Connection. Routy connects to your database from its own network, right now, and tells you whether it worked. If your database sits behind a firewall, it has to let Routy in. It worked, so save the connector.
Your connector is set up, and Routy starts writing to it. The badge shows its state. Pause stops the writing without deleting anything. Data that arrives while you are paused is not kept for later. Test Connection is here too, for after any change on your side, like a new password. And Delete removes the connector from Routy. It never touches the tables in your database.
Synced tables shows what Routy fills in your database. Clicks, page views and conversions are on from the start. The report tables, with what your affiliate programs report, stay off until your account manager turns them on. You always see what's being written. But choosing the tables is up to your account manager, so ask them to add or remove one. That's it. Once rows start arriving, Delivery health shows whether your database is keeping up. Reading it has a video of its own, and the help panel on this screen covers every figure.
Who can use this screen
Two things decide it, and they are separate.
Your plan. The database connector is not part of every plan. If this tab shows Unlock Database Connector, your plan does not include it. Whoever manages billing on your account sees an Upgrade plan button there; everyone else is told who to ask.
Your permissions. Account admins can do everything here. Anyone else on the team needs Database connector permission, which an account admin gives under Settings → Team when editing that person:
A new permission takes effect the next time that person signs in. Without any of them, the tab says You don't have access to the database connector. Someone who can only view it sees a View only badge on the card, and none of the buttons that change it.
Which tables are filled is a third decision, and it belongs to your account manager — see Synced tables.
Connection
The card at the top shows the engine, the host and port, the database, the schema and the user we connect as, and a badge for the connector's state.
- Test connection opens a connection from our network to yours, right now, and tells you what happened. Use it after any change at your end: a rotated password, a new firewall rule, a moved host.
- Pause stops us writing without deleting anything. The connector keeps its settings and its history, and Resume starts the writing again. Data that arrives while you are paused is not held for you — pause is not a buffer.
- Edit changes the connection details. Delete removes the connector from Routy; it never touches the tables in your database.
If the connector is in an error state, the reason we were given is shown under the connection details. That text comes from your database, not from us, so it is usually the fastest clue: an authentication failure, a missing schema, a refused connection.
Synced tables
This card shows every table the connector can fill. A green badge with a tick is a table we fill for you; a grey one is a table we leave empty.
Clicks, page views and conversions are on unless someone turns them off. The five report tables are off until your account manager turns them on.
Who can change it. Your account manager chooses the tables, with Change tables on this card, which opens the selection in a panel of its own. As the owner of the connector you see the same badges, so you always know what is being written, but the selection is not yours to change. Ask your account manager when you want a table added or removed.
When a change takes effect. Within about ten minutes. For a short while after a table is turned off, rows that were already on their way can still arrive.
History is not loaded for you. Turning a table on starts filling it with new and changed rows from that moment on. To bring in older data, ask your account manager to run a reload of that table for the dates you need.
Every table exists in your database, on or off. We create all eight when the connector is set up. A table that is switched off is simply left empty, and turning one off later stops filling it without dropping it or what is already in it.
Reading the report tables
- Deleted rows stay. When an affiliate program withdraws a row it reported,
we keep the row in your database and set
is_deletedto true, rather than removing it. Filter onis_deletedwhen you total these tables. - Amounts are in the row's own currency, the one the program reported in. Nothing is converted.
- Report rows are not tied to a click, because programs report totals, not
individual visits. The exception is dynamic-variable report events, which can
carry a
click_idwhen the program sends one.
Delivery health
Watch this video on its own page →
Transcript
Once your database connector is running, this is where you check that it is keeping up. It's on plans that include the connector, and only your account admins see it. The figures cover the period at the top right. Here, the last 24 hours. Start with the save rate: of the rows Routy handed to your database, the share it accepted. A row count alone can look fine either way. The rate is what tells you whether rows are going missing. This rate is a little under 100%, and most of the gap is duplicates. Routy delivers every row at least once, so sometimes a row arrives that your database already has. It is skipped, and nothing is lost. Duplicates are highest right after a catch-up, when a window of data is sent again. Dropped is different. These are rows Routy could not write at all. Above zero means data is missing from your database, and the gap won't close on its own.
The first chart shows what was saved each hour, with the save rate as the dashed line. Hover an hour to see what was attempted, saved, skipped and dropped in it.
The second chart separates the bad news. Dropped rows in red, failed operations in amber, and how long writes took. Here the writes got slow first, and an hour later rows were dropped. A climbing duration is often the first sign your database is under load.
When rows are dropped, note the hours, and check them against your own database logs. Test Connection checks that Routy can still reach your database. If rows are still missing, contact support with those hours. Every figure on this screen is explained in the help panel, one click away.
Four figures, covering the period in the selector at the top right.
A healthy period reads attempted = saved + duplicates skipped. Anything left over after those two is loss, and it is counted under Dropped.
The save rate is why these figures exist. "3 clicks saved in the last hour" reads identically whether we were handed 3 rows and saved all of them or handed 4,000 and saved 3 — and only the second is a problem. The rate answers that, and nothing else on the screen can.
Duplicates are not failures
We deliver at least once. That is deliberate: it means a row can arrive twice, and it means a row never silently fails to arrive because a delivery was retried. When a row we send is one your database already holds, the insert discards it and it counts as a duplicate skipped.
So a high duplicates figure is not data loss and is not something to report. It is at-least-once delivery working as designed, and it is at its highest right after a reload or a catch-up, when we deliberately re-send a window of data.
A dash instead of a save rate
The save rate shows a dash — not 0% — whenever the period has nothing to divide by:
- Nothing was written, because nothing happened. A quiet night on a low-volume account reads "nothing to write", which is not a failure.
- The period is older than the counters. We only started counting attempted rows recently, so any hour before then has rows saved and no attempted figure to compare them with. There is no save rate to show for those hours, and the dashed line on the chart breaks rather than dropping to zero.
Both cases are the same answer: we cannot tell you, for this period. They are not 0%, and you should not read them as one.
Conversions have no save rate
Conversions are written one at a time rather than in batches, so there is no batch of attempted conversions to compare against. Conversions is still reported as a count; it is deliberately left out of the save rate.
Similarly, Duplicates skipped and Dropped cover clicks, conversions and page views together. They cannot be attributed to one of the three, which is why they are shown as their own figures rather than subtracted from a row count.
Data ingestion over time
One point per hour across the selected period: clicks, conversions and page views saved, with the save rate as a dashed line on the right-hand axis.
Hovering an hour gives you all of it at once — what was saved, what was attempted, how many duplicates were skipped and how many rows were dropped — so you can check attempted = saved + duplicates skipped on the hour that looks wrong.
Two things to keep in mind when reading an individual hour:
- The figures are per hour, not running totals. On a very low-volume connector — a handful of rows a day — an hour reading 0 means nothing happened in that hour. Read a day in aggregate before concluding anything.
- An empty chart means we have nothing to show for the period, which is not the same as a broken connection. Check the state badge and Test connection before assuming the worst.
Errors, dropped and duration
The second chart separates the two kinds of bad news, because they are acted on differently:
- Dropped (red) is rows that did not get written. If this is above zero and stays there, data is missing from your database and the gap will not close on its own.
- Errors (amber) is operations that failed. A retry often succeeds, so a few errors with a save rate still near 100% is noise rather than a problem.
- Average duration is how long our writes took, on the right-hand axis. A climbing duration is usually the first sign that the database is under load, often before anything starts failing.
When something looks wrong
The save rate is well below 100% and duplicates do not cover the gap. Rows are being lost. Check the state badge and the error message, run Test connection, and look at whether the duration chart climbed at the same time — a database that got slow is a different problem from one that refused a write.
Dropped is above zero. Note the hours it happened in; that window is the one to compare against your own database logs.
Everything is zero and the connector is enabled. Nothing was handed to us to write in that period. Widen the period before investigating the connector.
The save rate is a dash for every hour. Either nothing was attempted, or the period predates the counters. Pick a recent period — the last 24 hours — and check again.
If rows are still missing after all of that, contact support with the connector engine, the hours involved and what the delivery figures read for them.