# Integration Conformance and Ingestion Completeness — Design **Date:** 2026-08-10 **Status:** Draft, awaiting review **Scope:** Sub-project C1 of six. Depends on A (OAuth connector) and B (catalog sync), both built. --- ## 1. Purpose Two things, in one piece because the second changes where the first stores its configuration. **Part 1 — conform to the platform's integration pattern.** Sub-project A stored the Shopify connection in a new table of its own. The platform already has a three-table integration pattern that thirteen other providers follow, and `shopify` is already a valid provider in it. A diverged from that convention. This corrects it. **Part 2 — capture the product data the recommendation matching specification needs** and that sub-project B does not collect, and make the HTTP product-API source connectable. **Success criteria:** 1. A Shopify connection appears in `integrations` and `integration_connect_tracker` exactly as the other thirteen providers do, with credentials read from `platform_configs`. 2. A merchant can connect an authenticated HTTP product API by supplying **only a base URL and credentials**, and its catalog syncs. Proven end to end against `dummyjson.com`. The schema mapping is inferred inside the pipeline; the merchant never reviews or approves it. 3. `tenant_relations`, `rating` and `review_count` are captured where a source exposes them. 4. A description-only edit no longer changes `content_hash`. ## 2. The established integration pattern Discovered by inspecting the live master database, not from documentation. ``` platform_configs per-platform OAuth app: clientId, clientSecret, redirectUri, scope | integrations per-tenant connection: provider, authType, accessToken, status | integration_connect_tracker lifecycle audit, jobName "integration_oauth_connect" ``` Thirteen platforms are configured, 133 connections tracked, and every redirect URI points at the Node API: ``` https://dev-api.galaxiq.ai/api/{platform}/callback ``` The tracker's `deliveryStatus` records the state machine all of them follow: ``` not_connected -> oauth_authorize_url_ready -> integration_configure_complete -> connected ``` with `outcome` of `success` or `failed`. **There is no `shopify` row in `platform_configs`.** This is the first Shopify connection the platform has had. ### How sub-project A diverged | Convention | What A built | |---|---| | Credentials in `platform_configs` | `.env` of the Python service | | Callback on the Node API | `localhost:8001/api/shopify/callback`, Python | | Connection in `integrations` + tracker | `product_sources` | A works and is live-verified. This document does not rebuild it — it makes it write to the right tables. The user has stated the OAuth flow itself will migrate to the other backend later; putting the data in the correct place now makes that a code move rather than a data migration. ## 3. What can and cannot conform `integrations.provider` is a Postgres enum with a fixed value list: facebook, instagram, tiktok, twitter, linkedin, mailchimp, slack, **shopify**, google_ads, pinterest, reddit, google, dropbox, onedrive, threads, google_analytics. **There is no value for a custom product API.** Adding one is a Sequelize migration owned by the Node application. | Source | Home | Reason | |---|---|---| | Shopify | `platform_configs` + `integrations` + tracker | `shopify` is a valid provider; full conformance available today | | Custom HTTP API | `product_sources` | Cannot be represented in the enum, and `integrations` has no column for a field map, `records_path`, `url_template` or `currency` | `product_sources` is retained for two jobs: HTTP-source sync configuration, and `last_synced_at` for every source. It is no longer the record of *whether* a Shopify tenant is connected — `integrations` is. ## 4. Writing an `integrations` row Verified constraints, from the live schema: | Column | Constraint | Value | |---|---|---| | `user` | **NOT NULL**, FK to `Users(id)` | derived — see below | | `provider` | NOT NULL enum | `shopify` | | `authType` | enum, default `oauth2` | `oauth2` | | `accessToken` | **NOT NULL** TEXT | Fernet ciphertext — see §5 | | `tenantId` | nullable, FK to `Tenants(id)` | the `org_` prefix stripped | | `organisationId` | nullable, FK | left null | | `status` | enum, default `ACTIVE` | `ACTIVE`, or `INACTIVE` on disconnect | | `metadata` | JSONB | shop domain, scopes, api version, currency, primary domain | ### Deriving the user `integrations.user` is NOT NULL with a foreign key, and the OAuth flow has no session — that is the `TODO(auth)` hole recorded in A. The user is therefore derived from the tenant: ```sql SELECT "userId" FROM "Tenants" WHERE id = ``` Verified: for the live test tenant this resolves to a real `Users` row. **Two of 28 tenants have a null `userId`.** For those the connection cannot be recorded in `integrations`, and the callback must fail with a clear message rather than crash on the constraint or silently skip the row. This is a symptom, not a design: the platform's own schema requires knowing who connected, and our flow does not. Closing `TODO(auth)` would remove the derivation entirely. ### Tenant id mapping The strategist service uses `org_8c32bf3e-6a18-4739-9b1c-94c0cf11125f`. The platform uses `8c32bf3e-6a18-4739-9b1c-94c0cf11125f`. Verified identical apart from the prefix, and the uuid matches `Tenants.id`. The mapping is a prefix strip, implemented once in a helper rather than inline at each call site. ## 5. Token storage — a deliberate deviation `integrations.accessToken` is plain `TEXT`. The other thirteen providers appear to store raw tokens there. A stores the Shopify token Fernet-encrypted. **This design stores the Fernet ciphertext in `accessToken` rather than downgrading to plaintext.** Matching a convention is not worth reducing the protection on a live credential that grants read access to a merchant's entire catalog. Consequences, accepted: - The Node application cannot read the Shopify token. It has no Shopify consumer today, so nothing breaks. - When the flow migrates to that backend, either the Fernet key travels with it or merchants re-authorise. Re-authorising is the safer of the two and is a one-click action. - `metadata.token_encoding` is set to `fernet` so the next reader knows without guessing. ## 6. Tracker rows The callback writes `integration_connect_tracker` rows using the same vocabulary as the other providers: | Point in flow | `deliveryStatus` | `outcome` | |---|---|---| | `/install` builds the authorize URL | `oauth_authorize_url_ready` | `success` | | Callback fails any security control | `not_connected` | `failed`, with `errorMessage` | | Token stored and verified | `connected` | `success` | `metadata` carries `jobName: "integration_oauth_connect"`, `connectStep`, `providerRaw`, and timestamps, matching the shape observed on existing rows. `errorMessage` never carries a token, a code, or a response body. ## 7. `platform_configs` A `shopify` row is added: `clientId`, `clientSecret`, `redirectUri`, `scope`, `isActive`. The Python service reads its client credentials from this row instead of `SHOPIFY_CLIENT_ID` and `SHOPIFY_CLIENT_SECRET` in `.env`, falling back to the environment only when the row is absent, so a missing row cannot break the working flow. This also fixes credential rotation properly: the secret exposed earlier in development gets rotated in one place that every service reads, rather than in a per-service `.env`. ## 8. Schema additions for the matching specification New columns on `{tenant}.strategist_products`, via B's existing `migrate_products_table`: ```sql tenant_relations JSONB NOT NULL DEFAULT '{}', -- {"related": [], "cross_sells": [], "upsells": []} rating REAL, review_count INT, featured_rank INT, missing_fields TEXT[] NOT NULL DEFAULT '{}' -- non-empty means incomplete, never served ``` One existing constraint is also relaxed: ```sql ALTER TABLE {tenant}.strategist_products ALTER COLUMN product_url DROP NOT NULL; ``` See §10 — a product whose source exposes no URL is stored rather than rejected. Dependents were checked: `find_by_url` simply never matches a null-URL row, and the crawler's scoped delete is unaffected because crawled products always carry a URL. The one visible consequence is that `to_card` returns a card with a null `url`, which a front end must render as unclickable rather than as a broken link. **Raw, not normalised.** The matching spec's B3 consumes `normalised_prominence`, but normalising needs the tenant's whole distribution, which belongs to the offline job. A value normalised at ingestion goes stale as soon as one product is added. **One JSONB column rather than three arrays.** A later sub-project flattens all three kinds into `source='tenant', score=1.0`. Three columns would encode a distinction nothing reads, while JSONB keeps it free for a future merchandiser view. `tenant_relations` holds the **source's external ids**, not GalaxiQ product keys. Resolving them happens when neighbours are materialised, because a referenced product may not be ingested yet when its referrer is normalised. ## 9. Capture ### Shopify A targeted metafield lookup, rather than widening the general metafields block: ```graphql complementary: metafield( namespace: "shopify--discovery--product_recommendation" key: "complementary_products" ) { value } ``` The value is a JSON array of product GIDs, parsed into `tenant_relations.related`. **This will be empty on the test store.** Profiling found only `test_data/binding_mount`, `test_data/snowboard_length`, `test_data/snowboard_weight`, `global/title_tag` and `global/description_tag`. Shopify has no native related-products field; merchant-supplied relations come from the Search & Discovery app. The path is built because A2 is the highest-weighted source in the matching specification (`1.00`, above complements at `0.80` and content at `0.75`) and should not be structurally impossible to populate. Shopify's Admin API exposes no rating or review count — those belong to review apps, each with its own metafield. `rating` and `review_count` stay null, and the matching spec's prominence term drops out, which its option 4 explicitly permits. ### HTTP sources Five new optional field-map keys: `related_ids`, `cross_sells`, `upsells`, `rating`, `review_count`, each resolved by the existing `catalog_fieldmap` executor. A source mapping none of them behaves exactly as today. ## 10. A product with no URL is stored, flagged, and not active A product URL is genuinely needed — a recommendation a visitor cannot click is worthless. Sub-project B therefore *rejects* any product without one. **But rejection is the wrong response when a source has no URLs at all.** The reference product API exposes none — no `url`, no `handle`, no slug. Under B's rule a merchant connects, every product fails validation, and they see an empty catalog with no explanation. A rule meant to protect them produces the worst possible outcome, and hides the actual problem. **New behaviour — store, flag, exclude:** | | | |---|---| | **Stored** | with `product_url` null, so the data is not lost and the gap is inspectable | | **Flagged** | `missing_fields` contains `product_url` | | **Not active** | a product with a non-empty `missing_fields` is incomplete and must never be served as a suggestion | | **Visible** | counted in the sync report and surfaced in the catalog listing, so the merchant can see and fix it | ```sql missing_fields TEXT[] NOT NULL DEFAULT '{}' ``` **Why a separate column rather than `status`.** `status` carries the *source's* own value — Shopify's `ACTIVE`, `DRAFT`, `ARCHIVED`. Overwriting it to mark incompleteness would destroy that, and a merchant would be unable to tell an archived product from an unlinkable one. `missing_fields` also extends: a future requirement — no image, no price — joins the same list without another column and without changing the rule that non-empty means incomplete. **Definition of active.** A product is active, and therefore eligible to be served, when `missing_fields` is empty. This is a data-completeness rule, not a recommendation-ranking rule: it says the record is unusable, not that the product is a poor suggestion. Ranking remains owned by separate work. The remaining hard rejections are unchanged: no `external_id`, no title or a title under three characters, no price or a negative price, no variants. Those cannot be flagged and carried, because there is nothing usable left to store. **Made loud, not silent.** The sync report gains `incomplete`, counted alongside `rejected`, so a source producing unlinkable products is obvious in the response rather than discovered weeks later by a merchant wondering why nothing is recommended. ## 11. Prices must be comparable across sources Every price rule in the matching specification compares two numbers directly: ``` price_mult = exp( -(ln(price_c / P))^2 / (2 * sigma^2) ) price_fit(b, a) accessories priced 5-40 percent of the anchor ``` **Those comparisons are meaningless across currencies, and nothing errors.** The live test tenant already holds 17 products priced in `INR`. Connect the reference HTTP API and it contributes `USD`. A ₹885 snowboard against a $9.99 item computes a ratio of 88 — the cheaper item is scored as wildly out of band when it is in fact five times the price. The system produces confident, wrong recommendations, and only for tenants with more than one source, which is precisely the configuration this whole sub-project enables. ### Reference currency Each tenant has one **reference currency**: the currency of its first active source, stored in `strategist_settings` under `reference_currency`. Every price is additionally stored converted into it. ```sql price_reference_cents INT, -- price_cents converted to the tenant's reference currency fx_rate_used REAL -- the rate applied, for audit and recomputation ``` `price_cents`, `price_max_cents` and `compare_at_cents` keep the **source's own currency** untouched — a price shown to a shopper must be the real one, and a converted figure must never be displayed. Only comparison uses the reference value. ### Where rates come from A small operator-maintained table in the master database: ```sql fx_rates ( base_currency text, quote_currency text, rate real, fetched_at timestamptz, PRIMARY KEY (base_currency, quote_currency) ) ``` **No external rates provider in this sub-project.** Adding one means a network dependency, a key to manage, and a staleness failure mode, inside a piece of work about ingestion. A seeded table is enough to make comparison correct, and a provider can replace it later without changing a single consumer, because everything reads `price_reference_cents`. A source whose currency equals the reference currency uses rate `1.0` and needs no row. ### When a rate is missing The product is **flagged incomplete**, exactly as for a missing URL: ``` missing_fields -> ["price_reference"] ``` so it is stored, visible and counted, but never served. Comparing an unconverted price is worse than not recommending the product at all, because the failure is invisible in the output and looks like a ranking opinion rather than a data gap. ## 12. The `content_hash` correction The matching specification's embedding input is: ``` title + " " + brand + " " + category_path.join(" > ") + " " + top_attributes ``` Description is **deliberately excluded** — long descriptions are dominated by boilerplate that pulls every product toward the same vector. B's `content_hash` includes description, so a description-only edit changes the hash and triggers a re-embed that cannot change the embedding. That is exactly the churn the incremental rule exists to prevent. **Fix:** remove description from `content_hash`. It stays in `record_hash`, so the row is still updated. **Accepted consequence:** every existing `content_hash` changes once, so the next sync re-embeds the whole catalog a final time. At 17 products this is free. The same change made after a large tenant onboards would not be, which is why it is made now. ## 13. Endpoints ``` POST /sources/http connect an HTTP source: base URL and credentials GET /sources list every connected source, any kind DELETE /sources/{kind}/{ref} disconnect a source ``` **A merchant supplies a base URL and credentials. Nothing else is required.** No field map, no `url_template`, no `currency`. Everything else is inferred by the pipeline on first sync. An earlier draft of this design had a fourth endpoint, `POST /sources/http/preview`, which proposed a field map for a merchant to review and approve before connecting. It was removed deliberately: a schema mapping is a machine problem, not a merchant decision, and requiring approval put a human gate in front of every connection. Human approval exists in exactly one place in this system — low-confidence pairings — and it belongs there, not here. Optional overrides are still accepted on `POST /sources/http` for a merchant whose API is unusual enough that inference gets it wrong, but none is required and the endpoint never rejects a connection for their absence. `GET /sources` reads Shopify connections from `integrations` and HTTP sources from `product_sources`, presenting one list. It generalises `/api/shopify/status`, which stays so the existing chat UI keeps working. `DELETE` sets `integrations.status = 'INACTIVE'` for Shopify, marks the `product_sources` row revoked for an HTTP source, and deletes that source's products using B's existing scoped delete. ## 14. Server-side request forgery `POST /sources/http/preview` makes this server fetch a **user-supplied URL** carrying **user-supplied credentials**. That is a textbook SSRF sink: pointed at `169.254.169.254` it reads cloud instance metadata; at `127.0.0.1` or an RFC1918 address it reaches internal services. The codebase has no such guard today. Before any request leaves the process: | Control | Rule | |---|---| | Scheme | `http` or `https` only | | Host | Resolve the hostname, then reject the **resolved IP** if loopback, link-local, private, reserved or multicast | | Redirects | Not followed — a redirect can point anywhere after the check passes | | Timeout | 10 seconds | | Response cap | 2 MB, enforced while streaming | The resolved-IP check is load-bearing. Validating the hostname string alone lets `http://localhost.attacker.com`, which resolves to `127.0.0.1`, straight through. The same guard applies to `catalog_http.fetch_products`, since a stored base URL is user-supplied and is fetched on every sync. ## 15. Schema inference, inside the pipeline The mapping is inferred by the sync pipeline, not by the merchant. **When:** on a source's first sync, and again only when inference has never succeeded or the stored map stops resolving. The result is cached in `product_sources.config.fields`, so a steady-state sync makes **zero** LLM calls. This is the same discipline as the rest of the system: a model authors a deterministic artifact, and the artifact is what runs. **Input:** the sample record's *structure* — keys, each key's type, and one truncated example value per key. Not the full payload; a single record can be tens of kilobytes of description prose that teaches the model nothing about shape. **Output:** constrained to the exact target names `catalog_fieldmap` understands, with values restricted to the `$.a.b[0]` syntax it can execute. **Every inferred path is executed against the sample before it is stored.** A path that fails to resolve is discarded, so a mapping that would break at sync time never reaches the catalog. ### Inferring what the payload does not contain Two values the matching pipeline needs are often absent from a product API entirely. The merchant is not asked for them, and their absence never blocks a connection. | Value | Inference | Fallback | |---|---|---| | `url_template` | derived from the base URL and the identified id key — `https://dummyjson.com/products` plus `id` yields `.../products/{external_id}` | none; products are stored with a null URL — see §10 | | `currency` | the tenant's existing currency, if any source already established one | null | If inference fails entirely — the model is unavailable, or the payload is unrecognisable — the source stays connected and syncs nothing until the next attempt, rather than being rejected. The failure is visible in the sync report, not silent. ## 16. Error handling | Condition | Response | |---|---| | URL fails the SSRF guard | 400, naming the rule, no request made | | Sample fetch times out or exceeds the cap | 502 | | `records_path` resolves to nothing | 400 | | LLM call fails | 502 with an empty proposal, so the merchant can map by hand | | Missing `url_template` or `currency` | 400, naming both | | Field map references a path absent from the sample | 400, listing them | | Tenant has a null `Tenants.userId` | 409, explaining the connection cannot be recorded | | `DELETE` on an unknown source | 404 | No endpoint echoes a stored credential, and no exception message may carry one. ## 17. Testing **Unit — the SSRF guard**, the highest-risk code here: `127.0.0.1`, `localhost`, `169.254.169.254`, `10.0.0.1`, `192.168.1.1`, `172.16.0.1`, `[::1]`, `file:///etc/passwd`, `gopher://`, and a hostname that *resolves* to a private address. Each rejected before a socket opens. **Unit — tenant id mapping:** `org_` maps to ``; a value without the prefix, and a malformed one, are rejected rather than silently passed to a uuid column. **Unit — field-map validation:** an unresolvable path is dropped from a proposal, and a `POST /sources/http` carrying one is rejected. **Unit — hashing:** changing only the description leaves `content_hash` unchanged and changes `record_hash`. **Unit — capture:** a Shopify node carrying the complementary-products metafield populates `tenant_relations.related`; one without yields `{}` rather than null. **Integration — conformance:** a successful callback writes exactly one `integrations` row with the derived user, the stripped tenant uuid, and Fernet ciphertext in `accessToken`; plus tracker rows in the observed vocabulary. A tenant with a null `userId` produces a 409 and no partial row. **Live:** connect `dummyjson.com` through `/preview` then `/sources/http`, sync it, and confirm products land with resolvable URLs. This is the first execution of `catalog_http` and the field-map executor against a real API — both are currently proven only against fixtures. ## 18. Known limitations **Stored credentials are static; an expiring token will go stale.** The `auth` config supports `bearer`, `header` and `none`, and the key is stored once at connect. That is correct for a permanent API key, which is what most product APIs issue. It is **not** correct for a short-lived token: the reference API returns a JWT valid for 60 minutes by default, alongside a refresh token this design does nothing with. Such a source syncs successfully at first and then fails with 401 once the token expires, and the sync will correctly mark it errored — the merchant simply has to reconnect. Refresh-token support is deliberately out of scope here: it needs a per-provider refresh flow, and no real merchant API in view issues short-lived tokens. It is recorded so the first 401 on a JWT-based source is recognised as this, not as a bug in the auth handling. **A2 yields nothing on the test store.** No relation metafield exists there. The path stays empty until a merchant hand-links products or installs Shopify's Search & Discovery app. **Prominence stays null for Shopify.** No rating or review count in the Admin API. **`integrations` cannot hold a custom API source** until the Node application adds an enum value. Tracked, not worked around. **Two of 28 tenants cannot be conformed** because `Tenants.userId` is null. They receive a clear 409 rather than a silent skip. **Five merchant screens the matching specification requires are unowned.** It calls for a correctable complement-category map (A3, described as worth more than any amount of tuning), a tenant blocklist and a price display band (B2), an empty-rate health view (B5), and per-tenant recommendation counters (D1). None exists; only `tests/chat_ui.html`, a test harness with a hardcoded tenant. Every endpoint in this and later sub-projects is therefore designed as a documented API contract rather than assuming a surface. ## 19. Out of scope A4 attribute extraction; the `product_neighbors` table and offline matching job; complement inference; online ranking; measurement counters; the merchandiser interfaces themselves. Each is a later sub-project, C2 through C6. Closing `TODO(auth)` is also out of scope, though §4 shows the platform's schema is already pushing against it.