# Dokploy MySQL — remote connection

Live Baskit pipeline database on Dokploy (not medmin).

## Connection

| Field | Value |
|--------|--------|
| **Host** | `102.37.22.152` |
| **Port** | `3307` |
| **Database** | `baskit` |
| **User** | `root` |
| **Password** | `baskit` |

- Inside Docker: hostname `mysql`, port `3306`
- Remote / tools: use the host + port **3307** above

Verified reachable remotely; `catalogue` holds the full price catalogue.

## Connect examples

### MySQL CLI

```bash
mysql -h 102.37.22.152 -P 3307 -u root -pbaskit baskit
```

### JDBC

```
jdbc:mysql://102.37.22.152:3307/baskit
```

### Viewer (read-only UI)

- URL: https://bworker.appnotify.co.za/
- Login: `baskit` / `baskit2026`

## Useful tables

| Table | Contents |
|--------|----------|
| `catalogue` | Full per-retailer product rows (PnP, Checkers, Woolworths) |
| `mvp_catalogue` | Matched MVP SKUs with cross-retailer prices |

## SQL schema (live)

Dumped from Dokploy MySQL (`SHOW CREATE TABLE`). Retailer values in `catalogue.retailer`: `pnp`, `checkers`, `woolworths`.

### `catalogue`

One row per product per retailer. Join MVP rows on `barcode_norm` ≈ `mvp_catalogue.barcode`.

```sql
CREATE TABLE `catalogue` (
  `retailer` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_key` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `barcode_raw` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `barcode_norm` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_instore_bc` tinyint NOT NULL DEFAULT '0',
  `name` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `brand` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `was_price` decimal(10,2) DEFAULT NULL,
  `image_url` text COLLATE utf8mb4_unicode_ci,
  `url` text COLLATE utf8mb4_unicode_ci,
  `bucket` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`retailer`,`product_key`),
  KEY `idx_barcode_norm` (`barcode_norm`),
  KEY `idx_retailer` (`retailer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

| Column | Meaning |
|--------|---------|
| `retailer` | `pnp` / `checkers` / `woolworths` |
| `product_key` | Store’s product id |
| `barcode_raw` | Barcode as scraped |
| `barcode_norm` | Digits-only join key (leading zeros stripped) |
| `is_instore_bc` | `1` = variable-weight / in-store barcode (usually exclude from matches) |
| `price` / `was_price` | Current and previous/promo price |
| `bucket` | Category / MVP bucket text when available |

### `mvp_catalogue`

One row per matched MVP barcode with prices pivoted across retailers.

```sql
CREATE TABLE `mvp_catalogue` (
  `barcode` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bucket` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `brand` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `n_retailers` tinyint DEFAULT NULL,
  `pnp_price` decimal(10,2) DEFAULT NULL,
  `checkers_price` decimal(10,2) DEFAULT NULL,
  `woolworths_price` decimal(10,2) DEFAULT NULL,
  `min_price` decimal(10,2) DEFAULT NULL,
  `max_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`barcode`),
  KEY `idx_bucket` (`bucket`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### Example pulls

```sql
-- All catalogue rows for one barcode
SELECT * FROM catalogue WHERE barcode_norm = '6009612471196';

-- MVP with savings
SELECT barcode, name, pnp_price, checkers_price, woolworths_price,
       min_price, max_price, (max_price - min_price) AS save_r
FROM mvp_catalogue
ORDER BY (max_price - min_price) DESC
LIMIT 50;

-- Counts by retailer
SELECT retailer, COUNT(*) FROM catalogue GROUP BY retailer;
```

### Note on `scraped_at`

Pipeline code may add `catalogue.scraped_at VARCHAR(32)` on the next full `load_mysql` rebuild. It is **not** present on the live DB yet.

## Security note

MySQL is published on the public IP with a simple root password. Prefer locking it down (firewall IP allowlist, stronger password, or SSH tunnel) if access should be limited to your machines only.