Stripe Merchant & AI Chat Memory Database Integration (GCP)
Budget: $30 – $250 USD
Project Overview
I have an AI Bible Study application currently functioning with a library of 66 books in a custom DSL framework. I need a backend expert to build the supporting database infrastructure on Google Cloud for:
1) Stripe merchant services (subscriptions/access control)
2) Persistent chat memory (resume chats across devices)
Important Boundaries
- Do NOT refactor or rewrite my application logic unless it is strictly required to connect DB + Stripe + memory.
- Do NOT change or reformat my existing DSL book content (66 books). That is complete and out of scope.
- Do NOT introduce unstable dependency pinning that could break builds unexpectedly (keep dependencies minimal and stable).
- Provide all deployment/verification steps as copy/paste bash commands.
Environment & Current Stack
- Platform: Google Cloud Platform (GCP)
- Current compute profile: e2-standard-4 (4 vCPU, 16 GB RAM) for the application
- Storage: current VM/GKE setup uses SSD Persistent Disk; DB storage must be SSD
- The app is already running; the new work must integrate without breaking production endpoints.
Expected Scale (Planning Target)
- Up to 20,000 users
- Up to 18 interactions/searches per user per day
- 6 months of retention target (see Memory Retention Requirements)
Architecture Preference
- Preferred DB: PostgreSQL (Cloud SQL Postgres preferred for reliability and managed backups)
- If you recommend an alternative (Firestore/MySQL/etc.), you must justify it against the acceptance tests and retention/performance needs. Postgres is still acceptable and preferred.
Scope of Work
1) Database Setup (Users, Subscriptions, Chat Memory)
Implement a relational database schema (PostgreSQL preferred) including:
A. Users/Auth
- users table: id, username/email (unique), password_hash (if app-side auth required), created_at, last_login_at, status, and basic metadata fields.
- Support future growth (more users, more metadata) without schema rewrites.
B. Stripe Subscription Mapping
- subscriptions table (or stripe_* fields) including:
- stripe_customer_id
- subscription_id
- subscription_status (active, past_due, canceled, etc.)
- current_period_end
- plan/product identifiers as needed
- Must support accurate gating of “paid” features based on DB state.
C. Chat Memory / History
- conversations table:
- conversation_id, user_id, created_at, last_active_at, title/label (optional)
- messages table:
- message_id, conversation_id, role (user/assistant/system), content, created_at
- Required indexes:
- users.username or users.email
- subscriptions.user_id
- conversations.user_id, last_active_at
- messages.conversation_id, created_at
- Messages must be returned in stable chronological order.
Memory Retention Requirements
- Retain chat history for at least 6 months OR implement a safe policy:
- Option 1: full transcript retained 6 months
- Option 2: full transcript retained 30–90 days + summarized long-term memory retained 6 months
- If you choose Option 2, define:
- summary table structure
- summarization trigger rules
- retrieval rules
- retention schedule
2) Stripe API & Webhook Implementation
- Connect to my existing Stripe merchant account (I will provide keys via Secret Manager / K8s secrets).
- Implement webhook listener with:
- strict Stripe signature verification
- idempotency handling (webhooks may be delivered multiple times)
- robust error handling + logging
- Events to support (at minimum):
- checkout.session.completed (or your recommended paid-flow event set)
- customer.subscription.created/updated/deleted
- invoice.payment_failed (to set past_due)
- Webhook updates must write to the database and drive access control.
3) Framework Integration & Testing
- Integrate DB read/write for:
- user subscription gating
- conversation/message persistence
- Do not introduce significant latency; queries must remain efficient and indexed.
- Provide a lightweight local/remote verification procedure (bash scripts) I can run.
4) Operations, Security, and Handoff
- Secrets must not be committed to git. Use GCP Secret Manager or Kubernetes secrets.
- Provide:
- migration tooling and repeatable migration steps (bash)
- backup/restore plan
- minimal monitoring/logging guidance (what to watch, where)
Sizing Guidance (for planning)
Given the target scale, storage can grow beyond 100 GB if retaining full transcripts for 6 months.
- Recommend starting DB storage in the 300–500 GB SSD range (Cloud SQL can autoscale disk).
- Recommend DB memory tier appropriate to workload; typically 16–32 GB RAM for the DB tier is safer at scale.
Final sizing can be adjusted based on your schema and retention plan, but it must meet the acceptance tests below.
Deliverables
- Documented database schema (tables, indexes, retention approach)
- Migration scripts and repeatable deployment steps (bash)
- Functional Stripe integration:
- customer creation (as required)
- subscription state sync
- webhook listener with signature verification + idempotency
- Working chat memory persistence:
- create conversation
- append messages
- list conversations
- load conversation messages
- Verified connection string/config instructions for the app
- Two scripts:
- deploy.sh (or equivalent) – deploy/migrate config
- verify.sh – runs acceptance checks below
Acceptance Tests (Must Pass)
A) Stripe + Access Control
1. Webhook signature verification
- Use Stripe test webhook delivery.
PASS: endpoint returns 2xx and DB updates.
FAIL: accepts unsigned payloads.
2. Subscription gating
- user subscription_status=inactive → paid features denied
- subscription_status=active → paid features allowed immediately
3. State transitions
- active → past_due → canceled updates reflected correctly in DB and access gating
4. Idempotency
- re-send same webhook event
PASS: no duplicated side effects (no duplicate subscriptions/messages)
B) Chat Memory
5. Conversation resume
- Start chat on device A, continue on device B
PASS: same conversation loads; messages persist correctly
6. Message integrity
PASS: stable ordering by created_at; no missing/duplicate messages
7. Retention policy
PASS: retention implemented and documented (6 months target)
C) Performance
8. Index verification
PASS: required indexes exist (see schema section)
9. Latency sanity
PASS: normal chat feels instant; no multi-second DB stalls under basic load test
D) Security
10. Secrets handling
PASS: Stripe keys and DB credentials only via Secret Manager/K8s secrets; nothing committed
Milestones & Payment (4 milestones)
Milestone 1 — Database + Schema (25%)
Deliver:
- Postgres running (Cloud SQL preferred)
- Schema + indexes + migrations
- Documented schema
Acceptance:
- Can create/query user/conversation/message
Milestone 2 — Chat Memory Integration (25%)
Deliver:
- Persistence layer + endpoints/functions for conversations/messages
- Resume across devices
Acceptance:
- Resume test passes
Milestone 3 — Stripe + Webhooks + Gating (35%)
Deliver:
- Stripe integration
- Webhook handler (signature verified + idempotent)
- Subscription gating wired to DB state
Acceptance:
- Stripe test checkout/webhook updates DB and gates access correctly
Milestone 4 — Hardening + Docs + Handoff (15%)
Deliver:
- Backup/restore plan
- Monitoring/logging notes
- Final deploy + verify bash scripts
Acceptance:
- verify.sh passes; restore procedure documented
Freelancer Screening Checklist (Required Skills)
- GCP + Cloud SQL Postgres (or Postgres on GKE with strong ops)
- Stripe webhooks + signature verification
- DB schema + indexing for chat apps
- Migrations + safe deployment workflow
- Strong security practices (secrets management, least privilege)
Disqualifiers
- Wants to refactor the AI/framework code unnecessarily
- Proposes skipping webhook signature verification
- Proposes storing chat history in flat files/local SQLite for production scale
Operational Requirement (Bash-first)
All steps must be provided as copy/paste bash commands.
Provide deploy.sh and verify.sh that I can run in Cloud Shell.
I have an AI Bible Study application currently functioning with a library of 66 books in a custom DSL framework. I need a backend expert to build the supporting database infrastructure on Google Cloud for:
1) Stripe merchant services (subscriptions/access control)
2) Persistent chat memory (resume chats across devices)
Important Boundaries
- Do NOT refactor or rewrite my application logic unless it is strictly required to connect DB + Stripe + memory.
- Do NOT change or reformat my existing DSL book content (66 books). That is complete and out of scope.
- Do NOT introduce unstable dependency pinning that could break builds unexpectedly (keep dependencies minimal and stable).
- Provide all deployment/verification steps as copy/paste bash commands.
Environment & Current Stack
- Platform: Google Cloud Platform (GCP)
- Current compute profile: e2-standard-4 (4 vCPU, 16 GB RAM) for the application
- Storage: current VM/GKE setup uses SSD Persistent Disk; DB storage must be SSD
- The app is already running; the new work must integrate without breaking production endpoints.
Expected Scale (Planning Target)
- Up to 20,000 users
- Up to 18 interactions/searches per user per day
- 6 months of retention target (see Memory Retention Requirements)
Architecture Preference
- Preferred DB: PostgreSQL (Cloud SQL Postgres preferred for reliability and managed backups)
- If you recommend an alternative (Firestore/MySQL/etc.), you must justify it against the acceptance tests and retention/performance needs. Postgres is still acceptable and preferred.
Scope of Work
1) Database Setup (Users, Subscriptions, Chat Memory)
Implement a relational database schema (PostgreSQL preferred) including:
A. Users/Auth
- users table: id, username/email (unique), password_hash (if app-side auth required), created_at, last_login_at, status, and basic metadata fields.
- Support future growth (more users, more metadata) without schema rewrites.
B. Stripe Subscription Mapping
- subscriptions table (or stripe_* fields) including:
- stripe_customer_id
- subscription_id
- subscription_status (active, past_due, canceled, etc.)
- current_period_end
- plan/product identifiers as needed
- Must support accurate gating of “paid” features based on DB state.
C. Chat Memory / History
- conversations table:
- conversation_id, user_id, created_at, last_active_at, title/label (optional)
- messages table:
- message_id, conversation_id, role (user/assistant/system), content, created_at
- Required indexes:
- users.username or users.email
- subscriptions.user_id
- conversations.user_id, last_active_at
- messages.conversation_id, created_at
- Messages must be returned in stable chronological order.
Memory Retention Requirements
- Retain chat history for at least 6 months OR implement a safe policy:
- Option 1: full transcript retained 6 months
- Option 2: full transcript retained 30–90 days + summarized long-term memory retained 6 months
- If you choose Option 2, define:
- summary table structure
- summarization trigger rules
- retrieval rules
- retention schedule
2) Stripe API & Webhook Implementation
- Connect to my existing Stripe merchant account (I will provide keys via Secret Manager / K8s secrets).
- Implement webhook listener with:
- strict Stripe signature verification
- idempotency handling (webhooks may be delivered multiple times)
- robust error handling + logging
- Events to support (at minimum):
- checkout.session.completed (or your recommended paid-flow event set)
- customer.subscription.created/updated/deleted
- invoice.payment_failed (to set past_due)
- Webhook updates must write to the database and drive access control.
3) Framework Integration & Testing
- Integrate DB read/write for:
- user subscription gating
- conversation/message persistence
- Do not introduce significant latency; queries must remain efficient and indexed.
- Provide a lightweight local/remote verification procedure (bash scripts) I can run.
4) Operations, Security, and Handoff
- Secrets must not be committed to git. Use GCP Secret Manager or Kubernetes secrets.
- Provide:
- migration tooling and repeatable migration steps (bash)
- backup/restore plan
- minimal monitoring/logging guidance (what to watch, where)
Sizing Guidance (for planning)
Given the target scale, storage can grow beyond 100 GB if retaining full transcripts for 6 months.
- Recommend starting DB storage in the 300–500 GB SSD range (Cloud SQL can autoscale disk).
- Recommend DB memory tier appropriate to workload; typically 16–32 GB RAM for the DB tier is safer at scale.
Final sizing can be adjusted based on your schema and retention plan, but it must meet the acceptance tests below.
Deliverables
- Documented database schema (tables, indexes, retention approach)
- Migration scripts and repeatable deployment steps (bash)
- Functional Stripe integration:
- customer creation (as required)
- subscription state sync
- webhook listener with signature verification + idempotency
- Working chat memory persistence:
- create conversation
- append messages
- list conversations
- load conversation messages
- Verified connection string/config instructions for the app
- Two scripts:
- deploy.sh (or equivalent) – deploy/migrate config
- verify.sh – runs acceptance checks below
Acceptance Tests (Must Pass)
A) Stripe + Access Control
1. Webhook signature verification
- Use Stripe test webhook delivery.
PASS: endpoint returns 2xx and DB updates.
FAIL: accepts unsigned payloads.
2. Subscription gating
- user subscription_status=inactive → paid features denied
- subscription_status=active → paid features allowed immediately
3. State transitions
- active → past_due → canceled updates reflected correctly in DB and access gating
4. Idempotency
- re-send same webhook event
PASS: no duplicated side effects (no duplicate subscriptions/messages)
B) Chat Memory
5. Conversation resume
- Start chat on device A, continue on device B
PASS: same conversation loads; messages persist correctly
6. Message integrity
PASS: stable ordering by created_at; no missing/duplicate messages
7. Retention policy
PASS: retention implemented and documented (6 months target)
C) Performance
8. Index verification
PASS: required indexes exist (see schema section)
9. Latency sanity
PASS: normal chat feels instant; no multi-second DB stalls under basic load test
D) Security
10. Secrets handling
PASS: Stripe keys and DB credentials only via Secret Manager/K8s secrets; nothing committed
Milestones & Payment (4 milestones)
Milestone 1 — Database + Schema (25%)
Deliver:
- Postgres running (Cloud SQL preferred)
- Schema + indexes + migrations
- Documented schema
Acceptance:
- Can create/query user/conversation/message
Milestone 2 — Chat Memory Integration (25%)
Deliver:
- Persistence layer + endpoints/functions for conversations/messages
- Resume across devices
Acceptance:
- Resume test passes
Milestone 3 — Stripe + Webhooks + Gating (35%)
Deliver:
- Stripe integration
- Webhook handler (signature verified + idempotent)
- Subscription gating wired to DB state
Acceptance:
- Stripe test checkout/webhook updates DB and gates access correctly
Milestone 4 — Hardening + Docs + Handoff (15%)
Deliver:
- Backup/restore plan
- Monitoring/logging notes
- Final deploy + verify bash scripts
Acceptance:
- verify.sh passes; restore procedure documented
Freelancer Screening Checklist (Required Skills)
- GCP + Cloud SQL Postgres (or Postgres on GKE with strong ops)
- Stripe webhooks + signature verification
- DB schema + indexing for chat apps
- Migrations + safe deployment workflow
- Strong security practices (secrets management, least privilege)
Disqualifiers
- Wants to refactor the AI/framework code unnecessarily
- Proposes skipping webhook signature verification
- Proposes storing chat history in flat files/local SQLite for production scale
Operational Requirement (Bash-first)
All steps must be provided as copy/paste bash commands.
Provide deploy.sh and verify.sh that I can run in Cloud Shell.