Created
October 22, 2012 14:02
-
-
Save pierre/3931646 to your computer and use it in GitHub Desktop.
Killbill database schema
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS accounts; | |
| CREATE TABLE accounts ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| external_key varchar(128) NULL, | |
| email varchar(128) NOT NULL, | |
| name varchar(100) NOT NULL, | |
| first_name_length int NOT NULL, | |
| currency char(3) DEFAULT NULL, | |
| billing_cycle_day_local int DEFAULT NULL, | |
| billing_cycle_day_utc int DEFAULT NULL, | |
| payment_method_id char(36) DEFAULT NULL, | |
| time_zone varchar(50) DEFAULT NULL, | |
| locale varchar(5) DEFAULT NULL, | |
| address1 varchar(100) DEFAULT NULL, | |
| address2 varchar(100) DEFAULT NULL, | |
| company_name varchar(50) DEFAULT NULL, | |
| city varchar(50) DEFAULT NULL, | |
| state_or_province varchar(50) DEFAULT NULL, | |
| country varchar(50) DEFAULT NULL, | |
| postal_code varchar(16) DEFAULT NULL, | |
| phone varchar(25) DEFAULT NULL, | |
| migrated bool DEFAULT false, | |
| is_notified_for_invoices boolean NOT NULL, | |
| created_date datetime NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| updated_date datetime DEFAULT NULL, | |
| updated_by varchar(50) DEFAULT NULL, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX accounts_id ON accounts(id); | |
| CREATE UNIQUE INDEX accounts_external_key ON accounts(external_key); | |
| CREATE INDEX accounts_tenant_record_id ON accounts(tenant_record_id); | |
| DROP TABLE IF EXISTS account_history; | |
| CREATE TABLE account_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| external_key varchar(128) NULL, | |
| email varchar(128) NOT NULL, | |
| name varchar(100) NOT NULL, | |
| first_name_length int NOT NULL, | |
| currency char(3) DEFAULT NULL, | |
| billing_cycle_day_local int DEFAULT NULL, | |
| billing_cycle_day_utc int DEFAULT NULL, | |
| payment_method_id char(36) DEFAULT NULL, | |
| time_zone varchar(50) DEFAULT NULL, | |
| locale varchar(5) DEFAULT NULL, | |
| address1 varchar(100) DEFAULT NULL, | |
| address2 varchar(100) DEFAULT NULL, | |
| company_name varchar(50) DEFAULT NULL, | |
| city varchar(50) DEFAULT NULL, | |
| state_or_province varchar(50) DEFAULT NULL, | |
| country varchar(50) DEFAULT NULL, | |
| postal_code varchar(16) DEFAULT NULL, | |
| phone varchar(25) DEFAULT NULL, | |
| migrated bool DEFAULT false, | |
| is_notified_for_invoices boolean NOT NULL, | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX account_history_target_record_id ON account_history(target_record_id); | |
| CREATE INDEX account_history_tenant_record_id ON account_history(tenant_record_id); | |
| DROP TABLE IF EXISTS account_emails; | |
| CREATE TABLE account_emails ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| account_id char(36) NOT NULL, | |
| email varchar(128) NOT NULL, | |
| is_active bool DEFAULT true, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX account_email_id ON account_emails(id); | |
| CREATE INDEX account_email_account_id_email ON account_emails(account_id, email); | |
| CREATE INDEX account_emails_tenant_account_record_id ON account_emails(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS account_email_history; | |
| CREATE TABLE account_email_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| account_id char(36) NOT NULL, | |
| email varchar(128) NOT NULL, | |
| is_active bool DEFAULT true, | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX account_email_target_record_id ON account_email_history(target_record_id); | |
| CREATE INDEX account_email_history_tenant_account_record_id ON account_email_history(tenant_record_id, account_record_id); | |
| /*! SET storage_engine=INNODB */; | |
| drop table if exists bst; | |
| create table bst ( | |
| record_id int(11) unsigned not null auto_increment | |
| , total_ordering bigint default 0 | |
| , bundle_id char(36) not null | |
| , account_id char(36) not null | |
| , external_key varchar(50) not null comment 'Bundle external key' | |
| , account_key varchar(50) not null comment 'Account external key' | |
| , subscription_id char(36) not null | |
| , requested_timestamp bigint not null | |
| , event varchar(50) not null | |
| , prev_product_name varchar(50) default null | |
| , prev_product_type varchar(50) default null | |
| , prev_product_category varchar(50) default null | |
| , prev_slug varchar(50) default null | |
| , prev_phase varchar(50) default null | |
| , prev_billing_period varchar(50) default null | |
| , prev_price numeric(10, 4) default 0 | |
| , prev_price_list varchar(50) default null | |
| , prev_mrr numeric(10, 4) default 0 | |
| , prev_currency varchar(50) default null | |
| , prev_start_date bigint default null | |
| , prev_state varchar(50) default null | |
| , next_product_name varchar(50) default null | |
| , next_product_type varchar(50) default null | |
| , next_product_category varchar(50) default null | |
| , next_slug varchar(50) default null | |
| , next_phase varchar(50) default null | |
| , next_billing_period varchar(50) default null | |
| , next_price numeric(10, 4) default 0 | |
| , next_price_list varchar(50) default null | |
| , next_mrr numeric(10, 4) default 0 | |
| , next_currency varchar(50) default null | |
| , next_start_date bigint default null | |
| , next_state varchar(50) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bst_key_index on bst (external_key, requested_timestamp asc); | |
| create index bst_tenant_account_record_id on bst(tenant_record_id, account_record_id); | |
| drop table if exists bac; | |
| create table bac ( | |
| record_id int(11) unsigned not null auto_increment | |
| , account_id char(36) not null | |
| , account_key varchar(50) not null | |
| , name varchar(100) not null | |
| , created_date bigint not null | |
| , updated_date bigint not null | |
| , balance numeric(10, 4) default 0 | |
| , last_invoice_date date default null | |
| , total_invoice_balance numeric(10, 4) default 0 | |
| , last_payment_status varchar(255) default null | |
| , payment_method varchar(50) default null | |
| , credit_card_type varchar(50) default null | |
| , billing_address_country varchar(50) default null | |
| , currency char(50) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create unique index bac_key_index on bac (account_key); | |
| create index bac_tenant_account_record_id on bac(tenant_record_id, account_record_id); | |
| drop table if exists bin; | |
| create table bin ( | |
| record_id int(11) unsigned not null auto_increment | |
| , invoice_id char(36) not null | |
| , invoice_number bigint default null | |
| , created_date bigint not null | |
| , updated_date bigint not null | |
| , account_id char(36) not null | |
| , account_key varchar(50) not null | |
| , invoice_date date not null | |
| , target_date date not null | |
| , currency char(50) not null | |
| , balance numeric(10, 4) default 0 comment 'amount_charged - amount_paid - amount_credited' | |
| , amount_paid numeric(10, 4) default 0 comment 'Sums of the successful payments made for this invoice minus the refunds associated with this invoice' | |
| , amount_charged numeric(10, 4) default 0 comment 'Sums of the invoice items amount' | |
| , amount_credited numeric(10, 4) default 0 comment 'Sums of the credit items' | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create unique index bin_key_index on bin (invoice_id); | |
| create index bin_tenant_account_record_id on bin(tenant_record_id, account_record_id); | |
| drop table if exists bii; | |
| create table bii ( | |
| record_id int(11) unsigned not null auto_increment | |
| , item_id char(36) not null | |
| , created_date bigint not null | |
| , updated_date bigint not null | |
| , invoice_id char(36) not null | |
| , item_type char(50) not null comment 'e.g. FIXED or RECURRING' | |
| , external_key varchar(50) default null comment 'Bundle external key (could be null for certain items)' | |
| , product_name varchar(50) default null | |
| , product_type varchar(50) default null | |
| , product_category varchar(50) default null | |
| , slug varchar(50) default null | |
| , phase varchar(50) default null | |
| , billing_period varchar(50) default null | |
| , start_date date default null | |
| , end_date date default null | |
| , amount numeric(10, 4) default 0 | |
| , currency char(50) default null | |
| , linked_item_id char(36) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create unique index bii_key_index on bii (item_id); | |
| create index bii_tenant_account_record_id on bii(tenant_record_id, account_record_id); | |
| drop table if exists bip; | |
| create table bip ( | |
| record_id int(11) unsigned not null auto_increment | |
| , payment_id char(36) not null | |
| , created_date bigint not null | |
| , updated_date bigint not null | |
| , ext_first_payment_ref_id varchar(255) default null | |
| , ext_second_payment_ref_id varchar(255) default null | |
| , account_key varchar(50) not null comment 'Account external key' | |
| , invoice_id char(36) not null | |
| , effective_date bigint default null | |
| , amount numeric(10, 4) default 0 | |
| , currency char(50) default null | |
| , payment_error varchar(255) default null | |
| , processing_status varchar(50) default null | |
| , requested_amount numeric(10, 4) default 0 | |
| , plugin_name varchar(50) default null | |
| , payment_type varchar(50) default null | |
| , payment_method varchar(50) default null | |
| , card_type varchar(50) default null | |
| , card_country varchar(50) default null | |
| , invoice_payment_type varchar(50) default null | |
| , linked_invoice_payment_id char(36) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create unique index bip_key_index on bip (payment_id); | |
| create index bip_tenant_account_record_id on bip(tenant_record_id, account_record_id); | |
| drop table if exists bos; | |
| create table bos ( | |
| record_id int(11) unsigned not null auto_increment | |
| , bundle_id char(36) not null | |
| , external_key varchar(50) not null comment 'Bundle external key' | |
| , account_key varchar(50) not null comment 'Account external key' | |
| , status varchar(50) not null | |
| , start_date bigint default null | |
| , end_date bigint default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bos_tenant_account_record_id on bos(tenant_record_id, account_record_id); | |
| drop table if exists bac_tags; | |
| create table bac_tags ( | |
| record_id int(11) unsigned not null auto_increment | |
| , account_id char(36) not null | |
| , account_key varchar(50) not null comment 'Account external key' | |
| , name varchar(50) not null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bac_tags_tenant_account_record_id on bac_tags(tenant_record_id, account_record_id); | |
| drop table if exists bac_fields; | |
| create table bac_fields ( | |
| record_id int(11) unsigned not null auto_increment | |
| , account_id char(36) not null | |
| , account_key varchar(50) not null comment 'Account external key' | |
| , name varchar(50) not null | |
| , value varchar(255) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bac_fields_tenant_account_record_id on bac_fields(tenant_record_id, account_record_id); | |
| drop table if exists bst_tags; | |
| create table bst_tags ( | |
| record_id int(11) unsigned not null auto_increment | |
| , bundle_id char(36) not null | |
| , external_key varchar(50) not null comment 'Bundle external key' | |
| , account_key varchar(50) not null comment 'Account external key' | |
| , name varchar(50) not null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bst_tags_tenant_account_record_id on bst_tags(tenant_record_id, account_record_id); | |
| drop table if exists bst_fields; | |
| create table bst_fields ( | |
| record_id int(11) unsigned not null auto_increment | |
| , bundle_id char(36) not null | |
| , external_key varchar(50) not null comment 'Bundle external key' | |
| , account_key varchar(50) not null comment 'Account external key' | |
| , name varchar(50) not null | |
| , value varchar(255) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bst_fields_tenant_account_record_id on bst_fields(tenant_record_id, account_record_id); | |
| drop table if exists bin_tags; | |
| create table bin_tags ( | |
| record_id int(11) unsigned not null auto_increment | |
| , invoice_id char(36) not null | |
| , name varchar(50) not null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bin_tags_tenant_account_record_id on bin_tags(tenant_record_id, account_record_id); | |
| drop table if exists bin_fields; | |
| create table bin_fields ( | |
| record_id int(11) unsigned not null auto_increment | |
| , invoice_id char(36) not null | |
| , name varchar(50) not null | |
| , value varchar(255) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bin_fields_tenant_account_record_id on bin_fields(tenant_record_id, account_record_id); | |
| drop table if exists bip_tags; | |
| create table bip_tags ( | |
| record_id int(11) unsigned not null auto_increment | |
| , payment_id char(36) not null | |
| , name varchar(50) not null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bip_tags_tenant_account_record_id on bip_tags(tenant_record_id, account_record_id); | |
| drop table if exists bip_fields; | |
| create table bip_fields ( | |
| record_id int(11) unsigned not null auto_increment | |
| , payment_id char(36) not null | |
| , name varchar(50) not null | |
| , value varchar(255) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| create index bip_fields_tenant_account_record_id on bip_fields(tenant_record_id, account_record_id); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS bus_ext_events; | |
| CREATE TABLE bus_ext_events ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| event_type varchar(32) NOT NULL, | |
| object_id varchar(64) NOT NULL, | |
| object_type varchar(32) NOT NULL, | |
| user_token char(36), | |
| created_date datetime NOT NULL, | |
| creating_owner char(50) NOT NULL, | |
| processing_owner char(50) DEFAULT NULL, | |
| processing_available_date datetime DEFAULT NULL, | |
| processing_state varchar(14) DEFAULT 'AVAILABLE', | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX `idx_bus_ext_where` ON bus_ext_events (`processing_state`,`processing_owner`,`processing_available_date`); | |
| CREATE INDEX bus_ext_events_tenant_account_record_id ON bus_ext_events(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS claimed_bus_ext_events; | |
| CREATE TABLE claimed_bus_ext_events ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| owner_id varchar(64) NOT NULL, | |
| claimed_date datetime NOT NULL, | |
| bus_event_id char(36) NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX claimed_bus_ext_events_tenant_account_record_id ON claimed_bus_ext_events(tenant_record_id, account_record_id); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS events; | |
| DROP TABLE IF EXISTS entitlement_events; | |
| DROP TABLE IF EXISTS subscription_events; | |
| CREATE TABLE subscription_events ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| event_type varchar(9) NOT NULL, | |
| user_type varchar(25) DEFAULT NULL, | |
| requested_date datetime NOT NULL, | |
| effective_date datetime NOT NULL, | |
| subscription_id char(36) NOT NULL, | |
| plan_name varchar(64) DEFAULT NULL, | |
| phase_name varchar(128) DEFAULT NULL, | |
| price_list_name varchar(64) DEFAULT NULL, | |
| current_version int(11) DEFAULT 1, | |
| is_active bool DEFAULT 1, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX subscription_events_id ON subscription_events(id); | |
| CREATE INDEX idx_ent_1 ON subscription_events(subscription_id, is_active, effective_date); | |
| CREATE INDEX idx_ent_2 ON subscription_events(subscription_id, effective_date, created_date, requested_date,id); | |
| CREATE INDEX subscription_events_tenant_account_record_id ON subscription_events(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS subscriptions; | |
| CREATE TABLE subscriptions ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| bundle_id char(36) NOT NULL, | |
| category varchar(32) NOT NULL, | |
| start_date datetime NOT NULL, | |
| bundle_start_date datetime NOT NULL, | |
| active_version int(11) DEFAULT 1, | |
| charged_through_date datetime DEFAULT NULL, | |
| paid_through_date datetime DEFAULT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX subscriptions_id ON subscriptions(id); | |
| CREATE INDEX subscriptions_bundle_id ON subscriptions(bundle_id); | |
| CREATE INDEX subscriptions_tenant_account_record_id ON subscriptions(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS bundles; | |
| CREATE TABLE bundles ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| external_key varchar(64) NOT NULL, | |
| account_id char(36) NOT NULL, | |
| last_sys_update_date datetime, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX bundles_id ON bundles(id); | |
| CREATE INDEX bundles_key ON bundles(external_key); | |
| CREATE INDEX bundles_account ON bundles(account_id); | |
| CREATE INDEX bundles_tenant_account_record_id ON bundles(tenant_record_id, account_record_id); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS invoice_items; | |
| CREATE TABLE invoice_items ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| type varchar(24) NOT NULL, | |
| invoice_id char(36) NOT NULL, | |
| account_id char(36) NOT NULL, | |
| bundle_id char(36), | |
| subscription_id char(36), | |
| plan_name varchar(50), | |
| phase_name varchar(50), | |
| start_date date NOT NULL, | |
| end_date date, | |
| amount numeric(10,4) NOT NULL, | |
| rate numeric(10,4) NULL, | |
| currency char(3) NOT NULL, | |
| linked_item_id char(36), | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX invoice_items_id ON invoice_items(id); | |
| CREATE INDEX invoice_items_subscription_id ON invoice_items(subscription_id ASC); | |
| CREATE INDEX invoice_items_invoice_id ON invoice_items(invoice_id ASC); | |
| CREATE INDEX invoice_items_account_id ON invoice_items(account_id ASC); | |
| CREATE INDEX invoice_items_tenant_account_record_id ON invoice_items(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS invoices; | |
| CREATE TABLE invoices ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| account_id char(36) NOT NULL, | |
| invoice_date date NOT NULL, | |
| target_date date NOT NULL, | |
| currency char(3) NOT NULL, | |
| migrated bool NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX invoices_id ON invoices(id); | |
| CREATE INDEX invoices_account_target ON invoices(account_id ASC, target_date); | |
| CREATE INDEX invoices_tenant_account_record_id ON invoices(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS invoice_payments; | |
| CREATE TABLE invoice_payments ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| type varchar(24) NOT NULL, | |
| invoice_id char(36) NOT NULL, | |
| payment_id char(36), | |
| payment_date datetime NOT NULL, | |
| amount numeric(10,4) NOT NULL, | |
| currency char(3) NOT NULL, | |
| payment_cookie_id char(36) DEFAULT NULL, | |
| linked_invoice_payment_id char(36) DEFAULT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX invoice_payments_id ON invoice_payments(id); | |
| CREATE INDEX invoice_payments ON invoice_payments(payment_id); | |
| CREATE INDEX invoice_payments_reversals ON invoice_payments(linked_invoice_payment_id); | |
| CREATE INDEX invoice_payments_tenant_account_record_id ON invoice_payments(tenant_record_id, account_record_id); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS blocking_states; | |
| CREATE TABLE blocking_states ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| blockable_id char(36) NOT NULL, | |
| type varchar(20) NOT NULL, | |
| state varchar(50) NOT NULL, | |
| service varchar(20) NOT NULL, | |
| block_change bool NOT NULL, | |
| block_entitlement bool NOT NULL, | |
| block_billing bool NOT NULL, | |
| created_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX blocking_states_id ON blocking_states(blockable_id); | |
| CREATE INDEX blocking_states_tenant_account_record_id ON blocking_states(tenant_record_id, account_record_id); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS sources; | |
| CREATE TABLE sources ( | |
| record_id int(11) unsigned not null auto_increment | |
| , source char(36) not null | |
| , created_date datetime default null | |
| , created_by varchar(50) default null | |
| , updated_date datetime default null | |
| , updated_by varchar(50) default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| CREATE UNIQUE INDEX source_unq on sources(source); | |
| CREATE INDEX created_date_record_id_dx on sources(created_date, record_id); | |
| CREATE INDEX sources_tenant_account_record_id on sources(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS categories; | |
| CREATE TABLE categories ( | |
| record_id int(11) unsigned not null auto_increment | |
| , category varchar(255) not null | |
| , created_date datetime default null | |
| , created_by varchar(50) default null | |
| , updated_date datetime default null | |
| , updated_by varchar(50) default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| CREATE UNIQUE INDEX event_category_unq on categories(category); | |
| CREATE INDEX categories_tenant_record_id on categories(tenant_record_id); | |
| DROP TABLE IF EXISTS metrics; | |
| CREATE TABLE metrics ( | |
| record_id int(11) unsigned not null auto_increment | |
| , category_record_id integer not null | |
| , metric varchar(255) not null | |
| , created_date datetime default null | |
| , created_by varchar(50) default null | |
| , updated_date datetime default null | |
| , updated_by varchar(50) default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| CREATE UNIQUE INDEX metric_unq on metrics(category_record_id, metric); | |
| CREATE INDEX metrics_tenant_record_id on metrics(tenant_record_id); | |
| DROP TABLE IF EXISTS timeline_chunks; | |
| CREATE TABLE timeline_chunks ( | |
| record_id bigint not null auto_increment | |
| , source_record_id integer not null | |
| , metric_record_id integer not null | |
| , sample_count integer not null | |
| , start_time integer not null | |
| , end_time integer not null | |
| , not_valid tinyint default 0 | |
| , aggregation_level tinyint default 0 | |
| , dont_aggregate tinyint default 0 | |
| , in_row_samples varbinary(400) default null | |
| , blob_samples mediumblob default null | |
| , account_record_id int(11) unsigned default null | |
| , tenant_record_id int(11) unsigned default null | |
| , primary key(record_id) | |
| ); | |
| CREATE UNIQUE INDEX source_record_id_timeline_chunk_metric_record_idx on timeline_chunks(source_record_id, metric_record_id, start_time, aggregation_level); | |
| CREATE INDEX valid_agg_host_start_time on timeline_chunks(not_valid, aggregation_level, source_record_id, metric_record_id, start_time); | |
| DROP TABLE IF EXISTS last_start_times; | |
| CREATE TABLE last_start_times ( | |
| time_inserted int not null primary key | |
| , start_times mediumtext not null | |
| ); | |
| INSERT INTO timeline_chunks(record_id, source_record_id, metric_record_id, sample_count, start_time, end_time, in_row_samples, blob_samples) | |
| VALUES (0, 0, 0, 0, 0, 0, null, null); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS payments; | |
| CREATE TABLE payments ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| account_id char(36) NOT NULL, | |
| invoice_id char(36) NOT NULL, | |
| payment_method_id char(36) NOT NULL, | |
| amount numeric(10,4), | |
| currency char(3), | |
| effective_date datetime, | |
| payment_status varchar(50), | |
| ext_first_payment_ref_id varchar(128), | |
| ext_second_payment_ref_id varchar(128), | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY (record_id) | |
| ); | |
| CREATE UNIQUE INDEX payments_id ON payments(id); | |
| CREATE INDEX payments_inv ON payments(invoice_id); | |
| CREATE INDEX payments_accnt ON payments(account_id); | |
| CREATE INDEX payments_tenant_account_record_id ON payments(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS payment_history; | |
| CREATE TABLE payment_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| account_id char(36) NOT NULL, | |
| invoice_id char(36) NOT NULL, | |
| payment_method_id char(36) NOT NULL, | |
| amount numeric(10,4), | |
| currency char(3), | |
| effective_date datetime, | |
| payment_status varchar(50), | |
| ext_first_payment_ref_id varchar(128), | |
| ext_second_payment_ref_id varchar(128), | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX payment_history_target_record_id ON payment_history(target_record_id); | |
| CREATE INDEX payment_history_tenant_account_record_id ON payment_history(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS payment_attempts; | |
| CREATE TABLE payment_attempts ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| payment_id char(36) NOT NULL, | |
| gateway_error_code varchar(32), | |
| gateway_error_msg varchar(256), | |
| processing_status varchar(50), | |
| requested_amount numeric(10,4), | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY (record_id) | |
| ); | |
| CREATE UNIQUE INDEX payment_attempts_id ON payment_attempts(id); | |
| CREATE INDEX payment_attempts_payment ON payment_attempts(payment_id); | |
| CREATE INDEX payment_attempts_tenant_account_record_id ON payment_attempts(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS payment_attempt_history; | |
| CREATE TABLE payment_attempt_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| payment_id char(36) NOT NULL, | |
| gateway_error_code varchar(32), | |
| gateway_error_msg varchar(256), | |
| processing_status varchar(50), | |
| requested_amount numeric(10,4), | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX payment_attempt_history_target_record_id ON payment_attempt_history(target_record_id); | |
| CREATE INDEX payment_attempt_history_tenant_account_record_id ON payment_attempt_history(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS payment_methods; | |
| CREATE TABLE payment_methods ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| account_id char(36) NOT NULL, | |
| plugin_name varchar(20) DEFAULT NULL, | |
| is_active bool DEFAULT true, | |
| external_id varchar(64), | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY (record_id) | |
| ); | |
| CREATE UNIQUE INDEX payment_methods_id ON payment_methods(id); | |
| CREATE INDEX payment_methods_active_accnt ON payment_methods(is_active, account_id); | |
| CREATE INDEX payment_methods_tenant_account_record_id ON payment_methods(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS payment_method_history; | |
| CREATE TABLE payment_method_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| account_id char(36) NOT NULL, | |
| plugin_name varchar(20) DEFAULT NULL, | |
| is_active bool DEFAULT true, | |
| external_id varchar(64), | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX payment_method_history_target_record_id ON payment_method_history(target_record_id); | |
| CREATE INDEX payment_method_history_tenant_account_record_id ON payment_method_history(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS refunds; | |
| CREATE TABLE refunds ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| account_id char(36) NOT NULL, | |
| payment_id char(36) NOT NULL, | |
| amount numeric(10,4), | |
| currency char(3), | |
| is_adjusted tinyint(1), | |
| refund_status varchar(50), | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY (record_id) | |
| ); | |
| CREATE UNIQUE INDEX refunds_id ON refunds(id); | |
| CREATE INDEX refunds_pay ON refunds(payment_id); | |
| CREATE INDEX refunds_accnt ON refunds(account_id); | |
| CREATE INDEX refunds_tenant_account_record_id ON refunds(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS refund_history; | |
| CREATE TABLE refund_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| account_id char(36) NOT NULL, | |
| payment_id char(36) NOT NULL, | |
| amount numeric(10,4), | |
| currency char(3), | |
| is_adjusted tinyint(1), | |
| refund_status varchar(50), | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX refund_history_target_record_id ON refund_history(target_record_id); | |
| CREATE INDEX refund_history_tenant_account_record_id ON refund_history(tenant_record_id, account_record_id); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS tenants; | |
| CREATE TABLE tenants ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| external_key varchar(128) NULL, | |
| api_key varchar(128) NULL, | |
| api_secret varchar(128) NULL, | |
| api_salt varchar(128) NULL, | |
| created_date datetime NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| updated_date datetime DEFAULT NULL, | |
| updated_by varchar(50) DEFAULT NULL, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX tenants_id ON tenants(id); | |
| CREATE UNIQUE INDEX tenants_api_key ON tenants(api_key); | |
| DROP TABLE IF EXISTS tenant_kvs; | |
| CREATE TABLE tenant_kvs ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| tenant_record_id int(11) unsigned default null, | |
| tenant_key varchar(64) NOT NULL, | |
| tenant_value varchar(1024) NOT NULL, | |
| is_active bool DEFAULT 1, | |
| created_date datetime NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| updated_date datetime DEFAULT NULL, | |
| updated_by varchar(50) DEFAULT NULL, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX tenant_kvs_key ON tenant_kvs(tenant_key); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS rolled_up_usage; | |
| CREATE TABLE rolled_up_usage ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| subscription_id char(36), | |
| unit_type varchar(50), | |
| start_date date NOT NULL, | |
| end_date date, | |
| amount numeric(10,10) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX rolled_up_usage_id ON rolled_up_usage(id); | |
| CREATE INDEX rolled_up_usage_subscription_id ON rolled_up_usage(subscription_id ASC); | |
| CREATE INDEX rolled_up_usage_tenant_account_record_id ON rolled_up_usage(tenant_record_id, account_record_id); | |
| /*! SET storage_engine=INNODB */; | |
| DROP TABLE IF EXISTS custom_fields; | |
| CREATE TABLE custom_fields ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| object_id char(36) NOT NULL, | |
| object_type varchar(30) NOT NULL, | |
| field_name varchar(30) NOT NULL, | |
| field_value varchar(255), | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) DEFAULT NULL, | |
| updated_date datetime DEFAULT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX custom_fields_id ON custom_fields(id); | |
| CREATE INDEX custom_fields_object_id_object_type ON custom_fields(object_id, object_type); | |
| CREATE UNIQUE INDEX custom_fields_unique ON custom_fields(object_id, object_type, field_name); | |
| CREATE INDEX custom_fields_tenant_account_record_id ON custom_fields(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS custom_field_history; | |
| CREATE TABLE custom_field_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| object_id char(36) NOT NULL, | |
| object_type varchar(30) NOT NULL, | |
| field_name varchar(30), | |
| field_value varchar(255), | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX custom_field_history_target_record_id ON custom_field_history(target_record_id); | |
| CREATE INDEX custom_field_history_object_id_object_type ON custom_fields(object_id, object_type); | |
| CREATE INDEX custom_field_history_tenant_account_record_id ON custom_field_history(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS tag_definitions; | |
| CREATE TABLE tag_definitions ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| name varchar(20) NOT NULL, | |
| description varchar(200) NOT NULL, | |
| is_active bool DEFAULT true, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX tag_definitions_id ON tag_definitions(id); | |
| CREATE INDEX tag_definitions_tenant_record_id ON tag_definitions(tenant_record_id); | |
| DROP TABLE IF EXISTS tag_definition_history; | |
| CREATE TABLE tag_definition_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| name varchar(30) NOT NULL, | |
| description varchar(200), | |
| is_active bool DEFAULT true, | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX tag_definition_history_id ON tag_definition_history(id); | |
| CREATE INDEX tag_definition_history_target_record_id ON tag_definition_history(target_record_id); | |
| CREATE INDEX tag_definition_history_name ON tag_definition_history(name); | |
| CREATE INDEX tag_definition_history_tenant_record_id ON tag_definition_history(tenant_record_id); | |
| DROP TABLE IF EXISTS tags; | |
| CREATE TABLE tags ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| tag_definition_id char(36) NOT NULL, | |
| object_id char(36) NOT NULL, | |
| object_type varchar(30) NOT NULL, | |
| is_active bool DEFAULT true, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX tags_id ON tags(id); | |
| CREATE INDEX tags_by_object ON tags(object_id); | |
| CREATE INDEX tags_tenant_account_record_id ON tags(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS tag_history; | |
| CREATE TABLE tag_history ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| target_record_id int(11) unsigned NOT NULL, | |
| object_id char(36) NOT NULL, | |
| object_type varchar(30) NOT NULL, | |
| tag_definition_id char(36) NOT NULL, | |
| is_active bool DEFAULT true, | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| created_date datetime NOT NULL, | |
| updated_by varchar(50) NOT NULL, | |
| updated_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX tag_history_target_record_id ON tag_history(target_record_id); | |
| CREATE INDEX tag_history_by_object ON tags(object_id); | |
| CREATE INDEX tag_history_tenant_account_record_id ON tag_history(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS notifications; | |
| CREATE TABLE notifications ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| created_date datetime NOT NULL, | |
| class_name varchar(256) NOT NULL, | |
| notification_key varchar(2048) NOT NULL, | |
| user_token char(36), | |
| future_user_token char(36), | |
| creating_owner char(50) NOT NULL, | |
| effective_date datetime NOT NULL, | |
| queue_name char(64) NOT NULL, | |
| processing_owner char(50) DEFAULT NULL, | |
| processing_available_date datetime DEFAULT NULL, | |
| processing_state varchar(14) DEFAULT 'AVAILABLE', | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE UNIQUE INDEX notifications_id ON notifications(id); | |
| CREATE INDEX `idx_comp_where` ON notifications (`effective_date`, `processing_state`,`processing_owner`,`processing_available_date`); | |
| CREATE INDEX `idx_update` ON notifications (`processing_state`,`processing_owner`,`processing_available_date`); | |
| CREATE INDEX `idx_get_ready` ON notifications (`effective_date`,`created_date`,`id`); | |
| CREATE INDEX notifications_tenant_account_record_id ON notifications(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS claimed_notifications; | |
| CREATE TABLE claimed_notifications ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| owner_id varchar(64) NOT NULL, | |
| claimed_date datetime NOT NULL, | |
| notification_id char(36) NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX claimed_notifications_tenant_account_record_id ON claimed_notifications(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS audit_log; | |
| /*! SET storage_engine=INNODB */; | |
| CREATE TABLE audit_log ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| id char(36) NOT NULL, | |
| table_name varchar(50) NOT NULL, | |
| target_record_id int(11) NOT NULL, | |
| change_type char(6) NOT NULL, | |
| created_by varchar(50) NOT NULL, | |
| reason_code varchar(255) DEFAULT NULL, | |
| comments varchar(255) DEFAULT NULL, | |
| user_token char(36), | |
| created_date datetime NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX audit_log_fetch_target_record_id ON audit_log(table_name, target_record_id); | |
| CREATE INDEX audit_log_user_name ON audit_log(created_by); | |
| CREATE INDEX audit_log_tenant_account_record_id ON audit_log(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS bus_events; | |
| CREATE TABLE bus_events ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| class_name varchar(128) NOT NULL, | |
| event_json varchar(2048) NOT NULL, | |
| user_token char(36), | |
| created_date datetime NOT NULL, | |
| creating_owner char(50) NOT NULL, | |
| processing_owner char(50) DEFAULT NULL, | |
| processing_available_date datetime DEFAULT NULL, | |
| processing_state varchar(14) DEFAULT 'AVAILABLE', | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX `idx_bus_where` ON bus_events (`processing_state`,`processing_owner`,`processing_available_date`); | |
| CREATE INDEX bus_events_tenant_account_record_id ON bus_events(tenant_record_id, account_record_id); | |
| DROP TABLE IF EXISTS claimed_bus_events; | |
| CREATE TABLE claimed_bus_events ( | |
| record_id int(11) unsigned NOT NULL AUTO_INCREMENT, | |
| owner_id varchar(64) NOT NULL, | |
| claimed_date datetime NOT NULL, | |
| bus_event_id char(36) NOT NULL, | |
| account_record_id int(11) unsigned default null, | |
| tenant_record_id int(11) unsigned default null, | |
| PRIMARY KEY(record_id) | |
| ); | |
| CREATE INDEX claimed_bus_events_tenant_account_record_id ON claimed_bus_events(tenant_record_id, account_record_id); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment