Database tables

Daftar isi

accesslog

Track web access

Schema
backoffice

CREATE TABLE accesslog (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('accesslog_id_seq'::regclass),
   userid integer,
   sessionid character varying,
   sessionstart timestamp without time zone NOT NULL DEFAULT now(),
   sessionend timestamp without time zone,
   screenx integer,
   screeny integer,
   httpuseragent text,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer,
   del boolean NOT NULL DEFAULT false
);

adminlevels

Spatial administrative unit levels (province, district etc)

Schema
reference

CREATE TABLE adminlevels (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('adminlevels_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

afkir

Culling activities

Schema
data

CREATE TABLE afkir (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('afkir_id_seq'::regclass),
   programafkirid integer NOT NULL REFERENCES programafkir,
   speciesid integer NOT NULL REFERENCES species,
   jumlah integer NOT NULL,
   locationid integer NOT NULL REFERENCES locations,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

ages

Animal age categories

Schema
reference

CREATE TABLE ages (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('ages_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

ageunits

Units for age measurement

Schema
reference

CREATE TABLE ageunits (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('ageunits_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   days integer
);

aireportanimals

Schema
data

CREATE TABLE aireportanimals (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('aireportanimals_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   aireportsid integer NOT NULL REFERENCES aireports,
   speciesid integer NOT NULL REFERENCES species,
   totaldead integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

aireports

Schema
data

CREATE TABLE aireports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('aireports_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   backyard boolean NOT NULL DEFAULT true,
   productionsystemid integer NOT NULL REFERENCES productionsystems,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

animal_types

Animal type classification for slaughter and population

Schema
reference

CREATE TABLE animal_types (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('animal_types_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   hiercode character varying UNIQUE,
   name character varying[] NOT NULL,
   species integer REFERENCES species,
   sex at_sex,
   purpose at_purpose,
   origin at_origin,
   productive boolean,
   age integer REFERENCES ages,
   population boolean NOT NULL DEFAULT true,
   slaughter boolean NOT NULL DEFAULT false,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

animalid

Schema
backoffice

CREATE TABLE animalid (
   id integer
);

animals

Animals in a laboratory submission. On submission may include several animals from several different owners

Schema
lab

CREATE TABLE animals (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('animals_id_seq'::regclass),
   labsubmissionid integer NOT NULL REFERENCES labsubmissions,
   animalident character varying NOT NULL,
   ownerid integer REFERENCES owners,
   animalid bigint REFERENCES hewan,
   speciesid integer NOT NULL REFERENCES species,
   sexid integer REFERENCES sex,
   age double precision,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   ageunitsid integer REFERENCES ageunits,
   msgid bigint
);

app_downloads

This table is for logging downloads. This is the only table that the app_downloads user can actually do anything other than read.

Schema
metadata

CREATE TABLE app_downloads (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('app_downloads_id_seq'::regclass),
   userid integer NOT NULL,
   reportid integer NOT NULL,
   outcome character varying,
   createdby integer NOT NULL DEFAULT 1,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

app_projects

This table holds project definitions for delivery by the app.php action

Schema
metadata

CREATE TABLE app_projects (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('app_projects_id_seq'::regclass),
   name character varying[] NOT NULL,
   description character varying[] NOT NULL,
   projectdefinition json,
   createdby integer NOT NULL DEFAULT 1,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

app_reports

This table holds reports for delivery by the app.php action

Schema
metadata

CREATE TABLE app_reports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('app_reports_id_seq'::regclass),
   name character varying[] NOT NULL,
   description character varying[] NOT NULL,
   reportdefinition json,
   createdby integer NOT NULL DEFAULT 1,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

app_reports

This field holds the full json string for the app download.

Schema
metadata

CREATE TABLE app_reports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('app_reports_id_seq'::regclass),
   name character varying[] NOT NULL,
   description character varying[] NOT NULL,
   reportdefinition json,
   createdby integer NOT NULL DEFAULT 1,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

app_static

This table holds static data for delivery by the app.php action

Schema
metadata

CREATE TABLE app_static (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('app_static_id_seq'::regclass),
   name character varying[] NOT NULL,
   description character varying[] NOT NULL,
   createdby integer NOT NULL DEFAULT 1,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   sql character varying,
   version integer NOT NULL DEFAULT 1
);

apphelp

Schema
reference

CREATE TABLE apphelp (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('apphelp_id_seq'::regclass),
   appid integer NOT NULL DEFAULT 1,
   pageid integer NOT NULL,
   pageref character varying,
   help character varying[],
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

asalbibit

Origin of seed for forage crops

Schema
reference

CREATE TABLE asalbibit (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('asalbibit_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

asuransihewan

Events table for an individual animal, used to store a range of different event types

Schema
data

CREATE TABLE asuransihewan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('asuransihewan_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   idhewan bigint REFERENCES hewan,
   idstatusasuransi integer REFERENCES statusasuransi,
   nomor bigint NOT NULL,
   masaawal date,
   masaakhir date,
   nilai bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL
);

auth_assignment

Schema
backoffice

CREATE TABLE auth_assignment (
   itemname character varying(64) PRIMARY KEY NOT NULL,
   userid character varying(64) PRIMARY KEY NOT NULL,
   bizrule text,
   data text
);

auth_item

Schema
backoffice

CREATE TABLE auth_item (
   name character varying(64) PRIMARY KEY NOT NULL,
   type integer NOT NULL,
   description text,
   bizrule text,
   data text
);

auth_item_child

Schema
backoffice

CREATE TABLE auth_item_child (
   parent character varying(64) NOT NULL,
   child character varying(64) NOT NULL
);

bmindotempprov

Schema
backoffice

CREATE TABLE bmindotempprov (
   id integer,
   name character varying,
   code locationcode,
   bpscode character varying,
   level integer,
   validfrom date,
   validto date,
   geom public.geometry(MultiPolygon,4326),
   centroid public.geometry(Point,4326),
   createdby integer,
   createdon timestamp without time zone,
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean,
   comment text,
   geom_proj public.geometry(MultiPolygon,3857),
   centroid_proj public.geometry(Point,3857),
   temp boolean
);

brewerpalettes

Colour definitions for charts

Schema
reference

CREATE TABLE brewerpalettes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('brewerpalettes_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

businessrules

Business rules definining custom actions to be taken during data processing

Schema
backoffice

CREATE TABLE businessrules (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('businessrules_id_seq1'::regclass),
   name character varying,
   description character varying,
   hook hook,
   caller character varying,
   ruleorder integer,
   rulefunction character varying,
   sql character varying,
   createdby integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

businessrulesold

Schema
backoffice

CREATE TABLE businessrulesold (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('businessrules_id_seq'::regclass),
   hook hook,
   caller character varying,
   ruleorder integer,
   rulefunction character varying,
   sql character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

cadrereports

A dummy table for testing of the spreadsheet parsing

Schema
data

CREATE TABLE cadrereports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('cadrereports_id_seq'::regclass),
   msgid character varying NOT NULL,
   reportdate date NOT NULL,
   location integer NOT NULL REFERENCES locations,
   syndrome integer REFERENCES syndromes,
   signids integer[],
   speciesid integer NOT NULL REFERENCES species,
   numcases integer NOT NULL,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL,
   modifiedon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL
);

cadreresponses

A dummy table for testing of the spreadsheet parsing

Schema
data

CREATE TABLE cadreresponses (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('cadreresponses_id_seq'::regclass),
   caseid parentlink NOT NULL REFERENCES cadrereports,
   msgid character varying NOT NULL,
   responsedate date NOT NULL,
   diagnosis integer[] NOT NULL,
   otherdiagnosis character varying,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL,
   modifiedon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL
);

case_animals

Individual animals in a disease case

Schema
data

CREATE TABLE case_animals (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('case_animals_id_seq'::regclass),
   idcase integer NOT NULL REFERENCES cases,
   idhewan integer NOT NULL REFERENCES hewan,
   msgid bigint,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean DEFAULT false
);

caseimages

Images from a disease case

Schema
data

CREATE TABLE caseimages (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('caseimages_id_seq'::regclass),
   msgid bigint NOT NULL,
   caseid integer REFERENCES cases,
   image character varying NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

caseresults

Outcome of a case (resolved, died, still sick)

Schema
data

CREATE TABLE caseresults (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('caseresults_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   result integer NOT NULL REFERENCES caseresulttypes,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   jumlah integer
);

caseresulttypes

Outcomes of clinical cases

Schema
reference

CREATE TABLE caseresulttypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('caseresulttypes_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   code character varying
);

cases

Master table for all field disease cases

Schema
data

CREATE TABLE cases (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('cases_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   msgid bigint NOT NULL,
   locationid integer NOT NULL REFERENCES locations,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false
);

containershipment

Schema
data

CREATE TABLE containershipment (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('containershipment_id_seq'::regclass),
   shipmentid integer REFERENCES shipments,
   receiptid integer REFERENCES receipt,
   stocktakeid integer REFERENCES stocktake,
   jeniskontainerid integer REFERENCES jeniskontainer,
   kondisikontainerid integer REFERENCES kondisikontainer,
   total integer NOT NULL,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

dashboard_report

Schema
backoffice

CREATE TABLE dashboard_report (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('dashboard_report_id_seq1'::regclass),
   name character varying[],
   modelid integer,
   order integer,
   active boolean,
   del boolean,
   createdby integer,
   createdon timestamp with time zone,
   modifiedby integer,
   modifiedon timestamp with time zone,
   sql text,
   typereport text,
   jsonobj json
);

dashboard_report_user

Schema
backoffice

CREATE TABLE dashboard_report_user (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('dashboard_report_user_id_seq'::regclass),
   id_report integer[],
   id_user integer
);

dashboard_widget

Schema
backoffice

CREATE TABLE dashboard_widget (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('dashboard_widget_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users,
   reportid integer NOT NULL REFERENCES reports,
   order integer,
   del boolean NOT NULL DEFAULT false,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp with time zone,
   widgettype character varying DEFAULT 'widget'::character varying
);

datatables

Schema
metadata

CREATE TABLE datatables (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('datatables_id_seq'::regclass),
   tblname character varying UNIQUE NOT NULL,
   displayname character varying[] NOT NULL,
   parenttbl integer REFERENCES datatables,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false
);

daterangetypes

Pre-defined date ranges for reporting

Schema
reference

CREATE TABLE daterangetypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('daterangetypes_id_seq'::regclass),
   name character varying[] UNIQUE NOT NULL,
   description character varying,
   sortorder integer,
   sql text,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false,
   startdate character varying,
   enddate character varying
);

deskripsihewan

Description of an animal for individual animal identification

Schema
data

CREATE TABLE deskripsihewan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('deskripsihewan_id_seq'::regclass),
   idhewan bigint REFERENCES hewan,
   cap character varying,
   warna integer REFERENCES warna,
   tanduk character varying,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

diagnoses

Definitive diagnosis for cases. May be submitted as part of lab submission or by field SMS.

Schema
data

CREATE TABLE diagnoses (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('diagnoses_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   diseaseid integer NOT NULL REFERENCES diseases,
   labsubmissionid integer REFERENCES labsubmissions,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL
);

disease_details

Schema
reference

CREATE TABLE disease_details (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('disease_details_id_seq'::regclass),
   diseaseid integer REFERENCES diseases,
   name character varying[] NOT NULL,
   other_names character varying[],
   description character varying[],
   diagnosis character varying[],
   treatment character varying[],
   prevention character varying[],
   species integer[],
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

diseaseevents

Master table of major disease events, used to group cases and programs

Schema
data

CREATE TABLE diseaseevents (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('diseaseevents_id_seq'::regclass),
   name character varying[] NOT NULL,
   startdate date NOT NULL DEFAULT now(),
   enddate date,
   diseaseid integer REFERENCES diseases,
   area integer[],
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

diseases

Master list of diseases

Schema
reference

CREATE TABLE diseases (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('diseases_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   synonym integer,
   priority boolean NOT NULL DEFAULT false,
   strategic boolean NOT NULL DEFAULT false,
   main boolean NOT NULL DEFAULT false,
   oie boolean NOT NULL DEFAULT false,
   contagious boolean NOT NULL DEFAULT false,
   zoonotic boolean NOT NULL DEFAULT false,
   exotic boolean NOT NULL DEFAULT false,
   prevalence real,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   synname character varying[]
);

diseasesigns

Probabiilty matrix of the occurrence of different signs with different diseases

Schema
reference

CREATE TABLE diseasesigns (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('diseasesigns_id_seq'::regclass),
   diseaseid integer NOT NULL REFERENCES diseases,
   signid integer NOT NULL REFERENCES signs,
   prob real NOT NULL DEFAULT 0.5,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

diseasespecies

Species susceptible to different diseases

Schema
reference

CREATE TABLE diseasespecies (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('diseasespecies_id_seq'::regclass),
   diseaseid integer NOT NULL REFERENCES diseases,
   speciesid integer NOT NULL REFERENCES species,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

distribusistraw

Schema
data

CREATE TABLE distribusistraw (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('distribusistraw_id_seq'::regclass),
   idstraw character varying NOT NULL,
   jumlah integer NOT NULL,
   tanggal timestamp without time zone NOT NULL DEFAULT now(),
   asal integer NOT NULL REFERENCES locations,
   tujuan integer NOT NULL REFERENCES locations,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

drugclasses

Classification of drugs by regulation status

Schema
reference

CREATE TABLE drugclasses (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('drugclasses_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

drugs

Master table of registered and other drugs

Schema
data

CREATE TABLE drugs (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('drugs_id_seq'::regclass),
   drugtypeid integer NOT NULL REFERENCES drugtypes,
   code character varying,
   hiercode character varying,
   name character varying NOT NULL,
   regnumber character varying,
   regyear integer,
   di character(1),
   bfpl character(1),
   manufacturer character varying,
   licensee character varying,
   composition character varying,
   class integer REFERENCES drugclasses,
   packaging character varying,
   doseunits units,
   indication character varying,
   comments character varying,
   synonym integer,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   paravet boolean NOT NULL DEFAULT false
);

drugtypes

Classification of drugs by functional group

Schema
reference

CREATE TABLE drugtypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('drugtypes_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   hiercode character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   synonym integer,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

emailnotifications

Schema
backoffice

CREATE TABLE emailnotifications (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('emailnotifications_id_seq'::regclass),
   type character varying,
   messageid character varying,
   topicarn character varying,
   message jsonb,
   notificationtime timestamp with time zone,
   signatureversion character varying,
   signature character varying,
   signingcerturl character varying,
   unsubscribeurl character varying,
   recipient character varying,
   createdby integer NOT NULL DEFAULT 0,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

errors

Log of SMS format errors

Schema
sms

CREATE TABLE errors (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('errors_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users,
   receivedtime timestamp without time zone,
   message character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

estrus

Schema
data

CREATE TABLE estrus (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('estrus_id_seq'::regclass),
   animals integer NOT NULL DEFAULT 1,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   del boolean NOT NULL DEFAULT false
);

eventcase

Link table to associate multiple cases with a single major disease event

Schema
data

CREATE TABLE eventcase (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('eventcase_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   diseaseeventid integer NOT NULL REFERENCES cases,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

exportreferencenamedranges

Definition of Excel named ranges for the reference table export system

Schema
reference

CREATE TABLE exportreferencenamedranges (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('exportreferencenamedranges_id_seq'::regclass),
   tabid integer NOT NULL REFERENCES exportreferencetabs,
   rangename character varying NOT NULL,
   range character varying NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

exportreferences

Master table for the reference table export system

Schema
reference

CREATE TABLE exportreferences (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('exportreferences_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   filename character varying,
   listsql character varying,
   listname character varying[],
   replysql character varying
);

exportreferencetabs

Tab (worksheet) content definition for the reference table export system

Schema
reference

CREATE TABLE exportreferencetabs (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('exportreferencetabs_id_seq'::regclass),
   exportreferenceid integer NOT NULL REFERENCES exportreferences,
   tabname character varying NOT NULL,
   sql character varying NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   sortorder integer NOT NULL DEFAULT 1
);

favoritereport

Schema
backoffice

CREATE TABLE favoritereport (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('favoritereport_id_seq'::regclass),
   reportid integer NOT NULL REFERENCES reports,
   label character varying[] NOT NULL,
   title character varying[] NOT NULL,
   menuaction character varying NOT NULL,
   type character varying NOT NULL,
   parent integer NOT NULL,
   displayorder integer,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer,
   del boolean DEFAULT false
);

fieldtypes

Originally this table was intended to define datatypes that would be used for metadata for our datafiles, to enable code that would allow importation of spreadsheet data into those tables. However, this is not happening yet. The only records in this table which are used are the ones with typecode = 'l' (small L), which define lookup types. These are used for the lookup types in parameter definitions for reports. There is no interface to define lookup types yet, but it is fairly simple to do it in sql. Lookup types can be defined based on a table, or a hard-coded sql, but hardcoding sql means that you can't get language-dependent strings returned as we can when referencing fields such as name[] in our reference tables.

Schema
metadata

CREATE TABLE fieldtypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('fieldtypes_id_seq'::regclass),
   typename character varying UNIQUE NOT NULL,
   description character varying NOT NULL,
   typecode character(1) NOT NULL,
   domaincond character varying,
   basetypeoid oid NOT NULL,
   schemaname character varying,
   tablename character varying,
   columnname character varying,
   filtercond character varying,
   synonyms boolean NOT NULL DEFAULT false,
   sqlquery character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

findings

Laboratory findings

Schema
reference

CREATE TABLE findings (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('findings_id_seq'::regclass),
   hiercode character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   section integer NOT NULL DEFAULT 1 REFERENCES labsections
);

form_def

Schema
backoffice

CREATE TABLE form_def (
   row_to_json json
);

fungsi

Animal function

Schema
reference

CREATE TABLE fungsi (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('fungsi_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   speciesid integer REFERENCES species,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

gestation

Reproductive parameters of key species for production module reports

Schema
reference

CREATE TABLE gestation (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('gestation_id_seq'::regclass),
   speciesid integer NOT NULL REFERENCES species,
   gestationperiod integer NOT NULL,
   variation integer NOT NULL,
   oestruscycle integer,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

group_permissions

Default permissions for a user group

Schema
backoffice

CREATE TABLE group_permissions (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('group_permissions_id_seq'::regclass),
   permission_typeid integer NOT NULL REFERENCES permission_types,
   groupid integer NOT NULL REFERENCES groups,
   permission integer NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

groups

User roles determining default permissions in the system

Schema
backoffice

CREATE TABLE groups (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('groups_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

hewan

Master table for individual animal identification

Schema
data

CREATE TABLE hewan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('hewan_id_seq'::regclass),
   identifikasi character varying,
   indukjantan bigint REFERENCES hewan,
   indukbetina bigint REFERENCES hewan,
   idspesies integer REFERENCES species,
   idsex integer REFERENCES sex,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   idfungsi integer REFERENCES fungsi,
   tanggallahir date,
   msgid bigint NOT NULL,
   image character varying[]
);

hijauan

Data table for forage crop production

Schema
data

CREATE TABLE hijauan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('hijauan_id_seq'::regclass),
   locationid integer NOT NULL REFERENCES locations,
   idjenishijauan integer NOT NULL REFERENCES jenishijauan,
   luas double precision NOT NULL,
   idasalbibit integer NOT NULL REFERENCES asalbibit,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL
);

importfields

Field definitions for Excel imports

Schema
metadata

CREATE TABLE importfields (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('importfields_id_seq'::regclass),
   tableid integer NOT NULL REFERENCES importtables,
   fieldname character varying,
   label character varying[] NOT NULL,
   required boolean NOT NULL DEFAULT true,
   keyfield boolean NOT NULL DEFAULT false,
   hidden boolean NOT NULL DEFAULT false,
   fieldtype integer NOT NULL REFERENCES fieldtypes,
   lookupsql character varying,
   lookuperror character varying,
   validationsql character varying,
   validationerror character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   ref character varying,
   copyprevious boolean NOT NULL DEFAULT false,
   varname character varying
);

importfieldtypes

Field types used for Excel imports

Schema
metadata

CREATE TABLE importfieldtypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('importfieldtypes_id_seq'::regclass),
   name character varying[] NOT NULL,
   description character varying,
   validationsql character varying,
   validationerror character varying,
   lookupsql character varying,
   lookuperror character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   quote boolean NOT NULL DEFAULT true,
   islist boolean NOT NULL DEFAULT false,
   exceltype character(1) NOT NULL DEFAULT 's'::bpchar
);

importjobs

Job definitions for Excel imports. May include multiple tables

Schema
metadata

CREATE TABLE importjobs (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('importjobs_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying NOT NULL,
   permissiontypeid integer NOT NULL REFERENCES permission_types,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   sms_alert boolean NOT NULL DEFAULT false
);

importtables

Table definitions for Excel imports

Schema
metadata

CREATE TABLE importtables (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('importtables_id_seq'::regclass),
   jobid integer NOT NULL REFERENCES importjobs,
   tablename character varying NOT NULL,
   tableorder integer NOT NULL DEFAULT 1,
   keyfield character varying NOT NULL DEFAULT 'id'::character varying,
   parenttable integer REFERENCES importtables,
   linkfield character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   keyexpression character varying,
   canupdate boolean NOT NULL DEFAULT false
);

inbox

Incoming SMS messsages

Schema
sms

CREATE TABLE inbox (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('inbox_id_seq'::regclass),
   senttime timestamp without time zone,
   receivedtime timestamp without time zone,
   sender character varying,
   receiver character varying,
   userid integer,
   msgid bigint,
   message character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

infrastructure

Master table of infrastructure (labs, offices, abattoirs etc)

Schema
reference

CREATE TABLE infrastructure (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('infrastructure_id_seq'::regclass),
   code character varying UNIQUE,
   name character varying NOT NULL,
   infrastructure_typeid integer NOT NULL REFERENCES infrastructure_types,
   locationid integer NOT NULL REFERENCES locations,
   address character varying,
   postcode character varying,
   phone character varying,
   fax character varying,
   contact integer,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   infratype integer UNIQUE,
   area integer[],
   shortname character varying
);

infrastructure_types

Classification of infrastructure

Schema
reference

CREATE TABLE infrastructure_types (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('infrastructure_types_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   hiercode character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

investigationanimals

Number of animals sick, dead, at risk etc, in a detailed case investigation. Also used to flag the resolution of an outbreak

Schema
data

CREATE TABLE investigationanimals (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('investigationanimals_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   speciesid integer NOT NULL REFERENCES species,
   sick integer,
   dead integer,
   slaughtered integer,
   atrisk integer,
   resolved boolean,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

jenisbibit

Types of forage crops

Schema
reference

CREATE TABLE jenisbibit (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('jenisbibit_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

jenishijauan

Types of forage crops

Schema
reference

CREATE TABLE jenishijauan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('jenishijauan_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

jeniskebun

Types of garden

Schema
reference

CREATE TABLE jeniskebun (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('jeniskebun_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

jeniskonsentrat

Types of for consentrat

Schema
reference

CREATE TABLE jeniskonsentrat (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('jeniskonsentrat_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

jeniskontainer

Types of containers

Schema
reference

CREATE TABLE jeniskontainer (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('jeniskontainer_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

kejadian

Events table for an individual animal, used to store a range of different event types

Schema
data

CREATE TABLE kejadian (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('kejadian_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   idhewan bigint REFERENCES hewan,
   idtipekejadian integer REFERENCES tipekejadian,
   datakejadian character varying,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL
);

keterangan

Comments on a case

Schema
data

CREATE TABLE keterangan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('keterangan_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   keterangan character varying,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

komentar

Comments on a case

Schema
data

CREATE TABLE komentar (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('komentar_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   komentar character varying,
   msgid bigint NOT NULL,
   createdby integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

kondisikontainer

condition of containers

Schema
reference

CREATE TABLE kondisikontainer (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('kondisikontainer_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

konsentrat

Data table for consentrat

Schema
data

CREATE TABLE konsentrat (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('konsentrat_id_seq'::regclass),
   infraid integer NOT NULL,
   idjeniskonsentrat integer NOT NULL REFERENCES jeniskonsentrat,
   jumlah double precision NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL
);

kursuspelatihan

Training courses

Schema
data

CREATE TABLE kursuspelatihan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('kursuspelatihan_id_seq'::regclass),
   name character varying[] NOT NULL,
   moduleid integer[],
   locationid integer NOT NULL,
   startdate date NOT NULL DEFAULT now(),
   enddate date,
   area integer[] NOT NULL,
   trainers integer[] NOT NULL,
   organiser integer NOT NULL DEFAULT 1 REFERENCES adminlevels,
   trainingtype integer NOT NULL DEFAULT 1 REFERENCES trainingtypes,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

labsections

Laboratory sections

Schema
reference

CREATE TABLE labsections (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('labsections_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

labsubmissions

Laboratory submissions master table

Schema
lab

CREATE TABLE labsubmissions (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('submissions_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   epinum character varying NOT NULL,
   submissionreason integer NOT NULL REFERENCES submissionreasons,
   caseid integer REFERENCES cases,
   survid integer REFERENCES surveillanceprograms,
   skkhid integer REFERENCES movement,
   otherid character varying,
   locationid integer REFERENCES locations,
   submittertype integer NOT NULL REFERENCES submittertypes,
   submitterid integer REFERENCES users,
   submittername character varying,
   submitteraddress character varying,
   submitterphone landline,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint,
   labid integer REFERENCES infrastructure,
   datesampled date,
   datereceived date
);

labtests

Laboratory tests defined for each laboratory, referencing testtypes (the master test table)

Schema
reference

CREATE TABLE labtests (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('labtests_id_seq'::regclass),
   testid integer NOT NULL REFERENCES testtypes,
   sectionid integer NOT NULL REFERENCES labsections,
   labid integer NOT NULL REFERENCES infrastructure,
   qualfinding boolean NOT NULL DEFAULT false,
   quantfinding boolean NOT NULL DEFAULT true,
   quantunitsid integer REFERENCES testunits,
   accredited boolean NOT NULL DEFAULT false,
   ref character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

languages

List of languages used in the system

Schema
backoffice

CREATE TABLE languages (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('languages_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

locationlevels

Schema
reference

CREATE TABLE locationlevels (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('locationlevels_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

locations

Master spatial data table with administrative boundaries

Schema
reference

CREATE TABLE locations (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('locations_id_seq'::regclass),
   name character varying,
   code locationcode,
   bpscode character varying,
   level integer,
   validfrom date DEFAULT ('now'::text)::date,
   validto date,
   geom public.geometry(MultiPolygon,4326),
   centroid public.geometry(Point,4326),
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   comment text,
   geom_proj public.geometry(MultiPolygon,3857),
   centroid_proj public.geometry(Point,3857),
   temp boolean DEFAULT false
);

logdelete

Schema
data

CREATE TABLE logdelete (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('logdelete_id_seq'::regclass),
   scema character varying(100),
   table character varying(100),
   key character varying(50),
   value character varying(100),
   delete_by integer REFERENCES users,
   delete_at timestamp without time zone
);

menu

Website main menu structure definition

Schema
backoffice

CREATE TABLE menu (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('menu_id_seq'::regclass),
   pagetitle character varying[] NOT NULL,
   accesslevel text NOT NULL,
   menuaction character varying,
   parent integer NOT NULL,
   displayorder integer NOT NULL,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer,
   modifiedon timestamp with time zone,
   modifiedby integer,
   del boolean NOT NULL DEFAULT false
);

menu2

Schema
backoffice

CREATE TABLE menu2 (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('menu2_id_seq'::regclass),
   pagetitle character varying[] NOT NULL,
   accesslevel text NOT NULL,
   menuaction character varying,
   parent integer NOT NULL,
   displayorder integer NOT NULL,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer,
   modifiedon timestamp with time zone,
   modifiedby integer,
   del boolean NOT NULL DEFAULT false
);

methods

Methods for laboratory tests

Schema
reference

CREATE TABLE methods (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('methods_id_seq'::regclass),
   code character varying UNIQUE,
   hiercode character varying UNIQUE NOT NULL,
   name character varying[] UNIQUE NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   reportid integer
);

modemlog

Log of modem status

Schema
sms

CREATE TABLE modemlog (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('modemlog_id_seq'::regclass),
   modem text,
   pulsacheck timestamp without time zone,
   credit integer,
   days integer,
   sent integer
);

modemstatus

Snapshot of modem status

Schema
sms

CREATE TABLE modemstatus (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('modemstatus_id_seq'::regclass),
   modem text,
   phone text,
   lastcheck timestamp without time zone,
   status text,
   sent integer,
   failed integer,
   received integer,
   ssi text,
   ber text,
   pulsacheck timestamp without time zone,
   credit integer,
   days integer,
   nport integer,
   incoming boolean,
   operatorid integer REFERENCES sms_operators,
   comment character varying
);

modulpelatihan

Training modules

Schema
reference

CREATE TABLE modulpelatihan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('modulpelatihan_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

months

Calendar months

Schema
reference

CREATE TABLE months (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('months_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

movement

Animal movement reporting (health certificate) master table

Schema
data

CREATE TABLE movement (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('movement_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   origin integer NOT NULL REFERENCES locations,
   destination integer NOT NULL REFERENCES locations,
   ownerid character varying,
   ownerphone character varying,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

movementanimals

List of species and number of animals for animal movement reporting

Schema
data

CREATE TABLE movementanimals (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('movementanimals_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   movementid integer NOT NULL REFERENCES movement,
   speciesid integer NOT NULL REFERENCES species,
   total integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

multilayermaps

Schema
report

CREATE TABLE multilayermaps (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('multilayermaps_id_seq'::regclass),
   reportid integer NOT NULL REFERENCES reports,
   mapreportid integer NOT NULL,
   savedreportid integer NOT NULL REFERENCES savedreports,
   listorder smallint,
   del boolean NOT NULL DEFAULT false,
   createdon timestamp without time zone,
   createdby integer,
   modifiedon timestamp without time zone,
   modifiedby integer
);

negativereports

Village-level negative disease reports

Schema
data

CREATE TABLE negativereports (
   id integer NOT NULL DEFAULT nextval('negativereports_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL,
   msgid bigint NOT NULL,
   checked boolean,
   createdby integer NOT NULL DEFAULT 1,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

news

News stories

Schema
backoffice

CREATE TABLE news (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('news_id_seq'::regclass),
   headline character varying[] NOT NULL,
   summary character varying[] NOT NULL,
   article character varying[],
   imageid integer,
   createdby integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   image character varying,
   type character varying
);

nitrogenshipment

Schema
data

CREATE TABLE nitrogenshipment (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('nitrogenshipment_id_seq'::regclass),
   shipmentid integer REFERENCES shipments,
   receiptid integer REFERENCES receipt,
   stocktakeid integer REFERENCES stocktake,
   volume double precision NOT NULL,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

odttemplates

Templates for ODT reports

Schema
report

CREATE TABLE odttemplates (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('odttemplates_id_seq'::regclass),
   filename character varying NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   sql character varying,
   infra_type integer,
   header_img character varying,
   footer_img character varying,
   id_infrastructure integer,
   name character varying
);

outbox

Outgoing SMS messages

Schema
sms

CREATE TABLE outbox (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('outbox_id_seq'::regclass),
   phone character varying,
   message character varying,
   msgid bigint,
   priority boolean NOT NULL DEFAULT false,
   modem character varying,
   queued timestamp without time zone,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

owners

Owners of animals in a laboratory submission

Schema
lab

CREATE TABLE owners (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('pemilik_id_seq'::regclass),
   name character varying,
   address character varying,
   phone phonenumber,
   locationid integer REFERENCES locations,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   userid integer,
   msgid bigint
);

parameters

Miscellaneous user defined system parameters

Schema
backoffice

CREATE TABLE parameters (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('parameters_id_seq'::regclass),
   name character varying NOT NULL,
   value character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

partisipanpelatihan

Participants at a training course

Schema
data

CREATE TABLE partisipanpelatihan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('partisipanpelatihan_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users,
   idkursuspelatihan integer NOT NULL REFERENCES kursuspelatihan,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

pemilik_hewan

Link table for animal ownership, between the hewan (animal ID) table and the users (owners) table

Schema
data

CREATE TABLE pemilik_hewan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('pemilik_hewan_id_seq'::regclass),
   idpemilik bigint NOT NULL REFERENCES users,
   idhewan bigint NOT NULL REFERENCES hewan,
   tanggalmulai timestamp without time zone NOT NULL DEFAULT now(),
   tanggalakhir timestamp without time zone,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint,
   idtipepemilik integer NOT NULL DEFAULT 1 REFERENCES tipepemilik
);

permission_types

Schema
backoffice

CREATE TABLE permission_types (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('permission_types_id_seq'::regclass),
   name character varying NOT NULL,
   description character varying[],
   userpermission boolean NOT NULL DEFAULT false,
   defaultvalue integer DEFAULT 0,
   preference boolean NOT NULL DEFAULT false,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

perubahansampel

Tabel Referensi Untuk Perubahan Sampel Produk Hewan

Schema
reference

CREATE TABLE perubahansampel (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('perubahansampel_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

plr_modules

Schema
backoffice

CREATE TABLE plr_modules (
   modseq integer PRIMARY KEY NOT NULL,
   modsrc text
);

plr_modules2

Schema
backoffice

CREATE TABLE plr_modules2 (
   modseq integer,
   modsrc text
);

population

Village/farm level animal population

Schema
data

CREATE TABLE population (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('population_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   animaltypeid integer NOT NULL REFERENCES animal_types,
   total integer NOT NULL,
   locationid integer NOT NULL REFERENCES locations,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

preventivetreatments

Non-disease associated individual animal treatments (worming, vaccination etc)

Schema
data

CREATE TABLE preventivetreatments (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('preventivetreatments_id_seq'::regclass),
   drugid integer NOT NULL REFERENCES drugs,
   dose numeric(8,3),
   idhewan integer NOT NULL REFERENCES hewan,
   msgid bigint,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

productionsystems

Production systems

Schema
reference

CREATE TABLE productionsystems (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('productionsystems_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   hiercode character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

programafkir

Culling programs

Schema
data

CREATE TABLE programafkir (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('programafkir_id_seq'::regclass),
   name character varying NOT NULL,
   startdate date NOT NULL DEFAULT now(),
   enddate date,
   mandatory boolean NOT NULL DEFAULT false,
   compensation boolean NOT NULL DEFAULT false,
   diseaseeventid integer REFERENCES diseaseevents,
   area integer[] NOT NULL,
   diseaseid integer[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

programtindakan

Disease control programs

Schema
data

CREATE TABLE programtindakan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('programtindakan_id_seq'::regclass),
   startdate date NOT NULL DEFAULT now(),
   enddate date,
   area integer[] NOT NULL,
   diseaseid integer[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   name character varying NOT NULL
);

prov

Schema
backoffice

CREATE TABLE prov (
   id integer,
   name character varying,
   code locationcode,
   bpscode character varying,
   level integer,
   validfrom date,
   validto date,
   geom public.geometry(MultiPolygon,4326),
   centroid public.geometry(Point,4326),
   createdby integer,
   createdon timestamp without time zone,
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean
);

purposes

Purposes for vaccination

Schema
reference

CREATE TABLE purposes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('purposes_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   type purposetypes,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

questions

Questions sent by SMS

Schema
data

CREATE TABLE questions (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('questions_id_seq'::regclass),
   question character varying,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   del boolean NOT NULL DEFAULT false
);

queuestats

Snapshot of SMS outgoing queue

Schema
sms

CREATE TABLE queuestats (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('queuestats_id_seq'::regclass),
   checked integer,
   failed integer,
   incoming integer,
   outgoing integer,
   sent integer,
   main integer,
   createdby integer,
   createdon timestamp without time zone
);

rabiesreports

Schema
data

CREATE TABLE rabiesreports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rabiesreports_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   caseid integer REFERENCES cases,
   speciesid integer NOT NULL REFERENCES species,
   animalsbitten integer NOT NULL,
   humansbitten integer NOT NULL,
   provoked boolean NOT NULL DEFAULT false,
   locationid integer NOT NULL REFERENCES locations,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

receipt

Schema
data

CREATE TABLE receipt (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('receipt_id_seq'::regclass),
   shipmentid integer NOT NULL REFERENCES shipments,
   datereceived timestamp with time zone NOT NULL DEFAULT now(),
   infrastructureid integer NOT NULL REFERENCES infrastructure,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

registrationmatrix

Permissions for different user types to register other users

Schema
reference

CREATE TABLE registrationmatrix (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('registrationmatrix_id_seq'::regclass),
   groupid integer NOT NULL REFERENCES groups,
   canregister integer NOT NULL REFERENCES groups,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

reportcategories

Schema
report

CREATE TABLE reportcategories (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reportcategories_id_seq'::regclass),
   label character varying[] NOT NULL,
   prompt character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false
);

reportlog

Schema
report

CREATE TABLE reportlog (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('paramslog_id_seq'::regclass),
   rptid integer,
   svdrptid integer,
   link text,
   uid integer,
   params jsonb,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 116,
   modifiedon timestamp with time zone,
   modifiedby integer,
   del boolean NOT NULL DEFAULT false
);

reportparams

Replaceable parameters for reports

Schema
report

CREATE TABLE reportparams (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('report_params_id_seq'::regclass),
   reportid integer NOT NULL,
   paramtype integer NOT NULL REFERENCES reportparamtypes,
   paramname character varying NOT NULL,
   prompt character varying[],
   lookuptyp integer REFERENCES fieldtypes,
   lookupqry character varying,
   lookupfld integer REFERENCES tablefields,
   checkvalue character varying,
   mandatory boolean NOT NULL DEFAULT false,
   dynamic boolean NOT NULL DEFAULT true,
   dfltval character varying,
   sortorder integer NOT NULL DEFAULT 1,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false
);

reportparamtypes

Parameter types for reports

Schema
report

CREATE TABLE reportparamtypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rptparamtypes_id_seq'::regclass),
   typename character varying UNIQUE NOT NULL,
   description character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false,
   validationregex character varying
);

reports

Master report definition table

Schema
report

CREATE TABLE reports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reports_id_seq'::regclass),
   reporttype report.reporttypes,
   name character varying[] NOT NULL,
   description character varying[],
   caption character varying[],
   footnote character varying[],
   permission integer REFERENCES permission_types,
   public boolean NOT NULL DEFAULT false,
   web boolean NOT NULL DEFAULT true,
   naturalorder integer,
   sql character varying,
   rcode character varying,
   createdby integer NOT NULL DEFAULT 0 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   source_database reportdatabase NOT NULL DEFAULT 'Replica'::backoffice.reportdatabase,
   readonly boolean NOT NULL DEFAULT false,
   maptype report.maptype,
   widget_update_interval integer NOT NULL DEFAULT 1880,
   cacheable boolean NOT NULL DEFAULT true,
   cache_validity integer NOT NULL DEFAULT 60,
   tag character varying,
   category character varying,
   thumbnails text,
   bigreportcached boolean NOT NULL DEFAULT false
);

reports

Time (in minutes) between updating widget data in static storage (default one day)

Schema
report

CREATE TABLE reports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reports_id_seq'::regclass),
   reporttype report.reporttypes,
   name character varying[] NOT NULL,
   description character varying[],
   caption character varying[],
   footnote character varying[],
   permission integer REFERENCES permission_types,
   public boolean NOT NULL DEFAULT false,
   web boolean NOT NULL DEFAULT true,
   naturalorder integer,
   sql character varying,
   rcode character varying,
   createdby integer NOT NULL DEFAULT 0 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   source_database reportdatabase NOT NULL DEFAULT 'Replica'::backoffice.reportdatabase,
   readonly boolean NOT NULL DEFAULT false,
   maptype report.maptype,
   widget_update_interval integer NOT NULL DEFAULT 1880,
   cacheable boolean NOT NULL DEFAULT true,
   cache_validity integer NOT NULL DEFAULT 60,
   tag character varying,
   category character varying,
   thumbnails text,
   bigreportcached boolean NOT NULL DEFAULT false
);

reportsubscriptions

Group and individual subscriptions to saved reports for automated periodic reporting

Schema
report

CREATE TABLE reportsubscriptions (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reportsubscriptions_id_seq'::regclass),
   svdrptid integer NOT NULL REFERENCES savedreports,
   userid integer REFERENCES users,
   groupid integer REFERENCES groups,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false,
   active boolean NOT NULL DEFAULT true
);

reporttags

Schema
report

CREATE TABLE reporttags (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reporttags_id_seq'::regclass),
   label character varying[] NOT NULL,
   prompt character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false
);

reporttags

Schema
backoffice

CREATE TABLE reporttags (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reporttags_id_seq'::regclass),
   label character varying[] NOT NULL,
   prompt character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false
);

response

Initial investigation of a disease report, including differential diagnosis

Schema
data

CREATE TABLE response (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('response_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   visited boolean NOT NULL DEFAULT true,
   diagnosisid integer[] NOT NULL,
   otherdiagnosis character varying,
   validatedk boolean NOT NULL DEFAULT true,
   validatedp boolean NOT NULL DEFAULT true,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

rest_client

Schema
backoffice

CREATE TABLE rest_client (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rest_client_id_seq'::regclass),
   client_name character varying NOT NULL,
   client_code character varying NOT NULL,
   email character varying NOT NULL,
   api_key character varying,
   ip_address character varying,
   website character varying,
   expired_request integer,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp(6) without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp(6) without time zone,
   del boolean NOT NULL DEFAULT false
);

results

Laboratory testing results

Schema
reference

CREATE TABLE results (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('results_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

riwayatvaksinasi

Vaccination history from priority disease investigation

Schema
data

CREATE TABLE riwayatvaksinasi (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('riwayatvaksinasi_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   diseaseid integer NOT NULL REFERENCES diseases,
   tanggal date,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

savedreportparams

Parameters for saved reports

Schema
report

CREATE TABLE savedreportparams (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('saved_report_params_id_seq'::regclass),
   svdrptid integer NOT NULL REFERENCES savedreports,
   rptparamid integer NOT NULL REFERENCES reportparams,
   paramvalue character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false
);

savedreports

Parameterised versions of reports for automated periodic reporting

Schema
report

CREATE TABLE savedreports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('saved_reports_id_seq'::regclass),
   reportid integer NOT NULL REFERENCES reports,
   name character varying[],
   active boolean NOT NULL DEFAULT true,
   frequency report.reportfrequency,
   delivery report.reportdelivery,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false,
   public boolean NOT NULL DEFAULT false
);

selfcheck

Schema
sms

CREATE TABLE selfcheck (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('selfcheck_id_seq'::regclass),
   modem character varying,
   sent timestamp without time zone,
   received timestamp without time zone
);

sessions

Schema
backoffice

CREATE TABLE sessions (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sessions_id_seq'::regclass),
   sessionid character varying,
   userid integer,
   createdon timestamp without time zone NOT NULL DEFAULT now()
);

sex

Animal sex

Schema
reference

CREATE TABLE sex (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sex_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

shipments

Schema
data

CREATE TABLE shipments (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('shipments_id_seq'::regclass),
   origininfraid integer NOT NULL REFERENCES infrastructure,
   destinfraid integer REFERENCES infrastructure,
   destuserid integer REFERENCES users,
   datesent timestamp with time zone NOT NULL DEFAULT now(),
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

signreports

Disease report for routine cases, including a list of clinical signs

Schema
data

CREATE TABLE signreports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('signreports_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   signsid integer[] NOT NULL,
   speciesid integer NOT NULL REFERENCES species,
   cases integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   animalid integer REFERENCES hewan
);

signs

Clinical signs

Schema
reference

CREATE TABLE signs (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('signs_id_seq'::regclass),
   code character varying UNIQUE,
   hiercode character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   description character varying[],
   level integer,
   synonym integer,
   pelsa boolean NOT NULL DEFAULT false,
   selectable boolean NOT NULL DEFAULT false,
   terminal boolean NOT NULL DEFAULT false,
   general boolean NOT NULL DEFAULT false,
   specific boolean NOT NULL DEFAULT false,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   synname character varying[]
);

signs_asia

Schema
reference

CREATE TABLE signs_asia (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('signs_asia_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   sign character varying[] NOT NULL,
   description character varying[],
   hier_code character varying NOT NULL,
   cadre boolean DEFAULT false,
   level integer,
   selectable boolean DEFAULT false,
   terminal boolean DEFAULT false,
   general boolean DEFAULT false,
   specific boolean DEFAULT false,
   valid_from date NOT NULL DEFAULT '2010-01-01'::date,
   valid_to date,
   modified_by integer NOT NULL DEFAULT 1,
   modified_on timestamp without time zone NOT NULL DEFAULT now(),
   synonym integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false
);

signspecies

Signs valid for different species

Schema
reference

CREATE TABLE signspecies (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('signspecies_id_seq'::regclass),
   signid integer NOT NULL REFERENCES signs,
   speciesid integer NOT NULL REFERENCES species,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

slaughtertotals

Daily abattoir slaughter totals by animal type

Schema
data

CREATE TABLE slaughtertotals (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('slaughtertotals_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   animaltypeid integer NOT NULL REFERENCES animal_types,
   total integer NOT NULL DEFAULT 0,
   infrastructureid integer NOT NULL REFERENCES infrastructure,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

sms

Master table for SMS message definitions

Schema
metadata

CREATE TABLE sms (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sms_id_seq'::regclass),
   name character varying[] NOT NULL,
   helptext character varying[],
   start_code character varying UNIQUE NOT NULL,
   error_msg character varying NOT NULL DEFAULT ::character varying,
   reply_sql character varying NOT NULL DEFAULT ::character varying,
   alert_sql character varying,
   protected boolean NOT NULL DEFAULT false,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   permission character varying,
   tablename character varying[],
   keyfield character varying[],
   userid_field character varying[] NOT NULL DEFAULT '{createdby}'::character varying[],
   msgid_field character varying[] NOT NULL DEFAULT '{msgid}'::character varying[],
   reportdate_field character varying[] NOT NULL DEFAULT '{createdon}'::character varying[],
   readonly boolean NOT NULL DEFAULT false,
   public boolean DEFAULT false,
   version integer NOT NULL DEFAULT 1,
   groupid integer REFERENCES sms_groups
);

sms_field_types

Field types for SMS message definitions

Schema
reference

CREATE TABLE sms_field_types (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sms_field_types_id_seq'::regclass),
   name character varying NOT NULL,
   field_type character varying NOT NULL,
   description character varying NOT NULL,
   del boolean NOT NULL DEFAULT false,
   app_type character varying
);

sms_fields

Field definitions for SMS messages

Schema
metadata

CREATE TABLE sms_fields (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sms_fields_id_seq'::regclass),
   smsid integer NOT NULL REFERENCES sms,
   natorder integer NOT NULL,
   name character varying[] NOT NULL,
   optional boolean NOT NULL DEFAULT false,
   data_type integer NOT NULL,
   groupsequence integer NOT NULL DEFAULT 0,
   lu_sql character varying DEFAULT ::character varying,
   fieldname character varying NOT NULL DEFAULT ::character varying,
   error_msg character varying NOT NULL DEFAULT ::character varying,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   targettable integer,
   hidden boolean NOT NULL DEFAULT false,
   title character varying,
   help character varying[],
   list_sql character varying
);

sms_format

Schema
backoffice

CREATE TABLE sms_format (
   ?column? text
);

sms_groups

Schema
metadata

CREATE TABLE sms_groups (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sms_groups_id_seq'::regclass),
   name character varying[] NOT NULL,
   comment character varying[],
   natord integer NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

sms_operators

Telephone network operators

Schema
reference

CREATE TABLE sms_operators (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sms_operators_id_seq'::regclass),
   name character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

sms_prefixes

Mobile phone prefixes

Schema
reference

CREATE TABLE sms_prefixes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sms_prefixes_id_seq'::regclass),
   prefix integer,
   operatorid integer NOT NULL REFERENCES sms_operators,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

smserrorchecks

Follow-up by coordinators of users who made errors with SMS formats

Schema
data

CREATE TABLE smserrorchecks (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('smserrorchecks_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users,
   contactdate timestamp without time zone NOT NULL,
   action smscheckaction,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer,
   del boolean NOT NULL DEFAULT false
);

species

Species and breeds

Schema
reference

CREATE TABLE species (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('species_id_seq'::regclass),
   name character varying[] NOT NULL,
   selectable boolean NOT NULL DEFAULT false,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   code character varying UNIQUE,
   hiercode character varying UNIQUE NOT NULL,
   level integer NOT NULL,
   sikhnascode integer,
   lab boolean NOT NULL DEFAULT false
);

specimenforms

The form of a laboratory specimen (perservative etc)

Schema
reference

CREATE TABLE specimenforms (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('specimenforms_id_seq'::regclass),
   code character varying,
   hiercode character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

specimens

Specimens from animals in a laboratory submission

Schema
lab

CREATE TABLE specimens (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('specimens_id_seq'::regclass),
   animalid integer NOT NULL REFERENCES animals,
   specimentypeid integer NOT NULL REFERENCES specimentypes,
   specimenformid integer NOT NULL REFERENCES specimenforms,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint
);

specimentypes

The type of a laboratory specimen (tissue of origin)

Schema
reference

CREATE TABLE specimentypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('specimentypes_id_seq'::regclass),
   code character varying,
   hiercode character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

sql_features

Schema
information_schema

CREATE TABLE sql_features (
   feature_id information_schema.character_data,
   feature_name information_schema.character_data,
   sub_feature_id information_schema.character_data,
   sub_feature_name information_schema.character_data,
   is_supported information_schema.yes_or_no,
   is_verified_by information_schema.character_data,
   comments information_schema.character_data
);

sql_implementation_info

Schema
information_schema

CREATE TABLE sql_implementation_info (
   implementation_info_id information_schema.character_data,
   implementation_info_name information_schema.character_data,
   integer_value information_schema.cardinal_number,
   character_value information_schema.character_data,
   comments information_schema.character_data
);

sql_languages

Schema
information_schema

CREATE TABLE sql_languages (
   sql_language_source information_schema.character_data,
   sql_language_year information_schema.character_data,
   sql_language_conformance information_schema.character_data,
   sql_language_integrity information_schema.character_data,
   sql_language_implementation information_schema.character_data,
   sql_language_binding_style information_schema.character_data,
   sql_language_programming_language information_schema.character_data
);

sql_packages

Schema
information_schema

CREATE TABLE sql_packages (
   feature_id information_schema.character_data,
   feature_name information_schema.character_data,
   is_supported information_schema.yes_or_no,
   is_verified_by information_schema.character_data,
   comments information_schema.character_data
);

sql_parts

Schema
information_schema

CREATE TABLE sql_parts (
   feature_id information_schema.character_data,
   feature_name information_schema.character_data,
   is_supported information_schema.yes_or_no,
   is_verified_by information_schema.character_data,
   comments information_schema.character_data
);

sql_sizing

Schema
information_schema

CREATE TABLE sql_sizing (
   sizing_id information_schema.cardinal_number,
   sizing_name information_schema.character_data,
   supported_value information_schema.cardinal_number,
   comments information_schema.character_data
);

sql_sizing_profiles

Schema
information_schema

CREATE TABLE sql_sizing_profiles (
   sizing_id information_schema.cardinal_number,
   sizing_name information_schema.character_data,
   profile_id information_schema.character_data,
   required_value information_schema.cardinal_number,
   comments information_schema.character_data
);

statusasuransi

status asuransi

Schema
reference

CREATE TABLE statusasuransi (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('statusasuransi_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

statusrepro

Reproductive statuses

Schema
reference

CREATE TABLE statusrepro (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('statusrepro_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

stocktake

Schema
data

CREATE TABLE stocktake (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('stocktake_id_seq'::regclass),
   stockdate timestamp with time zone NOT NULL DEFAULT now(),
   infrastructureid integer NOT NULL REFERENCES infrastructure,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

stokbibit

stokbibit hijauan

Schema
data

CREATE TABLE stokbibit (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('stokbibit_id_seq'::regclass),
   msgid bigint NOT NULL,
   jenishijauanid integer NOT NULL,
   jenisbibitid integer NOT NULL REFERENCES jenisbibit,
   total integer NOT NULL,
   harga integer NOT NULL,
   asalid integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

straws

Batch Number

Schema
data

CREATE TABLE straws (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('straws_id_seq'::regclass),
   idpejantan character varying NOT NULL,
   idpembuatan character varying NOT NULL,
   idbib integer NOT NULL REFERENCES infrastructure,
   idhewan bigint NOT NULL REFERENCES hewan,
   tanggalproduksi date NOT NULL,
   jumlah integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   spesies integer NOT NULL DEFAULT 22 REFERENCES species
);

straws

List of semen straws for artificial insemination

Schema
data

CREATE TABLE straws (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('straws_id_seq'::regclass),
   idpejantan character varying NOT NULL,
   idpembuatan character varying NOT NULL,
   idbib integer NOT NULL REFERENCES infrastructure,
   idhewan bigint NOT NULL REFERENCES hewan,
   tanggalproduksi date NOT NULL,
   jumlah integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   spesies integer NOT NULL DEFAULT 22 REFERENCES species
);

straws

Collection Centre

Schema
data

CREATE TABLE straws (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('straws_id_seq'::regclass),
   idpejantan character varying NOT NULL,
   idpembuatan character varying NOT NULL,
   idbib integer NOT NULL REFERENCES infrastructure,
   idhewan bigint NOT NULL REFERENCES hewan,
   tanggalproduksi date NOT NULL,
   jumlah integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   spesies integer NOT NULL DEFAULT 22 REFERENCES species
);

straws

Stud ID

Schema
data

CREATE TABLE straws (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('straws_id_seq'::regclass),
   idpejantan character varying NOT NULL,
   idpembuatan character varying NOT NULL,
   idbib integer NOT NULL REFERENCES infrastructure,
   idhewan bigint NOT NULL REFERENCES hewan,
   tanggalproduksi date NOT NULL,
   jumlah integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   spesies integer NOT NULL DEFAULT 22 REFERENCES species
);

straws

Date of production

Schema
data

CREATE TABLE straws (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('straws_id_seq'::regclass),
   idpejantan character varying NOT NULL,
   idpembuatan character varying NOT NULL,
   idbib integer NOT NULL REFERENCES infrastructure,
   idhewan bigint NOT NULL REFERENCES hewan,
   tanggalproduksi date NOT NULL,
   jumlah integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   spesies integer NOT NULL DEFAULT 22 REFERENCES species
);

strawshipment

Schema
data

CREATE TABLE strawshipment (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('strawshipment_id_seq'::regclass),
   shipmentid integer REFERENCES shipments,
   receiptid integer REFERENCES receipt,
   stocktakeid integer REFERENCES stocktake,
   breedid integer REFERENCES species,
   strawid integer REFERENCES straws,
   total integer NOT NULL,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   del boolean NOT NULL DEFAULT false
);

submission_reasons

Schema
reference

CREATE TABLE submission_reasons (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('submission_reasons_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

submissionreasons

Reason for laboratory submission

Schema
reference

CREATE TABLE submissionreasons (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('submissionreasons_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   description character varying[]
);

submissions

Notification of laboratory submissions from field cases. This is distinct from the lab.labsubmissions table (master table for laboratory submission management)

Schema
data

CREATE TABLE submissions (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('submissions_id_seq'::regclass),
   reportdate timestamp without time zone,
   userid integer NOT NULL REFERENCES users,
   msgid bigint,
   caseid integer NOT NULL REFERENCES cases,
   specimentypeid integer NOT NULL REFERENCES specimentypes,
   specimenformid integer NOT NULL REFERENCES specimenforms,
   total integer NOT NULL,
   labid integer NOT NULL REFERENCES infrastructure,
   sectionid integer REFERENCES labsections,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   del boolean NOT NULL DEFAULT false
);

submittertypes

Classification of laboratory submitter types

Schema
reference

CREATE TABLE submittertypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('submittertypes_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

sumber

Reference list of possible sources of introduction of infection

Schema
reference

CREATE TABLE sumber (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sumber_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

sumberpenyakit

Suspected source of infection as determined during outbreak investigations

Schema
data

CREATE TABLE sumberpenyakit (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sumberpenyakit_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   sumberid integer NOT NULL REFERENCES sumber,
   locationid integer REFERENCES locations,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

surveillance

Both lab-based and field surveillance reports

Schema
data

CREATE TABLE surveillance (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('surveillance_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   programid integer NOT NULL REFERENCES surveillanceprograms,
   locationid integer NOT NULL REFERENCES locations,
   speciesid integer NOT NULL REFERENCES species,
   specimens integer NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   labid integer,
   pos integer,
   neg integer
);

surveillanceprograms

List of surveillance programs

Schema
data

CREATE TABLE surveillanceprograms (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('surveillanceprograms_id_seq'::regclass),
   name character varying NOT NULL,
   startdate date NOT NULL DEFAULT now(),
   enddate date,
   area integer[] NOT NULL,
   diseaseid integer[] NOT NULL,
   specimentypeid integer[] NOT NULL,
   labsectionid integer[] NOT NULL,
   purpose integer NOT NULL DEFAULT 1,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   specimenformid integer[],
   diseaseeventid integer REFERENCES diseaseevents
);

survpasar

Tabel untuk surveilans pasar kesmavet

Schema
data

CREATE TABLE survpasar (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('survpasar_id_seq'::regclass),
   idinfrastruktur integer NOT NULL REFERENCES infrastructure,
   idkit integer NOT NULL REFERENCES testtypes,
   idproduk integer NOT NULL REFERENCES species,
   pos integer NOT NULL DEFAULT 0,
   neg integer NOT NULL DEFAULT 0,
   idtanda integer[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint
);

syndromereports

Disease report for priority cases including a syndrome classification

Schema
data

CREATE TABLE syndromereports (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('syndromereports_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   syndromeid integer NOT NULL REFERENCES syndromes,
   speciesid integer NOT NULL REFERENCES species,
   cases integer NOT NULL,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

syndromes

Defined syndromes related to priority diseases

Schema
reference

CREATE TABLE syndromes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('syndromes_id_seq'::regclass),
   code character varying(6) UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   description character varying[],
   targetdiseaseid integer,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   targetspeciesid integer[]
);

syndromes

Syndrome name in Bahasa [1] and English [2]

Schema
reference

CREATE TABLE syndromes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('syndromes_id_seq'::regclass),
   code character varying(6) UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   description character varying[],
   targetdiseaseid integer,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   targetspeciesid integer[]
);

syndromes

The disease which the syndrome might reflect

Schema
reference

CREATE TABLE syndromes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('syndromes_id_seq'::regclass),
   code character varying(6) UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   description character varying[],
   targetdiseaseid integer,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   targetspeciesid integer[]
);

tablefields

Schema
metadata

CREATE TABLE tablefields (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tablefields_id_seq'::regclass),
   tableid integer NOT NULL REFERENCES datatables,
   fldname character varying NOT NULL,
   displayname character varying[] NOT NULL,
   description character varying,
   typeid integer NOT NULL REFERENCES fieldtypes,
   keyfld boolean NOT NULL DEFAULT false,
   mandatory boolean NOT NULL DEFAULT false,
   multiple boolean NOT NULL DEFAULT false,
   hidden boolean NOT NULL DEFAULT false,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1
);

tandaklinis

Clinical signs observed during an outbreak investigation. Distinct from the signs reported in Signreport, as signreport signs are reproted by Pelsa, but TandaKlinis signs are reported by a vet

Schema
data

CREATE TABLE tandaklinis (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tandaklinis_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   signsid integer[] NOT NULL,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

targets

Target for laboratory tests (organism etc)

Schema
reference

CREATE TABLE targets (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('targets_id_seq'::regclass),
   name character varying[] NOT NULL,
   diseaseid integer REFERENCES diseases,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   hiercode character varying
);

templates

HTML templates for reporting

Schema
report

CREATE TABLE templates (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('templates_id_seq'::regclass),
   code character varying NOT NULL,
   template character varying[] NOT NULL,
   comment character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

temptesttypes

Schema
backoffice

CREATE TABLE temptesttypes (
   id integer,
   code character varying,
   name character varying[],
   targetid integer,
   methodid integer,
   createdby integer,
   createdon timestamp without time zone,
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean,
   rapid boolean
);

testresults

Results of laboratory testing

Schema
lab

CREATE TABLE testresults (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('testresults_id_seq'::regclass),
   testid integer NOT NULL REFERENCES tests,
   findingqual integer REFERENCES findings,
   findingquant double precision,
   resultid integer REFERENCES results,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   uncertaintytypeid integer REFERENCES uncertaintytypes,
   msgid bigint,
   jumlahpos integer,
   jumlahneg integer,
   jumlahrusak integer
);

testresults

Column to hold aggregated data values until processing arrangements can be agreed

Schema
lab

CREATE TABLE testresults (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('testresults_id_seq'::regclass),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('testresults_id_seq'::regclass),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('testresults_id_seq'::regclass),
   testid integer NOT NULL REFERENCES tests,
   testid integer NOT NULL REFERENCES tests,
   testid integer NOT NULL REFERENCES tests,
   findingqual integer REFERENCES findings,
   findingqual integer REFERENCES findings,
   findingqual integer REFERENCES findings,
   findingquant double precision,
   findingquant double precision,
   findingquant double precision,
   resultid integer REFERENCES results,
   resultid integer REFERENCES results,
   resultid integer REFERENCES results,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedby integer REFERENCES users,
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedon timestamp without time zone,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   uncertaintytypeid integer REFERENCES uncertaintytypes,
   uncertaintytypeid integer REFERENCES uncertaintytypes,
   uncertaintytypeid integer REFERENCES uncertaintytypes,
   msgid bigint,
   msgid bigint,
   msgid bigint,
   jumlahpos integer,
   jumlahpos integer,
   jumlahpos integer,
   jumlahneg integer,
   jumlahneg integer,
   jumlahneg integer,
   jumlahrusak integer,
   jumlahrusak integer,
   jumlahrusak integer
);

tests

Tests performed on a specimen in a laboratory submission

Schema
lab

CREATE TABLE tests (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tests_id_seq'::regclass),
   specimenid integer NOT NULL REFERENCES specimens,
   testtypeid integer NOT NULL REFERENCES testtypes,
   datetested timestamp without time zone NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint
);

testtypes

Master table of laboratory test types

Schema
reference

CREATE TABLE testtypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tests_id_seq'::regclass),
   code character varying UNIQUE,
   name character varying[] NOT NULL,
   targetid integer NOT NULL REFERENCES targets,
   methodid integer NOT NULL REFERENCES methods,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   rapid boolean NOT NULL DEFAULT false
);

testunits

Quantitative units for laboratory test findings

Schema
reference

CREATE TABLE testunits (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('testunits_id_seq'::regclass),
   name character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

tindakan

Reference table of disease control activity types

Schema
reference

CREATE TABLE tindakan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tindakan_id_seq'::regclass),
   code character varying NOT NULL,
   hiercode character varying NOT NULL,
   name character varying[] NOT NULL,
   units character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

tindakanlain

Specific disease control activities undertaken under a program

Schema
data

CREATE TABLE tindakanlain (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tindakanlain_id_seq'::regclass),
   programtindakanid integer NOT NULL REFERENCES programtindakan,
   tindakanid integer NOT NULL REFERENCES tindakan,
   jumlah integer NOT NULL,
   locationid integer NOT NULL REFERENCES locations,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

tipeakhir

Disposal types for animals

Schema
reference

CREATE TABLE tipeakhir (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tipeakhir_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

tipekejadian

Event types for individual animals

Schema
reference

CREATE TABLE tipekejadian (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tipekejadian_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   eventdata character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

tipepemilik

Schema
backoffice

CREATE TABLE tipepemilik (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tipepemilik_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

tipepenanganan

Types of procedures for non-case animals

Schema
reference

CREATE TABLE tipepenanganan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tipepenanganan_id_seq'::regclass),
   code character varying NOT NULL,
   hiercode character varying,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

trainingtypes

Classification of training course types

Schema
reference

CREATE TABLE trainingtypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('trainingtypes_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

translation

Multilingual translation table for strings displayed in SMS, reports and other outputs

Schema
backoffice

CREATE TABLE translation (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('translation_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   comment character varying,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   type boolean NOT NULL DEFAULT false,
   class integer
);

translationclasses

Schema
reference

CREATE TABLE translationclasses (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('translationclasses_id_seq'::regclass),
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

treatmentanimals

Animals treated associated with a case

Schema
data

CREATE TABLE treatmentanimals (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('treatmentanimals_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   treatmentid integer NOT NULL REFERENCES treatments,
   drugid integer NOT NULL REFERENCES drugs,
   dose numeric(8,3),
   animals integer NOT NULL DEFAULT 1,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   animalid integer[]
);

treatments

Master table of treatments associated with a case

Schema
data

CREATE TABLE treatments (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('treatments_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

ujicepat

Field rapid test results

Schema
data

CREATE TABLE ujicepat (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('ujicepat_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   testtypeid integer NOT NULL REFERENCES testtypes,
   speciesid integer NOT NULL REFERENCES species,
   pos integer NOT NULL,
   neg integer NOT NULL,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

uncertaintytypes

Uncertainty around a quantitative laboratory test finding (>, <, ~ etc)

Schema
reference

CREATE TABLE uncertaintytypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('uncertaintytypes_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

urlinsertfields

Metadata for system to allow data to be inserted into a table directly from a URL query string, which may be submitted from outside the website (eg. clicked from an email). This table defines the parameters to be submitted.

Schema
metadata

CREATE TABLE urlinsertfields (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('urlinsertfields_id_seq'::regclass),
   urlinsertjobid integer NOT NULL REFERENCES urlinsertjobs,
   paramname character varying NOT NULL,
   paramtype character varying NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer,
   del boolean NOT NULL DEFAULT false
);

urlinsertjobs

Metadata for system to allow data to be inserted into a table directly from a URL query string, which may be submitted from outside the website (eg. clicked from an email). This table defines the messages.

Schema
metadata

CREATE TABLE urlinsertjobs (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('urlinsertjobs_id_seq'::regclass),
   name character varying NOT NULL,
   insertsql character varying NOT NULL,
   replysql character varying NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedby integer,
   del boolean NOT NULL DEFAULT false
);

user_hierarchy

Schema
backoffice

CREATE TABLE user_hierarchy (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('user_hierarchy_id_seq'::regclass),
   superiorid integer REFERENCES users,
   inferiorid integer REFERENCES users,
   relation relationshiptype,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

user_permissions

Permissions set at the individual user level (overrides group permissions)

Schema
backoffice

CREATE TABLE user_permissions (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('user_permissions_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users,
   permission_typeid integer NOT NULL REFERENCES permission_types,
   permission integer NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

userreportslog

Schema
report

CREATE TABLE userreportslog (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('userreportslog_id_seq'::regclass),
   userid integer REFERENCES users,
   svdrptid integer[] NOT NULL,
   createdby integer NOT NULL DEFAULT 0 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

users

Master users table

Schema
backoffice

CREATE TABLE users (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('users_id_seq'::regclass),
   firstname character varying NOT NULL,
   surname character varying,
   phone phonenumber,
   email character varying,
   userlanguage integer NOT NULL DEFAULT 1,
   password character varying,
   pin text DEFAULT create_pin(),
   locationid integer,
   area integer[],
   infraid integer,
   groupid integer[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   picture character varying,
   time_expired integer DEFAULT 0,
   msgid bigint,
   deleted_email character varying,
   centroid_proj public.geometry(Point,3857),
   restclient_id integer REFERENCES rest_client
);

users_test

Schema
backoffice

CREATE TABLE users_test (
   id integer,
   firstname character varying,
   surname character varying,
   phone phonenumber,
   email character varying,
   userlanguage integer,
   password character varying,
   pin text,
   locationid integer,
   area integer[],
   infraid integer,
   groupid integer[],
   createdby integer,
   createdon timestamp without time zone,
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean,
   picture character varying,
   time_expired integer,
   msgid bigint
);

vaccinationprograms

Vaccination programs

Schema
data

CREATE TABLE vaccinationprograms (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('vaccinationprograms_id_seq'::regclass),
   name character varying NOT NULL,
   startdate date NOT NULL DEFAULT now(),
   enddate date,
   area integer[] NOT NULL,
   diseaseid integer[] NOT NULL,
   vaccineid integer[] NOT NULL,
   purpose integer NOT NULL DEFAULT 1,
   dosespusat integer NOT NULL DEFAULT 0,
   dosesprovinsi integer NOT NULL DEFAULT 0,
   doseskabupaten integer NOT NULL DEFAULT 0,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   diseaseeventid integer REFERENCES diseaseevents
);

vaccinations

Village/farm level vaccination events associated with a vaccination program

Schema
data

CREATE TABLE vaccinations (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('vaccinations_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   programid integer NOT NULL REFERENCES vaccinationprograms,
   locationid integer NOT NULL REFERENCES locations,
   speciesid integer NOT NULL REFERENCES species,
   firstdose integer NOT NULL DEFAULT 0,
   booster integer,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

vachistory

Vaccination history of an animal in a laboratory submission

Schema
lab

CREATE TABLE vachistory (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('vachistory_id_seq'::regclass),
   animalid integer REFERENCES animals,
   diseaseid integer REFERENCES diseases,
   vaccineid integer REFERENCES drugs,
   vacdate date,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint
);

valdata

Schema
backoffice

CREATE TABLE valdata (
   fsql character varying,
   ferr character varying,
   tsql character varying,
   terr character varying
);

warna

Animal colour

Schema
reference

CREATE TABLE warna (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('warna_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

weekdays

Days of the week

Schema
reference

CREATE TABLE weekdays (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('weekdays_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

yiisession

Session management for web system

Schema
backoffice

CREATE TABLE yiisession (
   id character varying PRIMARY KEY NOT NULL,
   expire integer,
   data text,
   recordid integer UNIQUE NOT NULL DEFAULT nextval('yiisession_recordid_seq'::regclass)
);

zoonoses

Data on human involvement in suspected zoonotic disease cases

Schema
data

CREATE TABLE zoonoses (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('zoonoses_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   terpapar integer NOT NULL,
   sakit integer NOT NULL,
   mati integer NOT NULL,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);