carlosarias/seedproject-api
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
seedproject-api/.reference_files/schema.sql
Carlos Arias a447b17e69 Initial commit: SeedProject API framework 
PHP MVC seed project with router, database layer, auth helpers,
LLM provider integrations, Stripe gateway, and console commands.

Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01VVcDB9cRQZRRmUivMHRQGL
2026-09-14 16:15:41 -05:00

808 lines
32 KiB
SQL
Raw Permalink Blame History

-- cpenginev2 schema dump
-- Generated: 2026-03-29 21:00:53
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table: cpe_agent_messages
-- ----------------------------
CREATE TABLE `cpe_agent_messages` (
`msgid` int(11) NOT NULL AUTO_INCREMENT,
`sessionid` int(11) NOT NULL,
`agentid` int(11) NOT NULL DEFAULT 0,
`role` enum('user','assistant') NOT NULL,
`content` longtext DEFAULT NULL,
`latency_ms` int(11) DEFAULT NULL,
`input_tokens` int(11) DEFAULT NULL,
`output_tokens` int(11) DEFAULT NULL,
`cost_usd` decimal(10,6) DEFAULT NULL,
`createdate` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`msgid`),
KEY `idx_sessionid` (`sessionid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ----------------------------
-- Table: cpe_agent_sessions
-- ----------------------------
CREATE TABLE `cpe_agent_sessions` (
`sessionid` int(11) NOT NULL AUTO_INCREMENT,
`agentid` int(11) NOT NULL DEFAULT 0,
`orgid` int(11) NOT NULL DEFAULT 0,
`crid` int(11) DEFAULT NULL COMMENT 'cpe_creditpulls.crid — NULL = demo/mock mode',
`provider` varchar(50) DEFAULT NULL COMMENT 'anthropic, openai, etc.',
`model` varchar(100) DEFAULT NULL,
`message_count` int(11) NOT NULL DEFAULT 0,
`createdate` datetime NOT NULL DEFAULT current_timestamp(),
`updatedate` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`sessionid`),
KEY `idx_agentid` (`agentid`),
KEY `idx_crid` (`crid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
-- ----------------------------
-- Table: cpe_aiassistant
-- ----------------------------
CREATE TABLE `cpe_aiassistant` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`agentid` int(11) DEFAULT 0,
`orgid` int(11) DEFAULT 0,
`crid` int(11) DEFAULT 0,
`promptid` int(11) DEFAULT 0,
`response` mediumtext DEFAULT NULL,
`jsonrequest` mediumtext DEFAULT NULL,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
-- ----------------------------
-- Table: cpe_creditpulls
-- ----------------------------
CREATE TABLE `cpe_creditpulls` (
`crid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(1) DEFAULT 0,
`agentid` int(1) DEFAULT 0,
`priceid` varchar(255) DEFAULT '0',
`invid` int(5) DEFAULT 0,
`profid` varchar(255) DEFAULT '0',
`custname` varchar(255) DEFAULT '0',
`custdob` varchar(255) DEFAULT '0',
`last4ss` varchar(255) DEFAULT '0',
`success` int(5) DEFAULT 0,
`apicall` int(1) NOT NULL DEFAULT 0,
`cpejson` text DEFAULT NULL,
`createdate` datetime NOT NULL DEFAULT current_timestamp(),
`status` int(5) DEFAULT 0,
`active` int(1) DEFAULT 1,
PRIMARY KEY (`crid`),
KEY `idx_org_history_id_orgid` (`crid`,`orgid`),
KEY `idx_org_history_id_orgid_custID` (`crid`,`orgid`)
) ENGINE=MyISAM AUTO_INCREMENT=1140 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: cpe_creditpulls_temp
-- ----------------------------
CREATE TABLE `cpe_creditpulls_temp` (
`cpeid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) DEFAULT 0,
`evni` varchar(255) DEFAULT '0',
`rawjson` mediumtext DEFAULT NULL,
`directory` varchar(255) DEFAULT '0',
`jsonfilename` varchar(255) DEFAULT '0',
`createdate` datetime DEFAULT current_timestamp(),
`active` int(11) DEFAULT 1,
PRIMARY KEY (`cpeid`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
-- ----------------------------
-- Table: cpe_org_pricing
-- ----------------------------
CREATE TABLE `cpe_org_pricing` (
`priceid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) NOT NULL,
`package_name` varchar(100) DEFAULT 'Credit Report Package',
`retail_price` decimal(6,2) NOT NULL COMMENT 'Total price client pays',
`our_price` decimal(6,2) NOT NULL COMMENT 'What all services cost us combined',
`includes_fico` tinyint(1) DEFAULT 0,
`includes_vantage` tinyint(1) DEFAULT 0,
`includes_idscan` tinyint(1) DEFAULT 0,
`includes_directory` tinyint(1) DEFAULT 0,
`active` tinyint(1) DEFAULT 1,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`priceid`),
UNIQUE KEY `orgid` (`orgid`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: cpe_orgs_documents
-- ----------------------------
CREATE TABLE `cpe_orgs_documents` (
`docid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) DEFAULT 0,
`userid` int(11) DEFAULT 0,
`document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
`type` varchar(255) DEFAULT '0',
`location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
`status` int(11) DEFAULT 0,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`docid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Table: cpe_products
-- ----------------------------
CREATE TABLE `cpe_products` (
`prodid` int(11) NOT NULL AUTO_INCREMENT,
`provider` varchar(20) DEFAULT '0',
`product_code` varchar(20) NOT NULL DEFAULT '0' COMMENT 'CR, FICO, VANTAGE, IDSCAN, DIRECTORY',
`product_name` varchar(100) NOT NULL DEFAULT '0',
`our_cost` decimal(6,2) NOT NULL DEFAULT 0.00 COMMENT 'What we pay Equifax/TransUnion',
`is_base` tinyint(1) DEFAULT 0 COMMENT '1 for Credit Report base, 0 for add-ons',
`active` tinyint(1) DEFAULT 1,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`prodid`),
UNIQUE KEY `product_code` (`product_code`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: cpe_tempreports
-- ----------------------------
CREATE TABLE `cpe_tempreports` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`agentid` int(11) DEFAULT NULL,
`orgid` int(11) DEFAULT NULL,
`template` varchar(255) DEFAULT '0',
`ceatedate` datetime DEFAULT current_timestamp(),
`active` int(1) DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_activitylog
-- ----------------------------
CREATE TABLE `sp_activitylog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`trigval` varchar(255) DEFAULT NULL,
`controller` varchar(64) DEFAULT NULL,
`action` varchar(64) DEFAULT NULL,
`method` varchar(8) DEFAULT NULL,
`useragent` varchar(512) DEFAULT NULL,
`device_type` varchar(16) DEFAULT NULL,
`browser` varchar(64) DEFAULT NULL,
`os` varchar(64) DEFAULT NULL,
`msgval` text DEFAULT NULL,
`device` varchar(255) DEFAULT NULL,
`host` varchar(255) DEFAULT NULL,
`location` varchar(255) DEFAULT NULL,
`ipaddress` varchar(255) DEFAULT '000.000.000',
`public` int(11) DEFAULT NULL,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_activitylog_userid_trigval_date` (`userid`,`trigval`,`createdate` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=420 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_api_requests
-- ----------------------------
CREATE TABLE `sp_api_requests` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`apikeyid` varchar(11) DEFAULT '0',
`userid` int(11) DEFAULT 0,
`requesting_ip` varchar(255) DEFAULT '0',
`request` varchar(255) DEFAULT '0',
`service` varchar(255) DEFAULT '0',
`apitype` varchar(255) DEFAULT '0' COMMENT 'tracker, or api',
`sitepage` varchar(255) DEFAULT '0',
`useragent` varchar(255) DEFAULT '0',
`sessionid` varchar(255) DEFAULT '0',
`sessiontime` varchar(255) DEFAULT '0',
`visitduration` varchar(255) DEFAULT '0',
`devicetype` varchar(255) DEFAULT '0',
`domain` varchar(255) DEFAULT '0',
`paramsvar` varchar(255) DEFAULT '0',
`referral` varchar(255) DEFAULT '0',
`language` varchar(255) DEFAULT '0',
`ismobile` varchar(255) DEFAULT '0',
`hascookies` varchar(255) DEFAULT '0',
`scrolldepth` varchar(255) DEFAULT '0',
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_userid_createdate` (`userid`,`createdate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_api_usage
-- ----------------------------
CREATE TABLE `sp_api_usage` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`requests` int(11) NOT NULL DEFAULT 0,
`report_date` date NOT NULL,
`report_type` enum('daily','monthly','yearly') NOT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_date_type` (`userid`,`report_date`,`report_type`),
KEY `idx_report_date` (`report_date`),
KEY `idx_report_type` (`report_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Table: sp_broker_commissions
-- ----------------------------
CREATE TABLE `sp_broker_commissions` (
`commission_id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT 0,
`orgid` int(11) DEFAULT 0,
`crid` int(11) DEFAULT 0,
`pull_date` datetime DEFAULT '0000-00-00 00:00:00',
`retail_price` decimal(10,2) NOT NULL DEFAULT 0.00,
`commission_type` varchar(255) DEFAULT '0',
`commission_rate` decimal(10,4) NOT NULL DEFAULT 0.0000,
`flat_fee_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
`commission_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
`payout_eligible_date` datetime DEFAULT '0000-00-00 00:00:00',
`payout_id` int(11) DEFAULT 0,
`paid_date` datetime DEFAULT '0000-00-00 00:00:00',
`status` int(11) DEFAULT 0,
`notes` varchar(255) DEFAULT '0',
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`commission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_broker_orgs
-- ----------------------------
CREATE TABLE `sp_broker_orgs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT 0,
`orgid` int(11) DEFAULT 0,
`commission_override_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
`commission_override_rate` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
`flat_fee_override` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
`start_date` datetime DEFAULT '0000-00-00 00:00:00',
`end_date` datetime DEFAULT '0000-00-00 00:00:00',
`assigned_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
`notes` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
`createdate` datetime DEFAULT current_timestamp(),
`active` int(11) DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Table: sp_broker_payouts
-- ----------------------------
CREATE TABLE `sp_broker_payouts` (
`payout_id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT NULL,
`payout_period_start` datetime DEFAULT '0000-00-00 00:00:00',
`payout_period_end` datetime DEFAULT '0000-00-00 00:00:00',
`total_commissions` decimal(10,2) NOT NULL DEFAULT 0.00,
`total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
`payment_method` varchar(255) DEFAULT NULL,
`payment_reference` varchar(255) DEFAULT NULL,
`payment_date` datetime DEFAULT '0000-00-00 00:00:00',
`status` int(11) DEFAULT NULL,
`processed_by` varchar(255) DEFAULT NULL,
`notes` varchar(255) DEFAULT NULL,
`createdate` datetime DEFAULT current_timestamp(),
`updatedate` datetime DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
PRIMARY KEY (`payout_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_countries
-- ----------------------------
CREATE TABLE `sp_countries` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`code` char(2) NOT NULL COMMENT 'Two-letter country code (ISO 3166-1 alpha-2)',
`name` varchar(64) NOT NULL COMMENT 'English country name',
`full_name` varchar(128) NOT NULL COMMENT 'Full English country name',
`iso3` char(3) NOT NULL COMMENT 'Three-letter country code (ISO 3166-1 alpha-3)',
`number` smallint(3) unsigned zerofill NOT NULL COMMENT 'Three-digit country number (ISO 3166-1 numeric)',
`continent_code` char(2) NOT NULL,
`display_order` int(11) NOT NULL DEFAULT 900,
PRIMARY KEY (`country_id`),
UNIQUE KEY `idx_code` (`code`) USING BTREE,
KEY `idx_continent_code` (`continent_code`) USING BTREE,
CONSTRAINT `sp_countries_ibfk_1` FOREIGN KEY (`continent_code`) REFERENCES `snpi_continents` (`code`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci ROW_FORMAT=COMPACT;
-- ----------------------------
-- Table: sp_cron_tasks
-- ----------------------------
CREATE TABLE `sp_cron_tasks` (
`cronid` int(11) NOT NULL AUTO_INCREMENT,
`keyval` varchar(255) DEFAULT '0',
`varval` varchar(255) DEFAULT '0',
`task` varchar(255) DEFAULT '0',
`response` text DEFAULT NULL,
`completed` datetime DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
`created` datetime DEFAULT current_timestamp(),
`active` int(11) DEFAULT 1,
PRIMARY KEY (`cronid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_email_templates
-- ----------------------------
CREATE TABLE `sp_email_templates` (
`emailtemplateid` int(11) NOT NULL AUTO_INCREMENT,
`type` mediumtext NOT NULL,
`slug` varchar(100) NOT NULL,
`language` varchar(40) DEFAULT NULL,
`name` mediumtext NOT NULL,
`subject` mediumtext NOT NULL,
`message` mediumtext NOT NULL,
`fromname` mediumtext NOT NULL,
`fromemail` varchar(100) DEFAULT NULL,
`plaintext` int(11) NOT NULL DEFAULT 0,
`active` tinyint(4) NOT NULL DEFAULT 0,
`order` int(11) NOT NULL,
PRIMARY KEY (`emailtemplateid`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_master
-- ----------------------------
CREATE TABLE `sp_master` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT '''0''',
`jvalue` text DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_menu_items
-- ----------------------------
CREATE TABLE `sp_menu_items` (
`item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`menu_id` int(10) unsigned NOT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
`label` varchar(100) NOT NULL,
`icon` varchar(100) DEFAULT NULL,
`type` enum('link','header','divider') DEFAULT 'link',
`url` varchar(255) DEFAULT NULL,
`perm_id` int(10) unsigned DEFAULT NULL,
`role_id` int(10) unsigned DEFAULT NULL,
`match_prefix` tinyint(4) DEFAULT 0,
`sort_order` int(11) DEFAULT 0,
`active` tinyint(4) DEFAULT 1,
PRIMARY KEY (`item_id`),
KEY `menu_id` (`menu_id`),
KEY `parent_id` (`parent_id`),
KEY `perm_id` (`perm_id`),
KEY `fk_menu_item_role` (`role_id`),
CONSTRAINT `fk_menu_item_role` FOREIGN KEY (`role_id`) REFERENCES `sp_roles` (`role_id`) ON DELETE SET NULL,
CONSTRAINT `sp_menu_items_ibfk_1` FOREIGN KEY (`perm_id`) REFERENCES `sp_permissions` (`perm_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Table: sp_menus
-- ----------------------------
CREATE TABLE `sp_menus` (
`menu_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`slug` varchar(50) NOT NULL,
`name` varchar(100) NOT NULL,
`active` tinyint(4) DEFAULT 1,
PRIMARY KEY (`menu_id`),
UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Table: sp_orgs
-- ----------------------------
CREATE TABLE `sp_orgs` (
`orgid` int(11) NOT NULL AUTO_INCREMENT,
`catid` int(11) DEFAULT 1,
`name` varchar(255) DEFAULT '0',
`address` varchar(255) DEFAULT '0',
`address2` varchar(255) DEFAULT '0',
`city` varchar(255) DEFAULT '0',
`state` varchar(255) DEFAULT '0',
`zip` int(11) DEFAULT 0,
`country` varchar(255) DEFAULT NULL,
`countrycode` varchar(11) DEFAULT NULL,
`phone` varchar(255) DEFAULT '0',
`email` varchar(255) DEFAULT '0',
`logo` varchar(255) DEFAULT '0',
`website` varchar(255) DEFAULT '0',
`json_social_media` text DEFAULT NULL,
`summary` text DEFAULT NULL,
`description` text DEFAULT NULL,
`updated_date` datetime DEFAULT '0000-00-00 00:00:00',
`taxid` varchar(255) DEFAULT '0',
`defaultorg` int(11) DEFAULT 0,
`creditlimit` varchar(20) DEFAULT '0',
`active` int(11) DEFAULT 1,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`orgid`)
) ENGINE=MyISAM AUTO_INCREMENT=199 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_orgs_category
-- ----------------------------
CREATE TABLE `sp_orgs_category` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'https://developer.bigcommerce.com/api-reference/9cc3a53863922-get-all-categories',
`name` varchar(255) DEFAULT '0',
`parentid` int(11) DEFAULT 0,
`description` text DEFAULT NULL,
`pagetitle` varchar(255) DEFAULT '0',
`image` varchar(255) DEFAULT '0',
`searchkeywords` text DEFAULT NULL,
`active` int(11) DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_orgs_meta
-- ----------------------------
CREATE TABLE `sp_orgs_meta` (
`orgmetaid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) DEFAULT 0,
`keyval` varchar(255) DEFAULT '0',
`metval` text DEFAULT NULL,
`createdate` datetime DEFAULT current_timestamp(),
`active` int(1) DEFAULT 1,
PRIMARY KEY (`orgmetaid`)
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_orgs_users_join
-- ----------------------------
CREATE TABLE `sp_orgs_users_join` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT 0,
`orgid` int(11) DEFAULT 0,
`orgrole` int(11) DEFAULT 0 COMMENT 'orgrole is temp.. Not sure how i want to handle this.. i don''t want multiple role tables.',
`orgadmin` int(11) DEFAULT 0 COMMENT 'orgadmin are account that have access to modifying the org information.',
`guest` int(11) DEFAULT 0 COMMENT 'if user is a guest, or invited into the org. OrgAdmin can''t delete or modify the account. Another way of thinking about it is ownership. Org Doesn''t have Ownership of the account. Can''t modify it.',
`created_date` datetime DEFAULT current_timestamp(),
`active` int(11) DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_permissions
-- ----------------------------
CREATE TABLE `sp_permissions` (
`perm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`perm_controller` varchar(50) NOT NULL DEFAULT '0',
`perm_action` varchar(50) NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT '0',
`viewroleid` varchar(255) DEFAULT '0' COMMENT 'role that can see these options',
`menu` int(11) DEFAULT 0,
`menuorder` int(11) DEFAULT 0,
PRIMARY KEY (`perm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_plugins
-- ----------------------------
CREATE TABLE `sp_plugins` (
`plugin_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`slug` varchar(64) NOT NULL,
`name` varchar(128) NOT NULL,
`version` varchar(20) NOT NULL DEFAULT '1.0.0',
`description` text DEFAULT NULL,
`author` varchar(128) DEFAULT NULL,
`enabled` tinyint(1) NOT NULL DEFAULT 0,
`installed_at` datetime NOT NULL DEFAULT current_timestamp(),
`updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`plugin_id`),
UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Table: sp_referral
-- ----------------------------
CREATE TABLE `sp_referral` (
`refid` int(11) NOT NULL AUTO_INCREMENT,
`refcode` varchar(255) DEFAULT '0',
`refurl` varchar(255) DEFAULT '0',
`ipaddress` varchar(255) DEFAULT '0',
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`refid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_role_perm
-- ----------------------------
CREATE TABLE `sp_role_perm` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_id` int(10) unsigned NOT NULL DEFAULT 0,
`perm_id` int(10) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `role_id` (`role_id`),
KEY `perm_id` (`perm_id`),
CONSTRAINT `sp_role_perm_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `sp_roles` (`role_id`),
CONSTRAINT `sp_role_perm_ibfk_2` FOREIGN KEY (`perm_id`) REFERENCES `sp_permissions` (`perm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_roles
-- ----------------------------
CREATE TABLE `sp_roles` (
`role_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`role_name` varchar(50) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`view` varchar(100) DEFAULT NULL,
`admin` int(11) DEFAULT NULL,
`onteam` int(11) DEFAULT NULL,
`menu` text DEFAULT NULL,
`defaultrole` int(11) DEFAULT NULL COMMENT 'default roleid is for the system to know which id to default the user to.',
`hidemenu` int(11) DEFAULT NULL,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_saas_invoice_activity
-- ----------------------------
CREATE TABLE `sp_saas_invoice_activity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`invid` int(11) NOT NULL,
`orgid` int(11) NOT NULL DEFAULT 0,
`event_type` varchar(64) NOT NULL COMMENT 'created, payment_attempted, payment_succeeded, payment_failed, viewed, etc.',
`label` varchar(255) NOT NULL,
`note` varchar(512) DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_invid` (`invid`),
KEY `idx_orgid` (`orgid`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Table: sp_saas_invoice_items
-- ----------------------------
CREATE TABLE `sp_saas_invoice_items` (
`itemid` int(11) NOT NULL AUTO_INCREMENT,
`invid` int(11) DEFAULT 0,
`priceid` varchar(255) DEFAULT '0',
`description` varchar(255) DEFAULT '0',
`amount` decimal(10,2) DEFAULT 0.00,
`quantity` int(11) DEFAULT 0,
`void` decimal(10,2) DEFAULT 0.00,
`total` decimal(10,2) DEFAULT 0.00,
`createdate` datetime DEFAULT current_timestamp(),
`active` int(11) DEFAULT 1,
PRIMARY KEY (`itemid`),
KEY `idx_invid` (`invid`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_saas_invoices
-- ----------------------------
CREATE TABLE `sp_saas_invoices` (
`invid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) NOT NULL DEFAULT 0,
`subid` varchar(255) DEFAULT '0',
`sinvid` varchar(255) DEFAULT '0',
`stripe_pmtid` varchar(255) NOT NULL DEFAULT '' COMMENT 'pm_xxx that was charged',
`description` varchar(255) DEFAULT '0',
`amountdue` decimal(10,2) DEFAULT 0.00,
`paid` decimal(10,2) DEFAULT 0.00,
`paidlast4` varchar(255) DEFAULT '0',
`paidtype` varchar(255) DEFAULT '0',
`balance` decimal(10,2) DEFAULT 0.00,
`discount` decimal(10,2) DEFAULT 0.00,
`hostedinv` varchar(255) DEFAULT '0',
`effective_at` datetime DEFAULT '0000-00-00 00:00:00',
`due_date` datetime DEFAULT NULL,
`paid_at` datetime DEFAULT NULL,
`canceled_at` datetime DEFAULT '0000-00-00 00:00:00',
`createdate` datetime DEFAULT current_timestamp(),
`status` varchar(255) DEFAULT 'draft' COMMENT 'this will be status like Pending, Paid, Refunded etc..',
`active` int(11) DEFAULT 1,
PRIMARY KEY (`invid`),
KEY `idx_orgid` (`orgid`),
KEY `idx_subid` (`subid`(191)),
KEY `idx_sinvid` (`sinvid`(191)),
KEY `idx_status` (`status`(30))
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_saas_plans
-- ----------------------------
CREATE TABLE `sp_saas_plans` (
`planid` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(255) DEFAULT '0',
`codemonth` varchar(255) DEFAULT NULL,
`codeyear` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`html` mediumtext DEFAULT NULL,
`monthly` decimal(6,2) DEFAULT 0.00,
`yearly` decimal(6,2) DEFAULT 0.00,
`price` decimal(6,2) DEFAULT 0.00,
`limit_minute` int(11) DEFAULT 0,
`credits` int(11) DEFAULT 0,
`active` int(11) DEFAULT 1,
PRIMARY KEY (`planid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_saas_pmtmethods
-- ----------------------------
CREATE TABLE `sp_saas_pmtmethods` (
`pmtid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) NOT NULL DEFAULT 0,
`stripe_cus_id` varchar(255) NOT NULL DEFAULT '',
`userid` int(11) DEFAULT 0,
`spmid` varchar(255) DEFAULT '0' COMMENT 'Stripe Payment Method ID',
`cardname` varchar(255) DEFAULT '0',
`cardtype` varchar(255) DEFAULT '0',
`last4` varchar(255) DEFAULT '0',
`exp_month` tinyint(2) NOT NULL DEFAULT 0,
`exp_year` smallint(4) NOT NULL DEFAULT 0,
`billingzip` varchar(20) NOT NULL DEFAULT '',
`is_primary` tinyint(1) NOT NULL DEFAULT 0,
`createdate` datetime DEFAULT current_timestamp(),
`active` int(11) DEFAULT 1,
PRIMARY KEY (`pmtid`),
UNIQUE KEY `idx_spmid` (`spmid`(191)),
KEY `idx_orgid` (`orgid`),
KEY `idx_userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_saas_stripe_response
-- ----------------------------
CREATE TABLE `sp_saas_stripe_response` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) NOT NULL DEFAULT 0,
`event_type` varchar(100) NOT NULL DEFAULT '' COMMENT 'e.g. charge.succeeded, invoice.paid',
`userid` int(11) DEFAULT 0,
`name` varchar(255) DEFAULT '0',
`email` varchar(255) DEFAULT '0',
`strid` varchar(255) DEFAULT '0',
`requtype` varchar(255) DEFAULT '0',
`amount` decimal(10,2) NOT NULL DEFAULT 0.00,
`chargeid` varchar(255) DEFAULT '0',
`chargecode` varchar(255) DEFAULT '0',
`chargereason` varchar(255) DEFAULT '0',
`chargemsg` varchar(255) DEFAULT '0',
`cardtype` varchar(255) DEFAULT '0',
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_orgid` (`orgid`),
KEY `idx_event_type` (`event_type`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_saas_subscription
-- ----------------------------
CREATE TABLE `sp_saas_subscription` (
`subid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) NOT NULL DEFAULT 0,
`userid` int(11) DEFAULT 0,
`planid` int(11) DEFAULT 0,
`stsubid` varchar(255) DEFAULT '0',
`status` varchar(30) NOT NULL DEFAULT 'active' COMMENT 'active | canceled | past_due | trialing | paused',
`trial_ends_at` datetime DEFAULT NULL,
`current_period_start` datetime DEFAULT NULL,
`current_period_end` datetime DEFAULT NULL,
`cancels_at` date DEFAULT '0000-00-00',
`cancelled_at` datetime DEFAULT '0000-00-00 00:00:00',
`reasoncancelled` varchar(255) DEFAULT '0',
`credits_usage` int(11) DEFAULT 0,
`bonus_credits` int(11) DEFAULT 0,
`nextbilling` datetime DEFAULT '0000-00-00 00:00:00',
`createdate` datetime DEFAULT current_timestamp(),
`updatedate` datetime DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
`active` int(11) DEFAULT 0,
PRIMARY KEY (`subid`),
KEY `idx_orgid` (`orgid`),
KEY `idx_userid` (`userid`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=487 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- ----------------------------
-- Table: sp_settings
-- ----------------------------
CREATE TABLE `sp_settings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`keyval` varchar(255) DEFAULT '0',
`group` varchar(100) NOT NULL DEFAULT 'general',
`metval` text DEFAULT NULL,
`status` tinyint(1) NOT NULL DEFAULT 0,
`encrypted` tinyint(1) NOT NULL DEFAULT 0,
`createdate` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_keyval` (`keyval`),
KEY `idx_group` (`group`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_states
-- ----------------------------
CREATE TABLE `sp_states` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`code` char(2) NOT NULL,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=60 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_users
-- ----------------------------
CREATE TABLE `sp_users` (
`userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT '0',
`password` varchar(64) DEFAULT '0',
`email` varchar(255) DEFAULT '0',
`avatar` varchar(255) DEFAULT '0',
`autho` varchar(255) DEFAULT '0' COMMENT 'this is for google authorization',
`mobile` varchar(255) DEFAULT '0' COMMENT 'otp authorization or 2step',
`address` varchar(255) NOT NULL DEFAULT '',
`city` varchar(255) NOT NULL DEFAULT '',
`state` varchar(255) NOT NULL DEFAULT '',
`zip` varchar(20) NOT NULL DEFAULT '',
`country` varchar(255) NOT NULL DEFAULT '',
`fname` varchar(255) DEFAULT '0',
`lname` varchar(255) DEFAULT '0',
`role_id` int(10) unsigned DEFAULT 2,
`sysadmin` int(11) DEFAULT 0,
`lastlogin` datetime DEFAULT '0000-00-00 00:00:00',
`created_date` datetime DEFAULT current_timestamp(),
`updated_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`ipaddress` varchar(255) DEFAULT '000.000.000.000',
`pwcodedate` datetime DEFAULT '0000-00-00 00:00:00',
`pwcode` varchar(255) DEFAULT '0',
`verify` varchar(255) DEFAULT '0',
`active` int(11) DEFAULT 1,
PRIMARY KEY (`userid`),
UNIQUE KEY `username` (`username`),
KEY `role_id` (`role_id`),
CONSTRAINT `sp_users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `sp_roles` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2566 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_users_api
-- ----------------------------
CREATE TABLE `sp_users_api` (
`apiid` int(11) NOT NULL AUTO_INCREMENT,
`orgid` int(11) DEFAULT 0,
`userid` int(11) DEFAULT 0,
`projectid` int(11) DEFAULT 0,
`apikey` varchar(100) DEFAULT NULL,
`apiname` varchar(255) DEFAULT '0',
`website` varchar(255) DEFAULT '0',
`description` text DEFAULT NULL,
`whitelist` text DEFAULT NULL,
`lockdomain` int(11) DEFAULT 0,
`createdate` datetime DEFAULT current_timestamp(),
`active` int(11) DEFAULT 1,
PRIMARY KEY (`apiid`),
UNIQUE KEY `ApiDups` (`apikey`)
) ENGINE=InnoDB AUTO_INCREMENT=5500 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: sp_users_meta
-- ----------------------------
CREATE TABLE `sp_users_meta` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT 0,
`keyval` varchar(255) DEFAULT '0',
`metval` text DEFAULT NULL,
`createdate` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=212 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
-- ----------------------------
-- Table: tmp_session
-- ----------------------------
CREATE TABLE `tmp_session` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` varchar(255) DEFAULT '000.000.000.000',
`lastcall` datetime DEFAULT current_timestamp(),
`created_date` datetime DEFAULT current_timestamp(),
`count` int(11) DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
SET FOREIGN_KEY_CHECKS=1;
Reference in a new issue View git blame Copy permalink