Page:
Database-Schema
Clone
Contents
Table of Contents
- Database Schema
- Tables Overview
- Detailed Schema
- #__mokosuitenpo_donors
- #__mokosuitenpo_funds
- #__mokosuitenpo_donations
- #__mokosuitenpo_pledges
- #__mokosuitenpo_campaigns
- #__mokosuitenpo_grants
- #__mokosuitenpo_volunteers
- #__mokosuitenpo_volunteer_hours
- #__mokosuitenpo_memberships
- #__mokosuitenpo_tax_receipts
- #__mokosuitenpo_events
- #__mokosuitenpo_event_registrations
Database Schema
MokoSuite NPO creates 12 tables in the #__mokosuitenpo_ namespace. All tables use InnoDB engine with utf8mb4 charset.
Tables Overview
| Table | Purpose | Key Relationships |
|---|---|---|
#__mokosuitenpo_donors |
Donor profiles extending CRM contacts | FK → #__contact_details |
#__mokosuitenpo_funds |
Restricted/unrestricted fund tracking | FK → ERP chart of accounts |
#__mokosuitenpo_donations |
Individual gifts with fund allocation | FK → donors, funds, campaigns |
#__mokosuitenpo_pledges |
Promised future donations | FK → donors, funds, campaigns |
#__mokosuitenpo_campaigns |
Fundraising drives with goals | FK → funds |
#__mokosuitenpo_grants |
Grant application/award lifecycle | FK → CRM contacts, funds |
#__mokosuitenpo_volunteers |
Volunteer profiles with skills | FK → #__contact_details |
#__mokosuitenpo_volunteer_hours |
Volunteer hours log | FK → volunteers |
#__mokosuitenpo_memberships |
Dues-based membership program | FK → #__contact_details |
#__mokosuitenpo_tax_receipts |
IRS-compliant donation receipts | FK → donors, donations |
#__mokosuitenpo_events |
Fundraisers, galas, community events | FK → campaigns |
#__mokosuitenpo_event_registrations |
Event attendance tracking | FK → events, contacts |
Detailed Schema
#__mokosuitenpo_donors
Extends CRM contacts with NPO-specific giving data.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
contact_id |
INT NOT NULL | FK to #__contact_details (unique) |
donor_type |
ENUM | individual, corporate, foundation, government, anonymous |
donor_level |
ENUM | prospect, first_time, repeat, major, legacy, lapsed |
lifetime_giving |
DECIMAL(15,2) | Running total of all gifts |
largest_gift |
DECIMAL(15,2) | Largest single donation |
first_gift_date |
DATE | Date of first donation |
last_gift_date |
DATE | Date of most recent donation |
gift_count |
INT UNSIGNED | Number of donations |
preferred_fund |
INT UNSIGNED | Default fund for donations |
communication_preference |
ENUM | email, mail, phone, none |
tax_id |
VARCHAR(50) | EIN for corporate donors |
recognition_name |
VARCHAR(255) | Public recognition name |
anonymous_giving |
TINYINT | Prefer anonymous |
notes |
TEXT | Internal notes |
Indexes: idx_contact (unique), idx_level, idx_type, idx_lifetime
#__mokosuitenpo_funds
Restricted vs unrestricted fund tracking with GL account links.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
name |
VARCHAR(255) | Fund name |
code |
VARCHAR(20) | Short code (unique) |
fund_type |
ENUM | unrestricted, temporarily_restricted, permanently_restricted, endowment, operating, capital |
description |
TEXT | Fund description |
gl_account_id |
INT UNSIGNED | FK to ERP chart of accounts |
target_amount |
DECIMAL(15,2) | Fund goal |
current_balance |
DECIMAL(15,2) | Current balance |
published |
TINYINT | Active/inactive |
Indexes: idx_code (unique), idx_type
#__mokosuitenpo_donations
Individual gifts with fund allocation and payment tracking.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
donor_id |
INT UNSIGNED | FK to donors |
fund_id |
INT UNSIGNED | FK to funds (default: 1) |
campaign_id |
INT UNSIGNED | FK to campaigns (nullable) |
amount |
DECIMAL(15,2) | Donation amount |
donation_type |
ENUM | cash, check, credit_card, ach, stock, crypto, in_kind, pledge, matching |
donation_date |
DATE | Date of gift |
payment_method |
VARCHAR(50) | Payment processor |
payment_reference |
VARCHAR(100) | Check number, transaction ID |
is_tax_deductible |
TINYINT | Tax deductible flag |
fair_market_value |
DECIMAL(15,2) | For in-kind donations |
in_kind_description |
TEXT | Description of in-kind gift |
is_recurring |
TINYINT | Recurring flag |
recurring_frequency |
ENUM | weekly, monthly, quarterly, annually |
recurring_end_date |
DATE | Recurring donation end date |
tribute_type |
ENUM | in_honor, in_memory |
tribute_name |
VARCHAR(255) | Tribute honoree |
receipt_sent |
TINYINT | Receipt sent flag |
receipt_sent_date |
DATE | Date receipt was sent |
acknowledgment_sent |
TINYINT | Acknowledgment sent flag |
order_id |
INT UNSIGNED | FK to CRM orders |
journal_entry_id |
INT UNSIGNED | FK to ERP journal entries |
Indexes: idx_donor, idx_fund, idx_campaign, idx_date, idx_type, idx_recurring
#__mokosuitenpo_pledges
Promised future donations with installment tracking.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
donor_id |
INT UNSIGNED | FK to donors |
fund_id |
INT UNSIGNED | FK to funds |
campaign_id |
INT UNSIGNED | FK to campaigns |
total_amount |
DECIMAL(15,2) | Total pledged |
amount_fulfilled |
DECIMAL(15,2) | Amount paid so far |
pledge_date |
DATE | Date pledged |
due_date |
DATE | Final due date |
frequency |
ENUM | one_time, monthly, quarterly, annually |
installments |
INT UNSIGNED | Number of installments |
status |
ENUM | active, fulfilled, partial, cancelled, lapsed |
Indexes: idx_donor, idx_status, idx_due
#__mokosuitenpo_campaigns
Fundraising drives with goals and progress tracking.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
title |
VARCHAR(255) | Campaign name |
description |
TEXT | Campaign description |
campaign_type |
ENUM | annual, capital, endowment, emergency, event, peer_to_peer, crowdfunding, grant_match |
fund_id |
INT UNSIGNED | FK to funds |
goal_amount |
DECIMAL(15,2) | Fundraising goal |
raised_amount |
DECIMAL(15,2) | Amount raised |
donor_count |
INT UNSIGNED | Number of donors |
start_date / end_date |
DATE | Campaign period |
status |
ENUM | planning, active, paused, completed, cancelled |
public_page |
TINYINT | Show on public donation page |
thermometer |
TINYINT | Show progress thermometer |
image |
VARCHAR(500) | Campaign image |
Indexes: idx_status, idx_type, idx_dates
#__mokosuitenpo_grants
Grant application, award, and reporting lifecycle.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
title |
VARCHAR(255) | Grant title |
funder_name |
VARCHAR(255) | Granting organization |
funder_contact_id |
INT | FK to CRM contacts |
amount_requested |
DECIMAL(15,2) | Amount applied for |
amount_awarded |
DECIMAL(15,2) | Amount received |
fund_id |
INT UNSIGNED | FK to funds |
status |
ENUM | prospect, writing, submitted, pending, awarded, declined, reporting, closed |
application_deadline |
DATE | Submission deadline |
submitted_date |
DATE | Date submitted |
award_date |
DATE | Date awarded |
start_date / end_date |
DATE | Grant period |
reporting_frequency |
ENUM | monthly, quarterly, semi_annual, annual, final |
next_report_due |
DATE | Next report deadline |
match_required |
TINYINT | Matching requirement |
match_ratio |
VARCHAR(20) | e.g., 1:1, 2:1 |
restrictions |
TEXT | Grant restrictions/requirements |
Indexes: idx_status, idx_funder, idx_deadline, idx_report_due
#__mokosuitenpo_volunteers
Volunteer profiles with skills and availability.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
contact_id |
INT NOT NULL | FK to #__contact_details (unique) |
status |
ENUM | prospect, active, inactive, alumni |
skills |
JSON | Skills array |
availability |
JSON | Day-of-week availability |
total_hours |
DECIMAL(10,2) | Cumulative hours |
start_date |
DATE | Volunteer start date |
background_check |
TINYINT | Background check completed |
background_check_date |
DATE | Date of background check |
emergency_contact_name |
VARCHAR(255) | Emergency contact |
emergency_contact_phone |
VARCHAR(50) | Emergency contact phone |
t_shirt_size |
VARCHAR(10) | For event gear |
Indexes: idx_contact (unique), idx_status
#__mokosuitenpo_volunteer_hours
Individual volunteer hour log entries.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
volunteer_id |
INT UNSIGNED | FK to volunteers |
activity |
VARCHAR(255) | Activity description |
hours |
DECIMAL(5,2) | Hours worked |
volunteer_date |
DATE | Date of service |
supervisor |
VARCHAR(255) | Supervising staff |
approved |
TINYINT | Approval status |
approved_by |
INT | Approving user |
Indexes: idx_volunteer, idx_date, idx_approved
#__mokosuitenpo_memberships
Dues-based membership program with tiered levels.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
contact_id |
INT NOT NULL | FK to contacts |
membership_level |
ENUM | basic, silver, gold, platinum, lifetime, honorary |
start_date / end_date |
DATE | Membership period |
annual_dues |
DECIMAL(10,2) | Annual fee |
status |
ENUM | active, expired, cancelled, pending, grace |
auto_renew |
TINYINT | Auto-renewal flag |
member_number |
VARCHAR(50) | Unique member ID |
joined_date |
DATE | Original join date |
Indexes: idx_contact, idx_level, idx_status, idx_end, idx_member_number (unique)
#__mokosuitenpo_tax_receipts
IRS-compliant donation acknowledgments.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
donation_id |
INT UNSIGNED | FK to donations |
donor_id |
INT UNSIGNED | FK to donors |
receipt_number |
VARCHAR(50) | Unique receipt number |
tax_year |
INT | Tax year |
amount |
DECIMAL(15,2) | Receipt amount |
date_issued |
DATE | Issue date |
delivery_method |
ENUM | email, mail, both |
sent |
TINYINT | Sent flag |
sent_date |
DATETIME | Date sent |
file_path |
VARCHAR(500) | PDF file path |
Indexes: idx_receipt_number (unique), idx_donor, idx_year, idx_donation
#__mokosuitenpo_events
Fundraisers, galas, and community events.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
title |
VARCHAR(255) | Event name |
description |
TEXT | Event description |
event_type |
ENUM | gala, auction, walkathon, golf, concert, dinner, community, virtual, hybrid |
campaign_id |
INT UNSIGNED | FK to campaigns |
venue |
VARCHAR(255) | Event location |
address |
TEXT | Full address |
start_date / end_date |
DATETIME | Event period |
ticket_price |
DECIMAL(10,2) | Ticket cost |
capacity |
INT UNSIGNED | Max attendees |
registered |
INT UNSIGNED | Current registrations |
revenue_goal |
DECIMAL(15,2) | Revenue target |
actual_revenue |
DECIMAL(15,2) | Actual revenue collected |
status |
ENUM | planning, registration_open, sold_out, in_progress, completed, cancelled |
public |
TINYINT | Publicly visible |
image |
VARCHAR(500) | Event image |
Indexes: idx_campaign, idx_type, idx_status, idx_date
#__mokosuitenpo_event_registrations
Event attendance and registration tracking.
| Column | Type | Description |
|---|---|---|
id |
INT UNSIGNED PK | Auto-increment |
event_id |
INT UNSIGNED | FK to events |
contact_id |
INT | FK to contacts |
guest_name |
VARCHAR(255) | Registrant name |
guest_email |
VARCHAR(255) | Registrant email |
guest_phone |
VARCHAR(50) | Registrant phone |
ticket_count |
INT UNSIGNED | Number of tickets |
amount_paid |
DECIMAL(10,2) | Payment amount |
dietary_restrictions |
VARCHAR(500) | Dietary needs |
table_assignment |
VARCHAR(50) | Seating |
status |
ENUM | registered, confirmed, attended, cancelled, no_show |
checked_in_at |
DATETIME | Check-in timestamp |
Indexes: idx_event, idx_contact, idx_status