popufare/server/sql_schema/create_tables.sqlite

--
-- Copyright (c) 2019 Clementine Computing LLC.
--
-- This file is part of PopuFare.
--
-- PopuFare is free software: you can redistribute it and/or modify
-- it under the terms of the GNU Affero General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- PopuFare is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU Affero General Public License for more details.
--
-- You should have received a copy of the GNU Affero General Public License
-- along with PopuFare.  If not, see <https://www.gnu.org/licenses/>.
--

-- AVLS data table receives all AVLS chirps and stores them for later reference
-- DROP TABLE IF EXISTS `avls_data`;
CREATE TABLE IF NOT EXISTS avls_data (
  id INTEGER PRIMARY KEY,

  equip_num INTEGER,
  driver INTEGER,
  paddle INTEGER,
  route  INTEGER,
  trip  INTEGER,
  stop  INTEGER,
  chirp_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  latitude REAL,
  longitude REAL,
  heading REAL,
  velocity REAL

);

-- Bus Pass Table holds all bus pass holders
-- DROP TABLE IF EXISTS `active_rider_table`;
CREATE TABLE IF NOT EXISTS active_rider_table (

  -- The following entries are stored on the client as well and kept in sync
  seq_num INTEGER UNIQUE PRIMARY KEY ,

  logical_card_id INTEGER NOT NULL,
  rfid_token VARCHAR(32),
  mag_token VARCHAR(32),
  rule_name VARCHAR(24),
  rule_param VARCHAR(24),

  -- The rest are for server-side recordkeeping...

  deleted BOOLEAN DEFAULT '0',

  parent_entity VARCHAR(32),
  notes  VARCHAR(64)


) ;

create INDEX active_rider_table_seq_idx on active_rider_table (seq_num);
create INDEX active_rider_table_card_idx on active_rider_table (logical_card_id);
create INDEX active_rider_table_card_seq_and_card on active_rider_table (logical_card_id, seq_num);


-- Billing Log Table

-- DROP TABLE IF EXISTS `billing_log`;
CREATE TABLE IF NOT EXISTS billing_log (
  id INTEGER PRIMARY KEY,

  -- MD5 sum of the orignal record as sent by the client, used to confirm storing the record
  conf_checksum VARCHAR(32) UNIQUE,

  -- Contents of the billing log entry coming from the client
  equip_num     INTEGER,
  driver        INTEGER,
  paddle        INTEGER,
  route         INTEGER,
  trip          INTEGER,
  stop          INTEGER,
  ride_time TIMESTAMP,
  latitude      REAL,
  longitude     REAL,
  action VARCHAR(16),
  rule  VARCHAR(24),
  ruleparam VARCHAR(24),
  reason VARCHAR(64),
  credential VARCHAR(32),
  logical_card_id INTEGER NOT NULL,
  cash_value INTEGER,
  stop_name VARCHAR(64)

) ;


-- DROP TABLE IF EXISTS `diagnostic_log`;
CREATE TABLE IF NOT EXISTS diagnostic_log (
  id INTEGER PRIMARY KEY,

  servertime TIMESTAMP default CURRENT_TIMESTAMP,
  loglvl VARCHAR(8),
  message VARCHAR(256)

  -- And some additional index data

) ;
create INDEX servertime_idx on diagnostic_log (servertime);
create INDEX lvl_msg_idx on diagnostic_log (servertime, loglvl);


-- DROP TABLE IF EXISTS `pass_option`;
create table pass_option(
  id INTEGER PRIMARY KEY,
  group_id    INTEGER,
  param       INTEGER,
  name        varchar(255),
  rule        varchar(255),
  db_rule     varchar(255),
  type        varchar(255),
  description varchar(255),
  num_opt     INTEGER   default 0,
  option0     varchar(255),
  option1     varchar(255),
  option2     varchar(255),
  option3     varchar(255),

  active      INTEGER default 0

) ;


-- DROP TABLE IF EXISTS rule_class;
CREATE TABLE IF NOT EXISTS rule_class (
  id INTEGER PRIMARY KEY,
  rulename VARCHAR(24),
  ruleclass VARCHAR(24)
) ;

-- DROP TABLE IF EXISTS update_level;
CREATE TABLE IF NOT EXISTS update_level (
  id INTEGER PRIMARY KEY,
  equip_num INTEGER NOT NULL DEFAULT 0,
  client_file VARCHAR(32) NOT NULL,
  checksum VARCHAR(32) NOT NULL,
  file_size INTEGER NOT NULL,
  file_path VARCHAR(512) NOT NULL,
  fileversion VARCHAR(32),

  -- The following field is never manually set and is used only to pull the latest value
  serial INTEGER NOT NULL

  -- These indicies should make the operation of "give me the latest of each update for bus number X" fast

);
create INDEX ul_equip_num_idx on update_level (equip_num);
create INDEX client_file_idx on update_level (client_file);
create INDEX serial_idx on update_level (serial);
create INDEX file_eqip_idx on update_level (client_file, equip_num);


CREATE TABLE IF NOT EXISTS bus_checkin_log (
  id INTEGER PRIMARY KEY,
  checkin_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  busunit_num INTEGER NOT NULL DEFAULT 0,
  equip_num INTEGER NOT NULL DEFAULT 0,
  eth0_mac VARCHAR(17),
  cell_imei VARCHAR(15),
  cell_imsi VARCHAR(15),
  version_data VARCHAR(256)

) ;

create INDEX time_index on bus_checkin_log (checkin_time); 
create INDEX bcl_equip_num_idx on bus_checkin_log (equip_num);
create INDEX busunit_num_idx on bus_checkin_log (busunit_num);

DROP TABLE IF EXISTS `user_pass`;
CREATE TABLE `user_pass` (
  user_pass_id INTEGER PRIMARY KEY,
  logical_card_id INTEGER default NULL,

  issued datetime default NULL,
  activated datetime default NULL,
  deactivated datetime default NULL,

  firstused datetime default NULL,
  lastused datetime default NULL,

  nrides_orig int(11) default NULL,
  nrides_remain int(11) default NULL,

  nday_orig int(11) default NULL,
  nday_expiration datetime default NULL,

  active tinyint(1) default 0,
  expired tinyint(1) default 0,

  rule varchar(255) default NULL,
  queue_order int(11) default NULL,

  comment varchar(255) default NULL,
  paytype varchar(255) default NULL

) ;


DROP TABLE IF EXISTS `user_card`;
CREATE TABLE `user_card` (
  logical_card_id INTEGER PRIMARY KEY,

  -- legacy, will remove in final phase2
  -- card_number varchar(128) UNIQUE default NULL,
  -- rfsite int(127) default NULL,
  -- rfid int(127) default NULL,
  --

  mag_token varchar(255) default null,
  rfid_token varchar(255) default null,

  comment varchar(255) default NULL,
  userid int(11) default NULL,

  issued datetime default NULL,
  active tinyint(1) default 1,
  deactivated datetime default NULL,

  lastused datetime default NULL,
  firstused datetime default NULL,

  group_id int(11) default NULL,
  issuetype varchar(255) default NULL

) ;


DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  group_id INTEGER PRIMARY KEY,
  group_name varchar(255) default NULL
) ;


DROP TABLE IF EXISTS `audit_user_pass`;
CREATE TABLE `audit_user_pass` (
  `audit_user_pass_id` INTEGER PRIMARY KEY,
  `old_user_pass_id` INTEGER default NULL,
  `old_logical_card_id` INTEGER default NULL,
  `old_issued` datetime default NULL,
  `old_activated` datetime default NULL,
  `old_deactivated` datetime default NULL,
  `old_firstused` datetime default NULL,
  `old_lastused` datetime default NULL,
  `old_nrides_orig` INTEGER default NULL,
  `old_nrides_remain` INTEGER default NULL,
  `old_nday_orig` INTEGER default NULL,
  `old_nday_expiration` datetime default NULL,
  `old_active` INTEGER default NULL,
  `old_rule` char(255) default NULL,
  `old_queue_order` INTEGER default NULL,
  `old_log_id` INTEGER default NULL,
  `old_comment` varchar(255) default NULL,
  `new_user_pass_id` INTEGER default NULL,
  `new_logical_card_id` INTEGER default NULL,
  `new_issued` datetime default NULL,
  `new_activated` datetime default NULL,
  `new_deactivated` datetime default NULL,
  `new_firstused` datetime default NULL,
  `new_lastused` datetime default NULL,
  `new_nrides_orig` INTEGER default NULL,
  `new_nrides_remain` INTEGER default NULL,
  `new_nday_orig` INTEGER default NULL,
  `new_nday_expiration` datetime default NULL,
  `new_active` INTEGER default NULL,
  `new_rule` char(255) default NULL,
  `new_queue_order` INTEGER default NULL,
  `new_log_id` INTEGER default NULL,
  `new_comment` varchar(255) default NULL,
  `comment` varchar(255) default NULL,
  `owner_id` INTEGER default NULL,
  --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `old_expired` INTEGER default NULL,
  `new_expired` INTEGER default NULL,
  `old_paytype` varchar(255) default NULL,
  `new_paytype` varchar(255) default NULL

) ;
create INDEX audit_user_pass_id_idx on audit_user_pass (audit_user_pass_id);
create INDEX audit_user_pass_timestamp_idx on audit_user_pass (timestamp);
create INDEX audit_user_pass_new_user_pass_id_idx on audit_user_pass (new_user_pass_id);
create INDEX audit_user_pass_old_user_pass_id_idx on audit_user_pass (old_user_pass_id);



DROP TABLE IF EXISTS `audit_user_card`;
CREATE TABLE `audit_user_card` (
  `audit_user_card_id` INTEGER PRIMARY KEY,
  `old_logical_card_id` INTEGER default NULL,
  `old_comment` varchar(255) default NULL,
  `old_lastused` datetime default NULL,
  `old_userid` INTEGER default NULL,
  `old_issued` datetime default NULL,
  `old_firstused` datetime default NULL,
  `old_group_id` INTEGER default NULL,
  `old_issuetype` varchar(255) default NULL,
  `new_logical_card_id` INTEGER default NULL,
  `new_comment` varchar(255) default NULL,
  `new_lastused` datetime default NULL,
  `new_userid` INTEGER default NULL,
  `new_issued` datetime default NULL,
  `new_firstused` datetime default NULL,
  `new_group_id` INTEGER default NULL,
  `new_issuetype` varchar(255) default NULL,
  --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `comment` varchar(255) default NULL,
  `old_mag_token` varchar(31) default NULL,
  `new_mag_token` varchar(31) default NULL,
  `old_rfid_token` varchar(31) default NULL,
  `new_rfid_token` varchar(31) default NULL,
  `old_active` INTEGER default NULL,
  `new_active` INTEGER default NULL,
  `old_deactivated` datetime default null,
  `new_deactivated` datetime default null

) ;
create INDEX audit_user_card_id_idx on audit_user_card (audit_user_card_id);
create INDEX audit_user_card_timestampe_idx on audit_user_card (timestamp);

create INDEX audit_user_card_new_logical_card_id_idx on audit_user_card (new_logical_card_id);
create INDEX audit_user_card_old_logical_card_id_idx on audit_user_card (old_logical_card_id);

create INDEX audit_user_card_new_mag_token_idx on audit_user_card (new_mag_token);
create INDEX audit_user_card_old_mag_token_idx on audit_user_card (old_mag_token);

create INDEX audit_user_card_new_rfid_token_idx on audit_user_card (new_rfid_token);
create INDEX audit_user_card_old_rfid_token_idx on audit_user_card (old_rfid_token);


DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
          `userid` INTEGER PRIMARY KEY,
          `username` char(255) default NULL,
          `comment` char(255) default NULL,
          `first_name` char(128) default NULL,
          `last_name` char(128) default NULL,
          `phone` char(31) default NULL,
          `email` char(128) default NULL,
          `address` char(255) default NULL,
          `city` char(127) default NULL,
          `state` char(127) default NULL,
          `zip` char(31) default NULL,
          `created` datetime default NULL,
          `active` INTEGER default NULL,
          `passwordhash` varchar(255) default NULL,
          `shipping_address` varchar(255) default NULL,
          `shipping_city` varchar(255) default NULL,
          `shipping_state` varchar(255) default NULL,
          `shipping_zip` varchar(255) default NULL,
          `shipping_name` varchar(255) default NULL,
          `shipping_country_code` varchar(32) default NULL,
          `shipping_country_name` varchar(255) default NULL,
          `reset_attempts` INTEGER default '0'
) ;


DROP TABLE IF EXISTS `audit_users`;
CREATE TABLE `audit_users` (
          `audit_users_id` INTEGER PRIMARY KEY,
          --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `comment` varchar(255) default NULL,
          `old_username` char(255) default NULL,
          `old_userid` INTEGER default NULL,
          `old_comment` char(255) default NULL,
          `old_first_name` char(128) default NULL,
          `old_last_name` char(128) default NULL,
          `old_phone` char(31) default NULL,
          `old_email` char(128) default NULL,
          `old_address` char(255) default NULL,
          `old_city` char(127) default NULL,
          `old_state` char(127) default NULL,
          `old_zip` char(31) default NULL,
          `old_created` datetime default NULL,
          `old_active` INTEGER default NULL,
          `old_passwordhash` varchar(255) default NULL,
          `new_username` char(255) default NULL,
          `new_userid` INTEGER default NULL,
          `new_comment` char(255) default NULL,
          `new_first_name` char(128) default NULL,
          `new_last_name` char(128) default NULL,
          `new_phone` char(31) default NULL,
          `new_email` char(128) default NULL,
          `new_address` char(255) default NULL,
          `new_city` char(127) default NULL,
          `new_state` char(127) default NULL,
          `new_zip` char(31) default NULL,
          `new_created` datetime default NULL,
          `new_active` INTEGER default NULL,
          `new_passwordhash` varchar(255) default NULL,
          `old_shipping_address` varchar(255) default NULL,
          `old_shipping_city` varchar(255) default NULL,
          `old_shipping_state` varchar(255) default NULL,
          `old_shipping_zip` varchar(255) default NULL,
          `old_shipping_name` varchar(255) default NULL,
          `old_shipping_country_code` varchar(255) default NULL,
          `old_shipping_country_name` varchar(32) default NULL,
          `old_reset_attempts` varchar(255) default NULL,
          `new_shipping_address` varchar(255) default NULL,
          `new_shipping_city` varchar(255) default NULL,
          `new_shipping_state` varchar(255) default NULL,
          `new_shipping_zip` varchar(255) default NULL,
          `new_shipping_name` varchar(255) default NULL,
          `new_shipping_country_code` varchar(255) default NULL,
          `new_shipping_country_name` varchar(32) default NULL,
          `new_reset_attempts` varchar(255) default NULL
) ;


-- Admin Web UI tables

DROP TABLE IF EXISTS `admins`;
CREATE TABLE `admins` (
          `userid` INTEGER PRIMARY KEY,
          `group_id` INTEGER default NULL,
          `username` char(255) default NULL,
          `password` char(255) default NULL,
          `active` INTEGER default 1,
          `comment` varchar(255) default null
) ;

DROP TABLE IF EXISTS `audit_admins`;
CREATE TABLE `audit_admins` (
          `audit_admins_id` INTEGER PRIMARY KEY,
          --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `comment` varchar(255) default NULL,
          `old_userid` INTEGER default NULL,
          `old_username` varchar(255) default NULL,
          `old_password` varchar(255) default NULL,
          `new_userid` INTEGER default NULL,
          `new_username` varchar(255) default NULL,
          `new_password` varchar(255) default NULL
) ;


DROP TABLE IF EXISTS `admins_session_info`;
CREATE TABLE `admins_session_info` (
 `id` INTEGER PRIMARY KEY,
          `userid` INTEGER default NULL,
          `sessionid` char(255) default NULL,
          `lastactive` datetime default NULL
) ;

DROP TABLE IF EXISTS `admin_groups`;
CREATE TABLE `admin_groups` (
  id INTEGER PRIMARY KEY,
          `userid` INTEGER default NULL,
          `group_id` INTEGER default NULL,
          `permissions` INTEGER default NULL
) ;

DROP TABLE IF EXISTS `rule_mappings`;
CREATE TABLE `rule_mappings` (
  id INTEGER PRIMARY KEY,
          `rule` char(255) NOT NULL default '',
          `rule_text` char(255) default NULL,
          `group_id` INTEGER default NULL,
          `active` INTEGER
) ;

DROP TABLE IF EXISTS `org_default_pass_value`;
CREATE TABLE `org_default_pass_value` (
          `id` INTEGER PRIMARY KEY,
          `name` varchar(255) default NULL,
          `nday` INTEGER default NULL,
          `nride` INTEGER default NULL,
          `description` varchar(255) default NULL,
          `start` datetime default NULL,
          `end` datetime default NULL
) ;

DROP TABLE IF EXISTS `org_default_card_value`;
CREATE TABLE `org_default_card_value` (
          `id` INTEGER PRIMARY KEY,
          `group_id` INTEGER default NULL,
          `mag_track` INTEGER default NULL,
          `rf_length` INTEGER default NULL,
          `rf_site` INTEGER default NULL
) ;


-- WEB API tables

DROP TABLE IF EXISTS `authorization_log`;
CREATE TABLE `authorization_log` (
          `authorization_log_id` INTEGER PRIMARY KEY,
          --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `authorization_type` varchar(255) default NULL,
          `authorization_code` varchar(255) default NULL,
          `user_id` INTEGER default NULL,
          `logical_card_id` INTEGER default NULL,
          `user_pass_id` INTEGER default NULL,
          `comment` varchar(255) default NULL
) ;

DROP TABLE IF EXISTS `org_card_order_queue`;
CREATE TABLE `org_card_order_queue` (
          `org_card_order_queue_id` INTEGER PRIMARY KEY,
          `userid` INTEGER default NULL,
          `logical_card_id` INTEGER default NULL,
          `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `processed` timestamp NULL default NULL,
          `comment` varchar(255) default NULL,
          `pending` INTEGER default NULL
) ;


DROP TABLE IF EXISTS `org_api_session`;
CREATE TABLE `org_api_session` (
  id INTEGER PRIMARY KEY,
          --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `ip` varchar(15) default NULL,
          `active` INTEGER default NULL,
          `server_token` varchar(255) default NULL,
          `user_token` varchar(255) default NULL
) ;

DROP TABLE IF EXISTS `org_api_log`;
CREATE TABLE `org_api_log` (
          `log_id` INTEGER PRIMARY KEY,
          --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `log` blob
) ;

DROP TABLE IF EXISTS `org_api_password_reset`;
CREATE TABLE `org_api_password_reset` (
  id INTEGER PRIMARY KEY,
          --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `token` varchar(255) default NULL,
          `userid` INTEGER default NULL,
          `email` varchar(255) default NULL,
          `active` INTEGER default NULL
) ;

DROP TABLE IF EXISTS `org_api_register_email`;
CREATE TABLE `org_api_register_email` (
  id INTEGER PRIMARY KEY,
          --`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
          `token` varchar(255) default NULL,
          `active` INTEGER default NULL,
          `email` varchar(255) default NULL
) ;



-- drivers, stops and paddles + misc

DROP TABLE IF EXISTS `drivers`;


CREATE TABLE `drivers` (
  `id` INTEGER PRIMARY KEY,
  `pin` varchar(8) NOT NULL default '',
  `name` varchar(32) default NULL
) ;


--
-- Table structure for table `stops`
--

DROP TABLE IF EXISTS `stops`;


CREATE TABLE `stops` (
  `id` INTEGER PRIMARY KEY,
  `latitude` double NOT NULL default '0',
  `longitude` double NOT NULL default '0',
  `name` varchar(32) default NULL
) ;


--
-- Table structure for table `paddles`
--

DROP TABLE IF EXISTS `paddles`;


CREATE TABLE `paddles` (
  `id` INTEGER PRIMARY KEY,
  `slot` INTEGER NOT NULL default '0',
  `arrival` time default NULL,
  `route` INTEGER default NULL,
  `trip` INTEGER default NULL,
  `stage` INTEGER default NULL,
  `stop` INTEGER default NULL,
  `stopid` INTEGER NOT NULL default '0'
) ;


--
-- Table structure for table `old_stops`
--

DROP TABLE IF EXISTS `old_stops`;


CREATE TABLE `old_stops` (
  `id` INTEGER PRIMARY KEY,
  `verstring` text,
  `latitude` double NOT NULL default '0',
  `longitude` double NOT NULL default '0',
  `name` varchar(32) default NULL
) ;


--
-- Table structure for table `old_paddles`
--

DROP TABLE IF EXISTS `old_paddles`;


CREATE TABLE `old_paddles` (
  `id` INTEGER PRIMARY KEY,
  `verstring` text,
  `slot` INTEGER NOT NULL default '0',
  `arrival` time default NULL,
  `route` INTEGER default NULL,
  `trip` INTEGER default NULL,
  `stage` INTEGER default NULL,
  `stop` INTEGER default NULL,
  `stopid` INTEGER NOT NULL default '0'
) ;


--
-- Table structure for table `live_stops`
--

DROP TABLE IF EXISTS `live_stops`;


CREATE TABLE `live_stops` (
  `id` INTEGER PRIMARY KEY,
  `latitude` double NOT NULL default '0',
  `longitude` double NOT NULL default '0',
  `name` varchar(32) default NULL
) ;


--
-- Table structure for table `live_paddles`
--

DROP TABLE IF EXISTS `live_paddles`;


CREATE TABLE `live_paddles` (
  `id` INTEGER PRIMARY KEY,
  `slot` INTEGER NOT NULL default '0',
  `arrival` time default NULL,
  `route` INTEGER default NULL,
  `trip` INTEGER default NULL,
  `stage` INTEGER default NULL,
  `stop` INTEGER default NULL,
  `stopid` INTEGER NOT NULL default '0'
) ;



DROP TABLE IF EXISTS `price_point`;
CREATE TABLE `price_point` (
  `id` INTEGER PRIMARY KEY,
  `price` double default NULL,
  `param` INTEGER default NULL,
  `name` varchar(255) default NULL,
  `rule` varchar(32) default NULL,
  `db_rule` varchar(32) default NULL,
  `group_id` INTEGER default NULL,
  `type` varchar(255) default NULL,
  `description` varchar(1024) default NULL,
  `num_opt` INTEGER default 0,
  `price_option0` varchar(255) default null,
  `price_option1` varchar(255) default null,
  `price_option2` varchar(255) default null,
  `price_option3` varchar(255) default null,
  `active` INTEGER default 0
) ;


drop table if exists billing_log_annotation;
create table if not exists billing_log_annotation (
  id INTEGER PRIMARY KEY,
  seq_num INTEGER not null,
  rule varchar(24),
  ruleparam varchar(24),
  reason varchar(64),
  credential varchar(32),
  note varchar(512),
  timestamp timestamp default current_timestamp
) ;