# Brink POS First Integration

## Purpose

This document describes the Brink POS first integration as the reference implementation of the ETL source adapter framework.

It is the single end-to-end reference for understanding which Brink API endpoints feed the pipeline, what staging tables they produce, what curated tables they populate, and how the four framework stages orchestrate the full flow.

## Overview

Brink POS is the first and currently only active source adapter for the KDSAI ETL pipeline. It integrates with Brink's SOAP-based API to pull order, labor, and settings data for restaurant tenants, transforming it through the ETL source adapter framework into a shared warehouse schema.

The integration covers the full extraction-to-curated path:

1. Extract raw SOAP responses from Brink and store them in S3.
2. Normalize the XML payloads into load-ready Parquet artifacts.
3. Load normalized artifacts into tenant-scoped Redshift staging tables.
4. Transform staging data into shared curated tables inside each tenant Redshift database.

## Active Brink Endpoints

The following Brink API endpoints are currently active in the extract flow:

| Endpoint | Description | Request Scope |
|---|---|---|
| `Sales2.GetOrders` | Retrieves order headers, entries, modifiers, payments, and related child entities for a business date. | Business date |
| `Labor.GetShifts` | Retrieves labor shifts and break records for a business date. | Business date |
| `Settings2.GetItems` | Retrieves menu items and item group associations for a snapshot date. | Snapshot date |
| `Settings2.GetEmployees` | Retrieves employee snapshots including jobs, permissions, and valid locations. | Snapshot date |
| `Settings2.GetJobs` | Retrieves job (workforce role) snapshots including menus and shift break types. | Snapshot date |

`Settings2.GetDestinations` is available as a staging table and is used as a lookup join in `curated.pos_orders`, but the standalone extract-to-staging flow is not yet independently scheduled.

## ETL Pipeline Stages

The Brink POS adapter uses four implemented stages through the ETL source adapter framework:

### 1. `extract_raw`

- Calls each active Brink SOAP endpoint and persists the raw XML response to S3 under the tenant-scoped raw prefix.
- Creates the initial ingestion batch and records raw artifact paths.
- Validates SOAP response envelopes and result codes before persisting.

### 2. `normalize_load`

- Reads raw XML artifacts from S3.
- Normalizes Brink XML payloads into Parquet files following the Brink normalization contracts.
- Writes load-ready Parquet to S3 under the tenant-scoped load prefix.
- Advances checkpoint on success, writes failed package on exception.

### 3. `load_to_staging`

- Resolves tenant warehouse connection and target staging tables.
- Runs Redshift `COPY` commands to load Parquet artifacts into staging tables.
- Records per-table load diagnostics.
- Advances checkpoint on success, creates failed package on exception.

### 4. `staging_to_curated`

- Validates required staging tables are present.
- Executes curated target SQL (DELETE then INSERT) inside a single transaction per tenant.
- Collects diagnostics for each curated target.
- Advances checkpoint on success, writes failed package on exception.

### Reserved stage: `curated_to_s3`

The framework reserves a fifth stage name (`curated_to_s3`) for future export of curated tables to S3 Parquet. This stage is not implemented.

## Staging Tables

The following staging tables are produced by the Brink extract flow, grouped by source endpoint:

### `Sales2.GetOrders`

| Staging Table | Description |
|---|---|
| `brink_sales2_orders` | Order headers with business date, amounts, status flags, terminal, and destination. |
| `brink_sales2_order_entries` | Individual line items within each order. |
| `brink_sales2_order_entry_voids` | Void records for order entries. |
| `brink_sales2_order_entry_taxes` | Tax records applied to order entries. |
| `brink_sales2_order_entry_promotions` | Promotion records applied to order entries. |
| `brink_sales2_order_entry_discounts` | Discount records applied to order entries. |
| `brink_sales2_order_entry_modifiers` | Modifier records applied to order entries. |
| `brink_sales2_order_entry_modifier_taxes` | Tax records applied to entry modifiers. |
| `brink_sales2_order_entry_modifier_promotions` | Promotion records applied to entry modifiers. |
| `brink_sales2_order_payments` | Payment tender records at the order level. |
| `brink_sales2_order_payment_details` | Detail records for order payments. |
| `brink_sales2_order_payment_additional_data` | Additional key-value data for order payments. |
| `brink_sales2_order_taxes` | Tax records at the order level. |
| `brink_sales2_order_discounts` | Discount records at the order level. |
| `brink_sales2_order_promotions` | Promotion records at the order level. |
| `brink_sales2_order_surcharges` | Surcharge records at the order level. |
| `brink_sales2_order_surcharge_taxes` | Tax records applied to order surcharges. |
| `brink_sales2_order_deposits` | Deposit records at the order level. |

### `Labor.GetShifts`

| Staging Table | Description |
|---|---|
| `brink_labor_shifts` | Labor shift records with timing, pay rate, tips, and worked minutes breakdowns. |
| `brink_labor_breaks` | Break records associated with shifts, including break type and duration. |

### `Settings2.GetItems`

| Staging Table | Description |
|---|---|
| `brink_settings2_items` | Menu item snapshots including name, price, cost, availability, and KDS routing. |
| `brink_settings2_item_groups` | Item-to-group associations with ordinal positioning. |
| `brink_settings2_item_modifier_groups` | Modifier group associations for menu items. |
| `brink_settings2_item_taxes` | Tax records for menu items. |

### `Settings2.GetEmployees`

| Staging Table | Description |
|---|---|
| `brink_settings2_employees` | Employee snapshots including personal details, hire/termination dates, and payroll info. |
| `brink_settings2_employee_jobs` | Employee-to-job associations. |
| `brink_settings2_employee_permissions` | Permission records for employees. |
| `brink_settings2_employee_valid_locations` | Location assignments for employees. |

### `Settings2.GetJobs`

| Staging Table | Description |
|---|---|
| `brink_settings2_jobs` | Job (workforce role) snapshots with role flags and export codes. |
| `brink_settings2_job_menus` | Menu assignments for jobs. |
| `brink_settings2_job_shift_break_types` | Shift break type configurations for jobs. |

### `Settings2.GetDestinations` (lookup only)

| Staging Table | Description |
|---|---|
| `brink_settings2_get_destinations` | Destination snapshots for order routing (dine-in, delivery, pickup). |

## Curated Targets

Seven curated tables are populated from Brink staging data. Each curated target runs a DELETE then INSERT inside a single transaction scoped by tenant and business date or snapshot date.

| Curated Target | Staging Dependencies | Scope |
|---|---|---|
| `curated.pos_orders` | `brink_sales2_orders`, `brink_settings2_get_destinations` | Business date |
| `curated.pos_order_items` | `brink_sales2_orders`, `brink_sales2_order_entries`, `brink_sales2_order_entry_modifiers`, `brink_settings2_items`, `brink_settings2_item_groups` | Business date |
| `curated.pos_menu_items` | `brink_settings2_items`, `brink_settings2_item_groups` | Snapshot date |
| `curated.employees` | `brink_settings2_employees` | Snapshot date |
| `curated.pos_jobs` | `brink_settings2_jobs` | Snapshot date |
| `curated.labor_shifts` | `brink_labor_shifts`, `brink_labor_breaks` | Business date |
| `curated.pos_destinations` | `brink_settings2_get_destinations` | Snapshot date |

## Data Flow Diagram

```mermaid
flowchart TD
    subgraph Brink API
        E1["Sales2.GetOrders"]
        E2["Labor.GetShifts"]
        E3["Settings2.GetItems"]
        E4["Settings2.GetEmployees"]
        E5["Settings2.GetJobs"]
        E6["Settings2.GetDestinations"]
    end

    subgraph S3 Raw
        R1["Raw XML payloads"]
    end

    subgraph S3 Load
        L1["Normalized Parquet artifacts"]
    end

    subgraph Redshift Staging
        S1["brink_sales2_orders<br/>+ 17 child tables"]
        S2["brink_labor_shifts<br/>brink_labor_breaks"]
        S3["brink_settings2_items<br/>brink_settings2_item_groups<br/>+ 2 child tables"]
        S4["brink_settings2_employees<br/>+ 3 child tables"]
        S5["brink_settings2_jobs<br/>+ 2 child tables"]
        S6["brink_settings2_get_destinations"]
    end

    subgraph Redshift Curated
        C1["curated.pos_orders"]
        C2["curated.pos_order_items"]
        C3["curated.pos_menu_items"]
        C4["curated.employees"]
        C5["curated.pos_jobs"]
        C6["curated.labor_shifts"]
        C7["curated.pos_destinations"]
    end

    E1 --> R1
    E2 --> R1
    E3 --> R1
    E4 --> R1
    E5 --> R1
    E6 --> R1

    R1 --> L1

    L1 --> S1
    L1 --> S2
    L1 --> S3
    L1 --> S4
    L1 --> S5
    L1 --> S6

    S1 --> C1
    S6 --> C1
    S1 --> C2
    S3 --> C2
    S3 --> C3
    S4 --> C4
    S5 --> C5
    S2 --> C6
    S6 --> C7
```

## Curated Target Details

### `curated.pos_orders`

Order header fact table. One row per business order. Enriched with `order_type`, `destination`, `service_channel`, and `fulfillment_channel` from a LEFT JOIN to `brink_settings2_get_destinations` on `destination_id`. Status is derived from `is_refund`, `is_future_order`, and `is_closed` flags. Payment method, terminal name, store metadata, currency, and loyalty fields are not yet populated.

### `curated.pos_order_items`

Order line fact table. One row per order entry. Quantity is set to 1 per Brink entry. Modifier data is aggregated from `brink_sales2_order_entry_modifiers` into JSON and a text summary. Category ID comes from `brink_settings2_item_groups` filtered to `item_group_ordinal = 1`. Category name is not yet populated.

### `curated.pos_menu_items`

Menu item snapshot dimension. One row per item per snapshot date. Category ID from `brink_settings2_item_groups` (ordinal 1). `is_86d` and `availability_status` are derived from `is_active`. `kds_delay` maps from `days_available`. Category name and station/KDS routing are not yet populated.

### `curated.employees`

Employee dimension. One row per employee snapshot. `is_active` is derived as `NOT COALESCE(terminated, FALSE)`. SCD2 columns (`effective_from_utc`, `effective_to_utc`, `is_current`) are deferred to NULL.

### `curated.pos_jobs`

Job (workforce role) dimension. One row per job snapshot. Contains boolean role flags (`is_bartender`, `is_delivery_driver`, `is_delivery_dispatcher`, `is_hostess`) and `training` flag. SCD2 columns and `is_active` are deferred to NULL.

### `curated.labor_shifts`

Labor shift fact table. One row per shift. Break data is aggregated inline from `brink_labor_breaks` via LEFT JOIN and GROUP BY: `break_count = COUNT(b.shift_id)`, `total_break_minutes = COALESCE(SUM(b.minutes), 0)`. `shift_number` maps from `s.number` in staging.

### `curated.pos_destinations`

Destination reference dimension. One row per destination snapshot. Used both as a standalone curated table and as a lookup join in `curated.pos_orders` for order routing context. SCD2 columns are deferred to NULL.

## Current Limitations

The following fields and capabilities are not yet populated or implemented:

### Unmapped curated fields

- `pos_orders`: `payment_method`, `terminal_name`, `store_id`, `store_name`, `points_used`, `additional_payment`, `currency_code`, `order_key`, `tenant_key` are NULL.
- `pos_order_items`: `category_name`, `kds_delay`, `order_item_key`, `currency_code`, `size_name` are NULL.
- `pos_menu_items`: `category_name`, `kds_station`, `menu_item_key`, `currency_code`, `timezone_name`, `86d_reason`, `86d_at` are NULL.
- `employees`, `pos_jobs`, `pos_destinations`: SCD2 columns (`effective_from_utc`, `effective_to_utc`, `is_current`), enrichment keys, and `currency_code` are NULL.

### Unimplemented capabilities

- `curated_to_s3` export stage is reserved but not implemented.
- `Settings2.GetDestinations` standalone extract scheduling is not active (used only as lookup join).
- Additional Brink endpoints such as `Settings2.GetTables`, `Settings2.GetKitchenQueues`, `Settings2.GetTaxes`, `Settings.GetTenders` are not in the active extract flow.
- Surrogate key generation (`order_key`, `employee_key`, `job_key`, `labor_shift_key`, `destination_key`, `menu_item_key`, `tenant_key`) is deferred.

## Related Documents

- [ETL Source Adapter Framework](etl-source-adapter-framework.md) - framework architecture and stage responsibilities
- [Warehouse Data Index](warehouse-data/index.md) - warehouse model overview
- [Brink POS Staging Model](warehouse-data/brinkPos-staging-model/index.md) - staging table documentation
- [Brink POS Curated Mappings](warehouse-data/brinkPos-curated-model-mapps/index.md) - curated-to-staging column mappings
- [Curated Model](warehouse-data/curated-model/index.md) - curated table documentation
- [ETL Ingestion Flow](../../flows/etl-ingestion.md) - end-to-end ingestion flow diagram
- [ETL Architecture](../../ETL.md) - top-level ETL and data ingestion architecture