Option A — Phase 1: Shopify App Skeleton & Database (Detailed Plan)

This document is the detailed plan for Phase 1 of AppiFire AI Chat (Option A). Prerequisites: Phase 0 complete. High-level overview: Option-A-Tech-Stack-and-Tasks.md.

---

Phase 1 objective

Have a runnable Shopify app that: (1) installs via OAuth, (2) stores sessions, (3) uses a Postgres database with the full RAG schema (including pgvector), and (4) registers webhooks for products and app uninstall. No product sync or chat logic yet — that is Phase 2 and 3.

---

1. Create Shopify app (shopify app init — Remix or React Router)

1.1 Create the app from CLI

The CLI command is shopify app init (not app create node). You can also use npm init @shopify/app@latest.

Step	Action
1	Open a terminal in the folder where you want the project (e.g. shopify-ai-chatbot).
2	Run: shopify app init
3	When prompted: App name: AppiFire AI Chat. Template: choose Remix or React Router (Shopify often recommends React Router). Package manager: npm or pnpm. If asked, create as public app.

Outcome: CLI creates the app in the Partner Dashboard, generates API key and secret, and creates a local codebase.

1.2 Expected folder structure

appifire-ai-chat/
  app/           # Remix routes, loaders, Shopify server (OAuth, session)
  extensions/    # Theme app extension (add in Phase 4)
  prisma/        # ORM and migrations (if using Prisma)
  shopify.app.toml
  package.json

1.3 Verify local run (optional)

• Run npm install (or pnpm), then shopify app dev (or the script from package.json).
• Confirm the app starts and you can open the install flow in a browser (you may need to complete scopes and DB in the next steps first).

Checklist:

• App created with shopify app init (Remix or React Router, public).
• Local project has app/, shopify.app.toml, and (if applicable) prisma/.
• Partner Dashboard shows the new app with API key and secret.

---

2. Configure shopify.app.toml (scopes, app URL when deployed)

2.1 Set required scopes

Open shopify.app.toml and set scopes so the app can read products, inventory, content, and themes (needed for product sync and storefront extension):

scopes = "read_products,read_inventory,read_content,read_themes"

If the file uses a different format (e.g. array), add these four scopes.

2.2 Set app URL and redirect URLs (for deployed backend)

For local development, the CLI usually provides a tunnel and sets URLs. For a deployed backend (e.g. after you deploy in Phase 6):

• App URL: https://ai-chat.appifire.com (this app’s subdomain; marketing site is appifire.com).
• Allowed redirection URL(s): e.g. https://ai-chat.appifire.com/auth/callback (match your OAuth callback route).

You can leave these pointing at the CLI tunnel until you deploy; then update them in both shopify.app.toml and the Partner Dashboard (App setup → URLs).

2.3 Re-authorize after scope changes

After changing scopes, run shopify app dev again; Shopify will ask to re-authorize the app on any installed dev store.

Checklist:

• read_products, read_inventory, read_content, read_themes set in shopify.app.toml.
• App URL / redirect URLs documented or set for later deployment (application_url, auth.redirect_urls in toml).

---

3. Create database and run migrations (full RAG schema + pgvector)

3.1 Create .env and load it (Shopify + DB vars from .env)

• In the app root (e.g. appifire-ai-chat/), create a .env file. Add at least:
  • SHOPIFY_API_KEY — from Partner Dashboard (same as client_id in shopify.app.toml).
  • SHOPIFY_API_SECRET — from Partner Dashboard → App → API credentials (Client secret).
  • SHOPIFY_APP_URL — https://ai-chat.appifire.com (or your tunnel URL when running shopify app dev).
  • SCOPES — comma-separated, e.g. read_products,read_inventory,read_content,read_themes (must match shopify.app.toml).
  • DATABASE_URL — your Phase 0 Postgres connection string (see 3.2).
  • OPENROUTER_API_KEY — (optional for Phase 1) for Phase 2/3.
• Ensure the app loads .env before reading these: add import "dotenv/config" at the very top of app/shopify.server.js, and add the dotenv dependency (e.g. npm install dotenv). Then SHOPIFY_API_KEY, SHOPIFY_API_SECRET, SHOPIFY_APP_URL, and SCOPES are all read from .env.
• Keep .env out of version control (it should be in .gitignore).

3.2 Connect the app to your Postgres

• Ensure your Phase 0 Postgres has pgvector enabled and you have a connection string (DATABASE_URL).
• Put the full URL in .env:

DATABASE_URL="postgresql://user:password@host:5432/dbname?sslmode=require"

For Neon, use the pooled connection string from the dashboard and add ?sslmode=require (and optionally &connect_timeout=30 if the DB wakes from suspend).

• In prisma/schema.prisma, set the datasource to PostgreSQL and env("DATABASE_URL"):

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

(If the template defaulted to SQLite, change provider from "sqlite" and url from "file:dev.sqlite" to the above.)

3.3 Tables to create (from RAG schema)

Create migrations (Prisma, Drizzle, or raw SQL) for the following. Reference: Option-A-Phase-01-Design database schema for RAG.md.

#	Table	Purpose
1	shops	Tenant: shopify_shop_id, shop_domain, access_token (encrypted), plan, status, timestamps.
2	products	shop_id, shopify_product_id, title, description, product_type, vendor, tags (jsonb), status, last_synced_at, timestamps.
3	product_variants	product_id, shopify_variant_id, title, price, sku, inventory_quantity, timestamps.
4	knowledge_sources	shop_id, type, name, source_url, created_at.
5	knowledge_documents	shop_id, source_id, external_ref_id, title, raw_text, hash, status, timestamps.
6	knowledge_chunks	shop_id, document_id, product_id (nullable), chunk_text, chunk_index, token_count, created_at.
7	embeddings	chunk_id, embedding vector(1536) (pgvector), model, created_at.
8	chat_sessions	shop_id, visitor_id, started_at, ended_at.
9	chat_messages	session_id, role, message_text, created_at. For assistant messages also: model_used, openrouter_cost (actual), charged_cost (after markup), markup_factor (e.g. 1.0 = 100%, 1.5 = 150%) — so each shop owner sees what was asked, what AI replied, cost, and what they were charged.
10	retrieved_chunks	chat_message_id, chunk_id, similarity_score, rank.
11	ingestion_jobs (optional)	shop_id, source_type, status, total/processed documents, error_message, timestamps.
12	openrouter_calls	shop_id, chat_message_id (nullable, for chat replies), endpoint (embeddings|chat), model, openrouter_cost (actual), charged_cost (after markup), markup_factor, tokens, created_at — per-call log for attribution and dashboards; link to assistant message when endpoint = chat.

3.4 Session storage

If the Shopify Remix template uses Prisma for session storage, add a Session (or equivalent) table as required by @shopify/shopify-app-remix (or your chosen package). Often this is a table with shop/session key and value. Follow the template’s migration for sessions so OAuth and embedded app work.

3.5 pgvector index on embeddings

After creating the embeddings table with a column of type vector(1536) (dimension required; do not use untyped vector), add an index for similarity search:

CREATE INDEX "embeddings_embedding_idx" ON "embeddings"
  USING ivfflat ("embedding" vector_cosine_ops) WITH (lists = 100);

• The column must be vector(1536) (or your model’s dimension); otherwise ivfflat fails with “column does not have dimensions”.
• Use ivfflat with vector_cosine_ops; do not use btree with vector_cosine_ops (invalid).
• Adjust lists as needed (e.g. rows/1000 for small datasets). Some pgvector versions cannot create ivfflat on an empty table — create the index after ingesting embeddings. Alternatively use HNSW if your pgvector version supports it.

3.6 Native UUID and indexes

A Prisma migration (20260224110000_native_uuid_and_indexes) converts all RAG table id and foreign key columns from TEXT to PostgreSQL native uuid and adds indexes on foreign keys and key lookups (e.g. shop_id, session_id, shopify_product_id, created_at on openrouter_calls). The Session table is left as TEXT for Shopify compatibility. Apply with npx prisma migrate deploy.

3.7 Run migrations

• If the template came with a Prisma migration for SQLite (e.g. create_session_table): that migration uses SQLite types (DATETIME, inline PRIMARY KEY). Before running against Postgres, edit the migration SQL file in prisma/migrations/.../migration.sql:
  • Replace every DATETIME with TIMESTAMP(3).
  • Replace SQLite-style PRIMARY KEY on the column with a constraint at the end: CONSTRAINT "TableName_pkey" PRIMARY KEY ("id").
  • Save the file, then run migrations.
• Run migrations:
  • Development: npx prisma migrate dev --name init_postgres (creates/applies migrations). If the shadow DB or main DB is in a bad state, you can run npx prisma migrate reset (drops all data and reapplies).
  • Production: npx prisma migrate deploy.
• Verify: connect to the DB and list tables; once you add the RAG tables, confirm embeddings.embedding is vector(1536) (with dimensions) and the ivfflat index exists. When deploying from a SQL dump (e.g. Dokploy), ensure the dump runs CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public; first and uses vector(1536) and ivfflat (see Design database schema and databases/).

Checklist:

• .env created with DATABASE_URL, SHOPIFY_API_KEY, SHOPIFY_API_SECRET, SHOPIFY_APP_URL, SCOPES; dotenv/config loaded at top of shopify.server.js.
• DATABASE_URL set and Prisma schema uses provider = "postgresql" and url = env("DATABASE_URL") (and directUrl for migrations).
• Any existing SQLite migration updated to PostgreSQL types (Session migration uses TIMESTAMP(3) and CONSTRAINT "Session_pkey").
• All 11–12 tables created (Session, Shop, Product, ProductVariant, KnowledgeSource, KnowledgeDocument, KnowledgeChunk, Embedding, ChatSession, ChatMessage, RetrievedChunk, IngestionJob, OpenRouterCall); RAG migrations + Session in place.
• pgvector index on embeddings.embedding created (deferred: create after ingesting embeddings; see 3.5 and design doc — run the CREATE INDEX ... ivfflat SQL manually when ready).
• Native UUID + indexes migration present (20260224110000_native_uuid_and_indexes); run npx prisma migrate deploy to apply.
• Migrations run successfully; DB verified. (Run npx prisma migrate deploy and confirm tables in DB.)

---

4. Implement OAuth install/callback and session storage

4.1 Use the template’s OAuth flow

The app created with shopify app init usually includes:

• Install route: redirects merchant to Shopify OAuth.
• Callback route: exchanges code for access token, stores session.

Ensure your app uses the session storage you configured (e.g. Prisma session table). The template typically expects a session to be saved with:

• Shop domain (or shop ID).
• Access token (encrypted).
• Scope and any other required fields.

4.2 Persist shop in shops table

When OAuth callback succeeds (or on first load after install), upsert a row into shops:

• shopify_shop_id (or shop domain as unique key).
• shop_domain.
• access_token (encrypted at rest).
• plan (e.g. free), status (e.g. active), created_at/updated_at.

This ties every subsequent operation (products, chunks, chat) to a tenant. Use the same shop identifier from the session for all DB writes.

4.3 Embedded app and App Bridge

The Remix template normally serves the embedded admin UI and uses App Bridge. Ensure:

• The app loads inside Shopify Admin in an iframe.
• Session is available to the embedded app (template usually handles this via session token or cookie).

Checklist:

• OAuth install and callback routes work; merchant can install the app on a dev store. (Implemented: auth.$.jsx + authenticate.admin().)
• Session is stored (e.g. in DB) and loaded on subsequent requests. (PrismaSessionStorage in shopify.server.js.)
• On install/callback, shops table is updated (insert or update by shop). (afterAuth hook in shopify.server.js.)
• Embedded app loads in Shopify Admin without auth errors. (app.jsx uses AppProvider embedded and authenticate.admin() in loader.)

Implementation notes (AppiFire): OAuth is handled by auth.$.jsx and authenticate.admin(). Session storage uses PrismaSessionStorage in app/shopify.server.js. The shops table is upserted in the afterAuth hook in app/shopify.server.js. The uninstall webhook marks the shop as uninstalled in app/routes/webhooks.app.uninstalled.jsx.

---

5. Register webhooks: products/create, products/update, products/delete, app/uninstalled

5.0 Where to find webhooks in the Shopify Partner panel

1. Open the Shopify Partner Dashboard: https://partners.shopify.com
2. In the left sidebar, click Apps.
3. Click your app (e.g. AppiFire AI Chat).
4. In the app’s left sidebar, go to Configuration (or App setup).
5. Scroll to Event subscriptions (or Webhooks).
   • Direct link pattern:
     https://partners.shopify.com/<your-partner-org-id>/apps/<your-app-id>/edit
     then open the Event subscriptions / Webhooks section on that page.
   • If you don’t see “Event subscriptions”, look for Webhooks under the same Configuration / App setup area.

There you add each Subscription (topic) and the Callback URL (your backend endpoint). Shopify sends POST requests to that URL when the event occurs.

Callback URL base for this app: https://ai-chat.appifire.com (or your tunnel URL when using shopify app dev).

Webhook topic	Callback URL (full)
App uninstalled	https://ai-chat.appifire.com/webhooks/app/uninstalled
App scopes updated	https://ai-chat.appifire.com/webhooks/app/scopes_update
Products create	https://ai-chat.appifire.com/webhooks/products/create
Products update	https://ai-chat.appifire.com/webhooks/products/update
Products delete	https://ai-chat.appifire.com/webhooks/products/delete

Replace ai-chat.appifire.com with your actual app host (e.g. your ngrok/tunnel URL in development).

5.1 Define webhook subscriptions

Register these webhooks so the app can react to catalog and lifecycle events (Phase 2 will implement handlers):

Topic	Purpose
products/create	New product added → sync and ingest into RAG.
products/update	Product changed → re-sync and re-embed affected chunks.
products/delete	Product removed → remove from DB and vector store.
app/uninstalled	Store uninstalled → mark shop inactive; implemented in webhooks.app.uninstalled.jsx.

Do you need to add these in the Shopify panel?
No, if you use the CLI to deploy. They are defined in shopify.app.toml under [webhooks.subscriptions]. When you run shopify app deploy (or create a version with the CLI), Shopify syncs these subscriptions from the TOML — you do not add them manually in the Partner or dev.shopify.com panel.
Yes, only if you create or edit an app version manually in the panel (dev.shopify.com or Partner Dashboard). Then use section 5.0 to open Event subscriptions and add each topic with the callback URL (e.g. https://ai-chat.appifire.com/webhooks/products/create).

5.2 Implement registration

• Recommended: Keep webhooks in shopify.app.toml (as in this repo). Deploy with shopify app deploy so the CLI registers them. No panel steps needed.
• Alternative: Register via the Partner Dashboard (see 5.0) or the Admin API; ensure the callback URL is your backend (e.g. https://ai-chat.appifire.com/webhooks/products/create).

5.3 Implement webhook HTTP handlers

Create HTTP endpoints that:

• Receive POST from Shopify with the webhook topic (in header or URL).
• Verify HMAC (or the verification method required by Shopify).
• Acknowledge quickly (e.g. return 200), then enqueue or process the payload asynchronously so Shopify does not retry.

In Phase 1 you can log the payload or no-op; in Phase 2 you will implement product sync and RAG updates inside these handlers.

Checklist:

• Webhooks registered for products/create, products/update, products/delete, app/uninstalled. (Defined in shopify.app.toml; also app/scopes_update.)
• Webhook endpoints implemented: verify HMAC, return 200, (optional) enqueue or no-op for now. (Routes: webhooks.app.uninstalled.jsx, webhooks.app.scopes_update.jsx, webhooks.products.create.jsx, webhooks.products.update.jsx, webhooks.products.delete.jsx; all use authenticate.webhook() and return 200.)
• When you run shopify app dev, webhooks point at your tunnel URL and are delivered successfully. (Manual: run dev and trigger events; check logs.)

---

6. Create a version (dev.shopify.com / Partner Dashboard)

When you create or edit an app version (e.g. Create version), set these values so they match your app and shopify.app.toml. Do not leave the example.com placeholders.

Field	Set to	Notes
Redirect URLs	https://ai-chat.appifire.com/auth/callback	OAuth callback; must match your app URL. For local dev use your tunnel URL (e.g. https://xxx.ngrok.io/auth/callback).
App URL	https://ai-chat.appifire.com	Where the embedded app is loaded (this app’s subdomain). For local dev use your tunnel URL.
Scopes	write_metaobject_definitions, write_metaobjects, write_products, read_products, read_inventory, read_content, read_themes	Must match shopify.app.toml and .env SCOPES. Request any that require permission.
Use legacy install flow	Off (unchecked)	Use the current OAuth flow.
Embed app in Shopify admin	On (checked)	This app is embedded.
Webhooks API Version	2026-04 (or latest)	Must match shopify.app.toml [webhooks] api_version.

If you leave Redirect URLs or App URL as example.com, install and embedded app load will fail.

Where to change the base app URL in the Shopify panel

• App URL and Redirect URLs are set per app version, not in a global app setting.
• Go to Shopify Partners → Apps → your app (AppiFire AI Chat) → App setup (or Configuration).
• Open Versions (or Create version / Edit version for the version you use).
• On the version form:
  • App URL — set to https://ai-chat.appifire.com (no trailing slash).
  • Redirect URLs — add https://ai-chat.appifire.com/auth/callback (comma-separated if you add more).
• If you manage Event subscriptions (webhooks) manually, each callback URL must use the same host, e.g. https://ai-chat.appifire.com/webhooks/products/create.
• Save the version and ensure it is the active version for the app.

Checklist:

• Redirect URLs = https://ai-chat.appifire.com/auth/callback (or your tunnel URL for dev). (Set when creating/editing a version in dev.shopify.com or Partner Dashboard.)
• App URL = https://ai-chat.appifire.com (or your tunnel URL for dev). (Set when creating/editing a version.)
• All required scopes added; optional scopes added if the app uses them. (Match shopify.app.toml scopes.)
• Embed app in Shopify admin = On. (Checkbox on version form.)
• Version created and set as the active version for the app. (Manual in panel.)

---

Phase 1 completion checklist

• App: Created with shopify app init (Remix or React Router, public app).
• Config: shopify.app.toml has required scopes and URLs documented/set.
• Database: All RAG tables + session table in schema and migrations; pgvector column on embeddings; ivfflat index to be created after ingestion (see 3.5). Run npx prisma migrate deploy to apply.
• OAuth: Install/callback work; session stored; shops row created/updated on install (code implemented).
• Webhooks: Registered in toml and handlers in place (verify + 200; logic in Phase 2).

Remaining manual steps: (1) Run npx prisma migrate deploy and confirm DB. (2) Create/set app version in dev.shopify.com or Partner Dashboard with correct Redirect URLs and App URL. (3) Optionally run shopify app dev and test install + webhook delivery.

Next: Phase 2 — Product sync, chunking, OpenRouter embeddings, and pgvector ingestion (see Option-A-Tech-Stack-and-Tasks.md § Phase 2).