How to Build an AI Analytics and Reporting Platform with Python, FastAPI, and LLMs
- 13 hours ago
- 11 min read
You uploaded the CSV. You opened it in Excel. You sorted a few columns, maybe made a quick pivot table — and you already know that none of this will end up in a presentation anyone will actually trust.
Building a reliable analytics pipeline from scratch is hard. Most BI tools are either too expensive, too opaque, or locked to a specific cloud vendor. Jupyter notebooks are flexible but don't export cleanly, don't validate inputs, and can't be handed to a non-technical stakeholder without hours of cleanup. And letting an LLM run raw code against your data without guardrails is a recipe for silent errors.
The AI Analytics and Reporting Platform is a self-contained web application that solves all of this in one pipeline. Users upload a dataset, receive automatic schema profiling, create an analysis plan (manually or via a natural language prompt), execute it through a deterministic engine, and get professional DOCX and PPTX reports — with every step logged and reproducible.
Real-world use cases this application supports:
Data analysts who need explainable, auditable analysis pipelines
Startups building lightweight internal dashboards without vendor lock-in
Freelancers delivering polished data reports to clients
CS students learning how end-to-end data platforms work
Technical founders who want BI without Tableau pricing
Consultants generating rapid reports from client-provided data
This post covers the system architecture, technology stack, implementation phases, and the non-obvious challenges you'll run into while building it. It does not include full source code — that's in the full course on labs.codersarts.com.
📄 Before you dive in — grab the free PRD template that maps out this entire system: architecture, API spec, sprint plan, and system prompt. [Download the free PRD]
How It Works: Core Concept
The naive approach to "AI analytics" is to pipe a user's question directly into an LLM and let it run code. It feels elegant. It breaks constantly.
The problem is context mismatch. An LLM doesn't know your schema, your column names, or whether your date column is stored as a string. Ask it to "find revenue trends" on a dataset where revenue is called net_amt_usd and the date column is named txn_dt_str, and you'll get either an error or a confident hallucination.
This platform solves that with a structured planning layer that sits between user intent and execution. Think of it like a compiler: the user writes human-readable intent, the planner translates it into a structured intermediate representation (the AnalysisPlan), and the execution engine runs only that validated plan against your actual data.
Here's how that looks as a pipeline:
Data Ingestion & Profile Phase:
[User uploads CSV/XLSX]
│
▼
[FastAPI stores file]
│
▼
[Pandas loads DataFrame]
│
▼
[Profiler: schema detection, null counts, stats, preview]
│
▼
[DatasetProfile saved to SQLite]
Analysis & Execution Phase:
[User enters query / selects manual config]
│
▼
[Planner: schema-grounded AnalysisPlan created]
│
▼
[Validator: plan checked against DatasetProfile]
│
▼
[Execution Engine: filter → aggregate → stats → correlate → anomaly detect]
│
▼
[Artifacts: tables, charts (PNG + Plotly), insight strings]
│
▼
[Report Generator: DOCX + PPTX from artifacts]
│
▼
[RunRecord persisted to SQLite]
The analogy: this is like the difference between asking a contractor to "just start building" versus handing them approved blueprints. The blueprints (the plan) must be validated before a single nail goes in.
This architecture solves the core failure modes of naive LLM analytics: hallucinated column names, unvalidated operations, untraceable results, and non-reproducible outputs.
System Architecture Deep Dive
Architecture Overview
The platform is structured into five distinct layers:
Frontend layer — A single-page application served by FastAPI, organized into four views: Data, Analyze, Results, and History. The frontend consumes JSON from the API and renders profile cards, plan previews, execution logs, charts, and download links. It is built with vanilla HTML/CSS/JavaScript to keep the stack lightweight and deployable anywhere.
Backend / API layer — FastAPI handles file uploads, dataset queries, plan creation, analysis execution, report serving, and history retrieval. All routes are stateless; state lives in SQLite. The API uses Pydantic models throughout for input validation and serialization.
AI / Planning layer — The planner module takes a user prompt and a DatasetProfile and produces a structured AnalysisPlan object. In the current implementation, this uses schema-grounded heuristics (keyword matching against column names, types, and stats). The architecture is ready for a live LLM to replace or augment the heuristics — the planner interface and the plan schema don't change.
Execution / Analytics layer — The execution engine takes an AnalysisPlan and a pandas DataFrame and runs each step deterministically: filtering, aggregation, descriptive statistics, correlation matrices, anomaly detection. Every step produces an ExecutionLog (with generated code and explanation) and zero or more ExecutionArtifact objects (tables, charts, insights, files).
Data / Persistence layer — SQLite (via a thin Storage class) persists DatasetRecord and RunRecord objects. Files are stored on disk (uploads, charts, reports). DuckDB is used for fast in-memory aggregation queries without needing a running database server.
Component Table
Component | Role | Technology Options |
File upload handler | Accept and store CSV/XLSX files | FastAPI + Python's shutil |
Schema profiler | Detect dtypes, nulls, stats, preview | pandas, custom profiling.py |
Planning engine | Translate user intent → AnalysisPlan | Heuristic rules / LLM (Claude, GPT-4o) |
Plan validator | Check plan fields against schema | Pydantic, custom validate_plan() |
Execution engine | Run analysis steps deterministically | pandas, DuckDB, scipy |
Chart renderer | Generate static + interactive charts | matplotlib/seaborn (PNG), Plotly (JSON) |
Report generator | Produce DOCX and PPTX from artifacts | python-docx, python-pptx |
API layer | Route all HTTP requests | FastAPI, Pydantic |
Persistence | Store datasets and run history | SQLite (dev), PostgreSQL (prod) |
Frontend | Single-page UI | Vanilla JS, HTML, CSS |
Data Flow Walkthrough
User selects a CSV or XLSX file in the Data tab and submits the upload form.
FastAPI receives the file, assigns a UUID, and saves it to disk.
pandas loads the file into a DataFrame; profile_dataframe() computes column dtypes, null counts, unique counts, sample values, and numeric stats.
A DatasetProfile is serialized and stored in SQLite. The UI renders column profile cards.
User navigates to the Analyze tab, chooses manual or AI mode.
In AI mode, the query string + DatasetProfile go to create_ai_plan(), which returns an AnalysisPlan with typed steps, metrics, group-by, filters, and chart definitions.
validate_plan() checks every field reference against the known column list. Errors are returned to the UI before execution.
User confirms the plan. FastAPI calls execute_plan(), which loops through each step, runs the computation, and appends artifacts and logs.
After execution, generate_docx_report() and generate_pptx_report() assemble the artifacts into downloadable files.
A RunRecord is saved to SQLite. The frontend renders charts, stats, insights, logs, and export links in the Results tab.
Non-Obvious Design Decisions
Decision 1: Structured plan as the interface contract. Rather than allowing the frontend to post raw operation parameters to the execution engine, every run must go through an AnalysisPlan. This enforces a hard boundary: the execution engine only knows how to run plans, not how to interpret user input. This separation is what makes plan previewing, history replay, and future LLM replacement all possible without touching the executor.
Decision 2: Dual chart artifact format. The execution engine generates both a static PNG (for reports) and a Plotly JSON specification (for interactive in-browser rendering). This is more work upfront but prevents a future rewrite: report generation never needs a browser, while the frontend never needs to download binaries.
Tech Stack Recommendation
Stack A: Beginner / Weekend Prototype
Perfect if you want to understand the architecture and get something running in 2–3 days.
Layer | Technology | Why |
Language | Python 3.11 | Batteries included, pandas/FastAPI ecosystem |
API framework | FastAPI | Automatic docs, Pydantic integration |
Data processing | pandas | Easy DataFrame ops, CSV/XLSX reading |
In-memory SQL | DuckDB | Fast aggregations, no server needed |
Charts | matplotlib + Plotly | matplotlib for PNG, Plotly for interactive |
Report export | python-docx, python-pptx | Pure Python, no external dependencies |
Database | SQLite | Zero setup, file-based |
Frontend | Vanilla JS + HTML | No build step, served directly by FastAPI |
Estimated cost: ~$0/month (runs entirely on your laptop or a $5/month VPS)
Stack B: Production-Ready
Built to handle multiple users, larger files, and real deployment.
Layer | Technology | Why |
Language | Python 3.12 | Latest performance improvements |
API framework | FastAPI + Gunicorn/Uvicorn | Multi-worker production server |
Data processing | pandas + Polars | Polars for large file speed |
In-memory SQL | DuckDB | Columnar performance on large datasets |
Planning AI | Claude claude-sonnet-4-6 or GPT-4o | Structured JSON planning via tool use |
Charts | Plotly + WeasyPrint | Vector export, branded themes |
Report export | python-docx, python-pptx, WeasyPrint | PDF support |
Database | PostgreSQL | Multi-user, proper indexing |
File storage | S3-compatible (AWS S3, MinIO) | Durable, scalable |
Auth | Auth0 / Clerk | JWT-based user sessions |
Deployment | Docker + Railway / Render | One-command deploy |
Estimated cost: ~$20–50/month for a small-scale production deployment (Render + managed Postgres + S3)
Implementation Phases
Phase 1: Data Ingestion and Profiling
This phase establishes the foundation. You build the file upload endpoint, the pandas loading logic that handles CSV and Excel formats, and the profiler that computes per-column statistics including null rates, unique value counts, numeric distributions, and sample values.
Key technical decisions at this stage:
How to handle files with encoding issues (UTF-8 vs Latin-1 fallback)
Whether to eagerly load all rows or sample large files first
How to represent the DatasetProfile schema so it's useful to both the planner and the validator
How to persist profiles in SQLite without bloating the DB with raw data
The non-obvious trap here is assuming all CSV files are well-formed. Real-world files have mixed dtypes, header rows embedded mid-file, and trailing blank rows. Building robust loading logic early saves hours of debugging later.
Handling mixed-type column detection and automatic dtype coercion is covered in detail in the full course with working, tested code.
Phase 2: Analysis Planning and Validation
This phase is the intellectual core of the application. You build the AnalysisPlan Pydantic schema, the manual plan builder (which takes UI form fields and converts them into plan objects), the AI plan builder (schema-grounded heuristics or LLM call), and the plan validator.
Key technical decisions:
Which step types to support: filter, aggregate, correlation, anomaly_detection, descriptive_stats, timeseries, forecast, distribution_analysis, categorical_analysis
How to design the validator to produce human-readable error messages
How to make the AI planner fail gracefully when the query is ambiguous
How to handle the plan round-trip: plan is created on the backend but must be displayable in the UI before the user confirms execution
The validator is where most bugs surface. A plan that references a column named revenue when the dataset only has Revenue (capitalization mismatch) should be caught here, not mid-execution with a cryptic pandas KeyError.
Writing a robust schema-grounded AI planner that avoids hallucinated column references is covered in detail in the full course with working, tested code.
Phase 3: Execution Engine
You build the step-by-step deterministic executor. For each step type in the plan, the executor knows how to run it, what artifacts to produce, and what to log. This is where DuckDB integration happens for fast aggregation, where correlation matrices are computed, and where anomaly detection runs.
Key technical decisions:
How to structure ExecutionLog objects (generated code, explanation, output preview)
Whether to use pandas or DuckDB for aggregation (DuckDB is faster for grouped sums at scale)
How to detect anomalies: IQR-based outlier detection vs. Z-score vs. isolation forest
How to handle execution failures gracefully: one failed step should not crash the entire run
The execution engine is also where you handle numeric stability issues. Correlation matrices on datasets with fewer than 3 non-null rows return NaN. Anomaly detection on constant columns returns division-by-zero errors. These edge cases must be handled with explicit guards.
Step-level execution logging, DuckDB aggregation, and anomaly detection with proper error guards are all covered in detail in the full course with working, tested code.
Phase 4: Visualization and Report Generation
You build the dual-output chart renderer (static PNG via matplotlib/seaborn + interactive Plotly JSON), then the DOCX and PPTX generators that assemble all artifacts into download-ready files.
Key technical decisions:
How to design a chart theme system that produces consistent, branded outputs
How to pass Plotly specs through the API without breaking JSON serialization
How to structure DOCX sections so that tables, charts, and insight paragraphs flow naturally
How to handle missing charts gracefully in the PPTX generator (not every run will have charts)
The report generation step is where presentation quality matters. python-docx's default styles produce ugly output; building a minimal but clean document style takes deliberate effort.
Chart theming, DOCX section assembly, and PPTX slide layout logic are covered in detail in the full course with working, tested code.
Phase 5: Persistence, Frontend, and History
You wire the SQLite storage layer, build the single-page frontend, and ensure the History tab can reload and replay any past run. This phase also covers the dataset list sidebar, the row explorer modal, and the run detail view.
Key technical decisions:
How to serialize and deserialize AnalysisPlan and ExecutionResult from SQLite JSON columns
How to handle large RunRecord objects without bloating the history endpoint
How to architect the frontend JS so that tab switching doesn't re-fetch data unnecessarily
How to implement pagination or lazy loading for large history lists
SQLite run persistence, history pagination, and frontend state management are covered in detail in the full course with working, tested code.
Common Challenges
1. The AI Planner Hallucinates Column Names
Root cause: LLMs trained on general code generate column names that look plausible but don't exist in the uploaded dataset.
Fix: Never pass column references to the executor without first running them through validate_plan(). The validator should do a case-insensitive lookup against the actual column list and return a clear error if a column is not found.
2. DuckDB Returns Incorrect Types After Aggregation
Root cause: DuckDB's native types don't always round-trip cleanly into pandas dtypes. A SUM() on an integer column may return a DuckDB-native numeric that pandas misidentifies.
Fix: Always call .df() to convert DuckDB results to pandas immediately after the query, and re-cast numeric columns explicitly before further processing.
3. Correlation Matrix Fails on Small or Constant Columns
Root cause: Pearson correlation requires variance. A column where every value is identical produces a denominator of zero.
Fix: Filter out constant columns before computing correlation. Log a warning in the ExecutionLog so the user understands why certain columns were excluded.
4. Chart PNG Generation Fails in Headless Environments
Root cause: matplotlib tries to use an interactive display backend (TkAgg, etc.) by default. On a server with no GUI, this raises a cannot connect to X server error.
Fix: Call matplotlib.use("Agg") before any chart generation code. This must happen before the first import matplotlib.pyplot in the execution path.
5. PPTX Report Has Broken Image Paths
Root cause: python-pptx adds images by file path. If the chart directory path is relative and the working directory changes between report generation and file serving, images silently disappear.
Fix: Always resolve chart file paths to absolute paths before passing them to the report generator.
6. Large Datasets Exceed pandas Memory on Upload
Root cause: pandas loads the entire file into RAM. A 200MB CSV with 2M rows can exhaust memory on a small VPS.
Fix: Add a row-count guard in the profiler. For files over a configurable threshold, use chunked reading to compute only the profile (not the full DataFrame), and use DuckDB for subsequent query execution.
7. SQLite Run Records Grow Without Bound
Root cause: Each RunRecord includes the full ExecutionResult as JSON, which includes chart artifact payloads and full log strings. After 500 runs, the DB becomes sluggish.
Fix: Store only a summary in the runs table. Write full RunRecord JSON to disk (a per-run file) and reference it by path.
Solving these issues took us over 40 hours of testing — the course walks you through each fix with working code.
Ready to Build This Yourself?
Understanding the architecture is the first step. Shipping working code is a different mountain entirely.
In the full course, you get everything you need to go from zero to a deployed, professional-grade AI analytics platform:
✅ Full source code for all 8 modules (profiling, planning, execution, charts, reports, frontend, storage, deployment)
✅ 24 step-by-step video lessons — one for each module and lesson in the curriculum
✅ Docker setup and docker-compose configuration for local and production deployment
✅ Tested configurations for both SQLite (dev) and PostgreSQL (prod)
✅ Deployment walkthrough to Railway, Render, or a VPS
✅ Working AI planner examples using both heuristic and LLM-backed approaches
✅ Lifetime access and free updates as the codebase evolves
✅ Community support via the Codersarts Discord
$30.00 Everything above.
Want someone to build it alongside you? Book a 1:1 guided session at $20/hour — a Codersarts engineer will pair with you through the full implementation, answer your architecture questions live, and help you customise the platform for your specific use case.
Conclusion
The AI Analytics and Reporting Platform is a five-layer pipeline: file ingestion and profiling, schema-grounded planning, validated deterministic execution, dual-format chart rendering, and professional DOCX/PPTX report generation — with every run stored for replay and inspection.
The simplest viable place to start is the beginner stack: Python + FastAPI + pandas + DuckDB + SQLite, running locally. Get the upload, profiling, and manual planning working first. Once that pipeline is solid, the AI planning layer and report generation slot in cleanly on top.
If you want the full source code, the video walkthrough, and the working Docker setup, the course at labs.codersarts.com has everything you need.
Comments