Buylo database synchronization bridge — External Integration Guide

Audience: third-party developers integrating with Buylo via the shared MySQL bridge. This document describes how your application connects to the MySQL instance that Buylo provisions for you, what data is exposed, what you can read and write, and how changes flow between your side and the Buylo platform.

1. What this is

Buylo operates an internal platform that tracks products and physical items (RFID-tagged). To share a slice of that data with your application — and to accept a piece of data back from you — a dedicated MySQL database is synchronized by the Buylo database synchronization bridge in both directions. From your perspective, the integration is a single MySQL database. You do not need to know anything about the upstream Buylo systems — the bridge hides all of it.

2. Connection

Buylo will provide you with the following values — that is all you need to connect:

Item	Description
Host	Hostname or IP of the MySQL endpoint
Port	TCP port (default `3306`, may be assigned per environment)
Database	Schema name to connect to
Username	Your dedicated account
Password	Shared securely; rotatable on request

Example client connection:

mysql -h <host> -P <port> -u <username> -p <database>

Any standard MySQL driver works (mysql2, PyMySQL, JDBC com.mysql.cj.jdbc.Driver, mysqlclient, etc.). If you have multiple environments (e.g. test1, production), you will receive a separate set of these values for each one.

3. The shared table: `buylo_products`

This is the single table you interact with.

Column	Type	Your access	Meaning
`hash`	`CHAR(32)`	read (PK)	Stable, opaque row identifier. One row = one tagged item.
`buylo_product_id`	`VARCHAR(64)`	read	Logical product identifier used by Buylo
`buylo_name`	`VARCHAR(255)`	read	Product name (localized from upstream)
`buylo_ean`	`VARCHAR(255)`	read	EAN / barcode
`buylo_active_packing_station_uuid`	`CHAR(36)`	read	UUID of the packing station currently holding this item, or `NULL` when not currently observed
`external_package_id`	`VARCHAR(64)`	read + write	Value owned by you — your reference back into Buylo
`created_at`	`TIMESTAMP`	read	Row creation time
`updated_at`	`TIMESTAMP`	read	Auto-updated on every change — useful for incremental polling

What one row represents

One row in buylo_products corresponds to one physical tagged item in Buylo’s catalog (typically an RFID-tagged unit). Multiple rows can share the same buylo_product_id — one product (SKU) often has many physical instances, each with its own hash.

Primary key behavior

hash is the primary key and is permanent for the lifetime of the row.
Treat it as opaque. Do not parse or derive meaning from it.
If an item is removed upstream, the row may be deleted. A re-created item may or may not get the same hash — treat it as a new row.

4. Permissions

Your account has exactly these privileges on the sync database, enforced at the MySQL server level:

Operation	Allowed?
`SELECT` on any column of `buylo_products`	✅
`UPDATE buylo_products SET external_package_id = … WHERE hash = …`	✅
`UPDATE` any other column (e.g. `buylo_name`)	❌ `ERROR 1143 — column access denied`
`INSERT INTO buylo_products`	❌
`DELETE FROM buylo_products`	❌
`ALTER / DROP / CREATE TRIGGER` on `buylo_products`	❌ `ERROR 1142 — table access denied`
Any access to other tables in the `external` schema	❌

These restrictions are by design and are re-verified on every deployment of the bridge service. They protect the sync pipeline from accidental damage. If you need more access (e.g. additional columns, write to another column), request a change through Buylo operations — column-level ownership is negotiated per contract.

5. Data flow

Buylo → you (read direction)

Buylo continuously propagates changes from its internal systems into buylo_products:

When item state changes upstream (e.g. name updated, EAN assigned, item appears/leaves a packing station), the corresponding buylo_* columns update.
When an item is newly registered upstream, a new row appears.
When an item is removed upstream, the row is deleted.
Typical latency: under 1 second after the upstream change commits.

You can observe changes either by periodic polling (WHERE updated_at > :last_seen) or by reading the binlog if you have a CDC pipeline (ask ops for binlog permissions).

You → Buylo (write direction)

You write a value into external_package_id:

UPDATE buylo_products
SET external_package_id = 'PKG-2026-0001'
WHERE hash = 'abcdef1234567890abcdef1234567890';

The bridge picks up your change asynchronously (typical latency: ~1 second) and propagates it into the upstream Buylo product record as your logical identifier for that item. Setting NULL clears the value upstream.

Boundaries that hold

You writing external_package_id does not trigger any echo of buylo_* columns back to you. The sync is column-aware: only “foreign” columns emit change events.
Buylo writing buylo_* columns does not alter your external_package_id — it is preserved across updates of all other columns.
Multiple simultaneous updates on different columns of the same row are safe — they merge cleanly.

What you cannot observe

The upstream Buylo platform, the tag-reading infrastructure, the location and topology of packing stations, and anything else behind the bridge is intentionally not visible. The MySQL table is the entire contract.

6. Optional: your own workspace schema

If your contract includes one, Buylo will provision a second MySQL database (typical name: external_workspace) on the same server. Your account has full privileges there:

CREATE / DROP / ALTER TABLE, CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE
All DML (SELECT / INSERT / UPDATE / DELETE)
All DDL within that schema

Use it for:

Cached or derived data you maintain yourself
Staging tables for batch imports
Triggers reacting to your own workspace writes
Views that join buylo_products (cross-schema SELECT is allowed)

Nothing you do in the workspace affects the sync pipeline or can leak writes into buylo_products. It is isolated. Example — a view joining both schemas:

USE external_workspace;

CREATE VIEW packaging_assignments AS
SELECT bp.hash,
       bp.buylo_product_id,
       bp.buylo_name,
       bp.external_package_id,
       my.shipped_at,
       my.carrier
FROM external.buylo_products AS bp
LEFT JOIN external_workspace.shipments AS my
       ON my.package_id = bp.external_package_id;

7. Typical application patterns

7.1 Read current state on a packing station

SELECT hash,
       buylo_product_id,
       buylo_name,
       buylo_ean,
       external_package_id
FROM buylo_products
WHERE buylo_active_packing_station_uuid = '12345678-1234-1234-1234-123456789abc';

7.2 Assign a package ID to a newly scanned item

UPDATE buylo_products
SET external_package_id = 'PKG-2026-0001'
WHERE hash = :hash_from_your_app;

7.3 Clear a package ID (e.g. shipment cancelled)

UPDATE buylo_products
SET external_package_id = NULL
WHERE external_package_id = 'PKG-2026-0001';

7.4 Incremental change feed (polling)

SELECT hash,
       buylo_product_id,
       buylo_active_packing_station_uuid,
       external_package_id,
       updated_at
FROM buylo_products
WHERE updated_at > :last_seen
ORDER BY updated_at, hash;

Persist the max updated_at you’ve processed; use it as :last_seen on the next poll. A recommended polling interval is 1–5 seconds.

7.5 Check whether an item is currently active

SELECT buylo_active_packing_station_uuid IS NOT NULL AS is_on_a_station
FROM buylo_products
WHERE hash = :hash;

8. Gotchas and guidelines

hash is opaque — 32-character string; do not parse, split, or derive from it.
NULL vs empty string — SET external_package_id = NULL clears the value upstream. SET external_package_id = '' sets an empty string, which is not the same — upstream will see a non-null empty string. Use NULL for “no value”.
Polling cadence — 1 Hz is sufficient for typical workloads. Going much faster wastes resources. If you need lower latency, ask about binlog replication.
Transactions — single-row UPDATEs are atomic on their own. For multi-row updates that must commit together, use an explicit transaction (START TRANSACTION; … COMMIT;).
Avoid massive IN (…) clauses — prefer temporary tables or batched updates for bulk operations of thousands of rows.
Character encoding — the database runs with utf8mb4. All text columns handle emoji and full Unicode.
Clock drift — updated_at comes from the MySQL server’s clock. If your polling logic compares against your own clock, beware of small skew and prefer querying “what MySQL considers now” with SELECT NOW(6).
Password rotation — when your password is rotated by Buylo ops, the change takes effect on the next bridge deployment (typically within minutes). Your old password becomes invalid at that moment; re-authenticate with the new one.

9. Errors you may encounter

MySQL error	Meaning	Action
`ERROR 1045 — Access denied for user`	Wrong credentials or source IP not whitelisted	Verify connection parameters; contact ops for IP allowlist
`ERROR 1143 — UPDATE command denied to user ... for column 'buylo_name'`	You tried to update a column you don’t own	Only `external_package_id` is writable
`ERROR 1142 — SELECT command denied to user ... for table 'buylo_sync_outbox'`	You tried to query a bridge-internal table	Only `buylo_products` is readable
`ERROR 1205 — Lock wait timeout exceeded`	A long-running transaction blocks your update	Retry after short backoff; avoid long transactions
Silent — row not updated	`WHERE` matched no row (e.g. wrong `hash`)	Verify the row exists with a `SELECT` first

10. Contact

For credentials, endpoint information, workspace provisioning, column access changes, schema questions, or incidents: support@buylo.ai

​Buylo database synchronization bridge — External Integration Guide

​1. What this is

​2. Connection

​3. The shared table: buylo_products

​What one row represents

​Primary key behavior

​4. Permissions

​5. Data flow

​Buylo → you (read direction)

​You → Buylo (write direction)

​Boundaries that hold

​What you cannot observe

​6. Optional: your own workspace schema

​7. Typical application patterns

​7.1 Read current state on a packing station

​7.2 Assign a package ID to a newly scanned item

​7.3 Clear a package ID (e.g. shipment cancelled)

​7.4 Incremental change feed (polling)

​7.5 Check whether an item is currently active

​8. Gotchas and guidelines

​9. Errors you may encounter

​10. Contact