Chapter 9: Architecture Decision Records

This chapter documents the key architectural decisions made during AdPriority’s design phase. Each decision follows a standardized format: Status, Context, Decision, and Consequences. These records serve as a permanent log of why the system is built the way it is, preventing future re-litigation of settled questions and providing onboarding context for new contributors.

ADR Index

ADR-001: Google Sheets for MVP Sync (Not Content API)

Status: Accepted

Date: 2026-02-10

Context

AdPriority needs to write custom label data to Google Merchant Center so that Google Ads campaigns can use those labels for product segmentation and bid optimization. There are two mechanisms to deliver custom label data to GMC:

1. Google Sheets supplemental feed: AdPriority writes data to a Google Sheet via the Sheets API. GMC is configured to fetch that Sheet on a daily schedule as a supplemental feed. Labels are applied to matching products automatically.

2. Content API for Shopping (v2.1): AdPriority calls the GMC Content API directly to update the customLabel0 through customLabel4 fields on each product. Updates take effect within minutes.

The Content API offers faster propagation but introduces significant complexity: OAuth consent for GMC access, per-product rate limits (2 updates/day/product), error handling for 124,060 variants, and merchant-side GMC configuration.

Decision

Use Google Sheets as the sole transport mechanism for the MVP (Phase 0-2).

Consequences

Positive:

• No GMC API authentication required from the merchant (simpler onboarding)
• Sheet is human-readable and debuggable (merchants can inspect the data)
• Proven with live Nexus data: 10/10 test products matched, zero issues
• Google Sheets API is well-documented and has generous quotas
• Merchants can manually edit individual rows in emergencies
• No rate limit concerns (Sheet can contain all 124,060 variants)

Negative:

• Labels propagate within 24 hours (not real-time)
• Merchant must manually register the Sheet as a supplemental feed in GMC (one-time setup, documented in onboarding wizard)
• Full sheet rewrite on each sync (acceptable for < 200k rows)
• Requires Google Sheets API OAuth (lighter than Content API OAuth)

Mitigations:

• Content API will be added in Phase 3+ for Growth/Pro tiers
• 24-hour latency is acceptable because priority changes are strategic (seasonal, weekly) not tactical (minute-by-minute)
• Onboarding wizard provides step-by-step GMC feed setup instructions

Validation

ADR-002: Express.js Over FastAPI

Status: Accepted

Date: 2026-02-06

Context

The backend framework must serve the REST API, handle Shopify OAuth, process webhooks, run scheduled jobs, and serve the compiled React frontend. Two candidates were evaluated:

1. Express.js + TypeScript: The dominant Node.js web framework. Matches Shopify’s official app templates, which use Node.js. The existing reference app at /volume1/docker/sales-page-app/ uses FastAPI (Python), but its Shopify integration required custom adapter code.

2. FastAPI (Python): High-performance async framework. Strong typing via Pydantic. The existing sales-page-app uses this stack, providing a reference implementation.

Decision

Use Express.js with TypeScript for the AdPriority backend.

Consequences

Positive:

• Shopify’s official @shopify/shopify-app-express package provides production-ready OAuth, session management, and webhook verification
• Single language (TypeScript) across frontend and backend reduces context switching
• Prisma ORM has first-class TypeScript support with generated types
• React frontend build outputs static files that Express serves natively
• node-cron and Bull (future) are mature job scheduling solutions
• NPM ecosystem has packages for Google Sheets API, Google OAuth, and Content API
• Shopify Polaris tooling (CLI, React bindings) assumes a JavaScript ecosystem

Negative:

• Cannot reuse FastAPI code from the existing sales-page-app
• Node.js single-threaded model requires careful handling of CPU-intensive work (not a concern for this I/O-bound application)
• Express.js 4.x requires manual async error handling (mitigated by express-async-errors package)

Alternatives Rejected:

ADR-003: Prisma ORM

Status: Accepted

Date: 2026-02-06

Context

AdPriority requires a database access layer that provides:

1. Type-safe queries (prevent runtime SQL errors)
2. Migration management (versioned schema changes)
3. Multi-tenant middleware support (automatic store_id scoping)
4. PostgreSQL compatibility
5. Reasonable learning curve for a single developer

Three ORMs were evaluated: Prisma, Drizzle, and Knex.

Decision

Use Prisma 5.x as the ORM for AdPriority.

Consequences

Positive:

• Type safety: Generated TypeScript types from schema definition. Query results are fully typed; field name typos are compile-time errors.
• Schema-as-code: schema.prisma serves as the single source of truth for the database schema. Human-readable, version-controlled.
• Migrations: prisma migrate dev generates SQL migration files automatically from schema changes. Migration history is tracked.
• Multi-tenant middleware: Prisma Client Extensions support the getTenantClient() pattern described in Chapter 6, enabling automatic store_id scoping on all queries.
• Tooling: prisma studio provides a GUI database browser for debugging. prisma generate regenerates the client after schema changes.
• Ecosystem: Widely adopted, extensive documentation, active community.

Negative:

• Prisma’s query engine adds a binary dependency (~20 MB) to the Docker image.
• Complex raw SQL queries (e.g., window functions, CTEs) require prisma.$queryRaw, losing some type safety.
• N+1 query patterns require explicit include statements (not automatically optimized).
• Schema changes require running prisma migrate dev, which may conflict with prisma db push if used inconsistently (project will use migrations exclusively).

Alternatives Rejected:

Schema Example

model Product {
  id                  String   @id @default(uuid())
  storeId             String   @map("store_id")
  store               Store    @relation(fields: [storeId], references: [id], onDelete: Cascade)
  shopifyProductId    BigInt   @map("shopify_product_id")
  shopifyVariantId    BigInt?  @map("shopify_variant_id")
  title               String?
  productType         String?  @map("product_type")
  priority            Int      @default(3)
  prioritySource      String   @default("default") @map("priority_source")
  priorityLocked      Boolean  @default(false) @map("priority_locked")
  syncStatus          String   @default("pending") @map("sync_status")
  createdAt           DateTime @default(now()) @map("created_at")
  updatedAt           DateTime @updatedAt @map("updated_at")

  @@unique([storeId, shopifyProductId, shopifyVariantId])
  @@index([storeId])
  @@index([storeId, priority])
  @@map("products")
}

ADR-004: Supplemental Feed (Not Primary Feed Replacement)

Status: Accepted

Date: 2026-02-06

Context

Google Merchant Center supports two feed types:

1. Primary feed: The authoritative source of product data (title, price, availability, images, etc.). For Shopify stores, this is typically managed by the Google & YouTube Shopify channel app, which syncs product data automatically.

2. Supplemental feed: An additive feed that overlays additional attributes onto products already present in the primary feed. It can add or overwrite specific fields (like custom labels) without affecting the core product data.

AdPriority needs to set custom_label_0 through custom_label_4 on GMC products. This can be done by either replacing the primary feed (taking over all product data management) or by adding a supplemental feed (only touching the custom label fields).

Decision

Use a supplemental feed exclusively. Never replace or interfere with the primary feed.

Consequences

Positive:

• Non-destructive: AdPriority cannot accidentally break product titles, prices, images, or availability in GMC. The primary feed remains untouched.
• Additive only: Custom labels are layered on top of existing product data. If AdPriority is uninstalled, labels simply revert to empty on the next primary feed update.
• Coexistence: Merchants can use AdPriority alongside other feed management tools without conflict.
• Simpler scope: AdPriority only needs to manage 6 columns (id + 5 labels), not the full product catalog schema (30+ fields).
• Lower risk: A bug in AdPriority cannot cause products to disappear from GMC or display incorrect prices.

Negative:

• Products must already exist in the primary feed before supplemental labels can be applied. If a product is not in GMC (e.g., not synced by the Shopify Google channel), AdPriority cannot add it.
• The id column in the supplemental feed must exactly match the primary feed’s product IDs. Any mismatch (case-sensitive) results in the label being silently ignored.

Mitigations:

• Reconciliation job (daily) checks for products in the AdPriority database that have no match in GMC, alerting the merchant.
• Onboarding wizard verifies that the Shopify Google channel is active before proceeding with feed setup.

ADR-005: 0-5 Scoring Scale

Status: Accepted

Date: 2026-02-06

Context

AdPriority needs a priority scoring system that:

1. Is simple enough for non-technical merchants to understand immediately
2. Maps cleanly to Google Ads campaign segmentation (listing group filters)
3. Provides enough granularity for meaningful budget differentiation
4. Works with GMC custom label constraints (max 1,000 unique values per label)

Scales considered:

Decision

Use an integer scale from 0 to 5, where 0 means “exclude from advertising” and 5 means “push hard with maximum budget.”

Consequences

Score definitions:

+-------+-------------+------------------------------------------+
| Score | Label       | Google Ads Behavior                      |
+-------+-------------+------------------------------------------+
|   5   | Push Hard   | Maximum budget, aggressive bidding       |
|   4   | Strong      | High budget, balanced approach           |
|   3   | Moderate    | Standard budget, conservative bidding    |
|   2   | Light       | Minimal budget, strict ROAS targets      |
|   1   | Minimal     | Very low budget, highest ROAS only       |
|   0   | Exclude     | No advertising spend                     |
+-------+-------------+------------------------------------------+

Positive:

• Six values (0-5) map perfectly to 6 PMAX asset groups or listing group subdivisions, each with its own budget allocation.
• Score 0 has a clear, unambiguous meaning: do not advertise this product. This handles out-of-stock, discontinued, and deliberately excluded items.
• The scale is intuitive. Merchants think in terms of “high priority” vs. “low priority,” not percentages or letter grades.
• Only 6 unique values in custom_label_0, well within GMC’s 1,000 limit.
• Integer type in the database with a CHECK constraint (0-5) prevents invalid values.

Negative:

• Six tiers may feel coarse for large catalogs (10,000+ products). Merchants may want finer control within a tier.
• No decimal values (e.g., 3.5 for “between moderate and strong”).

Mitigations:

• The other four custom labels (custom_label_1 through custom_label_4) provide additional segmentation dimensions (season, category, status, brand tier). Combined with the priority score, this gives merchants 6 x 4 x ~15 x 5 x 3 = ~5,400 possible product segments.
• Future enhancement: sub-tiers within a score (e.g., 4a, 4b) if merchant feedback indicates a need.

ADR-006: Cloudflare Tunnel for Deployment

Status: Accepted

Date: 2026-02-06

Context

Shopify apps must be accessible over HTTPS from Shopify’s servers for:

1. OAuth callback URLs
2. Webhook delivery
3. App Bridge iFrame loading

The AdPriority backend runs on a Synology NAS on a local network (192.168.1.26). Making it accessible from the internet requires one of:

Decision

Use Cloudflare Tunnel (via the existing cloudflared service at /volume1/docker/services/cloudflared/) to expose the AdPriority backend over HTTPS.

Consequences

Positive:

• Existing infrastructure: The cloudflared container is already running on the NAS for other services. Adding a route for AdPriority requires only a configuration change, not a new deployment.
• No open ports: The NAS firewall does not need any inbound port rules. The tunnel agent initiates an outbound connection to Cloudflare’s edge, which then proxies inbound traffic through the tunnel.
• Free TLS: Cloudflare provides TLS certificates automatically. No need to manage Let’s Encrypt or self-signed certificates.
• DDoS protection: Cloudflare’s edge network provides basic DDoS mitigation, rate limiting, and bot detection.
• Stable URLs: The tunnel provides a permanent hostname (e.g., app.adpriority.com) that does not change on NAS reboot or IP change.
• Zero cost: Cloudflare Tunnels are free for the traffic volumes AdPriority will generate.

Negative:

• Dependency on Cloudflare’s infrastructure. If Cloudflare has an outage, the app is unreachable (rare, but possible).
• Added latency: traffic routes through Cloudflare’s nearest edge node before reaching the NAS. Adds ~10-30ms per request (negligible for this use case).
• Tunnel token must be stored securely (already managed via .env file).

Configuration:

# cloudflared tunnel configuration (addition)
ingress:
  - hostname: app.adpriority.com
    service: http://adpriority:3010
  # ... existing routes ...
  - service: http_status:404

ADR-007: Variant-Level IDs in GMC

Status: Accepted

Date: 2026-02-10

Context

Google Merchant Center identifies Shopify products using a composite ID format. Analysis of the Nexus GMC export (124,060 products, exported 2026-02-10) revealed that all products use variant-level IDs, not product-level IDs.

Observed format:

shopify_US_{productId}_{variantId}

Real examples from the Nexus GMC export:

shopify_US_8779355160808_46050142748904
shopify_US_9128994570472_47260097118440
shopify_US_9057367064808_47004004712680
shopify_US_9238797418728_47750439567592
shopify_US_7609551716584_42582395650280

No product-level IDs (without the variant suffix) were found in the export. This means that even if a Shopify product has only one variant, GMC still represents it with the variant-level ID.

Decision

AdPriority will generate and match GMC product IDs at the variant level exclusively, using the format shopify_US_{productId}_{variantId}.

Consequences

Positive:

• 100% match rate with Nexus GMC products (validated with 10/10 test products)
• Future-proof: supports products with multiple variants (size x color), where each variant can have a different inventory status
• Enables variant-level priority overrides in future versions (e.g., exclude out-of-stock sizes while keeping in-stock sizes at priority 5)

Negative:

• More rows in the Google Sheet: Nexus has ~5,582 products but ~20,000 active variants, and 124,060 total GMC entries. The supplemental feed must include one row per variant, not one row per product.
• Priority is set at the product level in the MVP, meaning all variants of a product share the same priority score. This results in duplicate label values across variants (acceptable, not wasteful).
• Slightly more complex ID generation: must fetch variant IDs from Shopify, not just product IDs.

Key data points:

+--------------------------------+--------+
| Metric                         | Value  |
+--------------------------------+--------+
| Nexus products in Shopify      | 5,582  |
| Nexus active products          | 2,425  |
| Nexus variants in GMC          | 124,060|
| Estimated active variants      | ~20,000|
| ID format confirmed            | Yes    |
| Product-only IDs found         | 0      |
| Country code (Nexus)           | US     |
| Product ID length              | 13 digits |
| Variant ID length              | 14 digits |
+--------------------------------+--------+

GMC ID construction:

function buildGmcProductId(
  countryCode: string,   // "US"
  productId: bigint,     // 8779355160808
  variantId: bigint      // 46050142748904
): string {
  return `shopify_${countryCode}_${productId}_${variantId}`;
  // Result: "shopify_US_8779355160808_46050142748904"
}

Item Group ID (used for variant grouping in GMC): Just the Shopify product ID without prefix or variant suffix.

Item Group ID: 8779355160808

This groups all variants of a product together in GMC reporting and campaign structure while allowing individual variant targeting via the full shopify_US_{productId}_{variantId} offer ID.

ADR Template

Future architecture decisions should follow this template:

## ADR-NNN: [Title]

**Status**: Proposed | Accepted | Deprecated | Superseded by ADR-NNN

**Date**: YYYY-MM-DD

### Context

[Describe the situation, constraints, and forces at play.
What problem are we solving? What options exist?]

### Decision

[State the decision clearly in one or two sentences.]

### Consequences

**Positive**:
- [Benefit 1]
- [Benefit 2]

**Negative**:
- [Trade-off 1]
- [Trade-off 2]

**Mitigations**:
- [How negative consequences are addressed]

Chapter Summary

These seven ADRs establish the architectural foundation for AdPriority:

The common thread across all decisions is pragmatism over purity. Every choice prioritizes shipping a working MVP on existing infrastructure over theoretical architectural elegance. Complexity is deferred to later phases where it can be justified by real usage data and paying customers.