# Database Schema & Data Models Specification
## Personal & Agency Portfolio Platform: Er. Sujeet Pandit & TechoMaster

---

## 1. Overview & Data Architecture

The platform's data layer is designed to support both **Relational SQL (PostgreSQL / Supabase)** and **NoSQL Document (MongoDB Atlas)** persistence models. It manages client inquiries, project case studies, client testimonials, skill matrix records, quote estimations, and visitor telemetry.

```mermaid
erDiagram
    LEAD_INQUIRY ||--o{ QUOTE_ESTIMATE : contains
    LEAD_INQUIRY ||--o{ INQUIRY_STATUS_LOG : tracks
    PROJECT ||--|{ PROJECT_TAG : categorizes
    PROJECT ||--o{ PROJECT_METRIC : reports
    TESTIMONIAL }o--|| CLIENT_COMPANY : belongs_to
    SKILL ||--|{ SKILL_CATEGORY : classified_in
    ANALYTICS_EVENT }o--|| VISITOR_SESSION : logs

    LEAD_INQUIRY {
        uuid id PK
        string full_name
        string email
        string phone
        string subject
        string service_type
        text message
        string status
        jsonb metadata
        timestamp created_at
    }

    QUOTE_ESTIMATE {
        uuid id PK
        uuid inquiry_id FK
        string project_type
        string timeline_urgency
        jsonb selected_addons
        numeric estimated_cost_min
        numeric estimated_cost_max
        string currency
        timestamp created_at
    }

    PROJECT {
        uuid id PK
        string slug UK
        string title
        string subtitle
        string category
        text description
        text client_name
        string live_url
        string github_url
        string thumbnail_url
        boolean is_featured
        integer sort_order
        timestamp created_at
    }

    TESTIMONIAL {
        uuid id PK
        string author_name
        string author_position
        string company_name
        string company_url
        text review_text
        integer rating
        string avatar_url
        boolean is_verified
        boolean is_published
        timestamp created_at
    }

    SKILL {
        uuid id PK
        string name
        string category
        integer proficiency_pct
        integer years_experience
        string icon_svg
        boolean is_featured
        integer sort_order
    }

    ANALYTICS_EVENT {
        uuid id PK
        string event_name
        string event_category
        string page_path
        string user_agent
        string country_code
        jsonb event_payload
        timestamp created_at
    }
```

---

## 2. PostgreSQL DDL Specification

```sql
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Table: lead_inquiries
CREATE TABLE lead_inquiries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(30),
    subject VARCHAR(200) NOT NULL,
    service_type VARCHAR(80) DEFAULT 'General Inquiry',
    message TEXT NOT NULL,
    source VARCHAR(50) DEFAULT 'website_contact_form',
    status VARCHAR(30) DEFAULT 'NEW' CHECK (status IN ('NEW', 'CONTACTED', 'QUALIFIED', 'PROPOSAL_SENT', 'CLOSED_WON', 'CLOSED_LOST', 'SPAM')),
    metadata JSONB DEFAULT '{}'::jsonb,
    ip_address INET,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_lead_inquiries_email ON lead_inquiries(email);
CREATE INDEX idx_lead_inquiries_status ON lead_inquiries(status);
CREATE INDEX idx_lead_inquiries_created_at ON lead_inquiries(created_at DESC);

-- Table: quote_estimates
CREATE TABLE quote_estimates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    inquiry_id UUID REFERENCES lead_inquiries(id) ON DELETE SET NULL,
    project_type VARCHAR(80) NOT NULL,
    timeline_urgency VARCHAR(50) NOT NULL,
    selected_addons JSONB DEFAULT '[]'::jsonb,
    estimated_cost_min NUMERIC(10, 2) NOT NULL,
    estimated_cost_max NUMERIC(10, 2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USD',
    session_id VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_quote_estimates_inquiry ON quote_estimates(inquiry_id);

-- Table: projects
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    slug VARCHAR(120) UNIQUE NOT NULL,
    title VARCHAR(150) NOT NULL,
    subtitle VARCHAR(255),
    category VARCHAR(80) NOT NULL CHECK (category IN ('AI_ML', 'WEB_DEV', 'MOBILE_APP', 'CLOUD_DEVOPS', 'ENTERPRISE')),
    description TEXT NOT NULL,
    client_name VARCHAR(150),
    live_url VARCHAR(500),
    github_url VARCHAR(500),
    thumbnail_url VARCHAR(500),
    tech_stack JSONB DEFAULT '[]'::jsonb,
    metrics JSONB DEFAULT '[]'::jsonb,
    is_featured BOOLEAN DEFAULT false,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_projects_category ON projects(category);
CREATE INDEX idx_projects_is_featured ON projects(is_featured);

-- Table: testimonials
CREATE TABLE testimonials (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    author_name VARCHAR(120) NOT NULL,
    author_position VARCHAR(120) NOT NULL,
    company_name VARCHAR(150) NOT NULL,
    company_url VARCHAR(500),
    review_text TEXT NOT NULL,
    rating SMALLINT DEFAULT 5 CHECK (rating >= 1 AND rating <= 5),
    avatar_url VARCHAR(500),
    is_verified BOOLEAN DEFAULT true,
    is_published BOOLEAN DEFAULT true,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_testimonials_published ON testimonials(is_published, sort_order ASC);

-- Table: skills
CREATE TABLE skills (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    category VARCHAR(60) NOT NULL CHECK (category IN ('FRONTEND', 'BACKEND', 'AI_ML', 'CLOUD_DEVOPS', 'DATABASE', 'TOOLS')),
    proficiency_pct SMALLINT NOT NULL CHECK (proficiency_pct BETWEEN 1 AND 100),
    years_experience NUMERIC(3, 1) NOT NULL,
    icon_name VARCHAR(60),
    is_featured BOOLEAN DEFAULT true,
    sort_order INT DEFAULT 0
);

CREATE INDEX idx_skills_category ON skills(category, sort_order ASC);

-- Table: analytics_events
CREATE TABLE analytics_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_name VARCHAR(80) NOT NULL,
    event_category VARCHAR(60) NOT NULL,
    page_path VARCHAR(255) DEFAULT '/',
    user_agent TEXT,
    referrer VARCHAR(500),
    event_payload JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_analytics_events_name_date ON analytics_events(event_name, created_at DESC);
```

---

## 3. MongoDB Document Models (Mongoose Schema Reference)

```javascript
const mongoose = require('mongoose');
const { Schema } = mongoose;

// Lead Inquiry Schema
const LeadInquirySchema = new Schema({
    fullName: { type: String, required: true, trim: true, maxlength: 120 },
    email: { type: String, required: true, trim: true, lowercase: true, index: true },
    phone: { type: String, trim: true },
    subject: { type: String, required: true, maxlength: 200 },
    serviceType: { type: String, default: 'General Inquiry' },
    message: { type: String, required: true },
    source: { type: String, default: 'website_contact_form' },
    status: {
        type: String,
        enum: ['NEW', 'CONTACTED', 'QUALIFIED', 'PROPOSAL_SENT', 'CLOSED_WON', 'CLOSED_LOST', 'SPAM'],
        default: 'NEW',
        index: true
    },
    quoteEstimate: {
        projectType: String,
        timeline: String,
        addons: [String],
        costMin: Number,
        costMax: Number,
        currency: { type: String, default: 'USD' }
    },
    metadata: { type: Schema.Types.Mixed, default: {} }
}, { timestamps: true });

// Project Schema
const ProjectSchema = new Schema({
    slug: { type: String, required: true, unique: true },
    title: { type: String, required: true },
    subtitle: String,
    category: {
        type: String,
        enum: ['AI_ML', 'WEB_DEV', 'MOBILE_APP', 'CLOUD_DEVOPS', 'ENTERPRISE'],
        required: true,
        index: true
    },
    description: { type: String, required: true },
    clientName: String,
    liveUrl: String,
    githubUrl: String,
    thumbnailUrl: String,
    techStack: [{ name: String, category: String, icon: String }],
    metrics: [{ label: String, value: String }],
    isFeatured: { type: Boolean, default: false, index: true },
    sortOrder: { type: Number, default: 0 }
}, { timestamps: true });

module.exports = {
    LeadInquiry: mongoose.model('LeadInquiry', LeadInquirySchema),
    Project: mongoose.model('Project', ProjectSchema)
};
```

---

## 4. Data Retention, Backup & Privacy Compliance

| Data Entity | Retention Period | Data Classification | Privacy & Compliance (GDPR / DPDP India) |
|---|---|---|---|
| **Lead Inquiries** | 3 Years (or until deletion requested) | PII (Confidential) | Right to Access & Erasure supported. Email hashed for unsubscribe registry. |
| **Quote Calculations** | 1 Year | Non-PII (Commercial) | Anonymized aggregated data used for market estimation optimization. |
| **Visitor Telemetry** | 90 Days | Pseudonymous | IP addresses truncated and zero third-party tracker sharing. |
| **System Backups** | Daily snapshots, 30-day retention | Encrypted at rest (AES-256) | Multi-region backup replication. |