Database Design Rationale: Authentication & Project Management System (Deep Dive)
I’m not a prodigy. I didn’t start at 13. I learned the hard way — error by error, late night by late night. I still Google basic stuff, still mess up, still doubt myself. But I show up daily, build real things, and document the process without filters. No polished aesthetics. No fake “10x dev” talk. Just one guy trying to master his craft — publicly, consistently, and without shortcuts. If you relate to the grind more than the glory, welcome to my corner of the internet.
Purpose of this document
This article is written for future me and for any engineer who wants to understand why and how this system was designed. This is not just a schema dump — it is the thinking behind the schema. Every table exists for a reason. Every relationship encodes a business rule. The goal was to design a system that is secure, scalable, auditable, and evolvable, not just something that “works”.
1. Core Design Principles (Read This First)
Before tables, there are principles:
Separation of concerns
Profile data ≠ security data ≠ authorization ≠ business data.Auditability over convenience
If something important happens, the system remembers it.Soft delete > hard delete
Users make mistakes. Systems must forgive.Explicit relationships
If two things are related in real life, they are related in the database.Trade‑offs are allowed — ignorance is not
Some choices sacrifice strictness for flexibility. That is intentional.
Keep these in mind while reading the tables below.
2. Authentication & Identity Domain
2.1 User
What this table represents
A real human identity inside the system.
This table is deliberately not overloaded with security mechanics. It represents who the user is, not how the system defends against them.
Attributes explained:
id: UUID so identities are globally unique and unguessable.name: Display name for UI.username: Optional human‑friendly identifier.email: Primary login identifier.hashed_password: Password is never stored raw. Ever.phone_number: Used for recovery or MFA.profile_image: UX personalization.bio: Optional self‑description.social_links: External identity signals (kept separate from auth).mfa_enabled: Quick flag to enforce MFA at login.is_locked: Indicates account is temporarily blocked.lock_until: Prevents brute‑force retry loops.account_status: Active, suspended, disabled — explicit states matter.email_verified: Prevents fake account abuse.createdAt,updatedAt: Lifecycle tracking.
Why this matters
Keeping User clean prevents security logic from leaking into unrelated features.
2.2 UserSecurity
Purpose
Tracks risk and behavior, not identity.
Attributes explained:
suspicious_score: Adaptive risk score based on behavior.last_login_at: Detects abnormal login patterns.password_changed_count: Signals account churn or compromise.failed_login_attempts: Brute‑force detection.hashed_verification_token: Email verification flow.hashed_forgot_password_token: Secure password recovery.created_at,updated_at: Security state history.
Relationship
One‑to‑one with User. Security data changes more often and needs isolation.
2.3 LastDevice
Purpose
Represents where and how a user accesses the system.
Attributes explained:
device_id: Stable fingerprint per device.user_agent: Browser / OS info.ip_address: Network location.location: Geo approximation.is_trusted: Allows bypassing MFA on trusted devices.last_used: Device activity tracking.risk_score: Heuristic threat evaluation.device_trust_level: Low / medium / high.activity: Last known action context.
Why it exists
Modern security is device‑aware, not password‑only.
2.4 Session
Purpose
Represents an authenticated login lifecycle.
Attributes explained:
refresh_token: Long‑lived credential.expires_at: Session expiration.revoked_at: Immediate invalidation.device_id: Ties session to a device.ip_address,user_agent: For anomaly detection.
Why important
Enables logout‑all, session tracking, and zero‑trust enforcement.
2.5 MFACredential
Purpose
Stores multi‑factor authentication methods.
Attributes explained:
type: TOTP, SMS, Email, etc.secret_hash: MFA secret, hashed.backup_codes: Emergency recovery.device_trust_level: Risk‑aware MFA.is_active: Allows rotation.
2.6 OAuthProvider
Purpose
Maps external identity providers to internal users.
Attributes explained:
provider_name: Google, GitHub, etc.provider_user_id: External identity.access_token,refresh_token: OAuth lifecycle.
2.7 Notification
Purpose
Tracks all outbound communication.
Attributes explained:
type: Email, SMS, push.purpose: Verification, alert, reminder.recipient: Target address.status: Pending, sent, failed.provider: Email/SMS service used.error_message: Debugging failures.attempt_count: Retry logic.last_attempt_at: Backoff handling.
2.8 TelemetryEvent
Purpose
Passive analytics and system diagnostics.
Attributes explained:
screen_resolution,language: UX context.referrer: Traffic source.session_id: Behavioral correlation.timestamp: Event ordering.
2.9 AuditLog
Purpose
Security‑critical event tracking.
Attributes explained:
event_type: Login, password change, revoke.status: Success or failure.performed_by: Actor.token_id: Credential involved.ip_address: Forensics.
Audit logs are immutable by design.
3. Authorization & RBAC
Role
Defines what someone is in a context.
name: Human‑readable role.scope: Workspace or project.
Permission
Defines what actions are allowed.
action: create, update, delete.resource: task, project, epic.
RolePermission
Many‑to‑many mapping.
UserRole
Key idea: A user can be admin in one workspace and member in another.
4. Workspace & Project Domain
4.1 Workspace
Purpose
Tenant boundary.
Attributes explained:
slug: URL‑safe identifier.visibility: Private or public.plan: Monetization hook.archived: Read‑only state.deleted_at: Soft deletion.
4.2 WorkspaceMember
Tracks invitation and membership lifecycle.
4.3 Project
Purpose
Execution container.
Attributes explained:
key: Human‑friendly identifier.methodology: Scrum, Kanban, etc.priority: Business urgency.archived,deleted_at: Lifecycle.
4.4 Sprint
Time‑boxed execution window. Not soft‑deleted because history matters.
4.5 Epic
Large bodies of work.
progress: Derived completion metric.deleted_at: Recoverability.
4.6 Task
Atomic unit of work.
Attributes explained:
ticket_number: Local ordering.parent_task_id: Subtasks.position: Board ordering.estimated_hoursvsactual_hours: Planning accuracy.deleted_at: Safety.
4.7 Comment
Polymorphic discussion system.
entity_status,entity_id: Allows reuse across entities.is_edited,edited_at: Conversation integrity.
4.8 Attachment
Polymorphic file storage.
checksum: File integrity.file_size,file_type: Validation.deleted_at: Compliance & recovery.
4.9 AuditLogPMS
Tracks business actions.
old_value,new_value: Change history.entity_type,entity_id: Polymorphic audit.
5. Relationships Explained
Users sit at the center.
Workspaces isolate tenants.
Projects belong to workspaces.
Tasks roll up through epics and sprints.
RBAC is scoped, not global.
Comments and attachments are flexible by design.
6. Strengths of This Schema
Enterprise‑grade security model
Clear domain boundaries
Strong auditability
Designed for feature growth
Conscious trade‑offs
7. Weaknesses & Trade‑offs
Polymorphic relations rely on application enforcement
Soft deletes increase query complexity
Higher cognitive load for new contributors
These are accepted costs, not mistakes.
8. Closing Note
This schema prioritizes clarity of intent over simplicity of implementation. The complexity was intentional, because real systems are complex — pretending otherwise only delays the pain.
That said, no design is perfect.
If you’re reading this and something made you pause — good. If you see a flaw, a missing edge case, or a better approach, that’s even better. Architecture improves through critique, not silence.
💬 I actively invite you to share your thoughts:
What would you change?
What feels over‑engineered?
What feels under‑engineered?
What would break at scale?
Comments, improvements, counter‑arguments, and hard questions are all welcome. This system is meant to evolve — and good ideas deserve a place in that evolution.
9. Db diagram Link
I have also created database design diagram using dbdiagrams.io
Link : https://dbdiagram.io/d/Project-Management-System-694804234bbde0fd74edddaa
