# latincon_api — Database Documentation

> **Database:** `latincon_api`  
> **Engine:** MariaDB 10.3 / InnoDB  
> **Charset:** utf8mb4 / utf8mb4_unicode_ci  
> **Generated:** 2026-04-02  
> **Purpose:** API gateway database — authentication, DMC registry, customer master, and cross-DMC search cache.

---

## Overview

This is the **API gateway / interface database** — the single source of truth that sits between `latincon_new` (the platform backend) and the individual `latincon_dms` databases (one per DMC). Its responsibilities are:

- **API authentication** — `api_keys` and `refresh_tokens` secure all inter-service calls
- **DMC registry** — `dmcs` stores connection credentials for every DMS database instance
- **Customer master** — `cus__customers` is the authoritative customer record (read by `latincon_new` via a view)
- **Search cache** — `src__productcache` and `src__suppliercache` hold denormalised, FULLTEXT-indexed snapshots pulled from each DMS, enabling cross-DMC search without hitting individual databases
- **Standardised service taxonomy** — `std__services` provides a normalised service type list; `std__dmc_services` maps each DMC's local service IDs to the standard IDs

**Cross-database relationships:**
- `latincon_new.customers` is a VIEW over `cus__customers` in this database
- `latincon_new.dmcs` is a VIEW over `dmcs` in this database
- `src__productcache.dmc_id` + `product_id` reference records in the corresponding `latincon_dms` instance

### Naming Conventions

| Prefix | Module |
|--------|--------|
| `api_` / `refresh_` | API authentication |
| `cus__` | Customer management |
| `dmc` | DMC registry & geography |
| `ref__` | Reference / ISO lookup data |
| `src__` | Search cache (denormalised) |
| `std__` | Standardised taxonomy |
| `phinx_` | DB migration tracking |

### Junction Tables
`cus__customer_dmcinterests` and `cus__customer_dmcrelations` are junction tables linking customers to DMCs (interest = expressed interest, relation = active commercial relationship). `dmc_countries` maps DMCs to the countries they operate in.

**Total tables:** 23  
**Foreign-key relationships:** 15  

---

## Tables

### API Authentication

Role-based API key system with hierarchical key creation (`created_by` is self-referential). Keys store a SHA-256 hash (never the raw key), a short prefix for identification, per-key rate limits, and granular permissions JSON. Refresh tokens are short-lived and hashed.

#### `api_keys`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `int(11) UNSIGNED` | 🔑 PK. |
| `key_hash` | `varchar(64)` | SHA-256 hash of the API key |
| `prefix` | `varchar(8)` | First 8 chars for identification |
| `name` | `varchar(255)` | Descriptive name for the key |
| `role` | `enum('master','admin','regular')` |  |
| `password` | `varchar(255)` | Optional hashed password for key usage |
| `allowed_origins` | `longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin` | Allowed domains/IPs (null/empty = any, supports wildcards) |
| `created_by` | `int(11) UNSIGNED` | Parent key ID that created this key → `api_keys.id` |
| `is_active` | `tinyint(1)` |  |
| `expires_at` | `timestamp NULL` |  |
| `last_used_at` | `timestamp NULL` |  |
| `last_used_ip` | `varchar(45)` |  |
| `permissions` | `longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin` | Granular permissions for regular keys |
| `rate_limit` | `int(11) UNSIGNED` | Requests per hour (null = default) |
| `created_at` | `timestamp` |  |
| `updated_at` | `timestamp` |  |
| `deleted_at` | `timestamp NULL` |  |

**References:**
- `created_by` → `api_keys.id`

**Referenced by:**
- `api_keys.created_by`
- `refresh_tokens.api_key_id`

#### `refresh_tokens`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `int(11) UNSIGNED` | 🔑 PK. |
| `api_key_id` | `int(11) UNSIGNED` | → `api_keys.id` |
| `token_hash` | `varchar(64)` | SHA-256 hash of the refresh token |
| `expires_at` | `timestamp` |  |
| `revoked_at` | `timestamp NULL` |  |
| `created_at` | `timestamp` |  |
| `ip_address` | `varchar(45)` |  |
| `user_agent` | `varchar(500)` |  |

**References:**
- `api_key_id` → `api_keys.id`

### DMC Registry

`dmcs` is the authoritative registry of all DMC instances with their database connection strings. `dmc_countries` maps each DMC to the countries it operates in, with an optional `dmscountry_id` to translate between the API's country codes and the DMS's internal country codes.

#### `dmcs`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `tinyint(3) UNSIGNED` | 🔑 PK. |
| `name` | `varchar(45)` |  |
| `domain` | `varchar(255)` |  |
| `dmsdbhost` | `varchar(255)` |  |
| `dmsdbname` | `varchar(64)` |  |
| `dmsdbuser` | `varchar(80)` |  |
| `dmsdbpass` | `varchar(255)` |  |
| `memdbhost` | `varchar(255)` |  |
| `memdbname` | `varchar(64)` |  |
| `memdbuser` | `varchar(80)` |  |
| `memdbpass` | `varchar(255)` |  |
| `enabled` | `tinyint(1) UNSIGNED` |  |

**Referenced by:**
- `cus__customer_dmcinterests.dmc_id`
- `cus__customer_dmcrelations.dmc_id`
- `dmc_countries.dmc_id`

#### `dmc_countries`

| Column | Type | Notes |
|--------|------|-------|
| `dmc_id` | `tinyint(3) UNSIGNED` | 🔑 PK. → `dmcs.id` |
| `country_id` | `char(3)` | 🔑 PK. |
| `dmscountry_id` | `char(3)` |  |

**References:**
- `dmc_id` → `dmcs.id`

#### `dmccountries`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `char(3)` | 🔑 PK. |
| `name` | `varchar(50)` |  |

**Referenced by:**
- `dmccountrydetails.country_id`

#### `dmccountrydetails`

| Column | Type | Notes |
|--------|------|-------|
| `country_id` | `char(3)` | 🔑 PK. → `dmccountries.id` |
| `language_id` | `varchar(3)` | 🔑 PK. |
| `name` | `varchar(50)` |  |

**References:**
- `country_id` → `dmccountries.id`

### Customers

`cus__customers` is the master customer record (travel agencies / tour operators). Registration goes through `cus__customerregistrations` (approval workflow). Logos, language preferences, DMC interests, and active DMC relations are stored in companion tables.

#### `cus__customers`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `smallint(5) UNSIGNED` | 🔑 PK. |
| `name` | `varchar(50)` |  |
| `country_id` | `char(2)` | → `ref__countries.id` |
| `approved` | `tinyint(1) UNSIGNED` |  |

**References:**
- `country_id` → `ref__countries.id`

**Referenced by:**
- `cus__customerlogos.customer_id`

#### `cus__customerregistrations`

| Column | Type | Notes |
|--------|------|-------|
| `customer_id` | `smallint(5) UNSIGNED` | 🔑 PK. |
| `title` | `varchar(255)` |  |
| `first` | `varchar(255)` |  |
| `last` | `varchar(255)` |  |
| `address1` | `varchar(255)` |  |
| `address2` | `varchar(255)` |  |
| `city` | `varchar(50)` |  |
| `region` | `varchar(50)` |  |
| `zip` | `varchar(15)` |  |
| `email` | `varchar(50)` |  |
| `email2` | `varchar(50)` |  |
| `web` | `varchar(50)` |  |
| `phone` | `varchar(20)` |  |
| `phone2` | `varchar(20)` |  |
| `focus` | `tinyint(3) UNSIGNED` |  |
| `assocind` | `tinyint(1) UNSIGNED` |  |
| `assochead` | `tinyint(1) UNSIGNED` |  |
| `assocheadspec` | `varchar(50)` |  |
| `assoccoop` | `tinyint(1) UNSIGNED` |  |
| `assoccoopspec` | `varchar(50)` |  |
| `assocfran` | `tinyint(1) UNSIGNED` |  |
| `assocfranspec` | `varchar(50)` |  |
| `iata` | `tinyint(1) UNSIGNED` |  |
| `iatanumber` | `varchar(25)` |  |
| `accepted` | `tinyint(1) UNSIGNED` |  |
| `sentdate` | `date` |  |
| `approvedate` | `date` |  |
| `approveduser_id` | `smallint(5) UNSIGNED` |  |
| `note` | `longtext` |  |
| `notified` | `tinyint(1) UNSIGNED` |  |
| `regmail` | `tinyint(1) UNSIGNED` |  |

**References:**
- `referencecountry_id` → `ref__countries.id`

#### `cus__customerlogos`

| Column | Type | Notes |
|--------|------|-------|
| `customer_id` | `smallint(5) UNSIGNED` | 🔑 PK. → `cus__customers.id` |
| `file` | `varchar(255)` |  |

**References:**
- `customer_id` → `cus__customers.id`

#### `cus__customer_languages`

| Column | Type | Notes |
|--------|------|-------|
| `customer_id` | `smallint(5) UNSIGNED` | 🔑 PK. |
| `language_id` | `varchar(3)` | 🔑 PK. → `languages.id` |

**References:**
- `language_id` → `languages.id`

#### `cus__customer_dmcinterests`

| Column | Type | Notes |
|--------|------|-------|
| `customer_id` | `smallint(5) UNSIGNED` | 🔑 PK. |
| `dmc_id` | `tinyint(3) UNSIGNED` | 🔑 PK. → `dmcs.id` |

**References:**
- `dmc_id` → `dmcs.id`

#### `cus__customer_dmcrelations`

| Column | Type | Notes |
|--------|------|-------|
| `customer_id` | `smallint(5) UNSIGNED` | 🔑 PK. |
| `dmc_id` | `tinyint(3) UNSIGNED` | 🔑 PK. → `dmcs.id` |

**References:**
- `dmc_id` → `dmcs.id`

### Search Cache

Denormalised product and supplier snapshots pulled from each DMS instance. Keyed by `(dmc_id, product_id/supplier_id, language_id)`. Multiple FULLTEXT indexes (`name`, `description`, `tags`, `searchamenities`, and a combined `ft_search`) power cross-DMC search. Rebuilt by a sync process whenever DMS content changes.

#### `src__productcache`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `int(10) UNSIGNED` | 🔑 PK. |
| `dmc_id` | `tinyint(3) UNSIGNED` |  |
| `product_id` | `int(10) UNSIGNED` |  |
| `language_id` | `varchar(3)` |  |
| `service_id` | `tinyint(3) UNSIGNED` |  |
| `country_id` | `char(3)` |  |
| `region_id` | `smallint(5) UNSIGNED` |  |
| `location_id` | `smallint(5) UNSIGNED` |  |
| `name` | `varchar(120)` |  |
| `comment` | `varchar(120)` |  |
| `description` | `longtext` |  |
| `tags` | `text` |  |
| `searchamenities` | `text` |  |
| `classification` | `tinyint(3) UNSIGNED` |  |
| `recommended` | `tinyint(1) UNSIGNED` |  |
| `detail` | `tinyint(1) UNSIGNED` |  |
| `thumbnail` | `varchar(255)` |  |

**Fulltext indexes:** `name`, `comment`, `description`, `tags`, `searchamenities`, `name`,`comment`,`description`,`tags`,`searchamenities`

#### `src__suppliercache`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `int(10) UNSIGNED` | 🔑 PK. |
| `dmc_id` | `tinyint(3) UNSIGNED` |  |
| `supplier_id` | `smallint(5) UNSIGNED` |  |
| `language_id` | `varchar(3)` |  |
| `service_id` | `tinyint(3) UNSIGNED` |  |
| `country_id` | `char(3)` |  |
| `region_id` | `smallint(5) UNSIGNED` |  |
| `location_id` | `smallint(5) UNSIGNED` |  |
| `name` | `varchar(100)` |  |
| `description` | `longtext` |  |
| `tags` | `text` |  |
| `searchamenities` | `text` |  |
| `classification` | `tinyint(3) UNSIGNED` |  |
| `recommended` | `tinyint(1) UNSIGNED` |  |
| `detail` | `tinyint(1) UNSIGNED` |  |
| `thumbnail` | `varchar(255)` |  |

**Fulltext indexes:** `name`, `description`, `tags`, `name`,`description`,`tags`,`searchamenities`, `searchamenities`

#### `src__searchweights`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `varchar(10)` | 🔑 PK. |
| `weight` | `tinyint(3) UNSIGNED` |  |

### Standardised Services

`std__services` defines a normalised service type taxonomy shared across all DMCs. `std__dmc_services` maps each DMC's local `service_id` to the standard ID, enabling cross-DMC queries by service type. `std__servicedetails` provides translatable names.

#### `std__services`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `tinyint(3) UNSIGNED` | 🔑 PK. |
| `name` | `varchar(45)` |  |
| `parent_id` | `tinyint(3) UNSIGNED` | → `std__services.id` |
| `image` | `varchar(255)` |  |
| `enabled` | `tinyint(1)` |  |
| `lite` | `tinyint(1)` |  |
| `supplier` | `tinyint(1)` |  |
| `comment` | `tinyint(1)` |  |
| `ifsupplier` | `tinyint(1)` |  |
| `addservice` | `tinyint(1) UNSIGNED` |  |
| `incservice` | `tinyint(1) UNSIGNED` |  |
| `ispickup` | `tinyint(1) UNSIGNED` |  |
| `isdropoff` | `tinyint(1) UNSIGNED` |  |

**References:**
- `parent_id` → `std__services.id`

**Referenced by:**
- `std__dmc_services.service_id`
- `std__services.parent_id`

#### `std__servicedetails`

| Column | Type | Notes |
|--------|------|-------|
| `service_id` | `tinyint(3) UNSIGNED` | 🔑 PK. |
| `language_id` | `varchar(3)` | 🔑 PK. → `languages.id` |
| `name` | `varchar(45)` |  |

**References:**
- `language_id` → `languages.id`

#### `std__dmc_services`

| Column | Type | Notes |
|--------|------|-------|
| `service_id` | `tinyint(3) UNSIGNED` | 🔑 PK. → `std__services.id` |
| `dmc_id` | `tinyint(3) UNSIGNED` | 🔑 PK. |
| `dmcservice_id` | `tinyint(3) UNSIGNED` | 🔑 PK. |

**References:**
- `service_id` → `std__services.id`

### Reference Data

ISO country/nationality reference tables, shared with `latincon_new`.

#### `ref__countries`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `char(2)` | 🔑 PK. |
| `cca3` | `char(3)` |  |
| `currency_code` | `varchar(3)` |  |
| `callingcode` | `varchar(7)` |  |
| `region` | `varchar(8)` |  |
| `subregion` | `varchar(25)` |  |

**Referenced by:**
- `cus__customerregistrations.referencecountry_id`
- `cus__customers.country_id`
- `ref__countrydetails.country_id`
- `ref__nationalities.country_id`

#### `ref__countrydetails`

| Column | Type | Notes |
|--------|------|-------|
| `country_id` | `char(2)` | 🔑 PK. → `ref__countries.id` |
| `language_id` | `varchar(3)` | 🔑 PK. |
| `name` | `varchar(50)` |  |

**References:**
- `country_id` → `ref__countries.id`

### Languages

Language registry with a `dmslanguage_id` bridge to the integer IDs used inside each `latincon_dms` database.

#### `languages`

| Column | Type | Notes |
|--------|------|-------|
| `id` | `varchar(3)` | 🔑 PK. |
| `name` | `varchar(50)` |  |
| `name_en` | `varchar(50)` |  |
| `flag` | `varchar(50)` |  |
| `dmslanguage_id` | `tinyint(3) UNSIGNED` |  |
| `enabled` | `tinyint(1) UNSIGNED` |  |

**Referenced by:**
- `cus__customer_languages.language_id`
- `std__servicedetails.language_id`

### System

Phinx migration tracking table — records applied DB migrations.

#### `phinx_migrations`

| Column | Type | Notes |
|--------|------|-------|
| `version` | `bigint(20)` | 🔑 PK. |
| `migration_name` | `varchar(100)` |  |
| `start_time` | `timestamp NULL` |  |
| `end_time` | `timestamp NULL` |  |
| `breakpoint` | `tinyint(1)` |  |

---

## Key Relationships

```
api_keys (self-referential: created_by)
  └── refresh_tokens

dmcs
  ├── dmc_countries → dmccountries
  ├── cus__customer_dmcinterests → cus__customers
  ├── cus__customer_dmcrelations → cus__customers
  └── std__dmc_services → std__services

cus__customers
  ├── cus__customerregistrations
  ├── cus__customerlogos
  └── cus__customer_languages → languages

src__productcache  (dmc_id + product_id → latincon_dms.products)
src__suppliercache (dmc_id + supplier_id → latincon_dms.suppliers)

Cross-DB views (in latincon_new):
  latincon_new.customers → cus__customers
  latincon_new.dmcs      → dmcs
```

---

*Documentation auto-generated from `latincon_api.sql` schema dump.*