Google Cloud / BigQuery / GitHub Skill -- Valor Investigations

Examples of when to use evidence search:

• "What evidence exists of document manipulation?"
• "Who restricted Patricia from visiting Russell?"
• "What medications was Russell given?"
• "What did Emily Cooper do as Russell's attorney?"
• "Find everything about the hospice certification"
• "What role did Tempie Bartell play?"

The evidence search returns:

1. An AI Summary synthesized across multiple documents with citation numbers
2. A list of Source Documents with file names, categories, and paths
3. A total match count showing how many documents are relevant

After running the search, present the AI summary to Levi. If he wants to dig deeper into a specific source document, you can use the SQL mode to pull the full text.

Mode 2: SQL QUERY (structured data -- for specific lookups)

For exact counts, specific field lookups, data manipulation, or when Levi explicitly asks for SQL. This hits BigQuery directly with SQL.

When to use: The user wants a count, wants to filter by specific fields, needs to update data, or asks something structural about the database itself.

python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\bq_query.py" "SELECT COUNT(*) FROM \`valorinvestigates.valor_investigations.bingaman_documents\`"

Examples of when to use SQL:

• "How many documents do we have?"
• "List all categories"
• "Show me the schema for bingaman_documents"
• "Update the category on document X"
• "What's the row count across all tables?"

Mode 3: GITHUB SEARCH (research files, case law, legal memos)

For finding content in the ValorInvestigator GitHub repos -- legal research, case law analysis, memos, and synthesized documents that live in the repos rather than BigQuery.

When to use: The user asks about case law, legal research, memos, or previously written analysis. Also useful for finding what research already exists before doing new work.

python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\github_search.py" "search terms"
python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\github_search.py" "PETA OHSU" --repo dhs-public-records-filing
python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\github_search.py" --list-repos
python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\github_search.py" "search" --json

Key repos searched:

• dhs-public-records-filing -- DHS lawsuit filing package, case law, legal research
• Bingaman-Case-Evidence -- Complete evidence archive (court filings, forensics, medical, witness)
• odhs-state-health-law -- Oregon State Plan, Medicare, OAR citations
• oregon-legal-research -- Searchable case law + statutes database
• the-Quarterback -- Research repository

Important: GitHub search only indexes text files (.md, code). It does NOT search inside PDFs. For PDF content, use Evidence Search (Mode 1) instead.

Mode 4: CASE LAW SEARCH (legal arguments -- for lawsuit support)

For finding specific case law holdings, citations, and legal arguments from the fully analyzed Oregon public records case law database. This searches 5 local analysis files covering 13 Oregon cases and the AG Public Records Handbook.

When to use: The user asks about case law, legal holdings, attorney fees arguments, burden of proof, stonewalling doctrine, delay vs. denial, or any legal argument that supports the DHS lawsuit. This is the most targeted tool for "what case says X?" questions.

python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\case_law_search.py" "search terms"
python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\case_law_search.py" "attorney fees" --context 5   # more context lines
python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\case_law_search.py" --cases                        # list all cases with citations
python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\case_law_search.py" --list                         # list analysis files
python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\case_law_search.py" "burden" --json                # JSON output

Analysis files searched:

• CASE_LAW_STRATEGIC_REVIEW.md -- Master synthesis with 7 pillars, case tiers, strategic recommendations
• case_law_analysis_batch1.md -- Chaimov v. State (COA 2021, SC 2022, DAS 2022)
• case_law_analysis_batch2.md -- PETA v. OHSU, In Defense of Animals, Kessler, Merrick
• case_law_analysis_batch3.md -- Bialostosky, Sause v. Hummel, Upham v. Forster
• case_law_analysis_batch4.md -- Pamplin Media, Pride Disposal, LOC Handbook Ch. 14

Key cases available (with citations):

• Merrick v. City of Portland (313 Or App 469, 2021) -- stonewalling = denial, partial prevailing = fees
• PETA v. OHSU (2025 Oregon) -- delay vs. denial bright line, fees only for denial
• Chaimov v. State (369 Or 174, 2022) -- burden on agency, exemptions narrowly construed
• Kessler v. City of Portland (302 Or App 383, 2020) -- $57,900 fee award affirmed
• Bialostosky v. Cummings (321 Or App 563, 2022) -- exemptions must be specific per document

Examples of when to use case law search:

• "What case supports the argument that stonewalling equals denial?"
• "What's the citation for the attorney fees holding?"
• "What does the case law say about burden of proof?"
• "Find the PETA v OHSU delay vs denial analysis"
• "What case says ODHS has to search their email?"
• "What's the strongest case for mandatory fee shifting?"

The case law search returns matching lines with context from the analysis files, organized by source file. Use --cases to get a quick reference of all 13 cases with their citations.

Important: Case law search covers the analyzed markdown files, not the raw PDFs. For the original PDF text of the cases, use Evidence Search (Mode 1). For case law research in the GitHub repos (memos, filings), use GitHub Search (Mode 3).

DECISION GUIDE: Which mode?

When in doubt, use Evidence Search first for factual/investigative questions. Use Case Law Search for legal argument questions ("what case supports...", "what's the holding on...", "what citation do we need for..."). Use GitHub Search for previously written research memos. Fall back to SQL for precise counts.

NEW SESSION STARTUP -- DO THIS FIRST

Every new Claude session that needs BigQuery should start by running the health-check script. This confirms auth is working and shows what's available:

python "C:\Users\Big Levi\.claude\skills\gcloud-bigquery\scripts\bq_startup.py"

This script:

1. Gets a fresh auth token from the service account JSON key (no login required)
2. Lists all tables with row counts
3. Prints the project/dataset/region info
4. Confirms everything is ready to query

If the startup script prints AUTH_EXPIRED, the key file is missing or corrupted. Tell Levi: "The service account key at C:\Users\Big Levi.claude\keys\valorinvestigates-bigquery.json is missing or invalid." Do not retry. Do not fall back to local files. Stop.

AUTHENTICATION

Auth uses a service account JSON key at C:\Users\Big Levi\.claude\keys\valorinvestigates-bigquery.json. The google-auth library reads the key and mints a fresh token automatically. No gcloud auth login is ever needed.

Inline token pattern (for quick one-offs)

import json, urllib.request, sys, io

sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace')

SERVICE_ACCOUNT_KEY = r'C:\Users\Big Levi\.claude\keys\valorinvestigates-bigquery.json'
BQ_API = 'https://bigquery.googleapis.com/bigquery/v2/projects/valorinvestigates/queries'

def get_token():
    import google.oauth2.service_account as sa
    import google.auth.transport.requests as tr
    creds = sa.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_KEY,
        scopes=['https://www.googleapis.com/auth/bigquery']
    )
    creds.refresh(tr.Request())
    return creds.token

def bq_query(sql, token, timeout_ms=60000):
    body = json.dumps({
        'query': sql,
        'useLegacySql': False,
        'timeoutMs': timeout_ms
    }).encode()
    req = urllib.request.Request(BQ_API, data=body, headers={
        'Authorization': f'Bearer {token}',
        'Content-Type': 'application/json'
    })
    resp = urllib.request.urlopen(req)
    return json.loads(resp.read())

token = get_token()

If a query returns HTTP 401 or 403, call get_token() again and retry once.

PROJECT DETAILS

• Project: valorinvestigates (number: 989102413038)
• Dataset: valor_investigations
• Region: us-west1
• Account: [email protected]
• Full table prefix: valorinvestigates.valor_investigations.
• Vertex AI Engine: bingaman-search-v2 (15,554 documents indexed)
• Vertex AI Data Store: bingaman-evidence-v2

SCHEMA QUICK REFERENCE

Primary evidence table (used by Vertex AI search)

Chunked text table

Pipeline tables (being populated)

Legacy table (older import, fewer files)

COMMON SQL PATTERNS

Full-text search across all documents

SELECT document_id, file_name, file_path, category
FROM `valorinvestigates.valor_investigations.bingaman_documents`
WHERE LOWER(extracted_text) LIKE LOWER('%search term%')

Search document chunks (more granular, includes page numbers)

SELECT dc.chunk_id, dc.chunk_text, dc.page_number, d.file_name
FROM `valorinvestigates.valor_investigations.document_chunks` dc
JOIN `valorinvestigates.valor_investigations.bingaman_documents` d
  ON dc.doc_id = d.document_id
WHERE LOWER(dc.chunk_text) LIKE LOWER('%search term%')
ORDER BY d.file_name, dc.chunk_index

Count documents by category

SELECT category, COUNT(*) as doc_count
FROM `valorinvestigates.valor_investigations.bingaman_documents`
GROUP BY category
ORDER BY doc_count DESC

Get full text for a specific document

SELECT document_id, file_name, extracted_text
FROM `valorinvestigates.valor_investigations.bingaman_documents`
WHERE document_id = 'THE_DOC_ID'

DML operations (UPDATE, DELETE, INSERT)

Use a longer timeout (120000ms):

sql = """UPDATE `valorinvestigates.valor_investigations.bingaman_documents`
SET category = 'new_category'
WHERE document_id = 'some_id'"""
result = bq_query(sql, token, timeout_ms=120000)

HANDLING LARGE RESULTS

BigQuery paginates results. If totalRows is larger than what you received, use the pageToken:

result = bq_query(sql, token)
all_rows = result.get('rows', [])

while 'pageToken' in result:
    page_token = result['pageToken']
    job_id = result['jobReference']['jobId']
    page_url = f'https://bigquery.googleapis.com/bigquery/v2/projects/valorinvestigates/queries/{job_id}?pageToken={page_token}'
    req = urllib.request.Request(page_url, headers={
        'Authorization': f'Bearer {token}',
        'Content-Type': 'application/json'
    })
    result = json.loads(urllib.request.urlopen(req).read())
    all_rows.extend(result.get('rows', []))

ERROR HANDLING

GITHUB REPOS QUICK REFERENCE

CRITICAL REMINDERS

1. Evidence search first for factual questions, Case Law search first for legal questions. For investigative questions about case evidence, use Vertex AI (Mode 1). For legal argument questions ("what case supports..."), use Case Law Search (Mode 4). For precise counts or structural queries, use SQL (Mode 2). For research memos in GitHub repos, use GitHub Search (Mode 3).
2. Always use Standard SQL (useLegacySql: false).
3. Always backtick-escape table names in SQL: `valorinvestigates.valor_investigations.tablename`
4. Windows encoding: Always set sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace') at the top of any Python script.
5. Never search local files as a substitute for a database, GitHub, or case law query.
6. The primary evidence table is bingaman_documents (15,596 rows), not the older documents table (10,599 rows).
7. GitHub search only finds text files (.md, code). For PDF content, use Evidence Search.
8. GitHub rate limits: If you get rate limited, wait 60 seconds and retry. Don't spam the API.
9. Case law search covers analyzed markdown files, not raw PDFs. It searches 13 Oregon cases fully analyzed with citations, holdings, and quotable language. Use --cases for a quick reference of all cases.