LLD

1. Project Layout

The project follows a modular and layered architecture designed for scalability, maintainability, and separation of concerns.

The backend is implemented using FastAPI, with distinct folders for models, schemas, services, routes, utilities, and background workers.

Each layer has a clear responsibility:

Models Layer: Contains ORM classes mapping database tables.
Schemas Layer: Defines Pydantic models for request validation and API responses.
Service Layer: Implements business logic (NLP, chat assistant).
Routes Layer: Defines API endpoints exposed via FastAPI.
Utility Layer: Includes reusable components like embedding generation, configuration, logging, and security helpers.
Worker Layer: Handles background jobs such as scheduled chat message fetching and NLP processing.

This modular layout ensures that each component can evolve independently — for example, NLP services or integrations can scale separately from the main API.

The frontend is built using React and consumes REST APIs exposed by the FastAPI backend. It serves as the user interface for screens, chatbot interactions, and employee views.

📁 Repository Structure

/repo
├─ /api                     # FastAPI app
│  ├─ main.py
│  ├─ routers/
│  │   ├─ auth.py
│  │   ├─ chat.py
│  │   ├─ employees.py
│  ├─ models/                # ORM classes
│  └─ services/              # Business logic (NLP invocations, DB helpers)
├─ /workers
│  ├─ telegram_worker.py
│  ├─ googlechat_worker.py
│  ├─ scheduler.py
│  └─ nlp_worker.py
├─ /nlp
│  └─ embeddings.py
├─ /ragsvc
│  └─ rag.py                 # Retrieval + prompt assembly + LLM call
├─ /frontend                 # React app
│  ├── src/
│  │   ├── components/       # Reusable UI components (cards, modals, charts)
│  │   ├── pages/            # Dashboard and chatbot screens
│  │   ├── services/         # API service calls
│  │   ├── hooks/            # Custom React hooks
│  │   ├── contexts/         # Auth and user management
│  │   ├── assets/           # Icons, images, and theme files
│  │   └── utils/            # Helper functions
│  ├── public/               # Static assets
│  ├── package.json
│  ├── vite.config.js or next.config.js
│  └── README.md
├─ /db
│  └─ migrations.sql
├─ docker-compose.yml
├─ Dockerfile.api
└─ README.md

2. Database Schema — SQL DDL

The database schema defines all core entities and their relationships for the MVP.
It is implemented in PostgreSQL and pgvector for storing text embeddings used in semantic search.

The schema captures four main domains:

Employee Domain

Tables: employees, employee_tag
Manages employee profiles, departmental details, and tag associations.
Defines users of the system (employees and admins) and establishes relationships between employees and tags for access control and contextual filtering.

Message Domain

Tables: messages
Stores sanitized chat messages fetched from configured data sources.
Each message is tied to an employee (sender) and a data source (Telegram or Google Chat), and includes an embedding vector for semantic search.

Tag Domain

Tables: tags
Defines organizational tags or categories such as projects, teams, or topics.
Tags serve as contextual identifiers used to categorize employees, data sources, and messages.

Data Source Domain

Tables: data_sources
Represents external chat integrations (Google Chat or Telegram groups).
Each data source is linked to a specific tag and acts as an entry point for message ingestion.
Foreign key relationships use ON DELETE CASCADE for integrity.

Example: Deleting a project automatically removes related skills or employees linked via projects_employee.

🧑‍💼 employees

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    external_id JSONB,
    name VARCHAR(150) NOT NULL,
    role VARCHAR(50) NOT NULL,          -- e.g., 'Java Developer'
    dept VARCHAR(100),
    tenure VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    is_admin BOOLEAN DEFAULT FALSE
);

Notes:

is_admin defines access privileges (Employee/Admin).
is_active allows temporary deactivation without deletion.

💬 Messages

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES employees(id) ON DELETE SET NULL,
    data_source_id INT REFERENCES data_sources(id) ON DELETE CASCADE,
    text TEXT NOT NULL,
    embedding VECTOR(768),              -- semantic vector for AI search
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Notes:

embedding uses the pgvector extension for similarity-based AI search.
employee_id may be nullable if sender info is anonymized or unavailable.

🏷️ Tags

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    tag_name VARCHAR(100) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
);

Notes:

Tags are assigned to employees and data sources to define context (e.g., “Project Alpha”).
Uniqueness ensures no duplicate project or topic names.

🔗 Employee–Tag Relationship

CREATE TABLE employee_tag (
    id SERIAL PRIMARY KEY,
    employee_id INT REFERENCES employees(id) ON DELETE CASCADE,
    tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
    is_active BOOLEAN DEFAULT TRUE
);

Notes:

Defines a many-to-many relationship between employees and tags.
is_active allows toggling tag assignment without deleting the record.

🌐 Data Sources

CREATE TABLE data_sources (
    id SERIAL PRIMARY KEY,
    platform VARCHAR(50) NOT NULL,      -- e.g., 'telegram', 'google_chat'
    external_id VARCHAR(200) NOT NULL,  -- ID from external platform
    name VARCHAR(150) NOT NULL,
    tag_id INT REFERENCES tags(id) ON DELETE SET NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

Notes:

Links chat groups/spaces to organizational tags.
When a tag is deleted, tag_id becomes NULL but the data source remains for reference.