# blueillusion-vm-bq-sync

A FastAPI service that extracts data from Microsoft SQL Server views and synchronizes it to BigQuery, Databricks, or both. It supports full, incremental, hash-difference, and bounded backfill synchronization, with background job tracking in SQLite. The repository also contains an auxiliary Postman/cURL tooling package for generating and importing API test requests.

## Business Summary

This service moves retail data from a Microsoft SQL Server reporting database into cloud data platforms so Merchmix and other analytics systems can use current information. It can refresh all data, update only changed records, compare source and target data, and repair historical gaps. Operators can monitor jobs and receive Slack notifications when synchronization succeeds or fails.

## Capabilities

| Capability | Category | Status | Access | Exposure |
|---|---|---|---|---|
| Retail Data Synchronization | Other | `production` | `execute` | user_facing, agent_facing |
| Incremental Data Synchronization | Other | `production` | `execute` | user_facing, agent_facing |
| Data Change Detection | Reporting | `production` | `execute` | user_facing, agent_facing |
| Historical Data Backfill | Other | `production` | `execute` | user_facing, agent_facing |
| Source Table Discovery | Other | `production` | `read` | user_facing, agent_facing |
| Read-Only Source Querying | Reporting | `production` | `read` | user_facing, agent_facing |
| Draft Data Submission | Products | `partial` | `write` | user_facing, agent_facing |
| API Key Administration | Authentication | `production` | `write` | user_facing, agent_facing |
| Synchronization Job Monitoring | Reporting | `production` | `read` | user_facing, agent_facing |
| API Test Collection Generation | Other | `internal_only` | `execute` | internal |

**Retail Data Synchronization** — Copies data from Microsoft SQL Server views into BigQuery, Databricks, or both for downstream retail planning and analytics.

**Incremental Data Synchronization** — Updates cloud data using only records that changed since the previous synchronization, reducing processing time and data transfer.

**Data Change Detection** — Finds inserted, updated, or missing records by comparing source data with target data, with an option to remove records missing from the source.

**Historical Data Backfill** — Repairs missing or incorrect historical data for a selected table and date or range field.

**Source Table Discovery** — Lists the available reporting views in a Microsoft SQL Server schema so an operator can choose data to synchronize.

**Read-Only Source Querying** — Allows authorized operators to inspect source data with SQL queries restricted to SELECT statements.

**Draft Data Submission** — Accepts structured draft records for styles, purchase orders, and store transfers, supporting merchandise and operational planning workflows.

**API Key Administration** — Protects the service with API keys and allows an authorized operator to rotate or revoke the current key.

**Synchronization Job Monitoring** — Provides job identifiers and status information so operators can track long-running data transfers.

**API Test Collection Generation** — Creates cURL examples from the service's OpenAPI document and imports them into Postman for API testing.

## Workflows

**Full or incremental synchronization** — Starts a background extraction and loads the selected SQL Server data into one or more cloud targets.

1. An authenticated client submits a full or incremental sync request.
2. The API creates a job record and returns a job identifier.
3. The worker connects to Microsoft SQL Server through ODBC and extracts configured views or tables.
4. The worker writes data to BigQuery, Databricks, or both, using chunking and Parquet/GCS staging where configured.
5. The job status and table-level results are recorded and a Slack notification may be sent.
6. The client retrieves job status using the job identifier.

**Hash-difference synchronization** — Compares source and target records and applies detected changes.

1. An authenticated client submits a hash-diff request with keys and comparison options.
2. The worker compares source and destination data in chunks.
3. If changes exceed the configured threshold, the implementation can fall back to a full extract.
4. Changed records are merged into the destination and optionally missing records are deleted.
5. The job result reports counts, modes, keys, and deletions.

**Historical backfill and gap check** — Identifies and repairs records missing from a cloud target for a bounded historical period.

1. An authenticated client submits table, key, filter-column, value-column, and start/end date parameters.
2. The service validates identifiers and ensures the date range is bounded and ordered.
3. A gap-check endpoint compares normalized source and target keys and aggregates.
4. A backfill endpoint creates a filtered synchronization job for the selected range.
5. The client monitors the resulting job.

**API documentation test import** — Converts the running API contract into reusable Postman requests.

1. The importer fetches the FastAPI OpenAPI document.
2. It resolves schema references and generates example request bodies and cURL commands.
3. The cURL parser converts requests into Postman collection items.
4. The tooling imports requests into a new or existing Postman folder and can apply variable mappings.

## Architecture

A single FastAPI application provides the HTTP API and delegates long-running synchronization to worker functions executed as background jobs. Local SQLite stores job metadata, logs, and API-key hashes; SQLAlchemy/pyodbc reads Microsoft SQL Server, while Google Cloud and Databricks clients write target data. Deployment is VM-based, with CI/CD copying Python and shell files to an Azure VM on the main branch; an older Google Cloud Build deployment configuration is also present.

**Components:** FastAPI application and route handlers in main.py, Pydantic request models in schemas.py and draft_schemas.py, Synchronization and notification worker in worker.py, Backfill comparison and repair logic in backfill.py, SQLite job and API-key persistence in database.py, API-key authentication in security.py, Microsoft SQL Server access through SQLAlchemy and pyodbc, BigQuery and Google Cloud Storage integrations, Databricks SQL integration, Postman/cURL importer utilities

**Patterns:** HTTP JSON API, API-key authentication with hashed keys, Background job execution, Local SQLite state store with WAL mode, Chunked extraction and Parquet-based data handling, Configurable destination strategy: BigQuery, Databricks, or both, Infrastructure deployment by SSH/scp to a VM

## Interfaces

| Kind | Identifier | Description |
|---|---|---|
| `http` | `GET /version` | Returns the application version. |
| `http` | `POST /api/v1/keys/rotate` | Generates and stores a replacement API key for an authenticated caller. |
| `http` | `DELETE /api/v1/keys/revoke` | Revokes the authenticated API key. |
| `http` | `POST /api/v1/tables` | Lists SQL Server views in a requested schema. |
| `http` | `POST /api/v1/query` | Executes an authenticated SELECT-only query against SQL Server. |
| `http` | `POST /api/v1/sync/full` | Starts a full synchronization job. |
| `http` | `POST /api/v1/sync/incremental` | Starts an incremental synchronization job. |
| `http` | `POST /api/v1/sync/hash_diff` | Starts a hash-difference synchronization job. |
| `http` | `GET /api/v1/sync` | Returns synchronization job information. |
| `http` | `GET /api/v1/sync/{job_id}` | Returns status or details for a specific synchronization job. |
| `http` | `POST /api/v1/backfill` | Starts a bounded historical backfill job. |
| `http` | `POST /api/v1/backfill/check` | Checks source/target gaps for a bounded historical range. |
| `http` | `POST /api/v1/backfill/rolling` | Starts a rolling backfill operation. |
| `http` | `POST /api/v1/drafts` | Accepts structured style, purchase-order, or store-transfer draft data. |
| `other` | `FastAPI OpenAPI document` | The running application exposes the standard /openapi.json document used by the repository's Postman tooling. |
| `other` | `Postman importer CLI/Makefile` | Internal scripts generate cURL requests and import them into Postman collections. |

## Data

| Entity | Ownership | Description |
|---|---|---|
| Synchronization jobs | `owns` | Job identifiers, status, errors, and creation timestamps for background synchronization and backfill operations. |
| Job logs | `owns` | Messages associated with synchronization jobs. |
| API keys | `owns` | Hashed API keys, short prefixes, creation timestamps, and revocation state. |
| SQL Server source views and query results | `reads` | Retail and business data read from configured Microsoft SQL Server databases and schemas. |
| BigQuery target tables | `writes` | Synchronized source data written to configured Google BigQuery projects and datasets. |
| Databricks target tables | `writes` | Synchronized source data written to configured Databricks catalogs and schemas. |
| Style drafts | `writes` | Draft style metadata including codes, descriptions, merchandising classifications, season, colour, pricing, quantity, and manufacturer information. |
| Purchase-order drafts | `writes` | Draft purchase orders with supplier, dates, status, quantities, costs, and size-level lines. |
| Store-transfer drafts | `writes` | Draft store transfers with source/destination, style, status, quantities, metadata, and size-level lines. |

## Dependencies

| Name | Kind | Relationship | Criticality |
|---|---|---|---|
| Microsoft SQL Server | `database` | `reads` | `critical` |
| BigQuery | `database` | `writes` | `required` |
| Google Cloud Storage | `other` | `uses` | `supporting` |
| Databricks SQL | `database` | `writes` | `optional` |
| SQLite jobs database | `database` | `depends_on` | `critical` |
| Slack | `external_service` | `publishes` | `optional` |
| Postman API | `external_service` | `calls` | `supporting` |
| FastAPI | `library` | `uses` | `critical` |
| SQLAlchemy | `library` | `uses` | `critical` |
| Pandas and PyArrow | `library` | `uses` | `required` |

## Technology

Python, FastAPI, Uvicorn, Pydantic, SQLAlchemy, pyodbc, Microsoft SQL Server ODBC Driver 18, SQLite, Google Cloud BigQuery, Google Cloud Storage, Databricks SQL Connector, Pandas, PyArrow, Shell deployment scripts, Bitbucket Pipelines

## Limitations

- The evidence does not show the complete implementations of all route handlers, so exact response schemas and detailed sync semantics are not fully established.
- The /api/v1/drafts route and draft models are present, but the evidence does not establish whether drafts are persisted locally, written to a cloud target, or forwarded elsewhere.
- No retail planning calculations such as WSSI, OTB, range planning, size curves, markdown optimization, allocation, or replenishment are implemented in the provided source evidence; this repository is an integration and data-movement service.
- BigQuery and Databricks are supported by imports and request configuration, but deployment-specific credentials, connectivity, and target table conventions are environment-dependent.
- The service has a local SQLite state store and VM deployment configuration; no queue broker or separate job service is evidenced.
- The repository includes both Azure VM and older Google Cloud Build deployment configurations, so the authoritative production deployment path should be confirmed operationally.
- Some source excerpts are redacted or truncated, including authentication details and parts of worker.py; secrets and exact implementation details cannot be assessed from them.

## Agent Instructions

Use this repository for SQL Server-to-cloud data synchronization, historical backfills, source inspection, and synchronization job monitoring. Do not route merchandise-planning calculation requests here unless another service is identified.

- Authenticate API calls with the configured API-key mechanism; do not request, expose, or infer secret values.
- For data movement, first use POST /api/v1/tables to discover available SQL Server views, then choose full, incremental, hash-diff, or backfill synchronization based on the data-repair need.
- Treat synchronization and backfill operations as asynchronous: capture the returned job_id and poll GET /api/v1/sync/{job_id}.
- Use POST /api/v1/query only for read-only SELECT inspection; the request model explicitly rejects non-SELECT statements.
- When requesting incremental or hash-diff synchronization, provide valid primary-key and watermark/table configuration as required by SyncRequest validation.
- Use the backfill check endpoint before a repair when the goal is to quantify source-only and target-only records.
- Do not assume draft submission has durable storage or a downstream business effect until the complete /api/v1/drafts implementation is inspected.
- Do not claim this service performs WSSI, OTB, range planning, markdown, allocation, replenishment, or forecasting calculations based on the current evidence.
- For API testing or contract generation, use the internal postman-curl-importer tooling rather than treating it as a retail-facing capability.

## Source

| Field | Value |
|---|---|
| Repository | `blueillusion-vm-bq-sync` |
| Branch | `main` |
| Commit | `85238bd6e0d1` |
| Generated by | `gpt-5.6-luna` |

*Generated by the Merchmix Skills Platform from the current codebase.*