Mapping Spreadsheet Tabs in UI to DuckDB Tables

[vcanaran]

Here's a formatted Git ticket template based on your provided details:

---

Ticket Title: Mapping Spreadsheet Tabs in UI to DuckDB Tables

---

Problem Statement:

We need a way to map spreadsheet tabs in the UI to DuckDB tables within a single DuckDB file.

• This ticket serves as a format example for future tickets.
• Tickets should be small and atomic, with parent tickets mapping to specific child tickets for detailed tasks.
• This is a parent ticket for mapping, and there will be child tickets for each specific part of the implementation.

---

Background:

• Each DuckDB file is a database associated with a single workspace (Kubernetes pod).
• A DuckDB file contains multiple tables.
• The entire DuckDB file is linked to one workspace, which has a single broadcast (room) web socket. Users subscribed to the workspace receive data change notifications across all tables within the DuckDB file.
• Note: One room per workspace is sufficient; you don’t need one room per table.
• Ensure that your solution is achievable within the first week of October.
• Manage dependencies, validation, and error handling in the code.
• Follow proper linting and formatting standards.
• Use Visual Studio Code for development.
• Update your design to reflect this background.

---

Technology Stack:

1. JavaScript/TypeScript
2. Svelte
3. SvelteKit with Node.js and Socket.IO
4. AWS Elastic Cache (Redis clone) for both cache and pub/sub
5. DuckDB
6. Kubernetes on AWS (EKS)
7. Prometheus for monitoring
8. Grafana for logging, monitoring, and tracing

---

Environment:

• Use your own AWS Free Tier, and make compromises to minimize cloud spend.
• Track your AWS spend, and if necessary, get credits in advance from Liquid Analytics. You can request a separate sandbox project for this from prem@liquidanalytics.com.
• Be mindful of managing AWS costs effectively.

---

Approach:

1. Workspace UI Control:
   • Implement a UI control for the workspace that includes a set of tabs.
2. Tabs:
   • Each tab will correspond to a DuckDB table in the file associated with the workspace.
3. DataTable UI Control:
   • Implement a Svelte-based table view control (referred to as DataTable).
   • Each tab should instantiate this control, mapping it to a specific DuckDB table.

---

Design Steps:

1. Define the data flow for mapping tabs to DuckDB tables.
2. Design the Workspace UI control with dynamic tab generation.
3. Implement table view controls that communicate with the DuckDB file via web socket.
4. Ensure robust error handling, validation, and monitoring using Prometheus and Grafana.
5. Optimize for minimal AWS resource usage to stay within the Free Tier or approved budget.

---

This template can be adjusted based on the specific task (feature or defect), but it ensures clarity, small tasks, and proper ticketing practices.

[arunramachandran15]

Subtask split for the above design.

1. Implement duckdb manager using python. (Schema design and flow included)
2. Implement nodejs/socketio server for receiving/pushing realtime updates to/from svelte application and update the duckdb files via duckdb manager
3. Implement Svelte application for Sheets like UI and integration with nodejs server
4. Orchestration using K8s/ docker, ingress-nginx for routing
5. Deploy to EKS, aws account setup, service accounts, RBAC
6. Configure monitoring using prometheus and grafana operators