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.