如何将Google Assistant对话数据持久化至数据库？股票应用开发求助

Got it, let's tackle your two key needs: storing user data from Google Actions to avoid repetitive inputs, and building a stock portfolio system for daily queries. Here's a practical, step-by-step solution tailored to your use case:

First, the core goal here is to link user-specific data to their unique identity in Google Actions, so you can recall it anytime they interact with your Action.

• Use Google's Unique User ID
  Google Actions provides a user.id field in every request payload—this is a stable, unique identifier for each user interacting with your Action. Treat this as the primary key in your database to tie all their data to their account.

• Pick a Database That Fits Your Scale
  For most small-to-medium Action projects, Firebase Firestore is a no-brainer—it’s part of the Google ecosystem, integrates seamlessly with Actions, and requires minimal setup. If you prefer SQL, PostgreSQL or MySQL work great too.

• Save Data on First Collection
  When the user shares their stock holdings (company names, share counts), write this data to your database using their user.id as the key. Here’s a quick Node.js example for Firestore:

  const admin = require('firebase-admin');
  admin.initializeApp();
  
  // Save user's portfolio data
  async function saveUserPortfolio(userId, portfolio) {
    await admin.firestore()
      .collection('users')
      .doc(userId)
      .set({ portfolio: portfolio }, { merge: true });
    // The { merge: true } flag lets you update existing data without overwriting the whole document
  }
  

• Retrieve Data Automatically on Future Interactions
  Every time the user starts a conversation, fetch their stored data first. This way, you can skip asking for info they’ve already provided. Example retrieval code:

  async function getUserPortfolio(userId) {
    const userDoc = await admin.firestore()
      .collection('users')
      .doc(userId)
      .get();
    return userDoc.exists ? userDoc.data().portfolio : null;
  }
  

• Let Users Update Their Data
  Add intents like "UpdatePortfolio" or "RemoveStock" to modify existing entries. For example, if a user says "I bought 5 more Google shares", your Action can update the corresponding entry in their portfolio document.

Now, let’s map out how to structure your data and handle user queries effectively.

• Database Schema Design

  • NoSQL (Firestore): Each user document will have a portfolio array of objects, like this:

    {
      "portfolio": [
        { "companyName": "Alphabet Inc.", "ticker": "GOOGL", "shareCount": 10 },
        { "companyName": "Microsoft Corp.", "ticker": "MSFT", "shareCount": 5 }
      ]
    }
    

  • SQL: Create two tables:
    • users (columns: id [primary key], created_at)
    • user_portfolios (columns: id [primary key], user_id [foreign key to users.id], company_name, ticker, share_count, updated_at)

• Integrate Real-Time Stock Price Data
  To answer daily value queries, use a free stock API like Alpha Vantage or Yahoo Finance to fetch current prices. Cache prices for 15-30 minutes to avoid hitting rate limits and speed up responses.

• Implement Common Query Intents
  Set up Google Actions intents for the queries users will ask most often:

  • "What's my total portfolio value today?"
    Calculate by multiplying each stock’s share count by its current price, then sum all values.
  • "How much did my Apple shares gain today?"
    Fetch today’s price vs yesterday’s closing price, multiply the difference by the user’s share count.
  • "List all my stocks"
    Pull the portfolio array from the database and format it into a natural language response.

• Example Workflow for a Daily Query

  1. User says: "What’s my portfolio worth right now?"
  2. Your Action fetches the user’s portfolio using their user.id.
  3. For each stock in the portfolio, fetch the latest cached price (or hit the API if cache is stale).
  4. Compute the total value and respond: "Your portfolio is worth $1,560 today—Alphabet contributes $1,200 and Microsoft contributes $360."

• Encrypt Sensitive Data: Even stock holdings should be encrypted at rest (most databases offer built-in encryption) and in transit (use HTTPS for all API calls).
• Handle Edge Cases: If a user hasn’t set up a portfolio yet, respond with "It looks like you haven’t shared your investment details yet—would you like to add them now?" If the stock API is down, say "Sorry, I can’t pull stock prices right now. Try again in a few minutes."
• Rate Limit API Calls: Caching is key here—don’t flood stock APIs with requests, or you’ll get blocked from free tiers.

内容的提问来源于stack exchange，提问作者shiv kanth