Database Systems: Relational SQL & Document NoSQL

-- Enable native cryptographic UUID generation extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 1. Create Organizations Table (Parent)
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL,
  tier VARCHAR(20) DEFAULT 'starter' CHECK (tier IN ('starter', 'pro', 'enterprise')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Create Users Table with Referential Integrity & Constraints
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(60) NOT NULL,
  role VARCHAR(30) DEFAULT 'engineer',
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 1. INSERT: Create rows with RETURNING clause (avoids secondary roundtrip query!)
INSERT INTO users (org_id, email, password_hash, role)
VALUES 
  ('a1b2c3d4-0000-0000-0000-000000000001', 'shivan@devascent.me', '$2b$12$...', 'admin'),
  ('a1b2c3d4-0000-0000-0000-000000000001', 'alex@devascent.me', '$2b$12$...', 'engineer')
RETURNING id, email, created_at;

-- 2. SELECT: Filtering with WHERE, range matching, and sorting
SELECT id, email, role, created_at
FROM users
WHERE is_active = TRUE
  AND role IN ('admin', 'lead')
  AND email ILIKE '%@devascent.me' -- Case-insensitive pattern match
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- 3. UPDATE: Safe atomic modification (MANDATORY: Always supply WHERE!)
UPDATE users
SET role = 'principal', updated_at = NOW()
WHERE email = 'shivan@devascent.me';

-- 4. DELETE: Removing records
DELETE FROM users
WHERE is_active = FALSE AND created_at < NOW() - INTERVAL '90 days';

-- Aggregate query: List organizations with total member count and active status
SELECT 
  o.id AS org_id,
  o.name AS organization_name,
  o.tier,
  COUNT(u.id) AS total_members,
  COALESCE(MAX(u.created_at), o.created_at) AS latest_activity
FROM organizations o
-- Use LEFT JOIN so organizations with 0 users are still included in results!
LEFT JOIN users u ON o.id = u.org_id AND u.is_active = TRUE
GROUP BY o.id, o.name, o.tier, o.created_at
HAVING COUNT(u.id) >= 0
ORDER BY total_members DESC;

-- 1. Create a Composite Index (matches queries filtering on org_id AND is_active)
CREATE INDEX idx_users_org_active ON users (org_id, is_active);

-- 2. Performance Diagnostic: Inspect execution plan using EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT id, email 
FROM users 
WHERE org_id = 'a1b2c3d4-0000-0000-0000-000000000001' 
  AND is_active = TRUE;

import pkg from 'pg';
const { Pool } = pkg;

// Configure warm connection pool
export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Maximum 20 simultaneous connections
  idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
  connectionTimeoutMillis: 2000, // Fail-fast if connection takes > 2 seconds
});

// Exported safe query runner with automatic client release
export async function query(text, params) {
  const start = Date.now();
  // pool.query borrows a client from pool, runs query, and releases client automatically
  const res = await pool.query(text, params);
  const duration = Date.now() - start;
  
  if (process.env.NODE_ENV === 'development') {
    console.log(`📊 [SQL] executed in ${duration}ms | rows: ${res.rowCount}`);
  }
  return res;
}

// 1. INSERT: Insert document with nested objects and arrays
db.telemetry_logs.insertOne({
  nodeId: "node-east-41",
  metrics: { cpu: 42.8, ramUsage: 78.4 },
  tags: ["edge", "us-east", "v2"],
  timestamp: new Date()
});

// 2. QUERY: Deep nested property matching & array operators ($in, $gt)
db.telemetry_logs.find({
  "metrics.cpu": { $gt: 40 },
  tags: { $in: ["edge"] }
}).sort({ timestamp: -1 }).limit(5);

// 3. UPDATE: Atomic operators ($set, $inc, $push)
db.telemetry_logs.updateOne(
  { nodeId: "node-east-41" },
  { 
    $set: { "metrics.status": "healthy" },
    $push: { tags: "audited" } // Appends to array in-place
  }
);

import mongoose from 'mongoose';

// 1. Define Schema Blueprint
const sensorLogSchema = new mongoose.Schema({
  nodeId: {
    type: String,
    required: [true, 'nodeId is mandatory'],
    trim: true,
    index: true // Automatically generates MongoDB B-Tree index!
  },
  temperature: {
    type: Number,
    required: true,
    min: [-50, 'Temperature cannot be below -50C'],
    max: [150, 'Temperature cannot exceed 150C']
  },
  status: {
    type: String,
    enum: ['nominal', 'warning', 'critical'],
    default: 'nominal'
  },
  tags: [String]
}, { 
  timestamps: true // Automatically creates createdAt and updatedAt fields
});

// 2. Lifecycle Pre-Save Hook (Middleware)
sensorLogSchema.pre('save', function(next) {
  // Business logic: automatically elevate status if temperature spikes
  if (this.temperature > 85) {
    this.status = 'critical';
  }
  next();
});

// 3. Compile Model Constructor
export const SensorLog = mongoose.model('SensorLog', sensorLogSchema);

import pkg from 'pg';
import mongoose from 'mongoose';

// --- 1. PostgreSQL Connection Pool (Relational Core) ---
const { Pool } = pkg;
const pgPool = new Pool({ connectionString: process.env.POSTGRES_URL });

// --- 2. MongoDB Mongoose Connection (Telemetry Core) ---
await mongoose.connect(process.env.MONGO_URI);

// Mongoose Telemetry Model
const NodeTelemetry = mongoose.model('NodeTelemetry', new mongoose.Schema({
  clusterId: { type: String, required: true, index: true },
  metrics: Object,
  timestamp: { type: Date, default: Date.now }
}));

// --- 3. Unified Ingestion Controller ---
export async function recordTelemetryEvent({ organizationId, clusterId, metricsPayload }) {
  // Step A: Verify Organization existence & billing status in PostgreSQL (ACID check)
  const pgCheckQuery = `
    SELECT id, name, tier 
    FROM organizations 
    WHERE id = $1 AND tier IN ('pro', 'enterprise');
  `;
  const pgRes = await pgPool.query(pgCheckQuery, [organizationId]);

  if (pgRes.rowCount === 0) {
    throw new Error('Unauthorized: Organization not eligible for telemetry streams.');
  }

  // Step B: High-throughput ingestion of polymorphic sensor metrics into MongoDB
  const log = await NodeTelemetry.create({
    clusterId,
    metrics: metricsPayload
  });

  return {
    organization: pgRes.rows[0].name,
    telemetryId: log._id,
    ingestedAt: log.timestamp
  };
}