PersonaOps for Google Ecosystem

┌─────────────────────────────────────────────────────────────────────────────┐
│                    PERSONAOPS FOR GOOGLE ECOSYSTEM                          │
│                         Voice-to-Data Pipeline                              │
└─────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────┐
│      [USER VOICE INPUT]          │ ◄── Microphone / WebRTC / Meet
└────────────────┬─────────────────┘
                 │ Raw Audio Stream (16kHz PCM)
                 ▼
┌──────────────────────────────────┐
│  [GOOGLE CLOUD SPEECH-TO-TEXT]   │ ◄── StreamingRecognize API
│  - Chirp model (latest)          │     Speaker diarization
│  - Real-time transcription       │     Partial + final transcripts
└────────────────┬─────────────────┘
                 │ Raw Transcript
                 ▼
┌──────────────────────────────────┐
│ [GEMINI API - FUNCTION CALLING]  │ ◄── gemini-3.1-pro-preview
│  - Intent classification         │     Structured JSON output
│  - Entity extraction             │     Custom function declarations
└────────────────┬─────────────────┘
                 │ Typed Intent + Entities
                 ▼
┌──────────────────────────────────┐
│ [SCHEMA MANAGEMENT - SHEETS API] │ ◄── spreadsheets.values
│  - Schema registry lookup        │     Apps Script versioning
│  - Dynamic column addition       │     Non-breaking migrations
└────────────────┬─────────────────┘
                 │ Target Sheet + Column Mapping
                 ▼
┌──────────────────────────────────┐
│  [DATA PERSISTENCE - WORKSPACE]  │ ◄── Sheets API (batchUpdate)
│  - Structured: Google Sheets     │     Docs API (batchUpdate)
│  - Unstructured: Google Docs     │     Drive API (file creation)
│  - Attachments: Google Drive     │
└────────────────┬─────────────────┘
                 │
                 ▼
┌──────────────────────────────────┐
│ [WORKFLOW AUTOMATION LAYER]      │ ◄── Apps Script triggers
│  - Time-based processing         │     Cloud Functions (eventarc)
│  - Webhook receivers             │     Cloud Workflows (orchestration)
└────────────────┬─────────────────┘
                 │
    ┌────────────┼────────────────────────┐
    ▼            ▼                        ▼
┌─────────┐ ┌──────────┐    ┌─────────────────────────┐
│ BigQuery│ │ Looker   │    │ [PERSONAL INTELLIGENCE] │
│(Analyt- │ │ Studio   │    │  Cross-app reasoning     │
│ ics)    │ │(Dash-    │    │  Gmail + Photos + Search │
└─────────┘ │ boards)  │    └─────────────────────────┘
            └──────────┘

from google.cloud import speech_v1p1beta1 as speech
from google.cloud.speech_v1p1beta1 import types

# Configure streaming recognition with diarization
client = speech.SpeechClient()
config = types.RecognitionConfig(
    encoding=types.RecognitionConfig.AudioEncoding.LINEAR16,
    sample_rate_hertz=16000,
    language_code="en-US",
    model="latest_long",  # Chirp model
    enable_speaker_diarization=True,
    diarization_speaker_count=2,
    enable_automatic_punctuation=True,
)

streaming_config = types.StreamingRecognitionConfig(
    config=config,
    interim_results=True,  # Enable partial transcripts
)

# Process streaming audio
def process_audio_stream(audio_generator):
    requests = (types.StreamingRecognizeRequest(audio_content=chunk) 
                for chunk in audio_generator)
    responses = client.streaming_recognize(streaming_config, requests)

    for response in responses:
        for result in response.results:
            if result.is_final:
                # Final transcript with speaker tags
                yield extract_final_transcript(result)
            else:
                # Partial transcript for speculative processing
                yield extract_partial_transcript(result)

from google import genai
from google.genai import types

# Define the function declaration for CREATE intent
create_record_function = {
    "name": "create_personaops_record",
    "description": "Creates a new structured record from voice input in the appropriate Google Sheet",
    "parameters": {
        "type": "object",
        "properties": {
            "table_name": {
                "type": "string",
                "description": "Target sheet/table name (e.g., 'Sales_Log', 'Field_Report')"
            },
            "fields": {
                "type": "object",
                "description": "Key-value pairs of field names and typed values",
                "additionalProperties": True
            },
            "confidence": {
                "type": "number",
                "description": "Confidence score 0-1 for this extraction"
            }
        },
        "required": ["table_name", "fields"]
    }
}

# Define schema modification function
modify_schema_function = {
    "name": "modify_personaops_schema",
    "description": "Adds, renames, or removes columns from a PersonaOps-managed sheet",
    "parameters": {
        "type": "object",
        "properties": {
            "table_name": {"type": "string"},
            "action": {"type": "string", "enum": ["ADD_COLUMN", "RENAME_COLUMN", "REMOVE_COLUMN"]},
            "column_name": {"type": "string"},
            "column_type": {"type": "string", "enum": ["TEXT", "NUMBER", "DATE", "CURRENCY", "SELECT"]},
            "new_name": {"type": "string"}  # For rename operations
        },
        "required": ["table_name", "action", "column_name"]
    }
}

# Define query function
query_function = {
    "name": "query_personaops_data",
    "description": "Queries PersonaOps-managed sheets with filters and returns results",
    "parameters": {
        "type": "object",
        "properties": {
            "table_name": {"type": "string"},
            "filters": {
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "field": {"type": "string"},
                        "operator": {"type": "string", "enum": ["equals", "contains", "greater_than", "less_than", "on_or_after"]},
                        "value": {"type": "string"}
                    }
                }
            },
            "limit": {"type": "integer", "default": 10}
        },
        "required": ["table_name"]
    }
}

# Configure client with tools
client = genai.Client()
tools = types.Tool(function_declarations=[
    create_record_function,
    modify_schema_function,
    query_function
])
config = types.GenerateContentConfig(
    tools=[tools],
    thinking_level="high"  # Gemini 3.1 Pro feature for complex reasoning
)

# Process transcript
response = client.models.generate_content(
    model="gemini-3.1-pro-preview",
    contents=f"Process this voice transcript: '{transcript}'",
    config=config
)

/**
 * Adds a new column to a PersonaOps-managed sheet (non-breaking migration)
 * @param {string} tableName - Name of the table/sheet
 * @param {string} columnName - New column name
 * @param {string} columnType - Type: TEXT, NUMBER, DATE, CURRENCY, SELECT
 * @param {Array} options - For SELECT type, array of allowed values
 */
function addColumnToSchema(tableName, columnName, columnType, options = []) {
  const registrySheet = SpreadsheetApp.getActive()
    .getSheetByName('PersonaOps_Schema_Registry');

  // Find table in registry
  const data = registrySheet.getDataRange().getValues();
  let tableRow, sheetId, currentVersion, columnsJson;

  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === tableName) {
      tableRow = i + 1;
      sheetId = data[i][1];
      currentVersion = data[i][2];
      columnsJson = JSON.parse(data[i][5]);
      break;
    }
  }

  if (!sheetId) throw new Error(`Table ${tableName} not found in registry`);

  // Check if column already exists (idempotent)
  if (columnsJson[columnName]) {
    console.log(`Column ${columnName} already exists`);
    return;
  }

  // Update columns JSON
  columnsJson[columnName] = columnType;

  // Open target sheet and add column
  const targetSheet = SpreadsheetApp.openById(sheetId).getSheets()[0];
  const lastCol = targetSheet.getLastColumn();
  targetSheet.getRange(1, lastCol + 1).setValue(columnName);

  // Apply data validation for SELECT type
  if (columnType === 'SELECT' && options.length > 0) {
    const rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(options, true)
      .build();
    targetSheet.getRange(2, lastCol + 1, targetSheet.getMaxRows() - 1, 1)
      .setDataValidation(rule);
  }

  // Update registry
  registrySheet.getRange(tableRow, 3).setValue(currentVersion + 1);
  registrySheet.getRange(tableRow, 5).setValue(new Date());
  registrySheet.getRange(tableRow, 6).setValue(JSON.stringify(columnsJson));

  // Log schema evolution event
  logSchemaEvolution(tableName, 'ADD_COLUMN', columnName, columnType, currentVersion + 1);
}

from googleapiclient.discovery import build
from google.oauth2.credentials import Credentials

def append_structured_record(sheet_id: str, fields: dict, idempotency_key: str = None):
    """Append a structured record to a PersonaOps sheet with idempotency check"""
    sheets = build('sheets', 'v4', credentials=creds)

    # Idempotency check (prevent duplicate voice entries)
    if idempotency_key:
        existing = sheets.spreadsheets().values().get(
            spreadsheetId=sheet_id,
            range="A:A"  # Assuming first column is ID
        ).execute().get('values', [])
        existing_ids = {row[0] for row in existing if row}
        if idempotency_key in existing_ids:
            return {"status": "duplicate", "id": idempotency_key}

    # Map fields to column order based on schema registry
    ordered_values = map_fields_to_columns(sheet_id, fields)

    # Append row
    result = sheets.spreadsheets().values().append(
        spreadsheetId=sheet_id,
        range="A1",
        valueInputOption="USER_ENTERED",
        insertDataOption="INSERT_ROWS",
        body={"values": [ordered_values]}
    ).execute()

    return {"status": "success", "updated_range": result['updates']['updatedRange']}

// Apps Script - Append AI-summarized voice content to a Google Doc
function appendVoiceNoteToDoc(docId, transcript, aiSummary, speaker, timestamp) {
  const doc = DocumentApp.openById(docId);
  const body = doc.getBody();

  // Format entry with speaker attribution
  const entry = body.appendParagraph(`${speaker} - ${timestamp.toLocaleString()}`);
  entry.setHeading(DocumentApp.ParagraphHeading.HEADING3);

  body.appendParagraph(`"${transcript}"`).setItalic(true);
  body.appendParagraph(`AI Summary: ${aiSummary}`);
  body.appendParagraph('---');

  // Add comment for human review if low confidence
  // (using Docs API for comments)
}

// Apps Script - Detect manual edits to AI-populated fields
function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const newValue = e.value;
  const row = range.getRow();
  const col = range.getColumn();

  // Check if this sheet is PersonaOps-managed
  const registryData = getRegistryEntry(sheet.getName());
  if (!registryData) return;

  // Get the original AI-populated value from audit log
  const originalValue = getOriginalValue(sheet.getName(), row, col);

  if (originalValue && originalValue !== newValue) {
    // Log human override
    logHumanOverride({
      table: sheet.getName(),
      row: row,
      column: registryData.columns[col - 1],
      original_value: originalValue,
      human_value: newValue,
      timestamp: new Date()
    });

    // Propagate to external systems via webhook
    propagateCorrection(sheet.getName(), row, col, newValue);
  }
}

def enrich_voice_context_with_personal_intelligence(transcript: str, user_id: str):
    """
    Leverage Personal Intelligence to enrich voice-derived data with
    cross-application context before schema mapping.
    """
    # Personal Intelligence is a model capability, not a separate API
    # It activates when the user has enabled app connections in Gemini

    client = genai.Client()

    # The model automatically accesses connected apps when relevant
    response = client.models.generate_content(
        model="gemini-3.1-pro-preview",
        contents=f"""
        User {user_id} said: "{transcript}"

        Use available connected apps (Gmail, Photos, Search history) to:
        1. Verify or enrich any mentioned entities
        2. Provide missing context (e.g., full names from email contacts)
        3. Identify relevant past interactions or documents

        Return enriched context as JSON.
        """,
        config=types.GenerateContentConfig(
            thinking_level="high",
            response_mime_type="application/json"
        )
    )

    return json.loads(response.text)

# From Google's official tutorial 
from google.adk.agents import Agent
from google.adk.tools import GoogleSearch, MCPToolset
from google.adk.tools.mcp_tool.mcp_toolset import StdioServerParameters
from google.adk.agents.run_config import RunConfig, StreamingMode
from google.genai import types

# 1. Define PersonaOps-specific system instruction
SYSTEM_INSTRUCTION = """
You are PersonaOps Voice Agent, converting spoken observations into structured data.

When users speak:
1. Identify the intent: CREATE record, UPDATE record, QUERY data, or MODIFY schema
2. Extract all entities with appropriate types
3. Call the appropriate PersonaOps function with structured parameters
4. Confirm the action with the user

Available tables: Sales_Log, Field_Report, Client_Notes, Inventory
"""

# 2. Configure agent with tools
agent = Agent(
    name="personaops_voice_agent",
    model="gemini-3.1-pro-preview",
    instruction=SYSTEM_INSTRUCTION,
    tools=[
        GoogleSearch,  # For real-time entity validation
        MCPToolset(
            connection_params=StdioServerParameters(
                command='npx',
                args=["-y", "@modelcontextprotocol/server-google-maps"],
                env={"Maps_API_KEY": MAPS_API_KEY}
            ),
        )
    ],
)

# 3. Configure bidirectional streaming for natural conversation
run_config = RunConfig(
    streaming_mode=StreamingMode.BIDI,  # Allows user interruption
    speech_config=types.SpeechConfig(
        voice_config=types.VoiceConfig(
            prebuilt_voice_config=types.PrebuiltVoiceConfig(
                voice_name="en-US-Neural2-F"  # Natural voice
            )
        )
    ),
    response_modalities=["AUDIO"],
    output_audio_transcription=types.AudioTranscriptionConfig(),
    input_audio_transcription=types.AudioTranscriptionConfig(),
)

# 4. Asynchronous task management for real-time performance
async with asyncio.TaskGroup() as tg:
    tg.create_task(receive_client_messages(), name="ClientMessageReceiver")
    tg.create_task(send_audio_to_service(), name="AudioSender")
    tg.create_task(receive_service_responses(), name="ServiceResponseReceiver")

# Add to agent tools
personaops_functions = [
    create_record_function,  # Defined in Section 3.2
    modify_schema_function,
    query_function
]

agent = Agent(
    # ... existing config ...
    tools=[
        GoogleSearch,
        MCPToolset(...),
        *personaops_functions  # Custom PersonaOps functions
    ]
)

from googleapiclient.discovery import build
from google.oauth2.credentials import Credentials
import google.generativeai as genai

# 1. Configure Gemini for email classification with function calling
def classify_email_and_extract(subject: str, body: str, sender: str):
    """Classify email intent and extract structured data using Gemini"""

    client = genai.Client()

    response = client.models.generate_content(
        model="gemini-3.1-pro-preview",
        contents=f"""
        Analyze this email:
        From: {sender}
        Subject: {subject}
        Body: {body[:1000]}

        Determine:
        1. Intent: CREATE_RECORD, UPDATE_RECORD, QUERY, or IGNORE
        2. Target table from: Sales_Log, Client_Notes, Field_Report, Support_Ticket
        3. Extract structured fields based on the target table's schema
        """,
        config={
            "tools": [{
                "functionDeclarations": [create_record_function]
            }],
            "response_mime_type": "application/json"
        }
    )

    return json.loads(response.text)

# 2. Gmail processing loop
def process_personaops_emails():
    gmail = build('gmail', 'v1', credentials=creds)

    # Query unprocessed PersonaOps emails
    query = 'label:personaops-pending -label:personaops-processed'
    messages = gmail.users().messages().list(userId='me', q=query).execute()

    for msg in messages.get('messages', []):
        # Get email content
        email_data = gmail.users().messages().get(
            userId='me', id=msg['id'], format='full'
        ).execute()

        # Extract headers and body
        headers = email_data['payload']['headers']
        subject = next(h['value'] for h in headers if h['name'] == 'Subject')
        sender = next(h['value'] for h in headers if h['name'] == 'From')

        # Classify and extract with Gemini
        extracted = classify_email_and_extract(subject, get_body(email_data), sender)

        if extracted.get('intent') == 'CREATE_RECORD':
            # Append to appropriate PersonaOps sheet
            append_structured_record(
                sheet_id=get_sheet_id(extracted['table_name']),
                fields=extracted['fields'],
                idempotency_key=msg['id']  # Use email ID for deduplication
            )

        # Mark as processed
        gmail.users().messages().modify(
            userId='me',
            id=msg['id'],
            body={
                'addLabelIds': ['Label_123456'],  # personaops-processed label
                'removeLabelIds': ['Label_789012']  # personaops-pending label
            }
        ).execute()

/**
 * PersonaOps Schema Evolution Engine for Google Sheets
 * Triggered by Gemini function calls from voice input
 */

// Schema Registry Structure (stored in Properties Service for persistence)
const SCHEMA_REGISTRY_KEY = 'PERSONAOPS_SCHEMA_REGISTRY';

/**
 * Initialize or load schema registry
 */
function getSchemaRegistry() {
  const props = PropertiesService.getScriptProperties();
  const stored = props.getProperty(SCHEMA_REGISTRY_KEY);

  if (stored) {
    return JSON.parse(stored);
  }

  // Initialize empty registry
  return {
    tables: {},
    version: 1,
    migrations: []
  };
}

/**
 * Add column to existing sheet (non-breaking migration)
 * Called when user says "Add [column] field to [table]"
 */
function addColumnToTable(tableName, columnName, columnType, options = []) {
  const registry = getSchemaRegistry();

  // Validate table exists
  if (!registry.tables[tableName]) {
    throw new Error(`Table '${tableName}' not found in schema registry`);
  }

  const tableInfo = registry.tables[tableName];
  const sheet = SpreadsheetApp.openById(tableInfo.sheetId).getSheets()[0];

  // Check if column already exists (idempotent)
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  if (headers.includes(columnName)) {
    console.log(`Column '${columnName}' already exists in '${tableName}'`);
    return { status: 'exists', table: tableName, column: columnName };
  }

  // Add column header
  const newColIndex = headers.length + 1;
  sheet.getRange(1, newColIndex).setValue(columnName);

  // Apply formatting based on type
  const dataRange = sheet.getRange(2, newColIndex, sheet.getMaxRows() - 1, 1);

  switch(columnType) {
    case 'DATE':
      dataRange.setNumberFormat('yyyy-mm-dd');
      break;
    case 'CURRENCY':
      dataRange.setNumberFormat('$#,##0.00');
      break;
    case 'SELECT':
      if (options.length > 0) {
        const rule = SpreadsheetApp.newDataValidation()
          .requireValueInList(options, true)
          .build();
        dataRange.setDataValidation(rule);
      }
      break;
    case 'CHECKBOX':
      dataRange.insertCheckboxes();
      break;
  }

  // Update registry
  tableInfo.columns[columnName] = {
    type: columnType,
    options: options,
    added_at: new Date().toISOString(),
    added_in_version: registry.version
  };
  tableInfo.version += 1;
  registry.version += 1;

  // Record migration
  registry.migrations.push({
    table: tableName,
    action: 'ADD_COLUMN',
    column: columnName,
    type: columnType,
    timestamp: new Date().toISOString(),
    version: tableInfo.version
  });

  // Persist registry
  saveSchemaRegistry(registry);

  return {
    status: 'success',
    table: tableName,
    column: columnName,
    version: tableInfo.version
  };
}

/**
 * Create new table from voice-described schema
 */
function createTableFromVoice(tableName, fields) {
  const registry = getSchemaRegistry();

  // Check if table already exists
  if (registry.tables[tableName]) {
    throw new Error(`Table '${tableName}' already exists`);
  }

  // Create new spreadsheet
  const ss = SpreadsheetApp.create(`PersonaOps - ${tableName}`);
  const sheet = ss.getSheets()[0];

  // Set up headers and formatting
  const headers = Object.keys(fields);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // Apply column formatting
  headers.forEach((colName, index) => {
    const colType = fields[colName];
    const range = sheet.getRange(2, index + 1, sheet.getMaxRows() - 1);

    // Apply type-specific formatting (similar to addColumnToTable)
    applyColumnFormatting(range, colType);
  });

  // Freeze header row
  sheet.setFrozenRows(1);

  // Add alternating row colors for readability
  sheet.getRange('A:Z').applyRowBanding();

  // Register in schema registry
  registry.tables[tableName] = {
    sheetId: ss.getId(),
    sheetUrl: ss.getUrl(),
    columns: fields,
    version: 1,
    created_at: new Date().toISOString(),
    row_count: 0
  };
  registry.version += 1;

  saveSchemaRegistry(registry);

  return {
    status: 'created',
    table: tableName,
    sheetUrl: ss.getUrl(),
    sheetId: ss.getId()
  };
}

/**
 * Persist schema registry to Script Properties
 */
function saveSchemaRegistry(registry) {
  const props = PropertiesService.getScriptProperties();
  props.setProperty(SCHEMA_REGISTRY_KEY, JSON.stringify(registry));
}

from google import genai
from google.genai import types

class PersonaOpsPersonalIntelligence:
    """
    Enriches voice data using Gemini's Personal Intelligence capability,
    which reasons across Gmail, Photos, Search, and YouTube history.
    """

    def __init__(self):
        self.client = genai.Client()

    def enrich_voice_transcript(self, transcript: str, user_context: dict) -> dict:
        """
        Process voice input with Personal Intelligence context.

        Personal Intelligence automatically accesses:
        - Gmail: for contact info, past communications, appointments
        - Photos: for visual context, object recognition, location
        - Search history: for recent topics of interest
        - YouTube: for watched content related to query
        """

        # Construct prompt that activates Personal Intelligence
        prompt = f"""
        [PERSONAL INTELLIGENCE CONTEXT]
        User: {user_context.get('name', 'Unknown')}
        Voice input: "{transcript}"
        Current location: {user_context.get('location', 'Unknown')}
        Current time: {user_context.get('timestamp')}

        Using available connected apps (Gmail, Photos, Search, YouTube):
        1. Identify any missing context needed for data extraction
        2. Retrieve relevant information (contacts, past interactions, visual data)
        3. Enrich the voice-derived entities with this context

        Return enriched data as JSON with fields:
        - intent: CREATE_RECORD / UPDATE_RECORD / QUERY / SCHEMA_MODIFY
        - table: Target table name
        - entities: Key-value pairs with typed, enriched values
        - context_source: Which app provided enrichment (gmail/photos/search/youtube)
        - confidence: 0-1 score
        """

        response = self.client.models.generate_content(
            model="gemini-3.1-pro-preview",
            contents=prompt,
            config=types.GenerateContentConfig(
                thinking_level="high",
                response_mime_type="application/json"
            )
        )

        return json.loads(response.text)

    def proactive_schema_suggestion(self, recent_activity: list) -> list:
        """
        Analyze recent cross-app activity to suggest new schema fields.

        Example: If user has been emailing about "delivery dates" and
        searching for "shipping status", suggest adding tracking_number
        and estimated_delivery columns.
        """

        prompt = f"""
        [PROACTIVE SCHEMA ANALYSIS]
        Recent activity summary:
        {json.dumps(recent_activity, indent=2)}

        Based on patterns in this user's Gmail, Search, and other activity,
        suggest new fields that should be added to PersonaOps tables
        to better capture emerging data needs.

        Return suggestions as JSON array:
        [
          {{
            "table": "table_name",
            "suggested_field": "field_name",
            "field_type": "TEXT/NUMBER/DATE/etc",
            "reasoning": "Explanation based on observed patterns",
            "evidence_source": "gmail/search/photos"
          }}
        ]
        """

        response = self.client.models.generate_content(
            model="gemini-3.1-pro-preview",
            contents=prompt,
            config=types.GenerateContentConfig(
                thinking_level="high",
                response_mime_type="application/json"
            )
        )

        return json.loads(response.text)

Voice Input: "Log a meeting with Sarah about the Q3 proposal"

Without Personal Intelligence:
→ Entities: { contact: "Sarah", topic: "Q3 proposal" }

With Personal Intelligence:
→ Gmail: Finds recent email from "Sarah Chen" with subject "Q3 Proposal Draft"
→ Calendar: Identifies meeting scheduled for tomorrow at 2 PM
→ Photos: (none relevant)
→ Search: Recent searches for "proposal templates"
→ YouTube: (none relevant)

Enriched Output:
{
  "intent": "CREATE_RECORD",
  "table": "Meeting_Notes",
  "entities": {
    "contact_name": "Sarah Chen",
    "contact_email": "sarah.chen@company.com",
    "topic": "Q3 Proposal Review",
    "meeting_date": "2026-03-22T14:00:00Z",
    "related_document": "Q3 Proposal Draft (from Gmail attachment)",
    "preparation_notes": "Review proposal templates from recent searches"
  },
  "context_source": ["gmail", "calendar", "search"],
  "confidence": 0.94
}

// Apps Script - Generate report from PersonaOps data
function generateReportFromVoiceData(tableName, dateRange, templateId) {
  const registry = getSchemaRegistry();
  const tableInfo = registry.tables[tableName];

  if (!tableInfo) throw new Error(`Table ${tableName} not found`);

  // 1. Fetch data from PersonaOps sheet
  const sheet = SpreadsheetApp.openById(tableInfo.sheetId).getSheets()[0];
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const rows = data.slice(1).filter(row => {
    // Filter by date range
    const dateCol = headers.indexOf('Date');
    if (dateCol === -1) return true;
    const rowDate = new Date(row[dateCol]);
    return rowDate >= dateRange.start && rowDate <= dateRange.end;
  });

  // 2. Generate AI summary of data using Gemini
  const summary = generateDataSummary(tableName, headers, rows);

  // 3. Create report from template
  const doc = createReportFromTemplate(templateId, {
    '{{table_name}}': tableName,
    '{{date_range}}': `${dateRange.start.toLocaleDateString()} - ${dateRange.end.toLocaleDateString()}`,
    '{{record_count}}': rows.length,
    '{{ai_summary}}': summary,
    '{{generated_date}}': new Date().toLocaleString()
  });

  // 4. Insert data table into document
  insertDataTable(doc.getId(), headers, rows);

  return {
    docUrl: doc.getUrl(),
    docId: doc.getId(),
    recordCount: rows.length
  };
}

/**
 * Generate AI summary of sheet data using Gemini
 */
function generateDataSummary(tableName, headers, rows) {
  // Prepare data sample for Gemini (limit token usage)
  const sampleSize = Math.min(rows.length, 50);
  const sample = rows.slice(0, sampleSize);

  // Convert to structured format
  const dataJson = sample.map(row => {
    const obj = {};
    headers.forEach((h, i) => obj[h] = row[i]);
    return obj;
  });

  const prompt = `
    Analyze this ${tableName} data from PersonaOps (${rows.length} total records, showing ${sampleSize} sample):
    ${JSON.stringify(dataJson, null, 2)}

    Provide:
    1. Executive summary (2-3 sentences)
    2. Key trends observed
    3. Notable outliers or anomalies
    4. Recommended actions

    Format as markdown.
  `;

  // Call Gemini via Apps Script
  const response = callGeminiAPI(prompt);
  return response;
}

/**
 * Call Gemini API from Apps Script
 */
function callGeminiAPI(prompt) {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('GEMINI_API_KEY');

  const url = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-3.1-pro-preview:generateContent';

  const response = UrlFetchApp.fetch(`${url}?key=${apiKey}`, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({
      contents: [{
        parts: [{ text: prompt }]
      }],
      generationConfig: {
        thinking_level: "high"
      }
    }),
    muteHttpExceptions: true
  });

  const data = JSON.parse(response.getContentText());
  return data.candidates[0].content.parts[0].text;
}

┌─────────────────────────────────────────────────────────────────┐
│                    PRODUCTION PERSONAOPS                        │
│                     Google Ecosystem Stack                      │
└─────────────────────────────────────────────────────────────────┘

[Cloud Load Balancer]
         │
         ▼
[Cloud Run Services] ─────────────────────────────────────────────┐
├── Voice Gateway (WebRTC SFU)                                   │
├── STT Proxy (Speech-to-Text API)                               │
├── Gemini Orchestrator (Function Calling)                       │
└── Schema Service (Sheets API + Redis cache)                    │
         │                                                        │
         ▼                                                        │
[Cloud Workflows] ── Orchestration Layer ─────────────────────────┤
         │                                                        │
         ├──► [Sheets API] ── Structured Data Store              │
         ├──► [Docs API] ──── Unstructured Content               │
         ├──► [Drive API] ─── Attachments                        │
         ├──► [Gmail API] ─── Email Integration                  │
         └──► [BigQuery] ──── Analytics Sink                     │
                                                                  │
[Eventarc] ── Event Routing ─────────────────────────────────────┤
         │                                                        │
         └──► [Cloud Functions] ── Webhooks / External Sync      │
                                                                  │
[Personal Intelligence] ── Cross-App Context (Beta) ──────────────┘

Voice Input ──► Structured Data ──► Analytics ──► Pattern Detection
     ▲                                              │
     │                                              ▼
     └─────────── Prompt Optimization ──┐   Schema Evolution
                                        │         │
                                        └────┬────┘
                                             ▼
                                    Improved Extraction Accuracy