Middleware DB Schema

There is a mysql-workbench model of the schema in the cmp-spec repository on github.

User Table

Main users table for the API

Column Name	Column Type	Purpose	Unique	Auto Inc
id	int	A unique identifier for a user	true	true
email	varchar (128)	Users email address	true	false
password	varchar (32)	Minimum of salted MD5 hash	false	false
preference	text	JSON object of user preferences	false	false
fullName	text	The users full name	false	false
isPublic	boolean	Whether user wishes to be listed as a participant	false	false
countryCode	varchar(3)	ISO 3166-1-alpha-2	false	false
probeHMAC	varchar(32)	A unique key used as part of a probe uuid process	false	false
status	enum	pending / ok / suspended / banned	false	false
pgpKey (deprecated)	text	Single key (or another table with a pivot)	false	false
yubiKey (depcrecated)	text	JSON object of YubiKey public key elements	false	false
publicKey (deprecated)	text	The ORG (server side generated) public key for verifying signed requests	false	false
createdAt	timestamp	Date/Time this user account was created	false	false
administrator	tinyint	Confers the ability to run restricted API functions	false	false

Column Name	Column Type	Purpose	Unique	Auto Inc
id	int	An auto inc id for easy joins	true	true
uuid	varchar (32)	An MD5 hash derived from the user HMAC & something on the device (e.g. MAC address)	true	false
userID	int(11)	The user who registered this device	false	false
publicKey	text	In case we want to authenticate / do PKI	false	false
type	enum	rasbpi / android / atlas	false	false
lastSeen	datetime	Last time the probe was heard from	false	false
gcmRegID	text	The unique string used by Google Cloud Messaging	false	false
isPublic	boolean	Whether this probe is subscribed to the "public" url feed or the ORG directly-submitted queue	false	false
countryCode	varchar(3)	ISO 3166-1-alpha-2 locate of the probe	false	false
probeReqSent	int(11)	Number of probe requests we've sent to the device (may not be accurate for AMQP-driven probes)	false	false
probeRespRecv	int(11)	Number of probe responses we've received from the device	false	false
enabled	boolean	Is the probe enabled (admin)	false	false
frequency	int(11)	How often to send GCM probes (if enabled)	false	false
gcmType	int(11)	Whether this probe supports full GCM, partial GCM or manual polling	false	false

Column Name	Column Type	Purpose	Unique	Auto Inc
id	int	A unique identifier for the url	true	true
url	text	The full URL	false	false
md5Hash	varchar(32)	A safe way of communicating about the URL	false	false
source	enum	social, user submitted, canary, alexa	false	false
lastPolled	datetime	Last time the URL was dispatched to a probe	false	false
inserted	datetime	When the URL was added to the DB	false	false
polledAttempts	int	Number of times this URL has been probed (GCM Push / GET)	false	false
polledSuccess	int	Number of times a response for this URL has been received	false	false

Contains the result of a probe (probe_id) running a test of a url (url_id)

Column Name	Column Type	Purpose	Unique	Auto Inc
id	int	Unique Identifier for the result	yes	yes
urlID	int	ID of the URL tested	false	false
probeID	int	ID of the probe that conducted the test	false	false
config	int	The version of the configuration that the probe was running at the time of the test	false	false
ip_network	varchar(16)	The IP address of the probe at the time of running the test	false	false
status	varchar(8)	Text description of the result: "ok", "blocked", "error", "timeout", "dnserror", "rejected"	false	false
http_status	int	HTTP return code for the URL. -1 for errors.	false	false
network_name	varchar(64)	Name of the ISP that the test was run against (ref. ISPs table)	false	false
created	datetime	Date/Time of the test	false	false
filter_level	varchar(16)	Optional level of filtering enabled on the ISP at the time of the test	false	false

A list of ISPs that the database is aware of.

A table that lists the AS / Network names for an ISP. Multiple names can map to a single ISP.

Column Name	Column Type	Purpose	Unique	Auto Inc
id	int unsigned	Unique Identifier	true	true
ispID	int unsigned	ISP Identifier	false	false
alias	varchar(64)	Name returned by IP lookup to map to ISP	true	false
created	datetime	Date/Time of creation	false	false

Table used for caching IP <-> network lookups. Results are cached for 7 days

Submission info from users for a URL. A URL can be requested by many people over time.

Column Name	Column Type	Purpose	Unique	Auto Inc
id	int unsigned	Unique Identifier	true	true
urlID	int	URL ID number	false	false
userID	int	API user account that submitted the request	false	false
submission_info	text	Additional serialized data for the request - structure is entirely up to the client.	false	false
created	datetime	Date/Time of creation	false	false