Skip to content

SCHEMA_DOCUMENTATION.md

Latest commit

 

History

History
451 lines (359 loc) · 15.3 KB

SCHEMA_DOCUMENTATION.md

File metadata and controls

451 lines (359 loc) · 15.3 KB
Raw

StellarAid Database Schema Documentation

Overview

This document provides a comprehensive overview of the StellarAid database schema design, including all models, relationships, constraints, and design decisions.

Status: ✅ Schema validated and ready for first migration

Enums

UserRole

Defines user types in the system:

  • DONOR: Users who contribute funds to campaigns
  • CREATOR: Users who create fundraising campaigns
  • ADMIN: Administrative users with elevated privileges

CampaignStatus

Tracks the lifecycle of campaigns:

  • DRAFT: Campaign not yet submitted for approval
  • PENDING_APPROVAL: Awaiting admin review
  • ACTIVE: Approved and actively fundraising
  • COMPLETED: Campaign goal reached or deadline passed
  • CANCELLED: Creator cancelled the campaign
  • REJECTED: Admin rejected the campaign

DonationStatus

Tracks donation transaction states:

  • PENDING: Donation initiated, awaiting blockchain confirmation
  • CONFIRMED: Donation successfully confirmed on blockchain
  • REFUNDED: Donor initiated a refund
  • FAILED: Donation transaction failed

MilestoneStatus

Tracks milestone progress:

  • PENDING: Milestone not yet active
  • ACTIVE: Milestone currently in progress
  • COMPLETED: Milestone targets achieved
  • FAILED: Milestone target not reached by deadline

NotificationType

Types of notifications sent to users:

  • CAMPAIGN_CREATED: New campaign notification
  • CAMPAIGN_UPDATED: Campaign update notification
  • DONATION_RECEIVED: Campaign creator notified of donation
  • MILESTONE_REACHED: Notification when milestone is achieved
  • CAMPAIGN_COMPLETED: Campaign completion notification
  • DISPUTE_FILED: Dispute notification
  • DISPUTE_RESOLVED: Dispute resolution notification

DisputeStatus

Tracks dispute lifecycle:

  • OPENED: Dispute freshly filed
  • UNDER_REVIEW: Admin reviewing the dispute
  • RESOLVED: Dispute has been resolved
  • REJECTED: Dispute was invalid or rejected

AuditActionType

Types of auditable actions:

  • USER_CREATED: New user registration
  • CAMPAIGN_CREATED: New campaign created
  • CAMPAIGN_UPDATED: Campaign details modified
  • DONATION_MADE: Donation transaction recorded
  • MILESTONE_COMPLETED: Milestone marked complete
  • DISPUTE_FILED: New dispute opened
  • ADMIN_ACTION: Generic admin action

Data Models

User

Core user entity representing donors, creators, and administrators.

Fields:

  • id (UUID, PK): Unique identifier
  • email (String, UNIQUE): User email address
  • name (String, NULLABLE): User display name
  • role (UserRole, DEFAULT: DONOR): User type
  • walletAddress (String, UNIQUE, NULLABLE): Stellar blockchain wallet
  • bio (String, NULLABLE): User biography
  • isActive (Boolean, DEFAULT: true): Account activation status
  • createdAt (DateTime, DEFAULT: now()): Account creation timestamp
  • updatedAt (DateTime, AUTO): Last update timestamp

Relationships:

  • campaigns ← Campaign (one-to-many): Campaigns created by user
  • donations ← Donation (one-to-many): Donations made by user
  • updates ← Update (one-to-many): Campaign updates posted by user
  • notifications ← Notification (one-to-many): Notifications for user
  • disputes ← Dispute (one-to-many): Disputes filed by user
  • newsLetterSub ← Newsletter (one-to-one, NULLABLE): Newsletter subscription
  • auditLogs ← AuditLog (one-to-many): Audit logs generated by user

Indices:

  • email: Quick user lookups
  • role: Filter users by role
  • isActive: Filter active/inactive users

Constraints:

  • Email must be unique (prevents duplicate accounts)
  • Wallet address must be unique (one wallet per user)

Campaign

Represents fundraising campaigns on the platform.

Fields:

  • id (UUID, PK): Unique identifier
  • title (String): Campaign title
  • description (String): Campaign description
  • goalAmount (Decimal): Target fundraising amount
  • raisedAmount (Decimal, DEFAULT: 0): Current amount raised
  • status (CampaignStatus, DEFAULT: DRAFT): Campaign state
  • creatorId (String, FK): Reference to campaign creator
  • startDate (DateTime, NULLABLE): Campaign start date
  • endDate (DateTime, NULLABLE): Campaign deadline
  • imageUrl (String, NULLABLE): Campaign cover image URL
  • category (String, NULLABLE): Campaign category/tag
  • createdAt (DateTime, DEFAULT: now()): Creation timestamp
  • updatedAt (DateTime, AUTO): Last update timestamp

Relationships:

  • creator → User: Creator of campaign (REQUIRED, CASCADE delete)
  • donations ← Donation (one-to-many): Donations received
  • milestones ← Milestone (one-to-many): Campaign milestones
  • updates ← Update (one-to-many): Campaign progress updates
  • disputes ← Dispute (one-to-many): Related disputes

Indices:

  • creatorId: Find campaigns by creator
  • status: Filter by campaign status
  • createdAt: Sort campaigns chronologically
  • category: Filter by category

Constraints:

  • Creator reference is required and cascades on delete
  • raisedAmount tracks total donations (updated programmatically)

Donation

Individual donations from donors to campaigns.

Fields:

  • id (UUID, PK): Unique identifier
  • amount (Decimal): Donation amount
  • assetCode (String, DEFAULT: "XLM"): Blockchain asset code
  • txHash (String, UNIQUE, NULLABLE): Blockchain transaction hash
  • status (DonationStatus, DEFAULT: PENDING): Donation state
  • donorId (String, FK): Reference to donor
  • campaignId (String, FK): Reference to target campaign
  • donatedAt (DateTime, DEFAULT: now()): Donation timestamp
  • confirmedAt (DateTime, NULLABLE): Confirmation timestamp
  • createdAt (DateTime, DEFAULT: now()): Record creation time
  • updatedAt (DateTime, AUTO): Last update timestamp

Relationships:

  • donor → User: Donation maker (REQUIRED, CASCADE delete)
  • campaign → Campaign: Target campaign (REQUIRED, CASCADE delete)
  • disputes ← Dispute (one-to-many, NULLABLE): Related disputes

Indices:

  • donorId: Find donations by donor
  • campaignId: Find donations to campaign
  • status: Filter by status
  • createdAt: Sort by date

Constraints:

  • Composite Unique: (donorId, campaignId, txHash) - prevents duplicate donations
  • Singular Unique: txHash - blockchain transaction hash is globally unique
  • Both foreign keys cascade on delete

Milestone

Campaign milestone tracking for goal progression.

Fields:

  • id (UUID, PK): Unique identifier
  • campaignId (String, FK): Reference to parent campaign
  • title (String): Milestone title
  • description (String, NULLABLE): Milestone description
  • targetAmount (Decimal): Amount to raise for this milestone
  • status (MilestoneStatus, DEFAULT: PENDING): Milestone state
  • dueDate (DateTime, NULLABLE): Target completion date
  • completedAt (DateTime, NULLABLE): Actual completion timestamp
  • createdAt (DateTime, DEFAULT: now()): Creation timestamp
  • updatedAt (DateTime, AUTO): Last update timestamp

Relationships:

  • campaign → Campaign: Parent campaign (REQUIRED, CASCADE delete)

Indices:

  • campaignId: Find milestones for campaign
  • status: Filter by milestone status

Constraints:

  • Campaign reference required and cascades on delete

Update

Campaign progress updates posted by creators.

Fields:

  • id (UUID, PK): Unique identifier
  • campaignId (String, FK): Reference to campaign
  • creatorId (String, FK): Reference to update author
  • title (String): Update title
  • content (String): Update content/body
  • imageUrl (String, NULLABLE): Update image URL
  • createdAt (DateTime, DEFAULT: now()): Creation timestamp
  • updatedAt (DateTime, AUTO): Last update timestamp

Relationships:

  • campaign → Campaign: Parent campaign (REQUIRED, CASCADE delete)
  • creator → User: Update author (REQUIRED, CASCADE delete)

Indices:

  • campaignId: Find updates for campaign
  • creatorId: Find updates by creator
  • createdAt: Sort chronologically

Constraints:

  • Both foreign keys cascade on delete

Notification

User notifications system.

Fields:

  • id (UUID, PK): Unique identifier
  • userId (String, FK): Reference to recipient
  • type (NotificationType): Notification category
  • title (String): Notification title
  • message (String): Notification message
  • relatedId (String, NULLABLE): ID of related resource (campaign, donation, etc.)
  • isRead (Boolean, DEFAULT: false): Read status
  • createdAt (DateTime, DEFAULT: now()): Creation timestamp
  • updatedAt (DateTime, AUTO): Last update timestamp

Relationships:

  • user → User: Notification recipient (REQUIRED, CASCADE delete)

Indices:

  • userId: Find notifications for user
  • isRead: Filter read/unread notifications
  • createdAt: Sort by date

Constraints:

  • User reference required and cascades on delete

Dispute

Donation dispute tracking and resolution.

Fields:

  • id (UUID, PK): Unique identifier
  • donationId (String, FK, UNIQUE): Reference to disputed donation
  • filerId (String, FK): Reference to dispute filer
  • campaignId (String, FK): Reference to campaign
  • reason (String): Reason for dispute
  • description (String): Detailed description
  • status (DisputeStatus, DEFAULT: OPENED): Dispute state
  • resolution (String, NULLABLE): Resolution details
  • resolvedAt (DateTime, NULLABLE): Resolution timestamp
  • createdAt (DateTime, DEFAULT: now()): Creation timestamp
  • updatedAt (DateTime, AUTO): Last update timestamp

Relationships:

  • donation → Donation: Disputed donation (REQUIRED, CASCADE delete)
  • filer → User: Dispute filer (REQUIRED, CASCADE delete)
  • campaign → Campaign: Related campaign (REQUIRED, CASCADE delete)

Indices:

  • filerId: Find disputes by filer
  • campaignId: Find disputes for campaign
  • status: Filter by dispute status
  • createdAt: Sort by date

Constraints:

  • Unique: donationId - only one active dispute per donation
  • All foreign keys cascade on delete

Newsletter

Newsletter subscription management.

Fields:

  • id (UUID, PK): Unique identifier
  • userId (String, FK, UNIQUE): Reference to subscriber
  • email (String, UNIQUE): Subscriber email address
  • isSubscribed (Boolean, DEFAULT: true): Subscription status
  • subscribedAt (DateTime, DEFAULT: now()): Subscription timestamp
  • unsubscribedAt (DateTime, NULLABLE): Unsubscription timestamp
  • createdAt (DateTime, DEFAULT: now()): Creation timestamp
  • updatedAt (DateTime, AUTO): Last update timestamp

Relationships:

  • user → User: Subscriber (REQUIRED, CASCADE delete)

Indices:

  • email: Quick email lookups
  • isSubscribed: Find active subscribers

Constraints:

  • Both userId and email are unique (one subscription per user/email)
  • User reference cascades on delete

AuditLog

System audit trail for compliance and debugging.

Fields:

  • id (UUID, PK): Unique identifier
  • userId (String, FK, NULLABLE): Reference to user performing action
  • action (AuditActionType): Type of action
  • resourceType (String): Type of resource affected
  • resourceId (String): ID of affected resource
  • details (String, NULLABLE): JSON stringified additional details
  • ipAddress (String, NULLABLE): IP address of request origin
  • userAgent (String, NULLABLE): Browser/client user agent
  • createdAt (DateTime, DEFAULT: now()): Action timestamp

Relationships:

  • user → User: User performing action (NULLABLE, SET NULL on delete)

Indices:

  • userId: Find actions by user
  • action: Filter by action type
  • resourceType: Filter by resource type
  • createdAt: Sort chronologically

Constraints:

  • User reference is optional (allows recording actions even after user deletion)
  • Uses SET NULL on delete instead of CASCADE (preserves audit history)

Data Integrity Features

Cascade Delete Strategy

The following models cascade delete when parent is deleted:

  • Campaign → Donations, Milestones, Updates, Disputes
  • Donation → Disputes
  • User → Campaigns, Donations, Updates, Notifications, Newsletter

This ensures:

  • ✅ Data consistency (no orphaned records)
  • ✅ Clean removal of resources and all dependencies
  • ✅ Automatic cleanup of related data

Referential Integrity

  • NOT NULL constraints on all foreign key fields (except AuditLog.userId)
  • CASCADE DELETE enforces relationship integrity
  • UNIQUE constraints prevent duplicate entries

Audit Trail

  • All models include createdAt and updatedAt timestamps
  • AuditLog tracks all significant actions
  • IP addresses and user agents captured for security

Data Consistency

  • Composite unique constraints (e.g., Donation) prevent duplicates
  • Status fields maintain valid state transitions
  • raisedAmount in Campaign updated programmatically

Performance Optimizations

Strategic Indexing

All foreign keys indexed for:

  • Fast relationship lookups (JOIN performance)
  • Quick filtering by creator/donor
  • Efficient sorting and pagination

Category indices for:

  • Campaign filtering and discovery
  • User role-based queries

Timestamp indices for:

  • Chronological sorting
  • Date-range queries
  • Audit trail navigation

Query Optimization Recommendations

  1. Campaign Discovery: Use compound indices (status, createdAt)
  2. User Donations: Filter by (donorId, status, createdAt)
  3. Campaign Milestones: Query by campaignId first, then status
  4. Notifications: Always filter by userId first

Future Extensibility

Planned Enhancements

  • Comments: Add Comment model for community engagement
  • Reviews/Ratings: Add Review model for campaign ratings
  • Tags: Expand category field or add Tags model
  • Social Links: Extend User model with social profiles
  • Media Gallery: Extend Update with multiple media support
  • Payment Methods: Add PaymentMethod model for wallet diversity

Schema Migration Path

This schema is designed to support incremental feature additions without major restructuring.

Migration Checklist

Before running the first migration:

  • All 9 models defined: User, Campaign, Donation, Milestone, Update, Notification, Dispute, Newsletter, AuditLog
  • All required enums: UserRole, CampaignStatus, DonationStatus, MilestoneStatus, NotificationType, DisputeStatus, AuditActionType
  • Foreign keys and relationships defined
  • Cascade delete strategies implemented
  • Composite and unique constraints in place
  • Strategic indices on all foreign keys and frequently-queried fields
  • Timestamp tracking on all models
  • Schema validation passes: ✅
  • Schema reviewed and approved by team
  • First migration created: npx prisma migrate dev --name init
  • Database seeded with test data (optional)
  • TypeScript types generated: npx prisma generate

Review Notes

Total Models: 9 Total Enums: 7 Total Relationships: 22 Indices: 30+ Constraints: 12+

Design Principles Applied:

  1. ✅ Single Responsibility: Each model has a clear, focused purpose
  2. ✅ Domain Consistency: Naming and structure reflect StellarAid domain
  3. ✅ Referential Integrity: Strong relationships with cascading deletes
  4. ✅ Audit Trail: AuditLog captures all significant actions
  5. ✅ Performance: Strategic indices on all foreign keys and search fields
  6. ✅ Extensibility: Flexible design for future features
  7. ✅ Data Consistency: Unique constraints and status enums ensure valid states