Database Schema¶
Part:
saas-boilerplate/| Type: Database Documentation Generated: 2026-02-12 | Last Verified: 2026-03-26 | Scan Level: Exhaustive | Workflow: document-project v1.2.0 ORM: TypeORM 0.3 | Database: PostgreSQL | Entities: 29 total
Summary Statistics¶
| Metric | Count | Notes |
|---|---|---|
| Entities | 29 | Including 3 junction tables |
| Columns | ~233 | Across all entities |
| Relations | 30 | @ManyToOne, @OneToMany, @OneToOne, @ManyToMany |
| Enums | 11 | All defined inline in entity files |
| Explicit @Index decorators | 52 | Includes unique, partial, and composite |
| @DeleteDateColumn | 29/29 | ALL entities have soft-delete |
| @CreateDateColumn | 28/29 | Setting lacks it |
| @UpdateDateColumn | 28/29 | Setting lacks it |
| Redundant isDeleted boolean | 5 | Permission, Product, Role, User, Coupon |
| Junction tables | 3 | role_permission, user_roles, blog_categories_pivot |
| Custom methods | 0 | No entity methods |
| Lifecycle hooks | 0 | No @BeforeInsert, @AfterLoad, etc. |
| Migrations | 0 | Empty array in config |
| Subscribers | 0 | Empty array in config |
Entity Relationship Diagrams¶
Auth & User Management¶
┌───────────────┐ ┌──────────────┐ ┌──────────────┐
│ User │──────<│ UserRoles │>──────│ Role │
│ │ 1:N │ (junction) │ N:1 │ │
│ - id │ └──────────────┘ │ - id │
│ - name │ │ - name │
│ - email │─── roleId (primary) ────────>│ │
│ - password │ └──────┬───────┘
│ - status │ │
│ - provider │ ┌──────┴───────┐
│ - inviteOnly │ │role_permission│
└──┬──┬──┬──┬───┘ │ (junction) │
│ │ │ │ └──────┬───────┘
│ │ │ │ ┌──────────────┐ ┌──────┴───────┐
│ │ │ └───>│ UserSetting │ │ Permission │
│ │ │ 1:1 │ - 2FA config │ │ - route │
│ │ │ │ - provider │ │ - readonly │
│ │ │ │ - backupCodes│ └──────────────┘
│ │ │ └──────────────┘
│ │ │
│ │ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ │ │ │ LoginAttempt │ │OtpVerification│ │ Token │
│ │ │ │ - userId │ │ - email │ │ - userId │
│ │ │ │ - failedCount│ │ - optHash │ │ - token │
│ │ │ │ - lockUntil │ │ - expiresAt │ │ - expiresAt │
│ │ │ └──────────────┘ └──────────────┘ └──────────────┘
│ │ │
│ │ │ ┌──────────────┐
│ │ │ │ InviteUser │
│ │ │ │ - email │
│ │ │ │ - status │
│ │ │ └──────────────┘
│ │ │
Subscriptions & Payments¶
│ │ │
│ │ └──────>┌──────────────┐ ┌──────────────┐
│ │ 1:1 │ Subscription │──────>│ Package │
│ │ │ - status │ N:1 │ - name │
│ │ │ - startDate │ │ - price │
│ │ │ - endDate │ │ - features │
│ │ │ - stripeSubId│ │ - billingCycle│
│ │ └──────────────┘ └──────────────┘
│ │
│ │ ┌──────────────┐
│ │ │PackageCategory│
│ │ │ - name │
│ │ │ - slug │
│ │ └──────────────┘
│ │ (disconnected - no relation to Package)
│ │
│ └─────────>┌──────────────┐ ┌──────────────┐
│ 1:N │ Purchase │──────>│ Payment │
│ │ - amount │ N:1 │ - amount │
│ │ - packageId │ │ - currency │
│ │ - couponId ──┼── orphan FK, no @ManyToOne
│ └──────┬───────┘ │ - status │
│ │ │ - method │
│ │ N:1 └──────────────┘
│ ┌──────┴───────┐
│ │ Cupon │
│ │ - code │ (Purchase.couponId has NO @ManyToOne to Coupon)
│ │ - discountType│
│ │ - amountOff │
│ │ - duration │
│ └──────────────┘
│
Content Management¶
│
└────────────>┌──────────────┐ ┌──────────────────┐
1:N │ Blog │──M:M─>│ BlogCategory │
│ - title │ pivot │ - name │
│ - slug │ │ - slug │
│ - content │ │ - parentId (self) │
│ - imageUrl │ └──────────────────┘
│ - imageAlt │
│ - status │ ┌──────────────┐
│ - authorId │──1:N─>│ BlogTag │
│ - categoryId │ │ - name │
│ (orphan FK)│ │ - slug │
└──────┬───────┘ │ - blogId │
│ └──────────────┘
│ 1:N
┌──────┴───────┐
│BlogViewCount │
│ - count │
└──────────────┘
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ GenericPage │ │ Product │───>│ProductCategory│
│ - title │ │ - name │N:1 │ - name │
│ - slug │ │ - images[] │ │ - description│
│ - description│ │ - price │ └──────────────┘
│ - status │ │ - discountPrc│
│ - SEO fields │ │ - currency │
└──────────────┘ └──────────────┘
Navigation, Config & System¶
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Menu │───>│ MenuItem │ │EmailTemplate │
│ - name │1:N │ - title │ │ - title │
│ - slug │ │ - link │ │ - type │
└──────────────┘ │ - linkType │ │ - subject │
│ - iconClass │ │ - elements │
│ - order │ │ - html │
│ - postType │ └──────────────┘
│ - parentId │
│ - permissions│
└──────────────┘
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Setting │ │ AppConfig │ │ContactMessage│
│ - key │ │ - key │ │ - name │
│ - value │ │ - displayName│ │ - email │
│ - type │ │ - value │ │ - details │
│ - group │ │ - isEncrypted│ └──────────────┘
│ - order │ └──────────────┘
│ (no created/ │ ┌──────────────┐
│ updatedAt) │ │ Logger │
└──────────────┘ │ - status │
│ - success │
│ - ip │
│ - requestedAt│
└──────────────┘
Enums (11 Total, All Inline)¶
All enums are defined inline within entity files. None are shared across entities.
// Coupon enums (Cupon.ts)
enum CouponType { PERCENT = "percent", FIXED = "fixed" }
enum CouponDuration { ONCE = "once", REPEATING = "repeating", FOREVER = "forever" }
// Package enums (Package.ts)
enum BillingCycle { TRIAL = "trial", MONTHLY = "monthly", YEARLY = "yearly" }
enum PackageType { SUBSCRIPTION_BASED = "subscription_based", PRODUCT_BASED = "product_based" }
// ⚠ PackageType is defined but NEVER used on any column
// User/Auth enums (User.ts)
enum Provider { CREDENTIAL = "credential", GOOGLE = "google", FACEBOOK = "facebook", GITHUB = "github", OTP = "otp" }
enum Status { ACTIVE = "active", INACTIVE = "inactive", SUSPEND = "suspend" }
// UserSetting enum (UserSetting.ts)
enum TwoFactorProvider { EMAIL = "email", GOOGLE_AUTHENTICATOR = "google_authenticator", SMS = "sms" }
// InviteUser enum (InviteUser.ts)
enum InviteStatus { PENDING = "PENDING", ACCEPTED = "ACCEPTED" }
// Payment enums (Payment.ts)
enum PaymentStatus { PENDING = "pending", COMPLETED = "completed", FAILED = "failed", REFUNDED = "refunded" }
enum PaymentMethod { STRIPE = "stripe", PAYPAL = "paypal", LEMONSQUEEZY = "lemonsqueezy" }
// Subscription enum (Subscription.ts)
enum SubscriptionStatus { ACTIVE = "active", EXPIRED = "expired", PENDING = "pending", CANCELLED = "cancelled" }
Core Entities¶
User¶
Primary user entity with multi-role support.
@Entity()
export class User {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column({ unique: true })
email: string;
@Column({ select: false, nullable: true })
password: string;
// Primary role (one-to-many)
@ManyToOne(() => Role, (role) => role.users)
role: Role;
@Column({ nullable: true })
roleId: number;
// Extra roles (many-to-many via user_roles)
@ManyToMany(() => Role)
@JoinTable({ name: "user_roles" })
userRoles: Role[];
@Column({ nullable: true })
avatar: string;
@Column({ type: "enum", enum: Status, default: Status.ACTIVE })
status: Status; // active | inactive | suspend
@Column({ type: "enum", enum: Provider, default: Provider.CREDENTIAL })
provider: Provider; // credential | google | facebook | github | otp
@Column({ default: false })
inviteOnly: boolean;
@Column({ default: false })
isDeleted: boolean; // ⚠ Redundant with @DeleteDateColumn
@Column({ nullable: true })
stripeCustomerId: string;
@Column({ nullable: true })
lemonSqueezyCustomerId: string;
@Column({ type: "varchar", length: 20, nullable: true })
phone: string | null;
@Column({ type: "varchar", length: 255, nullable: true })
address: string | null;
// Relations
@OneToMany(() => Purchase, ...)
purchases: Purchase[];
@OneToOne(() => Subscription, ...)
subscription: Subscription;
@OneToMany(() => Blog, ...)
blogs: Blog[];
@OneToOne(() => UserSetting, ...)
setting: UserSetting;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Enums:
enum Provider {
CREDENTIAL = "credential",
GOOGLE = "google",
FACEBOOK = "facebook",
GITHUB = "github",
OTP = "otp",
}
enum Status {
ACTIVE = "active",
INACTIVE = "inactive",
SUSPEND = "suspend",
}
Role¶
Role definitions with permission assignments.
@Entity()
export class Role {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string; // e.g., "admin", "user", "super_admin"
@Column({ nullable: true, default: null })
displayName: string; // e.g., "Administrator", "Regular User"
@Column({ default: false })
isDeleted: boolean; // ⚠ Redundant with @DeleteDateColumn
// Users with this as primary role
@OneToMany(() => User, (user) => user.role)
users: User[];
// Users with this as extra role
@ManyToMany(() => User)
userRoles: User[];
// Permissions (many-to-many via role_permission)
@ManyToMany(() => Permission)
@JoinTable({ name: "role_permission" })
permissions: Permission[];
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Permission¶
Route-based permissions for RBAC.
@Entity()
export class Permission {
@PrimaryGeneratedColumn()
id: number;
@Column({ unique: true })
route: string; // e.g., "/users", "/users/create", "/blogs/edit"
@Column({ default: null })
displayName: string;
@Column({ default: false })
readonly: boolean; // System permissions that can't be deleted
@Column({ default: false })
isDeleted: boolean; // ⚠ Redundant with @DeleteDateColumn
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Subscription System¶
Package¶
Subscription packages/plans.
@Entity()
export class Package {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column({ nullable: true })
slug: string;
@Column({ type: "decimal", precision: 10, scale: 2, default: 0 })
price: number;
@Column({ default: 14 })
durationInDays: number;
@Column({ nullable: true })
type: string; // ⚠ Plain string, not enum. PackageType enum is defined but NEVER used here
@Column({ default: false })
isFree: boolean;
@Column({ type: "text", nullable: true })
description: string;
@Column({ default: true })
isActive: boolean;
@Column({ type: "json", nullable: true })
features: string[];
@Column({ type: "enum", enum: BillingCycle, default: BillingCycle.TRIAL })
billingCycle: BillingCycle;
@Column({ nullable: true })
stripePriceId: string;
@Column({ nullable: true })
lemonVariantId: string;
@OneToMany(() => Subscription, ...)
subscriptions: Subscription[];
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Enums:
enum BillingCycle {
TRIAL = "trial",
MONTHLY = "monthly",
YEARLY = "yearly",
}
enum PackageType {
SUBSCRIPTION_BASED = "subscription_based",
PRODUCT_BASED = "product_based",
}
// ⚠ PackageType is defined in Package.ts but NEVER used on any @Column
PackageCategory¶
Categories for organizing packages.
@Entity()
export class PackageCategory {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column({ unique: true })
slug: string;
@Column({ default: true })
isActive: boolean;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Note: Package imports PackageCategory but no
@ManyToOnerelationship is defined in the source code. The two entities are completely disconnected at the ORM level.
Subscription¶
User subscription records.
@Entity()
export class Subscription {
@PrimaryGeneratedColumn()
id: number;
@OneToOne(() => User)
@JoinColumn({ name: "userId" })
user: User;
@Column()
userId: number;
@ManyToOne(() => Package)
@JoinColumn({ name: "packageId" })
package: Package;
@Column()
packageId: number;
@Column({ type: "timestamp", nullable: true })
startDate: Date;
@Column({ type: "timestamp", nullable: true })
endDate: Date;
@Column({ type: "enum", enum: SubscriptionStatus, default: "active" })
status: SubscriptionStatus;
@Column({ nullable: true })
stripeSubscriptionId: string;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Enums:
enum SubscriptionStatus {
ACTIVE = "active",
EXPIRED = "expired",
PENDING = "pending",
CANCELLED = "cancelled",
}
Content Management¶
Blog¶
Blog posts with rich content, multiple categories, and per-blog tags.
@Entity('blogs')
@Index("IDX_BLOG_SLUG_ACTIVE", ["slug"], { unique: true, where: '"deletedAt" IS NULL' })
@Index("IDX_BLOG_STATUS_PUBLISHED", ["status", "publishedAt"], { where: '"deletedAt" IS NULL' })
@Index("IDX_BLOG_AUTHOR_STATUS", ["authorId", "status"], { where: '"deletedAt" IS NULL' })
export class Blog {
@PrimaryGeneratedColumn()
id: number;
@Column({ length: 200 })
title: string;
@Column({ unique: true })
slug: string;
@Column("text")
content: string;
@Column({ type: "text", nullable: true })
excerpt: string;
// Media
@Column({ nullable: true })
imageUrl: string;
@Column({ nullable: true })
imageAlt: string;
// Categories (many-to-many via blog_categories_pivot)
@ManyToMany(() => BlogCategory, (category) => category.blogs, { cascade: true })
@JoinTable({
name: "blog_categories_pivot",
joinColumn: { name: "blogId" },
inverseJoinColumn: { name: "categoryId" },
})
categories: BlogCategory[];
@Column({ nullable: true })
categoryId: number; // ⚠ Orphan FK — legacy from old single-category pattern, no @ManyToOne
// Tags (one-to-many, tags belong to this blog)
@OneToMany(() => BlogTag, (tag) => tag.blog, { cascade: true })
tags: BlogTag[];
// Author
@Column()
authorName: string;
@ManyToOne(() => User, (user) => user.blogs, { nullable: false, onDelete: "CASCADE" })
@JoinColumn({ name: "authorId" })
author: User;
@Column()
authorId: number;
// Status & Publishing
@Column({ default: "draft" })
status: "draft" | "published" | "archived";
@Column({ type: "timestamp", nullable: true })
publishedAt: Date;
@Column({ type: "boolean", default: false })
isFeatured: boolean;
@Column({ type: "boolean", default: false })
showOnSlider: boolean;
@Column({ type: "boolean", default: false })
isTrending: boolean;
// SEO
@Column({ nullable: true })
metaTitle: string;
@Column({ type: "text", nullable: true })
metaDescription: string;
@Column({ default: true })
robotsIndex: boolean;
@Column({ default: true })
robotsFollow: boolean;
@Column("simple-array", { nullable: true })
metaKeywords: string[];
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
BlogCategory¶
Hierarchical blog categories with self-referencing parent.
@Entity()
export class BlogCategory {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column({ unique: true })
slug: string;
@Column({ type: "text", nullable: true })
description: string;
@Column({ default: 1 })
serial: number;
// Self-referencing for hierarchy
@ManyToOne(() => BlogCategory, (item) => item.children, { nullable: true, onDelete: "CASCADE" })
parent: BlogCategory;
@Column({ nullable: true })
parentId: number;
@OneToMany(() => BlogCategory, (cat) => cat.parent)
children: BlogCategory[];
@ManyToMany(() => Blog, (blog) => blog.categories)
blogs: Blog[];
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
BlogTag¶
Tags belonging to individual blog posts (one-to-many from Blog).
@Entity('blog_tags')
@Index(["name", "deletedAt"])
@Index(["slug", "deletedAt"])
export class BlogTag {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string; // e.g., "SEO", "Link Building"
@Column({ type: "varchar", nullable: true })
slug: string;
@ManyToOne(() => Blog, (blog) => blog.tags, { onDelete: "CASCADE" })
@JoinColumn({ name: "blogId" })
blog: Blog;
@Column()
blogId: number;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
BlogViewCount¶
Aggregate view count tracking for blog analytics.
@Entity('blog_view_counts')
export class BlogViewCount {
@PrimaryGeneratedColumn()
id: number;
@ManyToOne(() => Blog, { onDelete: "CASCADE" })
@JoinColumn({ name: "blogId" })
blog: Blog;
@Column()
blogId: number;
@Column({ type: "int", default: 0 })
count: number;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn({ nullable: true })
deletedAt: Date;
}
GenericPage¶
CMS pages with flexible content.
@Entity('generic_pages')
export class GenericPage {
@PrimaryGeneratedColumn()
id: number;
@Column({ type: "varchar", length: 255 })
title: string;
@Column({ type: "varchar", length: 255 })
slug: string;
@Column({ type: "text" })
description: string;
@Column({ type: "varchar", length: 255, nullable: true })
image: string;
@Column({ type: "enum", enum: ["draft", "published", "archived"], default: "draft" })
status: "draft" | "published" | "archived";
// SEO
@Column({ type: "varchar", length: 255, nullable: true })
metaTitle: string;
@Column({ type: "text", nullable: true })
metaDescription: string;
@Column("simple-array", { nullable: true })
metaKeywords: string[];
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
EmailTemplate¶
Customizable email templates with JSONB block elements.
@Entity('email_templates')
export class EmailTemplate {
@PrimaryGeneratedColumn()
id: number;
@Column({ type: 'varchar', length: 255, default: null })
title: string;
@Column({ unique: true })
type: string; // forgot_password | verify_email | booking_reminder
@Column({ type: 'varchar', length: 255, default: null })
subject: string;
@Column({ type: "jsonb" })
elements: any[]; // BlockNote editor JSON blocks
@Column('text')
html: string; // Rendered HTML output
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Product System¶
Product¶
Product catalog items.
@Entity('products')
export class Product {
@PrimaryGeneratedColumn()
id: number;
@Column({ length: 100 })
name: string;
@Column({ type: "text", array: true, nullable: true })
images: string[]; // Array of image URLs
@Column({ type: "text", nullable: true })
description: string;
@ManyToOne(() => ProductCategory, (category) => category.products, { onDelete: "CASCADE" })
@JoinColumn({ name: "categoryId" })
category: ProductCategory;
@Column({ nullable: true })
categoryId: number;
@Column({ type: "decimal", precision: 10, scale: 2 })
price: number;
@Column({ type: "decimal", precision: 10, scale: 2, nullable: true })
discountPrice: number;
@Column({ length: 3, default: "USD" })
currency: string;
@Column({ default: false })
isDeleted: boolean; // ⚠ Redundant with @DeleteDateColumn
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
ProductCategory¶
Categories for products.
@Entity()
export class ProductCategory {
@PrimaryGeneratedColumn()
id: number;
@Index()
@Column()
name: string;
@Column({ type: "text", nullable: true })
description: string;
@OneToMany(() => Product, (product) => product.category)
products: Product[];
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn({ nullable: true })
deletedAt: Date;
}
Menu System¶
Menu¶
Navigation menus.
@Entity('menus')
export class Menu {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string; // e.g., "Navbar", "Footer"
@Column({ type: "varchar", unique: true, nullable: true })
slug: string; // e.g., "dashboard-sidebar-top"
@Column("simple-json", { nullable: true })
locations: string[]; // e.g., ["header", "footer"]
@OneToMany(() => MenuItem, (item) => item.menu)
items: MenuItem[];
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn({ nullable: true })
deletedAt: Date;
}
MenuItem¶
Nested menu items with self-referencing hierarchy.
@Entity('menu_items')
export class MenuItem {
@PrimaryGeneratedColumn()
id: number;
@Column({ nullable: true })
menuId: number;
@ManyToOne(() => Menu, { onDelete: "CASCADE" })
@JoinColumn({ name: "menuId" })
menu: Menu;
@Column({ type: "varchar", length: 255 })
title: string;
@Column({ type: "varchar", length: 255 })
link: string;
@Column({ type: "enum", enum: ["dynamic", "static"], nullable: true })
linkType: "dynamic" | "static";
@Column({ type: "varchar", length: 255, default: "_self" })
target: string;
@Column({ type: "varchar", length: 255, nullable: true })
iconClass: string;
@Column({ type: "varchar", length: 255, nullable: true })
color: string;
@Column({ nullable: true })
parentId: number;
@ManyToOne(() => MenuItem, (item) => item.children, { nullable: true, onDelete: "CASCADE" })
@JoinColumn({ name: "parentId" })
parent: MenuItem;
@OneToMany(() => MenuItem, (item) => item.parent)
children: MenuItem[];
@Column({ type: "int", default: 0 })
order: number;
@Column({ type: "enum", enum: ["blogs", "pages", "categories", "tags", "packages"], nullable: true })
postType: string;
@Column({ type: "int", nullable: true })
postId: number;
@Column({ type: "text", nullable: true })
parameters: string;
@Column("simple-array", { nullable: true })
permissions: string[];
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Supporting Entities¶
Payment¶
Payment transaction records.
@Entity()
export class Payment {
@PrimaryGeneratedColumn()
id: number;
@Column({ type: "decimal", precision: 12, scale: 2 })
amount: number;
@Column({ length: 3 })
currency: string; // e.g., "USD", "EUR"
@Column({ type: "enum", enum: PaymentStatus, default: "pending" })
status: PaymentStatus; // pending | completed | failed | refunded
@Column()
method: PaymentMethod; // ⚠ NOT declared as enum type in decorator — plain @Column()
@Column({ nullable: true })
referenceId: string; // e.g., Stripe paymentIntentId, PayPal orderId
@Column({ type: "timestamp" })
paymentDate: Date;
@Column({ type: "json", nullable: true })
metadata: Record<string, any>;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Enums:
enum PaymentStatus {
PENDING = "pending",
COMPLETED = "completed",
FAILED = "failed",
REFUNDED = "refunded",
}
enum PaymentMethod {
STRIPE = "stripe",
PAYPAL = "paypal",
LEMONSQUEEZY = "lemonsqueezy",
}
Purchase¶
Purchase/order records.
@Entity()
export class Purchase {
@PrimaryGeneratedColumn()
id: number;
@Column()
packageId: number;
@Column()
paymentId: number;
@Column({ nullable: true, default: null })
couponId: number; // ⚠ Orphan FK — has NO @ManyToOne to Coupon entity
@Column({ type: "decimal", precision: 12, scale: 5 })
originalPrice: number;
@Column({ type: "decimal", precision: 12, scale: 5, default: 0 })
discountAmount: number;
@Column({ type: "decimal", precision: 12, scale: 5 })
amountPaid: number;
@ManyToOne(() => User)
@JoinColumn({ name: "userId" })
user: User;
@Column()
userId: number;
@ManyToOne(() => Package)
@JoinColumn({ name: "packageId" })
package: Package;
@ManyToOne(() => Payment)
@JoinColumn({ name: "paymentId" })
payment: Payment;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Coupon¶
Discount/coupon codes. (Note: entity class is named Coupon but the source filename is Cupon.ts -- a known typo tracked in Finding 5.)
@Entity()
export class Coupon {
@PrimaryGeneratedColumn()
id: number;
@Column({ unique: true })
code: string;
@Column({ nullable: true })
description: string;
@Column({ type: "enum", enum: CouponType })
discountType: CouponType; // percent | fixed
@Column("decimal", { precision: 10, scale: 2 })
amountOff: number;
@Column({ nullable: true })
currency: string;
@Column({ type: "enum", enum: CouponDuration })
duration: CouponDuration; // once | repeating | forever
@Column({ type: "int", nullable: true })
durationInMonths: number; // Only valid if duration === "repeating"
@Column({ nullable: true })
stripeCouponId: string;
@Column({ default: true })
isActive: boolean;
@Column({ default: false })
isDeleted: boolean; // ⚠ Redundant with @DeleteDateColumn
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Enums:
enum CouponType {
PERCENT = "percent",
FIXED = "fixed",
}
enum CouponDuration {
ONCE = "once",
REPEATING = "repeating",
FOREVER = "forever",
}
ContactMessage¶
Contact form submissions.
@Entity('contact_messages')
export class ContactMessage {
@PrimaryGeneratedColumn()
id: number;
@Column({ type: "varchar", length: 255 })
name: string;
@Column({ type: "varchar", length: 255 })
email: string;
@Column({ type: "text" })
details: string;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
InviteUser¶
User invitation management.
@Entity('user_invites')
export class InviteUser {
@PrimaryGeneratedColumn()
id: number;
@Column({ unique: true })
email: string;
@Column({ type: "enum", enum: InviteStatus, default: "PENDING" })
status: InviteStatus; // PENDING | ACCEPTED
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Enums:
Setting¶
Application settings (key-value store with ordering and grouping).
@Entity('settings')
export class Setting {
@PrimaryGeneratedColumn()
id: number;
@Column({ type: "varchar", length: 255 })
key: string; // e.g., "site.name", "auth.website_type"
@Column({ type: "varchar", length: 255, nullable: true })
display_name: string;
@Column({ type: "text", nullable: true })
value: string;
@Column({ type: "text", nullable: true })
details: string;
@Column("simple-array", { nullable: true })
backupCodes: string[]; // ⚠ MISPLACED — belongs on UserSetting, not Setting
@Column({ type: "varchar", length: 255, nullable: true })
type: string; // text | number | boolean | json
@Column({ type: "int", nullable: true, default: 1 })
order: number;
@Column({ type: "varchar", length: 255, nullable: true })
group: string; // For grouping in UI
// ⚠ NO @CreateDateColumn — only entity missing it (28/29 have it)
// ⚠ NO @UpdateDateColumn — only entity missing it (28/29 have it)
@DeleteDateColumn()
deletedAt: Date;
}
AppConfig¶
System configuration entity with encryption support.
@Entity('app_configs')
@Index(["key"], { unique: true })
export class AppConfig {
@PrimaryGeneratedColumn()
id: number;
@Column({ type: "varchar", length: 150 })
key: string;
@Column({ type: "varchar", length: 255 })
displayName: string;
@Column({ type: "text" })
value: string;
@Column({ name: "is_encrypted", default: true })
isEncrypted: boolean;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
UserSetting¶
Per-user settings including 2FA configuration.
@Entity()
export class UserSetting {
@PrimaryGeneratedColumn()
id: number;
@OneToOne(() => User)
@JoinColumn()
user: User;
@Column()
userId: number;
@Column({ default: false })
isTwoFactorEnabled: boolean;
@Column({ type: "enum", enum: TwoFactorProvider, nullable: true })
twoFactorProvider: TwoFactorProvider; // email | google_authenticator | sms
@Index()
@Column({ type: "text", nullable: true, select: true })
twoFactorSecret: string; // ⚠ TOTP secret stored plaintext + indexed (security anti-pattern)
@Column({ type: "text", nullable: true, select: true })
tempTwoFactorSecret: string; // Temporary secret during 2FA setup
@Column("text", { nullable: true })
backupCodes: string; // Encrypted or concatenated codes
@Column({ type: "varchar", length: 10, default: "en" })
language: string; // e.g., "en", "bn"
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn({ nullable: true })
deletedAt: Date;
}
Enums:
enum TwoFactorProvider {
EMAIL = "email",
GOOGLE_AUTHENTICATOR = "google_authenticator",
SMS = "sms",
}
Internal/System Entities¶
LoginAttempt¶
Failed login tracking for security and rate limiting.
@Entity()
export class LoginAttempt {
@PrimaryGeneratedColumn()
id: number;
@ManyToOne(() => User, (user) => user.id, { nullable: false })
@JoinColumn({ name: "userId" })
user: User;
@Index()
@Column()
userId: number;
@Column({ default: 0 })
failedAttemptsCount: number;
@Column({ type: "timestamp", nullable: true })
lastFailedAt: Date;
@Column({ type: "timestamp", nullable: true })
lockUntil: Date;
@Index()
@Column({ default: false })
isBlocked: boolean;
@CreateDateColumn()
createdAt: Date;
@Index()
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn({ nullable: true })
deletedAt: Date;
}
OtpVerification¶
OTP codes for 2FA and passwordless login. (Note: source file is Otp.ts but class is OtpVerification.)
@Entity()
export class OtpVerification {
@PrimaryGeneratedColumn()
id: number;
@Index()
@Column()
email: string;
@Index()
@Column()
optHash: string; // [sic] — typo for "otpHash". Hashed OTP value
@Column({ type: "boolean", default: false })
isVerified: boolean;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@Column({ type: "timestamp", nullable: true })
expiresAt: Date;
@DeleteDateColumn({ nullable: true })
deletedAt: Date;
}
Token¶
Password reset and verification tokens.
@Entity()
export class Token {
@PrimaryGeneratedColumn()
id: number;
@Column()
userId: number; // ⚠ Orphan FK — raw number, no @ManyToOne to User
@Column()
token: string; // Hashed token
@Column({ type: "timestamp" })
expiresAt: Date;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Logger¶
HTTP request logging (database transport).
@Entity()
export class Logger {
@PrimaryGeneratedColumn()
id: number;
@Column({ type: "int", nullable: true })
status: number;
@Column()
responseTime: string;
@Column({ type: "boolean" })
success: boolean;
@Column({ nullable: true })
errorMessage: string;
@Column({ nullable: true })
userId: number; // ⚠ Orphan FK — raw number, no @ManyToOne to User
@Column()
ip: string;
@Column({ type: "timestamp with time zone" })
requestedAt: Date;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
UserRoles¶
Junction table for many-to-many User-Role relationship.
@Entity("user_roles")
export class UserRoles {
@PrimaryGeneratedColumn()
id: number;
@Column()
userId: number;
@Column()
roleId: number;
@CreateDateColumn()
createdAt: Date;
@UpdateDateColumn()
updatedAt: Date;
@DeleteDateColumn()
deletedAt: Date;
}
Dual-mapping issue: Both
UserRolesentity ANDUser.userRoles@JoinTabletarget the sameuser_rolestable. TypeORM manages the join table via@ManyToManyon User, while the explicitUserRolesentity provides direct query access. This creates a collision risk if both paths are used for writes.
Orphan FK Columns¶
These columns store foreign key IDs as raw numbers with no @ManyToOne decorator, meaning TypeORM does not enforce referential integrity:
| Entity | Column | Intended Target | Impact |
|---|---|---|---|
| Logger | userId | User | No cascade, no join loading |
| Token | userId | User | No cascade, no join loading |
| Purchase | couponId | Coupon | No cascade, no join loading |
| Blog | categoryId | BlogCategory | Deprecated legacy from single-category pattern |
Standalone Entities (Zero Incoming Relations)¶
These 11 entities have no other entity pointing to them via @ManyToOne, @OneToOne, or @ManyToMany:
AppConfig, ContactMessage, Coupon, EmailTemplate, GenericPage, InviteUser, Logger, OtpVerification, PackageCategory, Setting, Token
Complete Entity List (29 Entities)¶
| Entity | Domain | Table Name | Purpose | Has API | isDeleted? |
|---|---|---|---|---|---|
| User | Auth | user | User accounts | Yes | Yes (redundant) |
| Role | Auth | role | Role definitions | Yes | Yes (redundant) |
| Permission | Auth | permission | Route permissions | Yes | Yes (redundant) |
| UserRoles | Auth | user_roles | User-Role junction (dual-mapped) | -- | No |
| UserSetting | Auth | user_setting | Per-user settings & 2FA | Yes | No |
| Package | Commerce | package | Subscription plans | Yes | No |
| PackageCategory | Commerce | package_category | Plan categories (disconnected) | Yes | No |
| Subscription | Commerce | subscription | User subscriptions | Yes | No |
| Payment | Commerce | payment | Payment records | Yes | No |
| Purchase | Commerce | purchase | Purchase records | Yes | No |
| Coupon | Commerce | coupon | Discount codes (file: Cupon.ts) | Yes | Yes (redundant) |
| Blog | Content | blogs | Blog posts | Yes | No |
| BlogCategory | Content | blog_category | Blog categories (hierarchical) | Yes | No |
| BlogTag | Content | blog_tags | Blog tags (per-blog, OneToMany) | Yes | No |
| BlogViewCount | Content | blog_view_counts | Aggregate view counts (auto-managed) | -- | No |
| Product | Content | products | Products | Yes | Yes (redundant) |
| ProductCategory | Content | product_category | Product categories | Yes | No |
| GenericPage | Content | generic_pages | CMS pages | Yes | No |
| EmailTemplate | Content | email_templates | Email templates | Yes | No |
| Menu | Navigation | menus | Navigation menus | Yes | No |
| MenuItem | Navigation | menu_items | Menu items (hierarchical) | Yes | No |
| ContactMessage | Communication | contact_messages | Contact submissions | Yes | No |
| InviteUser | Admin | user_invites | User invitations | Yes | No |
| Setting | Config | settings | App settings (key-value) | Yes | No |
| AppConfig | Config | app_configs | System config (encrypted) | Yes | No |
| LoginAttempt | Security | login_attempt | Login security (internal) | -- | No |
| OtpVerification | Security | otp_verification | OTP codes (internal) | -- | No |
| Token | Security | token | Reset tokens (internal) | -- | No |
| Logger | System | logger | Log entries (internal) | -- | No |
Critical Issues Summary¶
| # | Issue | Severity | Entities |
|---|---|---|---|
| 1 | UserRole dual-mapping: explicit entity AND @JoinTable both target user_roles |
High | UserRoles, User |
| 2 | Setting missing @CreateDateColumn and @UpdateDateColumn | Medium | Setting |
| 3 | Redundant isDeleted boolean alongside @DeleteDateColumn | Low | Permission, Product, Role, User, Coupon |
| 4 | PackageType enum defined but NEVER used on any column | Low | Package |
| 5 | Payment.method not declared as enum type in decorator | Medium | Payment |
| 6 | twoFactorSecret stored plaintext + indexed | High | UserSetting |
| 7 | Setting.backupCodes misplaced (belongs on UserSetting) | Medium | Setting |
| 8 | Purchase.couponId has NO @ManyToOne to Coupon | Medium | Purchase |
| 9 | Blog.categoryId orphan from old single-category pattern | Low | Blog |
| 10 | Package imports PackageCategory but has NO relation | Low | Package, PackageCategory |
DataSource Configuration¶
// src/config/dbConfig.ts
export default new DataSource({
type: "postgres",
synchronize: config.nod_env === 'development', // [sic] — config key is "nod_env" (typo for "node_env")
entities: ['../entity/**/*.{ts,js}'], // Glob-loaded, all 29 entities
migrations: [], // Empty — no migration files
subscribers: [], // Empty — no subscribers
});
Migration Strategy¶
Current Approach¶
TypeORM synchronize is enabled conditionally based on environment:
// src/config/dbConfig.ts
synchronize: config.nod_env === 'development', // [sic] — config key is "nod_env" (typo for "node_env")
migrations: [],
- Development:
synchronize: true-- TypeORM auto-applies schema changes on startup. Entity changes are reflected immediately without manual intervention. - Production:
synchronize: false-- Schema changes are not auto-applied. However, no migration files or migration runner are configured (migrations: []).
Implications¶
- There is currently no migration pipeline for production schema changes.
- Production database schema updates must be handled manually (e.g., raw SQL or a future migration setup).
- To add a proper migration workflow, configure TypeORM CLI with a
migrations/directory and usetypeorm migration:generateandtypeorm migration:run.