DB model

Notice

Source and destination numbers` format is <number>.<TON>.<NPI> TON is (type of number) and NPI is (numbering plan identification)

TABLE inbox -- for received SMS

• id int(10) unsigned NOT NULL AUTO_INCREMENT -- received SMS ID
• src varchar(20) NOT NULL -- source number
• dst varchar(20) NOT NULL -- destination number
• msg varchar(2048) NOT NULL -- message body
• ts int(10) unsigned NOT NULL DEFAULT '0' -- unix timestamp of receiving SMS
• mt tinyint(3) unsigned NOT NULL DEFAULT '0' -- message type (0 - SMS, 4 - delivery report)

TABLE archive -- for received and processed SMS

• id int(10) unsigned NOT NULL DEFAULT '0' -- received SMS ID
• src varchar(20) NOT NULL -- source number
• dst varchar(20) NOT NULL -- destination number
• msg varchar(2048) NOT NULL -- message body
• ts int(10) unsigned NOT NULL DEFAULT '0' -- unix timestamp of receiving SMS
• mt tinyint(3) unsigned NOT NULL DEFAULT '0' -- message type (0 - SMS, 4 - delivery report)
• proc_ts int(10) unsigned NOT NULL DEFAULT '0' -- unix timestamp of processing this SMS by some service
• resp_id int(10) unsigned NOT NULL DEFAULT '0' -- ID of response SMS, created by service. If any
• srv_id tinyint(3) unsigned NOT NULL DEFAULT '0' -- ID of the service, which processed this SMS
• rslt tinyint(3) NOT NULL DEFAULT '0' -- ID of processing result

TABLE outbox -- for sending SMS

• id int(10) unsigned NOT NULL AUTO_INCREMENT -- sent SMS ID
• rel_id int(10) unsigned NOT NULL DEFAULT '0' -- related SMS (i. e. if it is a response to some received SMS). If any
• src varchar(20) NOT NULL -- source number
• dst varchar(20) NOT NULL -- destination number
• msg varchar(2048) NOT NULL -- message body
• ts int(10) unsigned NOT NULL DEFAULT '0' -- unix timestamp of putting this SMS into outbox
• rd tinyint(3) unsigned NOT NULL DEFAULT '0' -- request delivery (0 - no, 1 - yes)
• try_ts int(11) unsigned NOT NULL DEFAULT '0' -- unix timestamp of last sending attempt

TABLE sent -- for sent SMS

• id int(10) unsigned NOT NULL DEFAULT '0' -- sent SMS ID
• rel_id int(10) unsigned NOT NULL DEFAULT '0' -- related SMS (i. e. if it is a response to some received SMS). If any
• src varchar(20) NOT NULL -- source number
• dst varchar(20) NOT NULL -- destination number
• msg varchar(2048) NOT NULL -- message body
• ts int(10) unsigned NOT NULL DEFAULT '0' -- unix timestamp of putting this SMS into outbox
• rd tinyint(3) unsigned NOT NULL DEFAULT '0' -- request delivery (0 - no, 1 - yes)
• snt_ts int(10) unsigned NOT NULL DEFAULT '0' -- unix timestamp of sending SMS
• msg_id varchar(50) NOT NULL DEFAULT '' -- message ID from SMSC
• dl_ts int(10) unsigned DEFAULT NULL -- unix timestamp of SMS delivery (from delivery report)
• dl_stat tinyint(3) unsigned DEFAULT NULL -- delivery status

TABLE parts -- for received SMS parts

• src char(20) NOT NULL -- source number
• dst char(20) NOT NULL -- destination number
• msg char(255) NOT NULL -- message part body
• ts int(10) unsigned NOT NULL DEFAULT '0' -- unix timestamp of receiving this part
• ref smallint(6) unsigned NOT NULL -- reference (for identifying parts of SMS)
• pn tinyint(4) unsigned NOT NULL -- part number
• tp tinyint(4) unsigned NOT NULL -- total parts count

TABLE service -- for service catalog. Optional

• id int(11) unsigned NOT NULL -- service ID (you define)
• name char(255) NOT NULL -- service name
• exported_id int(11) unsigned NOT NULL -- for data extractions. optional

TABLE rslts -- for service results. Optional

• srv_id int(11) unsigned NOT NULL -- service ID
• code tinyint(3) NOT NULL DEFAULT '0' -- numeric result code (you define)
• dsc char(255) NOT NULL -- description

TABLE stats -- for statistics purposes

• ts int(11) unsigned NOT NULL DEFAULT '0' -- unix timestamp
• rec int(11) unsigned NOT NULL DEFAULT '0' -- received SMS count
• sent int(11) unsigned NOT NULL DEFAULT '0' -- sent SMS count

← Architecture | Integration Example →