# Reporting Data Warehouse — Phase 1 / 1.5 submit notes

## What's in this submit

A new **Reporting Data Warehouse** tool replacing the previous live-proxy reporting workflow.

- New controller: `app/Controllers/Tools/ProxyDataWarehouse.php` (cloned from `ReportingProxy`; live-proxy/caching paths removed, dashboard added).
- New view: `app/Views/Tools/ProxyDataWarehouseDashboard.php` (table of registered reports).
- New view: `app/Views/Tools/ProxyDataWarehouse.php` (registration form — Type / Saved Report / Schedule on one row).
- New gateway: `app/Controllers/External/DwGateway.php` → `GET /dw/v1/<slug>` returns the Parquet file with `proxykey` header auth (matches old proxy's auth model — encrypted, static).
- New async ingest endpoint: `app/Controllers/External/DwIngestRunner.php` → invoked by SchedulerService **or** directly by the dashboard's Refresh button via `Utils::executeAsyncPostCurl`. Auth via `Async-Key`.
- New CLI command: `php spark dw:ingest <reportId>` for one-off runs.
- New services in `app/Services/DataWarehouse/`: `IngestRunner`, `FlowPhpParquetWriter` (implementing `ParquetWriterInterface`), `ReportFieldMap`, `ScheduleMap`, `DwReportsService`, `IngestResult`, `IngestArtifact`.
- New config: `app/Config/DataWarehouse.php`.
- New migrations (run on staging):
  - `2026-05-11-100000_CreateClientApiKeysTable.php`
  - `2026-05-11-100100_CreateDwReportsTable.php`
  - `2026-05-11-100200_CreateDwRunsTable.php`
  - `2026-05-11-100300_CreateDwAccessLogTable.php`
- New composer dep: `flow-php/parquet ^0.37` (Parquet writer, pure PHP, MIT).
- Two new database tables for **`proxyDataWarehouse`** tool (created via `CREATE TABLE LIKE reportingProxy_*` on staging — see "Manual steps below"):
  - `proxyDataWarehouse_jobs`
  - `proxyDataWarehouse_logs`
  - `proxyDataWarehouse_executions`
  - `proxyDataWarehouse_secureMappings`

## Architecture summary

```
Power BI ────► GET /dw/v1/<slug>           (DwGateway: header auth, streams parquet)
                       │
                       ▼
              <localStagingDir>/<bmap_subdomain_hash>/<reportId>/latest.parquet
                       ▲
                       │   writes parquet
                       │
SchedulerService ─────► POST /external/dwIngestRunner (Async-Key auth)
                       │   or
Dashboard "Refresh now" Utils::executeAsyncPostCurl   ─► same endpoint
                       │
                       ▼
              IngestRunner::run(reportId)
                       │
                       │   paginates Businessmap API
                       ▼
              FlowPhpParquetWriter (streaming, embeds schema)
```

The scheduler is the **existing** `SchedulerService` you already run on cron. Rows in the `scheduler` table whose `path` is `/external/dwIngestRunner` are picked up automatically. No new cron entry needed.

## Auto-deploy behavior (CI/CD)

The auto-pull pipeline already handles everything:

1. `composer install` picks up the new `flow-php/parquet` dep on next deploy.
2. `php spark migrate` runs the new migrations on next scheduled tick:
   - `client_api_keys`, `dw_reports`, `dw_runs`, `dw_access_log` (warehouse-specific tables).
   - `proxyDataWarehouse_jobs`, `_logs`, `_executions`, `_secureMappings` (BaseTool-pattern tables
     required by `BaseTool::initJob` / `insertLog` and `ProxyDataWarehouseModel`; created by
     `2026-05-13-110000_CreateProxyDataWarehouseTables.php`, mirrors the `workspaceManagers`
     convention).
3. The existing `php spark scheduler:run` cron will pick up `dw_reports` rows whose
   `path` is `/external/dwIngestRunner` — no new cron entry needed.

## Storage paths

Parquet files are stored under a configurable root. The convention mirrors the rest of the
codebase (see `UPLOAD_PATH` usage in `Tools/UsersBoards`, `AiUsage`, `CustomFields`, etc.):

- **Prod**: define a `DW_PATH` constant at the server level (php.ini `auto_prepend_file`, or
  whatever mechanism currently defines `UPLOAD_PATH`). Point it at a directory **outside the
  docroot** with the same ownership and Apache-deny rules `UPLOAD_PATH` already uses. The
  gateway controller reads the files through PHP; Apache never serves them directly.
- **Local dev / no `DW_PATH`**: falls back to `writable/dw`, which is already protected by
  `writable/.htaccess` (`Require all denied`).

The directory tree (`<DW_PATH>/<bmap_subdomain_hash>/<report_id>/latest.parquet`) is auto-created
by `FlowPhpParquetWriter` on first ingest, so no manual `mkdir`/`chown` step is required as long
as the parent directory is writable by the cron + web user (same posture as `UPLOAD_PATH`).

## Security restored to production posture

All local-dev bypasses added during development have been removed. The codebase is back to its
pre-Phase-1 security posture in these files:

- `app/Controllers/Components/BaseTool.php` — full reCAPTCHA + Businessmap auth + privilege checks.
- `app/Controllers/Components/HTTPConnect.php` — `throwError()` exits with JSON error as before.
- `app/Libraries/CustomExceptionHandler.php` — phones home to Businessmap on every exception.
- `app/Config/Cookie.php` — `secure=true`, `samesite='None'` (requires HTTPS).
- `app/Controllers/External/DwIngestRunner.php` — always requires valid `Async-Key`.
- `app/Views/Tools/BmapLogin.php` — grecaptcha.execute is unconditional.
- `app/Controllers/Tools/ProxyDataWarehouse.php` — no dev stubs in `detectOwner`, `renderDashboard`, `createForm`.

The only `CI_ENVIRONMENT === 'development'` branch left from this work is in `app/Config/DataWarehouse.php`, which redirects the storage path to `WRITEPATH . 'dw'` so local devs don't need `/var/dw`. It is a path fallback only and does **not** affect security.

## Re-enabling local dev mode (recipe)

When you need to develop locally again without HTTPS / real Businessmap creds / Memcached, the
patches below restore the bypasses we used during Phase 1. They are limited to local dev because
they're gated on `CI_ENVIRONMENT === 'development'`.

1. **`app/Config/Cookie.php`** — allow plain-HTTP cookies:
   ```php
   public bool $secure = true;

   public function __construct()
   {
       if (env('CI_ENVIRONMENT') === 'development') {
           $this->secure = false;
           $this->samesite = 'Lax';
       }
   }
   ```

2. **`app/Libraries/CustomExceptionHandler.php` → `sendException()`** — top of method:
   ```php
   if (env('CI_ENVIRONMENT') === 'development') {
       log_message('error', 'CustomExceptionHandler [dev, sendException skipped]: ' . json_encode($exceptionData));
       return;
   }
   ```

3. **`app/Controllers/Components/HTTPConnect.php` → `throwError()`** — replace the `!getSilent()` branch:
   ```php
   if (!$this->getSilent()) {
       if (env('CI_ENVIRONMENT') === 'development') {
           log_message('error', 'HTTPConnect::throwError [dev, suppressed exit]: ' . json_encode($this->error));
           return;
       }
       exit(json_encode($this->error));
   }
   ```

4. **`app/Controllers/Components/BaseTool.php` → `checkLogins()`** — replace the "Check if subdomain and api key are valid" block with a `$devBypass` flag that skips `getAPILimits`/`getLoggedUser`/privilege check, stubbing `$loggedUser` to `['user_id' => 1, 'email' => env('SSO_USER_EMAIL') ?: 'localdev@example.com']`.

5. **`app/Controllers/Components/BaseTool.php` → `validateRecaptcha()`** — top of method:
   ```php
   if (env('CI_ENVIRONMENT') === 'development') {
       $this->session->set($this->currentClass . '_logged_in', true);
       return true;
   }
   ```

6. **`app/Controllers/External/DwIngestRunner.php` → `run()`** — replace the Async-Key check with:
   ```php
   if (env('CI_ENVIRONMENT') !== 'development' && !Utils::checkAsyncKey($asyncKey)) {
       return $this->response->setStatusCode(401)->setBody('Unauthorized');
   }
   ```

7. **`app/Views/Tools/BmapLogin.php`** — wrap `grecaptcha.execute(...)` so the form submits directly when `recaptchaSiteKey` is empty.

8. **`app/Controllers/Tools/ProxyDataWarehouse.php`** — stub `loggedUser` in `renderDashboard()` and `createForm()` when in dev, and short-circuit `detectOwner()` to `return true`.

After applying, set `.env`:
```
CI_ENVIRONMENT = development
app.baseURL = 'http://localhost:8888/businessmap-sa/public/'
database.default.hostname = 127.0.0.1
database.default.username = root
database.default.password = root
database.default.port = 8889
ENC_KEY = 'localdev-enc-key-32-chars-min-xx'
CREDS_APIKEY = 'localdev-creds-apikey'
PASSKEY2 = 'localdev-passkey-change-me'
SSO_USER_EMAIL = 'you@businessmap.io'
TESTING_SUBDOMAIN = '<real-or-placeholder>'
TESTING_APIKEY = '<real-or-placeholder>'
```

## Sanity check before merging

```sh
php spark routes                       # verify all dw / proxyDataWarehouse routes registered
php -l app/Controllers/Tools/ProxyDataWarehouse.php
php -l app/Controllers/External/DwGateway.php
php -l app/Controllers/External/DwIngestRunner.php
php -l app/Services/DataWarehouse/IngestRunner.php
php -l app/Services/DataWarehouse/FlowPhpParquetWriter.php
```