Sync tables
What syncs, in which direction, with which operations allowed.
A sync table is one table, in one direction. It says who owns the data, what the receiving side is allowed to do with it, and where it lands in the queue when changes are applied.
Setting one up for the first time
Get started walks through configuring a table and running the capture script. This page describes the fields and screens themselves.
Direction
Direction records authorship: which database is the source for this table.
| Direction | Source | Captured on | Applied to |
|---|---|---|---|
| Up | The school's local database | Local | Online |
| Down | The online database | Online | Local |
A side only captures the tables it owns, so direction decides which database's setup script grows a trigger for the table. A table edited on both sides is added once per direction.
Fields
Add sync table, and Edit in the row menu, open the same form.
| Field | Default | Notes |
|---|---|---|
| Table name | — | Picked from the known table names, or typed in |
| Primary key column | id | How a row is matched on the receiving side |
| Apply order | 100 | Lower numbers are applied first |
| Direction | Up | See above |
| Create new rows | on | Insert rows the receiving side does not have |
| Update existing rows | on | Overwrite matching rows; soft deletes arrive as updates |
| Delete rows | off | Remove rows on the receiving side |
Apply order keeps foreign keys satisfied. Changes are applied in this order, so a parent table needs a lower number than the rows that reference it. Unrelated tables stay at the default.
Delete is off by default. With it off, a delete on the source leaves the receiving row in place, so a mistaken mass delete cannot propagate.
The list
Each row shows the table name with its direction, the operations it allows as badges, its apply order, and whether it is active. Search covers name, direction, primary key and active state, with separate Direction and Status filters.
The row menu offers Copy table name, Edit, Deactivate or Activate, and Remove.
Deactivating discards what is queued
An inactive table disappears from the configuration the agent fetches, and the agent treats outbox rows for tables it does not recognise as unconfigured — they are marked handled and never sent. Deactivate to stop a table syncing, but expect changes captured while it was off to stay behind. Request a backfill after reactivating it.
Setup SQL
Configuring a table here does not capture anything by itself. The capture is done by database triggers, and those are created by the script behind Setup SQL.
The dialog has one tab per database, because each side only captures what it owns:
- Local database — triggers for the
UPtables. - Online database — triggers for the
DOWNtables.
The script creates a sync_outbox table plus three triggers per synced table, and is safe to
re-run. Above the script, Captures UP or Captures DOWN lists the tables it covers.
There are two ways to take the script out of the dialog:
- Copy puts it on the clipboard, for pasting into SQLyog, HeidiSQL or another client that
understands
DELIMITER. - Download .sql saves it as
<school code>-<side>-outbox.sql, for exampleriv001-local-outbox.sql, for piping into themysqlcommand line client.
A tab whose direction has no active tables reports that instead of a script, and both buttons are disabled.
Re-run it after every change to the table list
Adding a table in the dashboard does not create its triggers. Until the script is run again on that database, the table is configured but nothing is ever captured for it.
Checking the triggers
Each agent reads its database's capture triggers every cycle, and the school's Overview compares them with the tables configured here. The Capture triggers panel has one side per database:
| Status | Meaning |
|---|---|
| Up to date | Every configured table has exactly the triggers the script would create |
| Issues | A trigger is missing or out of date, so some changes are not captured, or are captured wrongly |
| Leftover triggers | A trigger the configuration no longer wants. What it captures is discarded |
| Not checked | No report yet. The agent reports on its next cycle |
The comparison uses the configuration as it is now, so changing a table shows the triggers as out of date straight away, before the agent has checked again. Re-running the script clears it on that agent's next cycle.
An out-of-date trigger says why: it records a different column than the configured key, fires
before the write instead of after it, or does not skip the agent's own writes and so sends
every applied change back. Only triggers named sync_<table>_… or writing to sync_outbox
are read. The application's own triggers are never sent to the monitor.
Leftovers on a removed table need dropping by hand
The script only drops triggers for tables it still covers. A trigger on a table that is no longer synced from that database stays until someone drops it; the panel shows the statement to run.