Structurify

Live Demo: structurify.web.app

Structurify is a production-ready, event-driven B2B SaaS platform that automates the transformation of unstructured, messy spreadsheet data (CSV, XLSX) into a standardized master schema using Google Gemini 2.5 Flash.

Built on a completely decoupled Serverless Fan-Out Architecture on Google Cloud Platform (GCP), Structurify ensures 0% web server blocking, high resilience under burst loads, and highly cost-effective scaling. It utilizes a LangGraph Map-Reduce pipeline to gracefully process files of immense scale by dynamically batching and error-correcting the LLM output.

---

Core Features

• Strict Schema Enforcement: Define exactly the JSON/Excel schema you need, and Structurify will enforce strict type-casting and structure.
• Sandbox Preview Mode: Process just the first 10 rows of a dataset to validate schema fit and AI transformation quality before committing to a full long-running job.
• Auto-Clean Mode: Don't know the schema? Structurify will automatically infer the schema from the file headers and repair capitalization, trim whitespace, and standardize date formats across the board.
• Email Notifications: Upload a massive dataset (over 1MB), and Structurify will immediately email you a tracking link to watch the live progress, followed by a final success email with your secure download URL.
• Enterprise SSO & Multi-Tenant Authentication: Provides robust authentication via Firebase Identity Platform, supporting Google OAuth, SAML, and OIDC enterprise SSO. Features automatic account linking for identity conflict resolution and maps users to isolated multi-tenant workspaces based on their provider tenantId.
• Massive Scalability: The backend acts as a lightweight router while heavy data processing is handled by scalable workers via Cloud Pub/Sub, preventing Gateway Timeouts on long jobs.
• Dynamic Configuration & Prompt Management: An integrated Admin UI backed by a real-time Firestore synchronization engine allows operators to hot-swap Gemini LLM models, tune chunk sizes, and edit system AI prompts entirely on the fly without ever redeploying code.
• Graceful Job Cancellation: Safely halt massive in-flight jobs via a UI cancel button. In-memory TTL caching on workers ensures instant cancellation without generating "ghost jobs" or burning Firestore read quotas.
• Custom Toast Notifications: Uses non-blocking react-hot-toast popups instead of native browser alerts to provide users with a clean, modern experience when editing settings or executing administrative actions.
• Enterprise Observability & Billing: Logs rich telemetry into Firestore (job_audits), tracking LLM Token Usage via atomic transactions, File Sizes, IP Addresses, and exact Job Runtimes to power strict rate limits and future billing models.

---

System Architecture

The architecture utilizes a robust asynchronous data pipeline:

1. Next.js Frontend: The user drops a messy spreadsheet and defines a target JSON schema (or leaves it blank for Auto-Clean).
2. Direct to GCS: The file is securely uploaded directly from the browser to a Google Cloud Storage bucket via a presigned URL generated by the Backend Gateway.
3. API Gateway (FastAPI): Logs the job in Firestore and dispatches a lightweight event to Google Cloud Pub/Sub.
4. Cloud Run Worker (FastAPI/Python): A massively scalable, asynchronous worker consumes the Pub/Sub push notification.
5. LangGraph Map-Reduce Pipeline:
   • Split: The worker chunks the file (e.g. 500 rows at a time).
   • Map (LangGraph): Each chunk is pushed to Gemini 2.5 Flash using strict response_schema parameters. If Gemini hallucinates or encounters an error, a LangGraph state-machine automatically retries the extraction up to 3 times.
   • Reduce: A transaction counter monitors the chunks. When all are complete, a final Reducer service uses DuckDB to compile them into a pristine .csv and generate comprehensive metadata.json, packaging them into a secure .zip archive.
6. Real-time UI: The frontend listens to Firestore via onSnapshot and instantly provides the user with a real-time progress bar and a secure download URL.

graph TD;
    A[User Uploads File] --> B[Next.js Frontend];
    B --> C{File > 1MB?};
    C -- Yes --> D[Send 'Job Started' Email];
    C -- No --> E[Bypass Email];
    D --> E;
    E --> F[Upload to Google Cloud Storage];
    F --> G[Pub/Sub Job Queue];
    G --> H[Cloud Run AI Workers];
    H --> I[Gemini 2.5 Flash Map-Reduce];
    I --> J[DuckDB Data Aggregation];
    J --> K[Zip Creation data.csv + metadata.json];
    K --> L[Firestore Job Updated];
    L --> M[Send 'Job Completed' Email with Download Link];

Loading

---

Admin & Audit Management

Structurify features robust observability and administrative controls via an integrated Role-Based Access Control (RBAC) system.

1. Comprehensive Admin Dashboard (/admin):
   • A real-time, glassmorphic dashboard protected by Firestore security rules.
   • Displays global platform metrics: Total Users, Active Processing Jobs, Total Tokens Burned, and Job Success Rates.
   • Admins can inspect complete metadata for any job, including the user's target schema, AI-generated column summaries, execution duration, and fatal stack traces.
2. Global Kill Switch:
   • If a runaway job is consuming too many resources, Admins can trigger the "Kill Switch" directly from the dashboard.
   • This hits a dedicated backend API that securely seeks all Pub/Sub subscription cursors to now(), instantly purging the queue and gracefully cancelling all active workloads.
3. Telemetry & Audit Logging:
   • Every job execution is rigorously tracked via the AuditService.
   • The system decouple identity (user_id) from origin (ip_address) for guest rate-limiting.
   • We track exact compute costs (LLM tokens burned) extracted from Gemini API responses and safely increment them in Firestore via atomic transactions.
4. History & Job Management:
   • Both registered users and unauthenticated guests can view a complete history of their past extractions.
   • Users can securely cancel runaway jobs mid-flight directly from their dashboard.
5. Editable Documentation System (/docs):
   • A public, real-time documentation page backed directly by Firestore.
   • Admins have access to a split-pane live Markdown editor (with Mermaid.js support) to rewrite and persist docs instantly.
   • Features a dynamic Releases tab that autonomously pulls public release notes directly from the GitHub API.

---

Tech Stack

• Frontend: Next.js 14, React, TailwindCSS, Firebase Client SDK
• Backend API Gateway: Python, FastAPI, Uvicorn
• Asynchronous Worker: Python, FastAPI, Pandas, LangGraph, Google GenAI SDK (Gemini 2.5 Flash), Tenacity
• Cloud Infrastructure: Google Cloud Platform (Cloud Run, Cloud Storage, Cloud Pub/Sub, Firestore, Artifact Registry, Secret Manager)
• Architecture Standard: Clean Architecture / Domain Driven Design (DDD)
• Testing: pytest, httpx, jest, React Testing Library

---

Google Cloud Platform (GCP) Services Used

Structurify heavily leverages GCP's serverless ecosystem to achieve massive scalability and low operational overhead. Here is how each service is utilized:

1. Cloud Run: Hosts both the FastAPI Gateway (structurify-backend) and the LangGraph Processing Engine (structurify-worker) as serverless containers. The backend is public-facing, while the worker is private and triggered internally.
2. Cloud Storage (GCS): Provides highly durable object storage.
   • raw-uploads bucket stores incoming, messy spreadsheets.
   • processed-outputs bucket stores the final, clean .xlsx and .csv files.
3. Cloud Pub/Sub: The backbone of the asynchronous event-driven architecture. The backend publishes events to the schema-transformation-jobs topic, which then securely pushes the workload to the Cloud Run Worker without holding open synchronous HTTP connections.
4. Firestore (Datastore): The primary NoSQL database. It logs every job's status, tracking progress in real-time. The frontend subscribes to these Firestore documents via onSnapshot to render the live loading timeline.
5. Artifact Registry: Acts as the secure, private container image registry. It stores the Docker images for both the backend and worker before they are deployed to Cloud Run.
6. Secret Manager: Securely stores sensitive credentials like the GEMINI_API_KEY. The Cloud Run worker pulls these directly into environment variables at runtime, ensuring keys are never exposed in plaintext.
7. Firebase Hosting: Serves the Next.js static frontend application globally with low-latency CDN caching.

---

Project Structure

The codebase strictly adheres to Clean Architecture principles across all microservices:

Structurify/
├── backend/                  # API Gateway Service
│   ├── src/
│   │   ├── api/routers       # FastAPI endpoints
│   │   ├── core/             # Environment configs (pydantic-settings)
│   │   ├── models/           # Pydantic request/response schemas
│   │   └── services/         # GCP abstractions (Storage, PubSub, Firestore)
│   └── tests/                # Pytest suites
│
├── worker/                   # Asynchronous Processing Engine
│   ├── src/
│   │   ├── api/              # PubSub Push endpoints (Map / Reduce routes)
│   │   └── services/         # LangGraph Chunk Processor, Reducer, File Parser, Email Service
│   └── tests/                # Pytest suites (Mocks GCP & Gemini)
│
├── frontend/                 # Next.js Application
│   ├── src/
│   │   ├── app/              # Next.js App Router orchestration
│   │   ├── components/       # Atomic UI (SchemaBuilder, Timeline, UploadZone)
│   │   └── hooks/            # Decoupled business logic (useFileUpload, useJobListener)
│   └── __tests__/            # Jest component & hook tests
│
├── deploy.sh                 # Fully automated CI/CD to Cloud Run
└── terraform/                # Automated IaC with Terraform for entire GCP environment

---

Local Development Setup

1. Prerequisites

• Node.js >= 20
• Python >= 3.10
• Google Cloud CLI (gcloud) installed and authenticated
• A Firebase Project (for the frontend client)

2. Environment Configuration

Navigate into the respective folders and copy the .env.example to .env (or .env.local for frontend).

Backend (backend/.env)

GOOGLE_CLOUD_PROJECT=your-gcp-project-id
RAW_BUCKET_NAME=raw-uploads-your-gcp-project-id
PUBSUB_TOPIC_ID=schema-transformation-jobs

Worker (worker/.env)

GOOGLE_CLOUD_PROJECT=your-gcp-project-id
RAW_BUCKET_NAME=raw-uploads-your-gcp-project-id
PROCESSED_BUCKET_NAME=processed-outputs-your-gcp-project-id
GEMINI_API_KEY=your-gemini-api-key
FRONTEND_URL=http://localhost:3000
SMTP_SERVER=smtp.gmail.com
SMTP_PORT=587
SMTP_USERNAME=your-email@gmail.com
SMTP_PASSWORD=your-app-password
SMTP_FROM_EMAIL=your-email@gmail.com

Frontend (frontend/.env.local)

NEXT_PUBLIC_FIREBASE_API_KEY=...
NEXT_PUBLIC_FIREBASE_AUTH_DOMAIN=...
NEXT_PUBLIC_FIREBASE_PROJECT_ID=...
# ... other firebase config
NEXT_PUBLIC_BACKEND_URL=http://localhost:8000

3. Running Locally

Start the Backend (Port 8000)

cd backend
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
uvicorn src.main:app --port 8000 --reload

Start the Worker (Port 8080)

cd worker
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
uvicorn src.main:app --port 8080 --reload

Start the Frontend (Port 3000)

cd frontend
npm install
npm run dev

4. Running Tests

• Backend: cd backend && PYTHONPATH=backend pytest backend/tests/
• Worker: cd worker && PYTHONPATH=worker pytest worker/tests/
• Frontend: cd frontend && npm run test

5. Generating Mock Data

To test the pipeline with large, complex datasets, you can generate clean or highly unstructured "messy" data using the provided generation script.

# Ensure Faker is installed in your environment
pip install Faker

# Generate 50,000 rows of both clean and messy data
python sample_data/generate_data.py --rows 50000 --type both

The script will output generated_clean_50000.csv and generated_messy_50000.csv directly into the sample_data/ folder (these files are safely git-ignored).

---

Cloud Deployment

👉 See DEPLOYMENT.md for full deployment instructions and architecture.