# Milestone 7 — Data mapping: existing PHP POS (`ssgplatforms_deeq`) → Kaabe SaaS tenant

**Status: PREPARATION — against the `pos.zip` baseline schema (165 tables).**

The mapping must be re-checked against the live database using the verification report and the backup copy. Any line marked **VERIFY** needs the real data.

## 1. Principle: the business is adopted in place, not moved

Kaabe uses **one database per business**, and the PHP POS stays the engine that runs on that database.

- **What doesn't change.** `ssgplatforms_deeq` keeps its name, its tables and every row. Every primary key and every historical record stays as it is: sale IDs, dates, prices, taxes, payments, stock, customers.
- **What the migration adds.** Two tables (`phppos_kaabe_meta`, `phppos_kaabe_location_meta`), two indexes on `phppos_sales`, a few `app_config` keys, five role templates, and one API key.
- **What gets created outside the business database.** The central Kaabe records.
- **What is never changed.** No row is updated, deleted or re-keyed in any business table.
- **One later change.** Each password hash is upgraded from MD5 to bcrypt when that user next signs in successfully (Milestone 1). This happens after cut-over and is not part of the migration.

Because of this, the validation (§6 of the migration plan) can require **identical checksums** for every POS table except the small, listed set that Kaabe adds to.

## 2. Important tables

Key columns come from `database/database.sql`. "Fate" says whether each record is preserved, transformed or newly generated.

| Existing table | Primary key | Important foreign keys | SaaS destination | Transformation | Data-quality check | Fate |
|---|---|---|---|---|---|---|
| `phppos_app_config` | key | — | Tenant DB (unchanged) + central `tenants` (name/contact copied once) | ADD keys `kaabe_entitlements`, `kaabe_status`; existing keys untouched. `company` → central `tenants.name` (verify with owner). | Company name may differ from the name the business uses — VERIFY with the business. | Preserved + new keys |
| `phppos_locations` | location_id | company_logo→app_files.file_id, tax_class_id→tax_classes.id | Tenant DB (unchanged) + `phppos_kaabe_location_meta` (kind) + central `tenant_locations` (mirror, via sync) | Every existing location → kind **branch** (migration #1). None becomes a warehouse automatically. Deleted locations keep deleted=1. | Locations that are really stores/warehouses must be confirmed by the business; a location changed to warehouse can no longer sell. | Preserved; meta generated |
| `phppos_registers` | register_id | location_id→locations.location_id | Tenant DB (unchanged) | None. Counted against the `registers` limit (Professional: 6 in placeholder plan). | More registers than the plan allows ⇒ limit blocks new registers only (existing kept). VERIFY count. | Preserved |
| `phppos_people` | person_id | image_id→app_files.file_id | Tenant DB (unchanged) | None. Shared by employees, customers, suppliers. | Duplicate people (same name/phone) are reported, never merged. | Preserved |
| `phppos_employees` | id | person_id→people.person_id | Tenant DB (unchanged); owner reference in `phppos_kaabe_meta.owner_person_id` and central `tenants.owner_pos_username` | Password column untouched (legacy MD5 upgraded to bcrypt at each user's next successful login — Milestone 1). Owner chosen by the business, NOT assumed to be person_id 1. | Duplicate/empty usernames, empty or non-MD5/bcrypt hashes, employees with no location, inactive owners — all REPORTED. | Preserved |
| `phppos_permissions / phppos_permissions_actions (+ _locations)` | module_id,person_id | person_id→employees.person_id, module_id→modules.module_id | Tenant DB (unchanged) | None. Plan gates act on top at runtime; no permission rows are deleted even for modules outside the plan. | Employees with permissions for modules outside the Professional plan lose access at runtime (reported per module). | Preserved |
| `phppos_permissions_templates (+ _template, _template_actions, …)` | id | — | Tenant DB | ADD five Kaabe templates (Business Owner, Business Admin, Branch Manager, Cashier, Inventory Manager) only if names do not already exist. Existing employees are NOT reassigned. | Name collision with existing templates ⇒ existing kept, Kaabe one skipped. | Preserved + new rows |
| `phppos_keys` | id | user_id→employees.person_id | Tenant DB | ADD one Kaabe platform key (SHA-1) + fingerprint in `phppos_kaabe_meta.platform_key_sha1`. Existing keys kept. | Existing business API keys count against `api_keys` (Professional: 2) — over-limit ⇒ no NEW keys; existing keep working only if the `api` module is in the plan (Professional: yes). | Preserved + 1 new |
| `phppos_items` | item_id | supplier_id→suppliers.person_id, category_id→categories.id, manufacturer_id→manufacturers.id, tax_class_id→tax_classes.id, main_image_id→item_images.image_id | Tenant DB (unchanged) | None. Counted against `products` limit. | Duplicate item_number/product_id, negative cost, deleted items with stock — REPORTED. | Preserved |
| `phppos_categories` | id | parent_id→categories.id, image_id→app_files.file_id | Tenant DB (unchanged) | None. | Orphan parent ids — REPORTED. | Preserved |
| `phppos_location_items` | location_id,item_id | location_id→locations.location_id, item_id→items.item_id, tax_class_id→tax_classes.id | Tenant DB (unchanged) | None. Stock per location stays authoritative; sync mirrors only totals. | Negative quantities, rows for deleted locations/items — REPORTED. | Preserved |
| `phppos_inventory` | trans_id | trans_items→items.item_id, trans_user→employees.person_id, location_id→locations.location_id, item_variation_id→item_variations.id | Tenant DB (unchanged) | None (stock movement history). | Rows referencing missing items/locations — REPORTED. | Preserved |
| `phppos_customers` | id | person_id→people.person_id, tier_id→price_tiers.id, tax_class_id→tax_classes.id, location_id→locations.location_id, default_term_id→terms.term_id, default_te… | Tenant DB (unchanged) | None. Never copied centrally. | Duplicate account numbers/emails, negative store-account balances — REPORTED. | Preserved |
| `phppos_suppliers` | id | person_id→people.person_id, tax_class_id→tax_classes.id | Tenant DB (unchanged) | None. | Duplicates — REPORTED. | Preserved |
| `phppos_sales` | sale_id | employee_id→employees.person_id, suspended→sale_types.id, return_sale_id→sales.sale_id, customer_subscription_id→customer_subscriptions.id, customer_id→custo… | Tenant DB (unchanged) → central `tenant_daily_metrics` (aggregates via Milestone 6 sync) | None. sale_id, sale_time, location_id, register_id, customer_id, totals, deleted/suspended, last_modified all untouched. ADD index `kaabe_sale_time` (#2) and `kaabe_last_modified` (#3) — indexes only. | sale_time is a MySQL TIMESTAMP: its displayed value depends on the server/session time_zone (see §5). Sales with NULL/zero dates, missing location, total ≠ items/payments — REPORTED. | Preserved |
| `phppos_sales_items (+ _taxes, _modifier_items)` | sale_id,item_id,line | item_id→items.item_id, sale_id→sales.sale_id, rule_id→price_rules.id, item_variation_id→item_variations.id, series_id→customers_series.id, items_quantity_uni… | Tenant DB (unchanged) | None. Quantities, prices, discounts, taxes untouched. | Lines referencing deleted items are normal (history). Missing sale_id parents — REPORTED. | Preserved |
| `phppos_sales_payments` | payment_id | sale_id→sales.sale_id | Tenant DB (unchanged) | None. | Payments whose sum ≠ sale total (normal for layaway/store account) — REPORTED with category. | Preserved |
| `phppos_receivings (+ _items)` | receiving_id | employee_id→employees.person_id, supplier_id→suppliers.person_id, location_id→locations.location_id, transfer_to_location_id→locations.location_id, signature… | Tenant DB (unchanged) | None. Transfers (transfer_to_location_id) are NOT sales and never enter sales metrics. | — | Preserved |
| `phppos_expenses` | id | location_id→locations.location_id, employee_id→employees.person_id, category_id→expenses_categories.id, approved_employee_id→employees.person_id, expense_ima… | Tenant DB (unchanged) | None. | — | Preserved |
| `phppos_giftcards` | giftcard_id | customer_id→customers.person_id | Tenant DB (unchanged) | None. Gated by `loyalty` module (Professional: yes). | — | Preserved |
| `phppos_sessions` | id | — | Tenant DB | Not migrated semantically; all users sign in again after cut-over (session cookie name/host change). | — | Preserved (stale) |
| `phppos_app_files` | file_id | — | Tenant DB (unchanged) | None. Counted against `storage_mb` via database size. | Database larger than 20 GB placeholder limit ⇒ uploads refused. VERIFY size. | Preserved |
| `phppos_migrations` | — | — | Tenant DB (unchanged) | Must equal the code version (20221115200805) — if lower, the POS's own migration runs on the COPY first and is reviewed. | Live version different from pos.zip — VERIFY (report). | Preserved |
| `(new) phppos_kaabe_meta` | — | — | Tenant DB | Generated: schema_version, owner_person_id, owner_configured, first_branch_configured, platform_key_sha1. | — | New |
| `(new) phppos_kaabe_location_meta` | — | — | Tenant DB | Generated: one row per location, kind=branch. | — | New |
| `Central: tenants, pos_instances, subscriptions, entitlement_pushes, tenant_sync_states, tenant_daily_metrics, tenant_locations, audit_logs` | — | — | Kaabe central DB | Generated by `kaabe:adopt` / Super Admin: tenant KB-code, slug (to confirm), encrypted DB credentials, Professional subscription, entitlements v1, sync backfill. | Credentials are the tenant's NEW rotated database password (audit S2), never the one in the old code. | New |

## 3. Mappings that cannot be decided from the code (VERIFY)

| # | Question | Needs | Default until confirmed |
|---|---|---|---|
| V1 | Who is the business owner (employee username)? | The business, plus the report's owner candidates | No default. `kaabe:adopt` requires `--owner`. |
| V2 | Business display name and sub-domain | The business, plus `app_config.company` from the report | Name from `company`; the sub-domain is chosen explicitly |
| V3 | Is any existing location actually a warehouse? | The business | All locations stay branches |
| V4 | Is the live POS code identical to `pos.zip`? | Report `file_diff.txt` / `changed_files.tar.gz` | Patches are applied only if the files match (`apply-overlay.sh` warns) |
| V5 | Live migration version | Report `migration_version_live` | Must be 20221115200805 |
| V6 | Server and session time zone of the current and target MariaDB | Report `database_server.time_zone` / `system_time_zone` | Target must use the same `time_zone` (see migration plan §5) |
| V7 | Does usage fit the Professional plan (branches, users, products, registers, storage, API keys)? | Report `professional_plan_fit` | If over: a per-business override is added, never data removal |
| V8 | Which optional modules does the business use today (appointments, work orders, deliveries, messages, price rules, loyalty, e-commerce)? | `tools/m7_profile.php` module usage | Professional includes all five SaaS modules and loyalty; e-commerce channels: 1 |
| V9 | Custom tables or columns added to the live database | Report `schema_diff.txt` | Kept untouched; listed in the exception report |

## 4. Every table (appendix)

All 165 tables are **preserved in place**. "FKs" is the number of foreign keys in the baseline schema.

| Table | Primary key | FKs | Referenced tables |
|---|---|---|---|
| `phppos_access` | id | 1 | keys |
| `phppos_additional_item_numbers` | item_id,item_number | 2 | item_variations, items |
| `phppos_app_config` | key | 0 | — |
| `phppos_app_files` | file_id | 0 | — |
| `phppos_appointment_types` | id | 0 | — |
| `phppos_appointments` | id | 4 | appointment_types, employees, locations, people |
| `phppos_attribute_values` | id | 1 | attributes |
| `phppos_attributes` | id | 1 | items |
| `phppos_categories` | id | 2 | app_files, categories |
| `phppos_currency_exchange_rates` | id | 0 | — |
| `phppos_customer_invoice_details` | invoice_details_id | 2 | customer_invoices, sales |
| `phppos_customer_invoice_payments` | payment_id | 1 | customer_invoices |
| `phppos_customer_invoices` | invoice_id | 3 | customers, locations, terms |
| `phppos_customer_subscriptions` | id | 5 | customers, item_variations, items, locations, sales |
| `phppos_customers` | id | 6 | locations, people, price_tiers, tax_classes, terms |
| `phppos_customers_series` | id | 3 | items, people, sales |
| `phppos_customers_series_log` | id | 1 | customers_series |
| `phppos_customers_taxes` | id | 1 | customers |
| `phppos_damaged_items_log` | id | 4 | item_variations, items, locations, sales |
| `phppos_delivery_categories` | id | 0 | — |
| `phppos_delivery_email_templates` | id | 0 | — |
| `phppos_delivery_files` | id | 2 | app_files, sales_deliveries |
| `phppos_delivery_item_kits` | delivery_item_kits_id | 2 | item_kits, sales_deliveries |
| `phppos_delivery_items` | delivery_items_id | 3 | item_variations, items, sales_deliveries |
| `phppos_delivery_statuses` | id | 0 | — |
| `phppos_ecommerce_locations` | location_id | 1 | locations |
| `phppos_employee_registers` | id | 2 | employees, registers |
| `phppos_employees` | id | 1 | people |
| `phppos_employees_app_config` | employee_id,key | 1 | employees |
| `phppos_employees_locations` | employee_id,location_id | 2 | employees, locations |
| `phppos_employees_reset_password` | id | 1 | employees |
| `phppos_employees_time_clock` | id | 2 | employees, locations |
| `phppos_employees_time_off` | id | 3 | locations, people |
| `phppos_expenses` | id | 5 | app_files, employees, expenses_categories, locations |
| `phppos_expenses_categories` | id | 1 | expenses_categories |
| `phppos_expenses_files` | id | 2 | app_files, expenses |
| `phppos_giftcards` | giftcard_id | 1 | customers |
| `phppos_giftcards_log` | id | 1 | giftcards |
| `phppos_grid_hidden_categories` | id | 2 | categories, locations |
| `phppos_grid_hidden_item_kits` | id | 2 | item_kits, locations |
| `phppos_grid_hidden_items` | id | 2 | items, locations |
| `phppos_grid_hidden_tags` | id | 2 | locations, tags |
| `phppos_inventory` | trans_id | 4 | employees, item_variations, items, locations |
| `phppos_inventory_counts` | id | 2 | employees, locations |
| `phppos_inventory_counts_items` | id | 3 | inventory_counts, item_variations, items |
| `phppos_item_attribute_values` | attribute_value_id,item_id | 2 | attribute_values, items |
| `phppos_item_attributes` | attribute_id,item_id | 2 | attributes, items |
| `phppos_item_images` | id | 3 | app_files, item_variations, items |
| `phppos_item_kit_images` | id | 2 | app_files, item_kits |
| `phppos_item_kit_item_kits` | id | 2 | item_kits |
| `phppos_item_kit_items` | id | 3 | item_kits, item_variations, items |
| `phppos_item_kits` | item_kit_id | 4 | categories, item_kit_images, manufacturers, tax_classes |
| `phppos_item_kits_modifiers` | item_kit_id,modifier_id | 2 | item_kits, modifiers |
| `phppos_item_kits_pricing_history` | id | 3 | employees, item_kits, locations |
| `phppos_item_kits_secondary_categories` | id | 2 | categories, item_kits |
| `phppos_item_kits_tags` | item_kit_id,tag_id | 2 | item_kits, tags |
| `phppos_item_kits_taxes` | id | 1 | item_kits |
| `phppos_item_kits_tier_prices` | tier_id,item_kit_id | 2 | item_kits, price_tiers |
| `phppos_item_variation_attribute_values` | attribute_value_id,item_variation_id | 2 | attribute_values, item_variations |
| `phppos_item_variations` | id | 2 | items, suppliers |
| `phppos_items` | item_id | 5 | categories, item_images, manufacturers, suppliers, tax_classes |
| `phppos_items_modifiers` | item_id,modifier_id | 2 | items, modifiers |
| `phppos_items_pricing_history` | id | 4 | employees, item_variations, items, locations |
| `phppos_items_quantity_units` | id | 1 | items |
| `phppos_items_secondary_categories` | id | 2 | categories, items |
| `phppos_items_secondary_suppliers` | id | 2 | items, suppliers |
| `phppos_items_serial_numbers` | id | 3 | item_variations, items, locations |
| `phppos_items_tags` | item_id,tag_id | 2 | items, tags |
| `phppos_items_taxes` | id | 1 | items |
| `phppos_items_tier_prices` | tier_id,item_id | 2 | items, price_tiers |
| `phppos_keys` | id | 1 | employees |
| `phppos_limits` | id | 1 | keys |
| `phppos_location_ban_item_kits` | id | 2 | item_kits, locations |
| `phppos_location_ban_items` | id | 2 | items, locations |
| `phppos_location_ban_tags` | id | 2 | locations, tags |
| `phppos_location_item_kits` | location_id,item_kit_id | 3 | item_kits, locations, tax_classes |
| `phppos_location_item_kits_taxes` | id | 2 | item_kits, locations |
| `phppos_location_item_kits_tier_prices` | tier_id,item_kit_id,location_id | 3 | item_kits, locations, price_tiers |
| `phppos_location_item_variations` | item_variation_id,location_id | 2 | item_variations, locations |
| `phppos_location_items` | location_id,item_id | 3 | items, locations, tax_classes |
| `phppos_location_items_taxes` | id | 2 | items, locations |
| `phppos_location_items_tier_prices` | tier_id,item_id,location_id | 3 | items, locations, price_tiers |
| `phppos_locations` | location_id | 2 | app_files, tax_classes |
| `phppos_logs` | id | 0 | — |
| `phppos_manufacturers` | id | 0 | — |
| `phppos_message_receiver` | id | 2 | employees, messages |
| `phppos_messages` | id | 1 | employees |
| `phppos_migrations` | — | 0 | — |
| `phppos_modifier_items` | id | 1 | modifiers |
| `phppos_modifiers` | id | 0 | — |
| `phppos_modules` | module_id | 0 | — |
| `phppos_modules_actions` | action_id,module_id | 1 | modules |
| `phppos_open_suspended_sales` | sale_id | 3 | employees, registers, sales |
| `phppos_people` | person_id | 1 | app_files |
| `phppos_people_files` | id | 1 | app_files |
| `phppos_people_name_prefixes` | id | 0 | — |
| `phppos_permissions` | module_id,person_id | 2 | employees, modules |
| `phppos_permissions_actions` | module_id,person_id,action_id | 3 | employees, modules, modules_actions |
| `phppos_permissions_actions_locations` | module_id,person_id,action_id,location_id | 4 | employees, locations, modules, modules_actions |
| `phppos_permissions_locations` | module_id,person_id,location_id | 3 | employees, locations, modules |
| `phppos_permissions_template` | template_id,module_id | 2 | modules, permissions_templates |
| `phppos_permissions_template_actions` | template_id,module_id,action_id | 3 | modules, modules_actions, permissions_templates |
| `phppos_permissions_template_actions_locations` | template_id,module_id,action_id,location_id | 4 | locations, modules, modules_actions, permissions_templates |
| `phppos_permissions_template_locations` | template_id,module_id,location_id | 3 | locations, modules, permissions_templates |
| `phppos_permissions_templates` | id | 0 | — |
| `phppos_price_rules` | id | 0 | — |
| `phppos_price_rules_categories` | id | 2 | categories, price_rules |
| `phppos_price_rules_item_kits` | id | 2 | item_kits, price_rules |
| `phppos_price_rules_items` | id | 2 | items, price_rules |
| `phppos_price_rules_locations` | id | 2 | locations, price_rules |
| `phppos_price_rules_manufacturers` | id | 2 | manufacturers, price_rules |
| `phppos_price_rules_price_breaks` | id | 1 | price_rules |
| `phppos_price_rules_tags` | id | 2 | price_rules, tags |
| `phppos_price_rules_tiers_exclude` | price_rule_id,tier_id | 2 | price_rules, price_tiers |
| `phppos_price_tiers` | id | 0 | — |
| `phppos_processing_return_logs` | id | 2 | employees, sales |
| `phppos_receivings` | receiving_id | 5 | app_files, employees, locations, suppliers |
| `phppos_receivings_items` | receiving_id,item_id,line | 5 | item_variations, items, items_quantity_units, receivings, suppliers |
| `phppos_receivings_items_taxes` | receiving_id,item_id,line,name,percent | 2 | items, receivings |
| `phppos_receivings_payments` | payment_id | 1 | receivings |
| `phppos_register_currency_denominations` | id | 0 | — |
| `phppos_register_log` | register_log_id | 3 | employees, registers |
| `phppos_register_log_audit` | id | 2 | employees, register_log |
| `phppos_register_log_denoms` | id | 2 | register_currency_denominations, register_log |
| `phppos_register_log_payments` | id | 1 | register_log |
| `phppos_registers` | register_id | 1 | locations |
| `phppos_registers_cart` | id | 1 | registers |
| `phppos_sale_types` | id | 0 | — |
| `phppos_sales` | sale_id | 12 | app_files, customer_subscriptions, customers, employees, locations, price_rules, price_tiers, registers, sale_types, sales |
| `phppos_sales_coupons` | id | 2 | price_rules, sales |
| `phppos_sales_deliveries` | id | 8 | delivery_statuses, employees, locations, people, sales, shipping_methods, shipping_zones, tax_classes |
| `phppos_sales_item_kits` | sale_id,item_kit_id,line | 4 | item_kits, price_rules, sales, suppliers |
| `phppos_sales_item_kits_modifier_items` | item_kit_id,sale_id,line,modifier_item_id | 3 | item_kits, modifier_items, sales |
| `phppos_sales_item_kits_taxes` | sale_id,item_kit_id,line,name,percent | 2 | item_kits, sales_item_kits |
| `phppos_sales_items` | sale_id,item_id,line | 9 | customers_series, employees, item_variations, items, items_quantity_units, price_rules, sales, suppliers |
| `phppos_sales_items_modifier_items` | item_id,sale_id,line,modifier_item_id | 3 | items, modifier_items, sales |
| `phppos_sales_items_notes` | note_id | 4 | employees, items, sales, workorder_statuses |
| `phppos_sales_items_taxes` | sale_id,item_id,line,name,percent | 2 | items, sales_items |
| `phppos_sales_payments` | payment_id | 1 | sales |
| `phppos_sales_work_orders` | id | 5 | app_files, employees, sales, workorder_statuses |
| `phppos_sessions` | id | 0 | — |
| `phppos_shipping_methods` | id | 2 | shipping_providers, tax_classes |
| `phppos_shipping_providers` | id | 0 | — |
| `phppos_shipping_zones` | id | 1 | tax_classes |
| `phppos_store_accounts` | sno | 2 | customers, sales |
| `phppos_store_accounts_paid_sales` | id | 2 | sales |
| `phppos_supplier_invoice_details` | invoice_details_id | 2 | receivings, supplier_invoices |
| `phppos_supplier_invoice_payments` | payment_id | 1 | supplier_invoices |
| `phppos_supplier_invoices` | invoice_id | 3 | locations, suppliers, terms |
| `phppos_supplier_store_accounts` | sno | 2 | receivings, suppliers |
| `phppos_supplier_store_accounts_paid_receivings` | id | 2 | receivings |
| `phppos_suppliers` | id | 2 | people, tax_classes |
| `phppos_suppliers_taxes` | id | 1 | suppliers |
| `phppos_tags` | id | 0 | — |
| `phppos_tax_classes` | id | 1 | locations |
| `phppos_tax_classes_taxes` | id | 1 | tax_classes |
| `phppos_terms` | term_id | 0 | — |
| `phppos_work_order_files` | id | 2 | app_files, sales_work_orders |
| `phppos_work_order_log` | id | 2 | employees, sales_work_orders |
| `phppos_work_orders_email_templates` | id | 0 | — |
| `phppos_workorder_checkbox_groups` | id | 0 | — |
| `phppos_workorder_checkboxes` | id | 1 | workorder_checkbox_groups |
| `phppos_workorder_checkboxes_states` | checkbox_id,workorder_id | 2 | sales_work_orders, workorder_checkboxes |
| `phppos_workorder_statuses` | id | 0 | — |
| `phppos_zips` | name | 1 | shipping_zones |