Skip to content

DETAILED_GUIDE.md

Latest commit

 

History

History
293 lines (225 loc) · 12.7 KB

DETAILED_GUIDE.md

File metadata and controls

293 lines (225 loc) · 12.7 KB
Raw

Detailed Guide

Architecture

TalkToMyExcel keeps the application small:

  • Flask renders the UI and exposes JSON endpoints.
  • Docker reads user-uploaded tabular files, returns safe CSV extracts plus metadata, and runs generated Python analysis when needed.
  • DuckDB stores the imported datasets for each user workspace.
  • Chroma stores embeddings for selected text columns.
  • A query router selects or combines exact, semantic, and sandboxed analysis paths.
  • Per-user conversation history supports contextual follow-up questions.
  • Regolo.ai is the recommended OpenAI-compatible provider for chat and embeddings.

The server never imports Pandas or OpenPyXL to read user files. File profiling and extraction happen in the sandbox image.

Sandbox Image

The running app needs a Docker image named by SANDBOX_IMAGE. By default, .env.example sets both SANDBOX_IMAGE and PYTHON_SANDBOX_IMAGE to:

talktomyexcel-sandbox:latest

That single image is used for two isolated jobs:

  • File profiling and extraction during upload staging.
  • Generated Python analysis for advanced calculation questions.

The normal build is:

docker build -f Dockerfile.sandbox -t talktomyexcel-sandbox:latest .

This starts from python:3.11-slim and installs the sandbox data libraries declared in Dockerfile.sandbox: pandas, openpyxl, xlrd, and pyarrow. Use this build for a regular local setup or a regular server deployment.

There is also an advanced build variant:

docker build -f Dockerfile.sandbox \
  --build-arg SANDBOX_BASE=code-interpreter:latest \
  --build-arg INSTALL_SANDBOX_DEPS=0 \
  -t talktomyexcel-sandbox:latest .

This exists only for environments that already maintain a compatible sandbox base image with the required Python data libraries preinstalled. SANDBOX_BASE selects that base image, while INSTALL_SANDBOX_DEPS=0 avoids reinstalling the same dependencies inside this project build. If the base image is not available, or if you are not deliberately managing a shared sandbox base image, use the normal build.

Users and Workspaces

Each user gets an isolated workspace:

app/data/workspaces/<user_id>/
app/uploads/workspaces/<user_id>/

Each user can keep multiple imported datasets in the same workspace. Uploading a new file is safe during staging. Importing with replace_existing=false adds the file to the workspace; importing with replace_existing=true clears the current datasets first. Individual datasets can be removed without deleting the rest of the workspace.

Conversation history and the last payload estimate are also stored per user. Importing a dataset clears that history so a new data context cannot accidentally reuse an old conversation.

Provider Settings

Built-in provider definitions live in app/default_providers.json. The active selection and any custom definitions are stored in app/data/settings.json, which is created on first start.

Regolo.ai is preconfigured:

REGOLO_API_KEY=...

The admin can choose:

  • Chat provider/model
  • Embedding provider/model

Local embeddings are supported through sentence-transformers. They keep semantic text local but need a compatible PyTorch runtime.

To add an OpenAI-compatible endpoint, stop the application and add provider definitions to app/data/settings.json. Keep secrets in environment variables, not in this JSON file. For example:

{
  "chat": {
    "provider": "acme-chat",
    "model": "chat-model",
    "temperature": 0.2
  },
  "embedding": {
    "provider": "acme-embedding",
    "model": "embedding-model"
  },
  "custom_llm_providers": [
    {
      "id": "acme-chat",
      "name": "Acme Chat",
      "type": "openai_compatible",
      "base_url": "https://api.example.com/v1",
      "api_key_env": "ACME_API_KEY",
      "requires_api_key": true,
      "models": ["chat-model"],
      "default_model": "chat-model"
    }
  ],
  "custom_embedding_providers": [
    {
      "id": "acme-embedding",
      "name": "Acme Embeddings",
      "type": "openai_compatible",
      "base_url": "https://api.example.com/v1",
      "api_key_env": "ACME_API_KEY",
      "requires_api_key": true,
      "models": ["embedding-model"],
      "default_model": "embedding-model"
    }
  ]
}

Then set ACME_API_KEY in .env, restart the application, and verify the selection on the Settings page. Provider IDs must be unique across their own chat or embedding list.

Changing only the chat provider or model affects later requests immediately. Changing the embedding provider/model, SEMANTIC_CHUNK_SIZE, or SEMANTIC_CHUNK_OVERLAP makes existing Chroma vectors incompatible with the new configuration. Restart after environment changes and run a full semantic index rebuild before using semantic search. Existing vectors are not migrated automatically.

File Flow

  1. POST /api/staging
  2. Server saves the upload to workspace staging storage.
  3. Docker profiles the file and writes normalized CSV outputs.
  4. UI shows sheets, preview rows, and suggested semantic columns.
  5. POST /api/workbooks imports selected sheets and columns.
  6. DuckDB stores the imported tables with dataset-scoped names.
  7. Chroma adds semantic documents only for the newly imported dataset. Removing a dataset deletes only its documents.

A full semantic-index rebuild remains available from the UI and through POST /api/semantic-index/rebuild. It is mandatory after changing the embedding provider/model or semantic chunk settings, and is also useful for maintenance or recovery.

Query Flow

The default ROUTER_VERSION=legacy query engine uses routed spreadsheet tools:

  • Exact status/serial questions use DuckDB.
  • Broad count questions use deterministic DuckDB summaries.
  • Exact filters, dates, aggregates, and explicit group-by questions use read-only SQL.
  • Similar-problem questions use embeddings.
  • Hybrid questions filter structured rows and then apply semantic search.
  • Multi-intent questions can combine SQL and semantic evidence before final synthesis.
  • Advanced numeric, diff, missing-ID, and multi-step calculation questions can use generated Python in a short-lived Docker sandbox.
  • Follow-up questions such as "same, but only open" use recent conversation context to resolve the current request before routing.

The chat model receives the question, compact schema/result context, and recent conversation context when applicable. Routes tied to identifiable worksheet rows can also return source references; aggregate and generated-analysis routes may not. The chat model is not used as the database.

When remote embeddings are selected, the embedding endpoint receives the text from semantic columns during import/rebuild and the user's semantic query at search time. Select local embeddings if that text must not leave the host.

A technically valid query that returns no rows is treated as a final empty result. Fallback routes are reserved for technical failures or unsupported paths, avoiding a different interpretation of a valid zero-result query.

For Python analysis, the application exports the active DuckDB workspace tables to temporary CSV files, mounts them read-only at /input, runs generated Python with network disabled, and reads /output/result.json. The manifest includes dataset filenames so generated code can compare files. The container is removed after the run.

See routing.md for route details and evaluation.md for the private golden Q/A workflow.

ROUTER_VERSION=v2 enables intent-first routing: a model resolves the requested data and operations, a validator checks references and conversions, and a deterministic planner chooses explicit execution steps. SQL filters and aggregates are compiled from that intent; Python exports contain only the selected tables and filtered rows. Clarifications are first-class API responses. See routing-v2.md for enablement, limitations, rollback and the public synthetic evaluation. The legacy description above applies only to legacy mode.

Conversation Controls

The application keeps at most 20 recent messages per user. Legacy routing uses the most recent exchange only when the current question contains a follow-up reference. V2 also retains the last validated intent, filters, displayed row IDs and pending clarification, and resolves follow-ups without a keyword gate.

The UI shows an estimated context percentage based on the last LLM payload when available. The clear button removes history, saved intent/clarification and the payload estimate through POST /api/session/clear. Import, dataset deletion and semantic-index rebuild also clear the saved conversation state.

User administration

Administrators can open Users in the top navigation to view accounts and create a new one. The directory shows account names and email addresses, roles, enabled/disabled status, and creation dates. With JavaScript enabled, search by name or email and combine role/status filters; the account count updates as you type. The full directory and creation form also work without JavaScript.

The creation form explains User and Admin access. Failed submissions preserve the entered name, email and role, but never refill the password. Successful creation shows a confirmation and marks the new account in the directory. Accounts can sign in immediately using the password set by the administrator. Each account has its own files and conversation. Manage account opens the account details, role/status controls, password reset and deletion form. Updating an email keeps the same account ID and workspace. Disabling access preserves all data. Deletion requires typing the account email, removes the account permanently, and retains stored files and conversation on the server. Recreating the email creates a different account and does not restore access to the old workspace. Temporary demo accounts continue to use the existing expiry/cleanup lifecycle.

Every regular user can open Password to change their own password, providing the current password and entering the new password twice. Administrators can set a new password for other accounts without their old password. Passwords are never refilled after a failed submission. Password changes revoke other sessions; the session performing its own password change remains signed in. Disabling an account or changing its role also revokes existing sessions, and re-enabling it does not restore old sessions. Revocation applies on the next authenticated request; requests already in progress are not cancelled.

Account forms use session-bound CSRF tokens. Permissions and account updates are validated inside a shared file lock. The application blocks self-deletion, self-disabling and changes that would remove the last enabled administrator. Existing accounts default to session version zero; no data migration is required.

Optional Demo Sessions

Set these values to expose the temporary demo button on the login page:

DEMO_ENABLED=true
DEMO_TIMEOUT_MINUTES=30

Each demo visitor gets an isolated, initially empty workspace. Expired demo users and their workspace files are removed on later demo activity. Production deployments should also schedule cleanup so it does not depend on new visitors. Use the same virtual-environment interpreter and operating-system account as the application. For an installation under /srv/TalkToMyExcel, a cron entry can be:

*/5 * * * * /srv/TalkToMyExcel/.venv/bin/python /srv/TalkToMyExcel/scripts/cleanup_demo_users.py >> /srv/TalkToMyExcel/app/logs/demo-cleanup.log 2>&1

Change /srv/TalkToMyExcel to the real absolute path. Run the command manually once before installing the cron entry and check that it prints the number of deleted users.

Same-host Concurrency

Users, provider settings, and conversation sessions use atomic JSON replacement plus inter-process file locks. This supports multiple Gunicorn workers sharing one local data directory. Do not mount the same JSON files into several app hosts and treat them as a distributed database; use a single application host or migrate that state to a transactional database before horizontal scaling.

Logs

Set:

LOG_LEVEL=DEBUG
LOG_FORMAT=json
LOG_DIR=app/logs

Useful fields include request_id, user_id, workspace_id, staging_id, workbook_id, and selected query route.

Troubleshooting

If upload profiling fails, check Docker:

docker images | grep talktomyexcel-sandbox
docker build -f Dockerfile.sandbox -t talktomyexcel-sandbox:latest .

Use the advanced SANDBOX_BASE / INSTALL_SANDBOX_DEPS=0 build only when the chosen base image already contains pandas, openpyxl, xlrd, and pyarrow. Otherwise the sandbox worker will not be able to read uploaded Excel, CSV, TSV, or Parquet files.

If Regolo.ai calls fail, verify REGOLO_API_KEY and selected model names in Settings.