এক্সপ্রেসে মাল্টি টেন্যান্ট এপিআই তৈরি

import sqlite3 from "sqlite3";
import { open } from "sqlite";

const dbPromise = open({
  filename: "./notes.db",
  driver: sqlite3.Database
});

(async () => {
  const db = await dbPromise;

  await db.run(`
    CREATE TABLE IF NOT EXISTS tenants (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT UNIQUE NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  await db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      role TEXT CHECK(role IN ('admin', 'tenant', 'user')) DEFAULT 'user',
      tenant_id INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    )
  `);

  await db.run(`
    CREATE TABLE IF NOT EXISTS notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      tenant_id INTEGER NOT NULL,
      title TEXT NOT NULL,
      content TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id),
      FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    )
  `);
})();

export default dbPromise;

import express from "express";
import bcrypt from "bcryptjs";
import jwt from "jsonwebtoken";
import dbPromise from "../db.js";
import { authenticate, authorizeRoles } from "../middleware/auth.js";

const router = express.Router();
const JWT_SECRET = "s9d8f7s9df7s9dadsfdf7s9df7s9df"; // use env in prod

// 🧱 Register Tenant (only admin can do this)
router.post("/register-tenant", authenticate, authorizeRoles("admin"), async (req, res) => {
  const { tenantName, username, password } = req.body;
  const db = await dbPromise;

  const hashedPassword = await bcrypt.hash(password, 10);

  try {
    const tenantResult = await db.run("INSERT INTO tenants (name) VALUES (?)", [tenantName]);
    const tenantId = tenantResult.lastID;

    await db.run(
      "INSERT INTO users (username, password, role, tenant_id) VALUES (?, ?, 'tenant', ?)",
      [username, hashedPassword, tenantId]
    );

    res.json({ message: "Tenant created", tenantId });
  } catch (err) {
    res.status(400).json({ error: "Tenant or username already exists" });
  }
});

// 👤 Register Regular User (only tenant admin can do this)
router.post("/register", authenticate, authorizeRoles("tenant"), async (req, res) => {
  const { username, password } = req.body;
  const db = await dbPromise;

  const hashedPassword = await bcrypt.hash(password, 10);

  try {
    const result = await db.run(
      "INSERT INTO users (username, password, role, tenant_id) VALUES (?, ?, 'user', ?)",
      [username, hashedPassword, req.user.tenant_id]
    );
    res.json({ message: "User created", id: result.lastID });
  } catch (err) {
    res.status(400).json({ error: "Username already exists" });
  }
});


// 🔑 Login (works for all roles)
router.post("/login", async (req, res) => {
  const { username, password } = req.body;
  const db = await dbPromise;
  const user = await db.get("SELECT * FROM users WHERE username = ?", [username]);
  if (!user) return res.status(400).json({ error: "Invalid credentials" });

  const valid = await bcrypt.compare(password, user.password);
  if (!valid) return res.status(400).json({ error: "Invalid credentials" });

  const token = jwt.sign(
    { id: user.id, role: user.role, tenant_id: user.tenant_id },
    JWT_SECRET,
    { expiresIn: "1h" }
  );

  res.json({ token, role: user.role });
});

export default router;

import jwt from "jsonwebtoken";

const JWT_SECRET = "s9d8f7s9df7s9dadsfdf7s9df7s9df"; // ⚠️ use process.env.JWT_SECRET in production

// ✅ Verify JWT and attach user info to request
export const authenticate = (req, res, next) => {
  const authHeader = req.headers.authorization;

  if (!authHeader || !authHeader.startsWith("Bearer ")) {
    return res.status(401).json({ error: "No or invalid token provided" });
  }

  const token = authHeader.split(" ")[1];

  try {
    const decoded = jwt.verify(token, JWT_SECRET);
    req.user = decoded; // contains { id, role, tenant_id }
    next();
  } catch (err) {
    res.status(401).json({ error: "Invalid or expired token" });
  }
};

// ✅ Restrict access by role(s)
export const authorizeRoles = (...allowedRoles) => {
  return (req, res, next) => {
    if (!req.user) {
      return res.status(401).json({ error: "User not authenticated" });
    }

    if (!allowedRoles.includes(req.user.role)) {
      return res.status(403).json({ error: "Forbidden: insufficient permissions" });
    }

    next();
  };
};

import express from "express";
import dbPromise from "../db.js";
import { authenticate } from "../middleware/auth.js";

const router = express.Router();

// ✅ All routes require authentication
router.use(authenticate);

/**
 * 📘 Create Note
 * - Regular users can create notes under their tenant
 * - Tenants and Admins can also create (optional)
 */
router.post("/", async (req, res) => {
  const { title, content } = req.body;
  const { id, tenant_id } = req.user;
  const db = await dbPromise;

  try {
    const result = await db.run(
      "INSERT INTO notes (user_id, tenant_id, title, content) VALUES (?, ?, ?, ?)",
      [id, tenant_id, title, content]
    );

    res.json({
      id: result.lastID,
      user_id: id,
      tenant_id,
      title,
      content,
    });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to create note" });
  }
});

/**
 * 📖 Get All Notes
 * - Admin: see all notes
 * - Tenant: see all notes under their tenant
 * - User: see only their own notes
 */
router.get("/", async (req, res) => {
  const db = await dbPromise;
  const { role, id, tenant_id } = req.user;

  let notes;

  try {
    if (role === "admin") {
      notes = await db.all("SELECT * FROM notes ORDER BY created_at DESC");
    } else if (role === "tenant") {
      notes = await db.all(
        "SELECT * FROM notes WHERE tenant_id = ? ORDER BY created_at DESC",
        [tenant_id]
      );
    } else {
      notes = await db.all(
        "SELECT * FROM notes WHERE user_id = ? ORDER BY created_at DESC",
        [id]
      );
    }

    res.json(notes);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch notes" });
  }
});

/**
 * 🔍 Get Single Note
 * - Admin: can access any note
 * - Tenant: can access notes within their tenant
 * - User: only their own notes
 */
router.get("/:id", async (req, res) => {
  const db = await dbPromise;
  const { role, id, tenant_id } = req.user;

  try {
    let note;

    if (role === "admin") {
      note = await db.get("SELECT * FROM notes WHERE id = ?", [req.params.id]);
    } else if (role === "tenant") {
      note = await db.get(
        "SELECT * FROM notes WHERE id = ? AND tenant_id = ?",
        [req.params.id, tenant_id]
      );
    } else {
      note = await db.get(
        "SELECT * FROM notes WHERE id = ? AND user_id = ?",
        [req.params.id, id]
      );
    }

    if (!note) return res.status(404).json({ error: "Note not found" });
    res.json(note);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch note" });
  }
});

/**
 * ✏️ Update Note
 */
router.put("/:id", async (req, res) => {
  const { title, content } = req.body;
  const db = await dbPromise;
  const { role, id, tenant_id } = req.user;

  try {
    let result;

    if (role === "admin") {
      result = await db.run(
        "UPDATE notes SET title = ?, content = ? WHERE id = ?",
        [title, content, req.params.id]
      );
    } else if (role === "tenant") {
      result = await db.run(
        "UPDATE notes SET title = ?, content = ? WHERE id = ? AND tenant_id = ?",
        [title, content, req.params.id, tenant_id]
      );
    } else {
      result = await db.run(
        "UPDATE notes SET title = ?, content = ? WHERE id = ? AND user_id = ?",
        [title, content, req.params.id, id]
      );
    }

    if (result.changes === 0) return res.status(404).json({ error: "Note not found or access denied" });
    res.json({ message: "Note updated successfully" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to update note" });
  }
});

/**
 * ❌ Delete Note
 */
router.delete("/:id", async (req, res) => {
  const db = await dbPromise;
  const { role, id, tenant_id } = req.user;

  try {
    let result;

    if (role === "admin") {
      result = await db.run("DELETE FROM notes WHERE id = ?", [req.params.id]);
    } else if (role === "tenant") {
      result = await db.run(
        "DELETE FROM notes WHERE id = ? AND tenant_id = ?",
        [req.params.id, tenant_id]
      );
    } else {
      result = await db.run(
        "DELETE FROM notes WHERE id = ? AND user_id = ?",
        [req.params.id, id]
      );
    }

    if (result.changes === 0) return res.status(404).json({ error: "Note not found or access denied" });
    res.json({ message: "Note deleted successfully" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to delete note" });
  }
});

export default router;

sqlite3 notes.db

INSERT INTO users (username, password, role)
VALUES ('superadmin', '$2a$12$UozpQlgo0bnkyIStzH8THegMJgKt0J70a1FtX9k0a2NMtD/5E7fLK', 'admin');

{
  "tenantName": "TenantOne",
  "username": "tenantadmin1",
  "password": "tenant123"
}

{
  "username": "tenantadmin1",
  "password": "tenant123"
}

{
  "username": "jack",
  "password": "123123",
  "tenantName" : "tenant1"
}