# ThekedarAI - Database Schema Documentation

## Database Choice

**Recommended:** MongoDB (NoSQL) for MVP flexibility  
**Alternative:** PostgreSQL/MySQL (SQL) for strict data integrity

---

## MongoDB Collections

### 1. users Collection

Stores user account information.

```javascript
{
  _id: ObjectId("65b8f9c7d4e5a1b2c3d4e5f6"),
  name: "John Doe",
  email: "john@example.com",
  passwordHash: "$2b$10$abcdefghijklmnopqrstuvwxyz", // bcrypt hash
  authProvider: "email", // enum: "email" | "google" | "guest"
  photoURL: "https://storage.thekedarai.com/users/photo.jpg",
  isGuest: false,
  stats: {
    totalDesigns: 25,
    savedDesigns: 12,
    generationsUsed: 18,
    generationsLimit: 20 // -1 for unlimited
  },
  subscription: {
    tier: "free", // enum: "free" | "pro" | "enterprise"
    expiresAt: null
  },
  createdAt: ISODate("2026-01-15T10:30:00Z"),
  updatedAt: ISODate("2026-01-30T14:20:00Z"),
  lastLoginAt: ISODate("2026-01-30T09:15:00Z")
}
```

**Indexes:**

```javascript
db.users.createIndex({ email: 1 }, { unique: true });
db.users.createIndex({ authProvider: 1 });
db.users.createIndex({ createdAt: -1 });
```

**Validation Rules:**

- `email`: Required, unique, valid email format
- `passwordHash`: Required for email auth, nullable for guest/Google
- `authProvider`: Required, one of allowed values
- `stats.generationsLimit`: -1 for unlimited, positive integer for limit

---

### 2. tokens Collection

Stores authentication tokens for session management.

```javascript
{
  _id: ObjectId("65b8f9c7d4e5a1b2c3d4e5f7"),
  userId: ObjectId("65b8f9c7d4e5a1b2c3d4e5f6"),
  token: "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
  expiresAt: ISODate("2026-02-06T10:30:00Z"),
  createdAt: ISODate("2026-01-30T10:30:00Z"),
  lastUsedAt: ISODate("2026-01-30T14:20:00Z"),
  deviceInfo: {
    platform: "ios", // "ios" | "android" | "web"
    deviceId: "ABC123-DEF456",
    appVersion: "1.0.0"
  }
}
```

**Indexes:**

```javascript
db.tokens.createIndex({ token: 1 }, { unique: true });
db.tokens.createIndex({ userId: 1 });
db.tokens.createIndex({ expiresAt: 1 });
```

**TTL Index (Auto-delete expired tokens):**

```javascript
db.tokens.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 });
```

---

### 3. designs Collection

Stores all design generation data.

```javascript
{
  _id: ObjectId("65b8f9c7d4e5a1b2c3d4e5f8"),
  userId: ObjectId("65b8f9c7d4e5a1b2c3d4e5f6"),

  // Design parameters
  roomType: "living-room",
  style: "modern",
  budget: "standard",

  // Images
  originalImage: {
    url: "https://storage.thekedarai.com/uploads/original/abc123.jpg",
    path: "uploads/original/abc123.jpg",
    size: 2048576, // bytes
    width: 1920,
    height: 1080,
    format: "jpeg"
  },
  generatedImage: {
    url: "https://storage.thekedarai.com/uploads/generated/xyz789.jpg",
    path: "uploads/generated/xyz789.jpg",
    size: 1843200,
    width: 1920,
    height: 1080,
    format: "jpeg"
  },

  // Status tracking
  status: "completed", // "pending" | "processing" | "completed" | "failed"
  progress: 100, // 0-100
  errorMessage: null,

  // AI generation details
  aiProvider: "replicate",
  aiModel: "stability-ai/stable-diffusion-xl-refiner-1.0",
  prompt: "Professional interior design of a living-room in modern style...",
  negativePrompt: "blurry, low quality, distorted...",
  processingTime: 42, // seconds

  // Cost estimation
  estimatedCost: {
    min: 15000,
    max: 25000,
    currency: "INR",
    breakdown: {
      basePerSqm: 1200,
      roomSize: 12,
      budgetMultiplier: 1.0,
      roomMultiplier: 1.0,
      cityMultiplier: 1.0
    }
  },

  // Material suggestions
  suggestedMaterials: [
    "Vitrified Tiles",
    "Laminate Flooring",
    "Standard Paint",
    "Good Quality Fixtures"
  ],

  // Additional metadata
  metadata: {
    roomSize: 12, // sq.m
    city: "tier2",
    notes: "User custom notes"
  },

  // User actions
  saved: true,
  viewCount: 5,
  shareCount: 2,

  // Timestamps
  createdAt: ISODate("2026-01-30T10:30:00Z"),
  updatedAt: ISODate("2026-01-30T10:31:30Z"),
  completedAt: ISODate("2026-01-30T10:31:30Z")
}
```

**Indexes:**

```javascript
db.designs.createIndex({ userId: 1, createdAt: -1 });
db.designs.createIndex({ status: 1 });
db.designs.createIndex({ roomType: 1 });
db.designs.createIndex({ style: 1 });
db.designs.createIndex({ saved: 1, userId: 1 });
db.designs.createIndex({ createdAt: -1 });

// Compound index for filtered queries
db.designs.createIndex({
  userId: 1,
  saved: 1,
  status: 1,
  createdAt: -1,
});
```

---

### 4. analytics Collection (Optional - MVP Phase 2)

Track user behavior and app usage.

```javascript
{
  _id: ObjectId("65b8f9c7d4e5a1b2c3d4e5f9"),
  userId: ObjectId("65b8f9c7d4e5a1b2c3d4e5f6"),
  eventType: "design_generated", // "signup" | "login" | "design_generated" | "design_saved" | etc.
  eventData: {
    designId: ObjectId("65b8f9c7d4e5a1b2c3d4e5f8"),
    roomType: "living-room",
    style: "modern",
    processingTime: 42
  },
  deviceInfo: {
    platform: "ios",
    osVersion: "17.2",
    appVersion: "1.0.0"
  },
  timestamp: ISODate("2026-01-30T10:31:30Z")
}
```

**Indexes:**

```javascript
db.analytics.createIndex({ userId: 1, timestamp: -1 });
db.analytics.createIndex({ eventType: 1, timestamp: -1 });
db.analytics.createIndex({ timestamp: -1 });
```

**TTL Index (Auto-delete after 90 days):**

```javascript
db.analytics.createIndex(
  { timestamp: 1 },
  { expireAfterSeconds: 7776000 }, // 90 days
);
```

---

### 5. feedback Collection (Optional - MVP Phase 2)

Store user feedback and ratings.

```javascript
{
  _id: ObjectId("65b8f9c7d4e5a1b2c3d4e5fa"),
  userId: ObjectId("65b8f9c7d4e5a1b2c3d4e5f6"),
  designId: ObjectId("65b8f9c7d4e5a1b2c3d4e5f8"),
  rating: 4, // 1-5 stars
  comment: "Great AI quality, cost estimate was accurate!",
  feedbackType: "design_quality", // "design_quality" | "app_usability" | "cost_accuracy" | "general"
  status: "pending", // "pending" | "reviewed" | "resolved"
  createdAt: ISODate("2026-01-30T11:00:00Z")
}
```

**Indexes:**

```javascript
db.feedback.createIndex({ userId: 1, createdAt: -1 });
db.feedback.createIndex({ designId: 1 });
db.feedback.createIndex({ status: 1, createdAt: -1 });
```

---

## MySQL/PostgreSQL Schema (Alternative)

### SQL Schema

```sql
-- Users table
CREATE TABLE users (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),
  auth_provider VARCHAR(50) NOT NULL DEFAULT 'email',
  photo_url VARCHAR(512),
  is_guest BOOLEAN DEFAULT FALSE,

  -- Stats (stored as JSON in MySQL 5.7+)
  stats JSON,

  -- Subscription
  subscription_tier VARCHAR(50) DEFAULT 'free',
  subscription_expires_at TIMESTAMP NULL,

  -- Timestamps
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_login_at TIMESTAMP NULL,

  INDEX idx_email (email),
  INDEX idx_auth_provider (auth_provider),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tokens table
CREATE TABLE tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  token VARCHAR(255) UNIQUE NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_used_at TIMESTAMP NULL,

  -- Device info
  platform VARCHAR(20),
  device_id VARCHAR(100),
  app_version VARCHAR(20),

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_token (token),
  INDEX idx_user_id (user_id),
  INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Designs table
CREATE TABLE designs (
  id VARCHAR(36) PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,

  -- Design parameters
  room_type VARCHAR(100),
  style VARCHAR(100),
  budget VARCHAR(50),

  -- Images (stored as JSON)
  original_image JSON,
  generated_image JSON,

  -- Status
  status VARCHAR(50) DEFAULT 'pending',
  progress INT DEFAULT 0,
  error_message TEXT,

  -- AI details
  ai_provider VARCHAR(100),
  ai_model VARCHAR(255),
  prompt TEXT,
  negative_prompt TEXT,
  processing_time INT,

  -- Cost (stored as JSON)
  estimated_cost JSON,

  -- Materials (stored as JSON array)
  suggested_materials JSON,

  -- Metadata (stored as JSON)
  metadata JSON,

  -- User actions
  saved BOOLEAN DEFAULT FALSE,
  view_count INT DEFAULT 0,
  share_count INT DEFAULT 0,

  -- Timestamps
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_created (user_id, created_at),
  INDEX idx_status (status),
  INDEX idx_room_type (room_type),
  INDEX idx_style (style),
  INDEX idx_saved (saved, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Analytics table (optional)
CREATE TABLE analytics (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id VARCHAR(36),
  event_type VARCHAR(100) NOT NULL,
  event_data JSON,
  device_info JSON,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_user_timestamp (user_id, timestamp),
  INDEX idx_event_type (event_type, timestamp),
  INDEX idx_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Feedback table (optional)
CREATE TABLE feedback (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id VARCHAR(36),
  design_id VARCHAR(36),
  rating INT CHECK (rating >= 1 AND rating <= 5),
  comment TEXT,
  feedback_type VARCHAR(100),
  status VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (design_id) REFERENCES designs(id) ON DELETE SET NULL,
  INDEX idx_user (user_id, created_at),
  INDEX idx_design (design_id),
  INDEX idx_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## Data Migration Scripts

### MongoDB to SQL Migration

```javascript
// Example: Export MongoDB designs to SQL
db.designs.find().forEach((design) => {
  const sql = `
    INSERT INTO designs (id, user_id, room_type, style, budget, status, created_at)
    VALUES (
      '${design._id}',
      '${design.userId}',
      '${design.roomType}',
      '${design.style}',
      '${design.budget}',
      '${design.status}',
      '${design.createdAt.toISOString()}'
    );
  `;
  print(sql);
});
```

---

## Backup Strategy

### MongoDB Backup

```bash
# Daily backup
mongodump --uri="mongodb://localhost:27017/thekedarai" --out=/backups/$(date +%Y%m%d)

# Restore
mongorestore --uri="mongodb://localhost:27017/thekedarai" /backups/20260130
```

### MySQL Backup

```bash
# Daily backup
mysqldump -u root -p thekedarai > backup_$(date +%Y%m%d).sql

# Restore
mysql -u root -p thekedarai < backup_20260130.sql
```

---

## Data Retention Policy

| Data Type     | Retention Period | Action After Period     |
| ------------- | ---------------- | ----------------------- |
| User accounts | Indefinite       | User-requested deletion |
| Designs       | 1 year inactive  | Archive or delete       |
| Tokens        | 7 days expired   | Auto-delete (TTL index) |
| Analytics     | 90 days          | Auto-delete (TTL index) |
| Feedback      | 1 year           | Archive                 |
| Error logs    | 30 days          | Auto-delete             |

---

**Database Version:** v1.0  
**Last Updated:** January 2026  
**Status:** ✅ Schema Ready for Implementation