📦 My Auth Package

📌 Overview

This authentication package provides a ready-to-use database schema for user management using Drizzle ORM. The schema includes essential tables for users, roles, permissions, authentication tokens, and account linking.

These schema files will be automatically generated in your project when you install and use this package, ensuring a consistent database structure for authentication and authorization.

📥 Schema Files

After installing the package, the following schema files will be created under src/db/schema/user-management:

1️⃣ Users Table (users.ts)

Manages user accounts with fields for email, password, role, verification status, and timestamps.

const usersTable = pgTable("users", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  email: varchar({ length: 91 }).notNull().unique(),
  password: varchar({ length: 91 }).notNull(),
  isVerified: boolean().notNull().default(false),
  isDeleted: boolean().notNull().default(false),
  registeredAt: timestamp("registered_at"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
  roleId: integer("role_id")
    .references(() => rolesTable.id)
    .notNull(),
});

✅ Supports user authentication and role-based access control (RBAC).

2️⃣ Roles Table (roles.ts)

Defines user roles such as Admin, Moderator, User, etc..

const rolesTable = pgTable("roles", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar({ length: 91 }).notNull().unique(),
  description: text(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

✅ Used for assigning roles to users.

3️⃣ Permissions Table (permissions.ts)

Stores granular permissions for different features.

const permissionsTable = pgTable("permissions", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar({ length: 91 }).notNull().unique(),
  description: text(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

✅ Useful for defining fine-grained access controls.

4️⃣ Permission-to-Roles Table (permission_roles.ts)

Links roles to permissions.

const permissionToRolesTable = pgTable(
  "permission_to_roles",
  {
    permissionId: integer("permission_id").references(
      () => permissionsTable.id
    ),
    roleId: integer("role_id").references(() => rolesTable.id),
  },
  (t) => ({
    unq: unique().on(t.permissionId, t.roleId),
  })
);

✅ Allows mapping multiple permissions to a single role.

5️⃣ User-Accounts Table (user_accounts.ts)

Links users to external authentication providers (OAuth, etc.).

const usersToAccountsTable = pgTable("users_to_accounts", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  userId: integer("user_id").references(() => usersTable.id),
  accountId: integer("account_id").references(() => accountsTable.id),
});

✅ Used for third-party login integrations (Google, GitHub, etc.).

6️⃣ Refresh Tokens Table (refresh_tokens.ts)

Manages JWT refresh tokens for authentication.

const refreshTokensTable = pgTable("refresh_tokens", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  token: text().notNull(),
  userId: integer("user_id").notNull(),
  expiresAt: timestamp("expires_at").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  deviceInfo: varchar({ length: 255 }).notNull(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

✅ Supports secure session handling with refresh tokens.

7️⃣ Accounts Table (accounts.ts)

Stores authentication provider details.

const accountsTable = pgTable("accounts", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  provider_name: varchar({ length: 91 }).notNull(),
});

✅ Used for external authentication providers.

** Zod Schema Validations (authValidation.ts)**

import { z } from "zod";

export const registerUserSchema = z.object({
  email: z.string().email(),
  password: z.string().min(8),
  confirmPassword: z.string().min(8),
});

export const verifyOnRegisterSchema = z.object({
  token: z.string(),
  code: z.string(),
  email: z.string().email(),
});

export const loginUserSchema = z.object({
  email: z.string().email(),
  password: z.string().min(8),
});

export const forgotPasswordSchema = z.object({
  email: z.string().email(),
});

export const verifyOnResetPasswordSchema = z.object({
  code: z.string(),
  token: z.string(),
  email: z.string().email(),
  password: z.string().min(8),
  confirmPassword: z.string().min(8),
});

✅ Used for external authentication providers.

📖 How to Use

1️⃣ Install the Package

npm install my-auth-package

2️⃣ Generate Schema Files

After installation, run the following command to generate the database schema:

npm run generate-schema

This will create all the schema files under:

src/db/schema/user-management/

3️⃣ Apply Schema to Database

To apply the schema to your database, use Drizzle ORM migrations:

drizzle-kit push

✅ Now, your authentication system is fully set up! 🎉

🔹 Why Use This Schema?

• ✅ Pre-configured for authentication & authorization
• ✅ Supports role-based access control (RBAC)
• ✅ Easily extendable and customizable
• ✅ Works with Drizzle ORM for better database management

📌 Next Steps

• [ ] Customize the schema (Add custom fields like full_name, profile_picture, etc.)
• [ ] Set up authentication logic (Use the schema with authentication flows)
• [ ] Deploy the database (Connect to PostgreSQL and run migrations)

🚀 Get Started Today!

This package automates schema generation for authentication-based applications. Simply install, run the generator, and start using role-based authentication in your project! 🚀

Would you like me to add versioning instructions for when new fields are added? 😊