chiral-db 1.0.0

A session-scoped self-adaptive database framework for logical object-oriented data management.

Assignment 3: Logical Dashboard & Transactional Validation

Course: CS 432 – Databases (Semester II, 2025 - 2026)
Instructor: Dr. Yogesh K. Meena

Team Members:

• Deep Buha (24110082) - [e-mail]
• Devansh Lodha (23110091) - [e-mail]
• Laxmidhar Panda (24110185) - [e-mail]
• Yuvraj Rathod (24110293) - [e-mail]
• Viraj Solanki (24110348) - [e-mail]

---

1. Overview

This repository implements the final phase of the session-scoped, self-adaptive database framework. It introduces a purely logical React-based dashboard interface and a robust Transaction Coordination Layer.

The framework autonomously translates abstract JSON requests into complex PostgreSQL LEFT JOIN and JSONB operations. By fully unifying the relational and document-store models into a single PostgreSQL engine, the system eliminates the need for Two-Phase Commit (2PC) coordinators while strictly guaranteeing ACID properties across normalized child tables and schema-less documents.

---

2. Steps to Execute the Code

Prerequisites

• Docker Engine (Must be running)
• Just (Command runner): brew install just / apt install just / choco install just
• uv (Python package manager): curl -LsSf https://astral.sh/uv/install.sh | sh

Setup & Configuration

Clone the repository and set up the environment variables:

cp .env.example .env

Run the Data Population Pipeline

Initialize the database, start the FastAPI engine, and stream 1,000 highly nested records through the simulation server to trigger autonomous schema inference and DDL materialization.

just demo2

Run the Logical Dashboard

Build and launch the React/Vite dashboard container. The dashboard strictly enforces logical boundaries, hiding all SQL tables and physical JSONB columns.

just webapp

Access the dashboard at: http://localhost:5173

Run the ACID Validation Suite

Execute the automated testing suite validating Atomicity, Consistency, Isolation, and Durability across multi-table insertions and concurrent JSONB mutations.

just test-acid

Teardown

Terminate background processes and destroy database containers/volumes.

just stop
just down

---

3. Project Structure

chiral-db/
├── demo2.py                          # E2E pipeline trigger (Ingestion -> Analysis -> DDL)
├── Justfile                          # Automation commands
├── src/chiral/
│   ├── main.py                       # FastAPI entry point & Logical Schema APIs
│   ├── core/
│   │   └── query_service.py          # AST Compiler, JSONB abstraction hooks, Logical Reconstruction
│   ├── db/
│   │   ├── query_builder.py          # jsonb_set compilation and parameterized JOIN generation
│   │   └── schema.py                 # DDL Materialization for dynamic parent and child tables
│   └── worker/
│       └── migrator.py               # Data splitting and zero-casting SQL/JSONB inserts
├── tests/
│   └── test_acid_properties.py       # Automated ACID validation experiments
└── webapp/dashboard/
    ├── src/
    │   ├── App.tsx                   # Main SPA shell
    │   ├── CrudPanel.tsx             # Key-Value update builder and Logical CRUD executor
    │   └── api.ts                    # Backend integration and session telemetry fetcher
    └── Dockerfile                    # Containerization for the frontend

---

4. Key Architectural Implementations

• Strict UI Abstraction: The React dashboard relies exclusively on the /schema/logical/{session_id} API. It autonomously scrubs physical schema markers. Physical tables (chiral_data_comments) and document-store identifiers (overflow_data) are completely invisible to the client.
• Logical Data Reconstruction: Flat relational rows generated by dynamic LEFT JOIN queries cause a Cartesian explosion. The query_service intercepts raw SQLAlchemy outputs, hashes parent tuples, and safely merges duplicated rows into unified, object-oriented JSON arrays in-memory.
• Nested Transaction Atomicity: For synchronous multi-table operations (e.g., inserting a document with nested arrays), the framework relies on sql_session.begin_nested(). If dynamic child-table insertions fail, the parent SQL record is entirely rolled back to prevent orphaned data.
• JSONB Isolation (No Lost Updates): Concurrent logical updates targeting distinct fields within the same schema-less document are serialized safely. The engine compiles these as atomic jsonb_set(COALESCE(...)) commands, utilizing engine-level row locks to eliminate read-modify-write race conditions.