Operational Problem
Manual imports are slow and fragile. Operators need to upload hundreds or thousands of records without editing every row by hand.
Engineering Case Study / Data Platform
Visual Hive: turning messy event spreadsheets into trusted AI-ready data.
Visual Hive needed a system that could ingest event data from CSV/XLSX files, map unpredictable client headers to a structured schema, clean and validate records, mutate Directus safely, and keep Qdrant synchronized for semantic search. The hard part was not uploading files. The hard part was preserving trust across every transformation.
01 / Problem
Enterprise event data is messy before it becomes “AI-ready”.
Event platforms receive data from many operators, exhibitors, speakers, sponsors, and registration systems. The same concept can arrive as “Session Title”, “Event Name”, “Talk”, or “Title”. Dates can be strings, Excel serials, or inconsistent timezone formats. Speaker-session relationships may be embedded as comma-separated names instead of stable IDs.
Data Problem
CSV/XLSX files contain inconsistent headers, encodings, delimiters, empty rows, malformed quotes, missing required fields, and relationship fields that need lookup logic.
AI Problem
The AI assistant is only useful if Directus and Qdrant agree. If relational data and vector payloads drift, semantic retrieval becomes stale or misleading.
02 / Decision Log
The architecture prioritizes correctness before automation.
For ingestion systems, being fast is not enough. A fast bad import creates corrupted downstream analytics and hallucinated assistant answers.
| Decision | Why | Rejected Alternative | Trade-off |
|---|---|---|---|
| Explicit pipeline stages | Extract, analyze, clean, validate, mutate, and sync are separately inspectable. | One large upload function. | More code, but much easier debugging. |
| Fail-fast validation | Bad CSV/XLSX should be rejected before it mutates Directus. | Best-effort silent cleanup. | Operators must fix bad files, but trust is preserved. |
| Directus as source of truth | Operators need CMS control, relational data, permissions, and auditability. | Qdrant-first or JSON-only storage. | Vector sync becomes a second responsibility. |
| Qdrant as derived index | Semantic search needs payload-filtered vectors, but vectors should be rebuildable from Directus. | Store only embeddings without relational backing. | Requires freshness tracking and sync observability. |
| Multi-instance Directus | Main instance owns users/events; event instances isolate event-specific operational data. | Single global schema for all events. | More deployment complexity, better event isolation. |
03 / Pipeline
The ingestion flow is a state machine, not a black box.
Each upload becomes an `ingestion_jobs` record with status, progress, current stage, error message, and JSON snapshots for each pipeline state. This makes a failed import diagnosable instead of mysterious.
04 / File Parsing
The parser handles real spreadsheet chaos, not only happy-path CSVs.
CSV Edge Cases
LF/CRLF line endings, comma/semicolon/tab delimiters, UTF-8 files, whitespace-only rows, trailing newlines, and malformed quote rejection.
XLSX Edge Cases
First-sheet parsing, null cell normalization, formula results, header auto-detection, merged-cell behavior, and numeric values converted into strings for validation.
Fail-Fast Rejection
Unmatched quotes, inconsistent column counts, and structurally invalid files are rejected before cleanup or mutation begins.
05 / Directus Topology
Main Directus manages control-plane state; event Directus manages event data.
The app separates platform-level concerns from event-level content. Main Directus stores events, users, API keys, and owner/viewer relations. Event Directus stores sessions, speakers, exhibitors, attendees, general info, supports, conversations, messages, traces, top questions, and ingestion jobs.
06 / Schema Mapping
Smart column matching converts client language into system language.
The ingestion system cannot require every client to use perfect internal field names. The analyzer maps headers to canonical fields through normalization, synonyms, and fuzzy matching. This is where automation creates leverage without giving up control.
Why Not Manual Mapping Only?
Manual mapping works for one import but collapses when operators repeat the same job across many events. Synonyms encode institutional memory so repeated imports get faster.
Why Not Fully Automatic Mutation?
Header confidence should influence workflow. Ambiguous mappings should be surfaced to operators instead of silently corrupting canonical fields.
07 / Mutation Strategy
Bulk writes must be explainable before they are executed.
The mutation layer compares cleaned rows with existing Directus data, calculates creates/updates/skips/deletes, and then applies changes. This makes bulk import behavior visible and avoids accidental overwrites.
- Extract rows. CSV/XLSX data becomes normalized row objects with headers and values.
- Analyze headers. Columns are mapped to canonical collection fields using synonyms and fuzzy matching.
- Clean values. Whitespace, array-like fields, booleans, dates, durations, and URLs are normalized.
- Validate schema. Required fields, types, relationship fields, and invalid rows are caught before mutation.
- Apply diff. Directus receives only the required create/update/delete operations.
08 / Vector Sync
Qdrant is a derived semantic index, not the source of truth.
After Directus mutation, records are converted into semantic documents and upserted into Qdrant with payload metadata. This allows the assistant to retrieve event-specific knowledge while keeping Directus authoritative.
semantic_document = collection_type + title + description + structured_fields
qdrant_payload = { event_id, collection, source_id, updated_at, directus_url }
vector_freshness = directus.updated_at <= qdrant.payload.updated_at
09 / Security
The security model protects credentials, event boundaries, and ingestion inputs.
This product handles external files, API keys, Directus tokens, Qdrant credentials, and event-level authorization. That means security is not a final layer; it shapes the data model.
10 / Observability
Trustworthy ingestion needs stage-level observability.
A generic uptime dashboard does not answer whether imports are reliable. The useful metrics are parse failures, mapping confidence, validation errors, mutation volume, vector lag, job duration, and operator review load.
11 / API Contract
The ingestion job contract exposes progress and failure without leaking implementation details.
POST /api/events/:eventId/ingestion/jobs
Content-Type: multipart/form-data
file: sessions.xlsx
collection: sessions
202 Accepted
{
"job_id": "job_01J...",
"status": "PENDING",
"progress": 0,
"current_stage": "Queued for extraction"
}
GET /api/events/:eventId/ingestion/jobs/:jobId
200 OK
{
"status": "VALIDATING",
"progress": 62,
"current_stage": "Checking required fields and relationship references",
"validation_state": {
"valid_rows": 486,
"invalid_rows": 14,
"errors": ["row 52: missing duration"]
}
}
12 / Demo Gallery
The demo should show data becoming safer at every step.
Replace the placeholders with Playwright captures from `/home/ikniz/Work/Coding/SvelteKit/data-ingestion`: upload screen, mapping review, validation result, Directus diff, Qdrant sync, and dashboard analytics.
13 / Video
The best video is not a feature tour. It is a data trust story.
Visual Hive demo video slotRecord: upload messy XLSX → map headers → inspect validation → mutate Directus → sync Qdrant → ask semantic question → inspect analytics.
14 / Lessons
The main lesson: ingestion is product engineering, not just ETL plumbing.
What Worked
Separating pipeline stages made the system explainable. Directus stayed authoritative, Qdrant remained rebuildable, and operators could reason about import failures instead of guessing from logs.
What I Would Improve
The next iteration should add a richer mapping confidence UI, per-stage duration charts, import rollback snapshots, and vector freshness alerts for stale Qdrant payloads.
Need to turn messy business data into a reliable AI-ready pipeline?
I design ingestion systems that preserve correctness before they automate everything.
Send me your data bottleneck mythonggg@gmail.com