> ## Documentation Index
> Fetch the complete documentation index at: https://docs.buylo.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Buylo database synchronization bridge — External Integration Guide

# 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:

```bash theme={null}
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`:

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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)

```sql theme={null}
UPDATE buylo_products
SET external_package_id = NULL
WHERE external_package_id = 'PKG-2026-0001';
```

### 7.4 Incremental change feed (polling)

```sql theme={null}
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

```sql theme={null}
SELECT buylo_active_packing_station_uuid IS NOT NULL AS is_on_a_station
FROM buylo_products
WHERE hash = :hash;
```

***

## 8. Gotchas and guidelines

1. **`hash` is opaque** — 32-character string; do not parse, split, or derive from it.
2. **`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".
3. **Polling cadence** — 1 Hz is sufficient for typical workloads. Going much faster wastes resources. If you need lower latency, ask about binlog replication.
4. **Transactions** — single-row `UPDATE`s are atomic on their own. For multi-row updates that must commit together, use an explicit transaction (`START TRANSACTION; … COMMIT;`).
5. **Avoid massive `IN (…)` clauses** — prefer temporary tables or batched updates for bulk operations of thousands of rows.
6. **Character encoding** — the database runs with `utf8mb4`. All text columns handle emoji and full Unicode.
7. **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)`.
8. **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](mailto:support@buylo.ai)**