VC-HB3-Accelerator/DLE
1
1
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases 1 Wiki Activity
Files
main
DLE/docs.en/back-docs/tables-system.md

7.3 KiB
Raw Permalink Blame History

English | Русский

DLE Spreadsheet (Tables) System

Internal working document

Table of Contents

  1. System overview
  2. Database architecture
  3. Field types
  4. Features
  5. Table relations
  6. AI (RAG) integration
  7. API reference
  8. Examples
  9. Security

System overview

What it is

The tables system in DLE is a full database with a GUI — similar to Notion Database or Airtable — built into the app.

Features

  • 6 field types (text, number, relation, lookup, multiselect, multiselect-relation)
  • Relations between tables (1:1, 1:N, N:N)
  • Lookup and data rollup
  • Filtering and sorting
  • Real-time updates (WebSocket)
  • AI integration (RAG search)
  • AES-256 encryption for data
  • Placeholders for API access
  • Cascade delete, bulk operations

vs Excel/Sheets

Excel/Sheets DLE Tables
Data typing Weak Strict (6 types)
Relations No Yes (relation, lookup)
AI search No Yes (RAG, vector)
Real-time Partial Full (WebSocket)
Encryption No AES-256
API Limited Full REST

Database architecture

Main tables (PostgreSQL)

  • user_tables: id, name_encrypted, description_encrypted, is_rag_source_id, created_at, updated_at
  • user_columns: id, table_id, name_encrypted, type_encrypted, placeholder_encrypted, placeholder, options (JSONB), order, created_at, updated_at
  • user_rows: id, table_id, order, created_at, updated_at
  • user_cell_values: id, row_id, column_id, value_encrypted, created_at, updated_at, UNIQUE(row_id, column_id)
  • user_table_relations: id, from_row_id, column_id, to_table_id, to_row_id, created_at, updated_at

Cascade: delete table → columns, rows, cell values, relations. Indexes on relation columns for performance.

Field types

  1. Text — plain text
  2. Number — numeric
  3. Multiselect — multiple choices from a list (options.choices)
  4. Multiselect-relation — multiple rows from another table (options: relatedTableId, relatedColumnId); stored in user_table_relations
  5. Relation — single row in another table (1:1 or N:1); one row in user_table_relations
  6. Lookup — value pulled from related table (options: relatedTableId, relatedColumnId, lookupColumnId)

Features

CRUD

  • Create table: POST /tables (name, description, isRagSourceId); name/description encrypted
  • Add column: POST /tables/:id/columns (name, type, order, purpose, options)
  • Add row: POST /tables/:id/rows (auto index for RAG if applicable)
  • Save cell: POST /tables/cell (row_id, column_id, value); upsert; update vector store
  • Delete row: DELETE /tables/row/:rowId (rebuild vector store)
  • Delete column: DELETE /tables/column/:columnId (cascade relations and cell values)
  • Delete table: DELETE /tables/:id (admin; cascade all)

Filtering

  • GET /tables/:id/rows?product=… — by product
  • ?tags=… — by user tags
  • ?relation_<columnId>=<to_row_ids> — by relation
  • ?multiselect_<columnId>=… — by multiselect

Placeholders

Auto-generated from column name (transliteration, uniqueness). Used for API: GET /tables/:id/data?fields=placeholder1,placeholder2.

Order

PATCH /tables/:id/rows/order with array of { rowId, order }.

WebSocket

Events: table-update, table-relations-update, tags-update. Frontend subscribes and reloads when needed.

Bulk

Select rows (checkboxes); bulk delete or other actions; after delete, vector store rebuilt as needed.

Table relations

  • N:1 (Relation): one column links to one row in another table (e.g. Task → Project).
  • N:N (Multiselect-relation): multiple links (e.g. Contacts → Tags).
  • Lookup: show a field from the related row (e.g. Order → Product → Product.price).

API: GET/POST/DELETE on /tables/:tableId/row/:rowId/relations (column_id, to_table_id, to_row_id or to_row_ids for multiselect).

AI (RAG) integration

  • Tables (or selected tables) can be RAG sources. Columns with purpose “question” and “answer” are used for vector indexing.
  • On row create/update: rows with question/answer are upserted into vector store (FAISS). On row delete: rebuild for that table.
  • Rebuild index: POST /tables/:id/rebuild-index (admin). Reads question/answer columns and rebuilds vector index.
  • RAG flow: User asks → vector search in table(s) → top_k results with score and metadata (answer, product, userTags, priority) → LLM gets context and generates reply. Filter by product/tags in metadata if needed.

API reference (summary)

  • Tables: GET/POST /tables, GET/PATCH/DELETE /tables/:id
  • Columns: POST /tables/:id/columns, PATCH/DELETE /tables/column/:columnId
  • Rows: POST /tables/:id/rows, DELETE /tables/row/:rowId, PATCH /tables/:id/rows/order
  • Cell: POST /tables/cell (upsert)
  • Filtered rows: GET /tables/:id/rows?…
  • RAG: POST /tables/:id/rebuild-index
  • Relations: GET/POST/DELETE …/row/:rowId/relations
  • Placeholders: GET /tables/:id/placeholders, GET /tables/placeholders/all

Examples

FAQ for AI

Create table with columns: Question (purpose: question), Answer (purpose: answer), Product (multiselect, purpose: product), Tags (multiselect, purpose: userTags). Add rows; enable as RAG source. AI will search by question and return answer; filter by product/tags.

CRM

Tables: Companies (name, website, industry), Contacts (name, email, company relation, company website lookup). Relation from Contact to Company; lookup “company website” from Company via relation.

Tasks

Tables: Projects (name, status), Tasks (name, project relation, priority, status). Filter tasks by project: GET .../rows?relation_<projectColumnId>=<projectRowId>.

Security

  • Encryption: AES-256 for name_encrypted, description_encrypted, value_encrypted, placeholder_encrypted. placeholder and options can be plain for indexing/performance.
  • Access: View for authenticated users; edit for users with edit rights; delete and rebuild-index for admins (e.g. userAccessLevel.hasAccess). Token balance checked for access level.
  • SQL: Parameterized queries only. Validate types, existence, uniqueness (e.g. placeholder). Cascade deletes to avoid orphaned data. Optional rate limiting on tables routes.

Performance

  • Parallel queries for table meta, columns, rows, cell values where possible. Indexes on user_table_relations. UNIQUE(row_id, column_id) for fast upsert. WebSocket instead of polling. Optional server-side cache for frequent GET /tables/:id.

Typical: GET /tables 50–100 ms, GET /tables/:id 150–300 ms, POST cell 100–200 ms, rebuild-index 1–5 s. Tables up to ~10k rows fine; larger may need pagination/lazy load.

Limits and future

Current: no server-side pagination, no formulas, no grouping, no change history, limited server-side sort. Possible: pagination, sort params, formula fields, audit table, export/import, table templates.

© 2024-2026 Alexander Viktorovich Tarabanov. All rights reserved.

Version: 1.0.0 | Date: February 28, 2026 | Status: Internal