Database tables

Daftar isi

accesslog

Track web access

Schema
backoffice

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

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),
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   createdby integer NOT NULL REFERENCES users,
   name character varying[] NOT NULL
);

afkir

Culling activities

Schema
data

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

ages

Animal age categories

Schema
reference

CREATE TABLE ages (
   del boolean NOT NULL DEFAULT false,
   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
);

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 (
   userid integer NOT NULL REFERENCES users,
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('aireportanimals_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   msgid bigint NOT NULL
);

aireports

Schema
data

CREATE TABLE aireports (
   modifiedby integer REFERENCES users,
   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(),
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

alasanpenolakan

alasan penolakan pemotongan

Schema
reference

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

animal_types

Animal type classification for slaughter and population

Schema
reference

CREATE TABLE animal_types (
   population boolean NOT NULL DEFAULT true,
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('animal_types_id_seq'::regclass),
   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 (
   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,
   msgid bigint,
   ageunitsid integer REFERENCES ageunits,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('animals_id_seq'::regclass)
);

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 (
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   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
);

app_projects

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

Schema
metadata

CREATE TABLE app_projects (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   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,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer
);

app_reports

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

Schema
metadata

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

app_reports

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

Schema
metadata

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

app_static

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

Schema
metadata

CREATE TABLE app_static (
   del boolean NOT NULL DEFAULT false,
   modifiedby integer,
   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,
   modifiedon timestamp without time zone,
   version integer NOT NULL DEFAULT 1,
   sql character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now()
);

apphelp

Schema
reference

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

asalbibit

Origin of seed for forage crops

Schema
reference

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

asuransihewan

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

Schema
data

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

auth_assignment

Schema
backoffice

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

auth_item

Schema
backoffice

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

auth_item_child

Schema
backoffice

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

bcs

Reference table for the BCS categories and the corresponding intervals

Schema
reference

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

bmindotempprov

Schema
backoffice

CREATE TABLE bmindotempprov (
   createdon timestamp without time zone,
   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,
   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 (
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('brewerpalettes_id_seq'::regclass),
   code character varying NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   createdby integer NOT NULL REFERENCES users,
   del boolean NOT NULL DEFAULT false
);

businessrules

Business rules definining custom actions to be taken during data processing

Schema
backoffice

CREATE TABLE businessrules (
   modifiedon timestamp without time zone,
   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,
   name character varying,
   description character varying,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('businessrules_id_seq1'::regclass)
);

businessrulesold

Schema
backoffice

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

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),
   modifiedby integer NOT NULL,
   modifiedon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   numcases integer NOT NULL,
   speciesid integer NOT NULL REFERENCES species,
   signids integer[],
   syndrome integer REFERENCES syndromes,
   location integer NOT NULL REFERENCES locations,
   reportdate date NOT NULL,
   msgid character varying NOT NULL
);

cadreresponses

A dummy table for testing of the spreadsheet parsing

Schema
data

CREATE TABLE cadreresponses (
   createdby integer NOT NULL,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   otherdiagnosis character varying,
   diagnosis integer[] NOT NULL,
   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,
   modifiedby integer NOT NULL,
   modifiedon timestamp with time zone NOT NULL DEFAULT now()
);

case_animals

Individual animals in a disease case

Schema
data

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

caseimages

Images from a disease case

Schema
data

CREATE TABLE caseimages (
   modifiedon timestamp without time zone,
   caseid integer REFERENCES cases,
   msgid bigint NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('caseimages_id_seq'::regclass),
   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,
   del boolean NOT NULL DEFAULT false
);

caseresults

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

Schema
data

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

caseresulttypes

Outcomes of clinical cases

Schema
reference

CREATE TABLE caseresulttypes (
   del boolean NOT NULL DEFAULT false,
   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,
   code character varying
);

cases

Master table for all field disease cases

Schema
data

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

containershipment

Schema
data

CREATE TABLE containershipment (
   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,
   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
);

dashboard_report

Schema
backoffice

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

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_user integer,
   id_report 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,
   widgettype character varying DEFAULT 'widget'::character varying,
   modifiedon timestamp with time zone,
   order integer,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   del boolean NOT NULL DEFAULT false
);

datatables

Schema
metadata

CREATE TABLE datatables (
   createdby integer NOT NULL DEFAULT 1,
   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(),
   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 (
   startdate character varying,
   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,
   enddate character varying
);

deskripsihewan

Description of an animal for individual animal identification

Schema
data

CREATE TABLE deskripsihewan (
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   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,
   createdon timestamp without time zone NOT NULL DEFAULT now()
);

diagnoses

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

Schema
data

CREATE TABLE diagnoses (
   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,
   createdby integer NOT NULL REFERENCES users,
   labsubmissionid integer REFERENCES labsubmissions,
   diseaseid integer NOT NULL REFERENCES diseases,
   caseid integer NOT NULL REFERENCES cases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('diagnoses_id_seq'::regclass)
);

disease_details

Schema
reference

CREATE TABLE disease_details (
   diagnosis character varying[],
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   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[],
   treatment character varying[],
   prevention character varying[],
   species integer[],
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now()
);

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,
   del boolean NOT NULL DEFAULT false,
   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
);

diseases

Master list of diseases

Schema
reference

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

diseasesigns

Probabiilty matrix of the occurrence of different signs with different diseases

Schema
reference

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

diseasespecies

Species susceptible to different diseases

Schema
reference

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

distribusistraw

Schema
data

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

drugclasses

Classification of drugs by regulation status

Schema
reference

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

drugs

Master table of registered and other drugs

Schema
data

CREATE TABLE drugs (
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('drugs_id_seq'::regclass),
   paravet boolean NOT NULL DEFAULT false,
   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
);

drugtypes

Classification of drugs by functional group

Schema
reference

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

emailnotifications

Schema
backoffice

CREATE TABLE emailnotifications (
   modifiedon timestamp without time zone,
   type character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('emailnotifications_id_seq'::regclass),
   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,
   del boolean NOT NULL DEFAULT false
);

errors

Log of SMS format errors

Schema
sms

CREATE TABLE errors (
   userid integer NOT NULL REFERENCES users,
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('errors_id_seq'::regclass),
   receivedtime timestamp without time zone
);

estrus

Schema
data

CREATE TABLE estrus (
   animals integer NOT NULL DEFAULT 1,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('estrus_id_seq'::regclass),
   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 (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   caseid integer NOT NULL REFERENCES cases,
   diseaseeventid integer NOT NULL REFERENCES cases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('eventcase_id_seq'::regclass),
   createdby integer NOT NULL REFERENCES users
);

exportreferencenamedranges

Definition of Excel named ranges for the reference table export system

Schema
reference

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

exportreferences

Master table for the reference table export system

Schema
reference

CREATE TABLE exportreferences (
   listsql character varying,
   replysql character varying,
   listname character varying[],
   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
);

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),
   del boolean NOT NULL DEFAULT false,
   sortorder integer NOT NULL DEFAULT 1,
   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
);

favoritereport

Schema
backoffice

CREATE TABLE favoritereport (
   label character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('favoritereport_id_seq'::regclass),
   reportid integer NOT NULL REFERENCES reports,
   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(),
   del boolean DEFAULT false,
   modifiedby integer,
   modifiedon timestamp without time zone,
   createdby integer NOT NULL REFERENCES users
);

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

findings

Laboratory findings

Schema
reference

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

form_def

Schema
backoffice

CREATE TABLE form_def (
   row_to_json json
);

fungsi

Animal function

Schema
reference

CREATE TABLE fungsi (
   code character varying NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   speciesid integer REFERENCES species,
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('fungsi_id_seq'::regclass),
   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 (
   createdby integer NOT NULL REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('gestation_id_seq'::regclass),
   speciesid integer NOT NULL REFERENCES species,
   oestruscycle integer,
   variation integer NOT NULL,
   gestationperiod integer NOT NULL,
   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 (
   permission_typeid integer NOT NULL REFERENCES permission_types,
   groupid integer NOT NULL REFERENCES groups,
   permission integer NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('group_permissions_id_seq'::regclass),
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone
);

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),
   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
);

hewan

Master table for individual animal identification

Schema
data

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

hijauan

Data table for forage crop production

Schema
data

CREATE TABLE hijauan (
   msgid bigint NOT NULL,
   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,
   idjeniskebun integer
);

importfields

Field definitions for Excel imports

Schema
metadata

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

importfieldtypes

Field types used for Excel imports

Schema
metadata

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

importjobs

Job definitions for Excel imports. May include multiple tables

Schema
metadata

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

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 (
   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,
   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,
   msgid bigint,
   message character varying,
   receiver character varying,
   userid integer
);

infrastructure

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

Schema
reference

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

infrastructure_types

Classification of infrastructure

Schema
reference

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

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

jenisbibit

Types of forage crops

Schema
reference

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

jenishijauan

Types of forage crops

Schema
reference

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

jeniskebun

Types of garden

Schema
reference

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

jeniskonsentrat

Types of for consentrat

Schema
reference

CREATE TABLE jeniskonsentrat (
   createdby integer NOT NULL REFERENCES users,
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('jeniskonsentrat_id_seq'::regclass),
   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
);

jeniskontainer

Types of containers

Schema
reference

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

kejadian

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

Schema
data

CREATE TABLE kejadian (
   idtipekejadian integer REFERENCES tipekejadian,
   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,
   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
);

kejadiankelompok

Events table for a group of animals, used to store a range of different event types

Schema
data

CREATE TABLE kejadiankelompok (
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   idtipekejadian integer REFERENCES tipekejadian,
   idklasifikasi integer,
   nilai double precision,
   jumlahhewan 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,
   msgid bigint NOT NULL,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('kejadiankelompok_id_seq'::regclass),
   infrastructureid integer NOT NULL REFERENCES infrastructure
);

keterangan

Comments on a case

Schema
data

CREATE TABLE keterangan (
   del boolean NOT NULL DEFAULT false,
   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
);

komentar

Comments on a case

Schema
data

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

kondisikontainer

condition of containers

Schema
reference

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

konsentrat

Data table for consentrat

Schema
data

CREATE TABLE konsentrat (
   idjeniskonsentrat integer NOT NULL REFERENCES jeniskonsentrat,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('konsentrat_id_seq'::regclass),
   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,
   infraid integer NOT NULL
);

kursuspelatihan

Training courses

Schema
data

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

labsections

Laboratory sections

Schema
reference

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

labsubmissions

Laboratory submissions master table

Schema
lab

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

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),
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   name character varying[] NOT NULL,
   code character varying UNIQUE NOT NULL,
   modifiedby integer REFERENCES users
);

locationlevels

Schema
reference

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

locations

Master spatial data table with administrative boundaries

Schema
reference

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

logdelete

Schema
data

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

menu

Website main menu structure definition

Schema
backoffice

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

menu2

Schema
backoffice

CREATE TABLE menu2 (
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('menu2_id_seq'::regclass),
   pagetitle character varying[] NOT NULL,
   accesslevel text NOT NULL
);

methods

Methods for laboratory tests

Schema
reference

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

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 (
   sent integer,
   comment character varying,
   operatorid integer REFERENCES sms_operators,
   incoming boolean,
   nport integer,
   days integer,
   credit integer,
   pulsacheck timestamp without time zone,
   ber text,
   ssi text,
   received integer,
   failed integer,
   status text,
   lastcheck timestamp without time zone,
   phone text,
   modem text,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('modemstatus_id_seq'::regclass)
);

modulpelatihan

Training modules

Schema
reference

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

months

Calendar months

Schema
reference

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

movement

Animal movement reporting (health certificate) master table

Schema
data

CREATE TABLE movement (
   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,
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   ownerphone character varying,
   ownerid character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('movement_id_seq'::regclass)
);

movementanimals

List of species and number of animals for animal movement reporting

Schema
data

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

multilayermaps

Schema
report

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

negativereports

Village-level negative disease reports

Schema
data

CREATE TABLE negativereports (
   msgid bigint NOT NULL,
   id integer NOT NULL DEFAULT nextval('negativereports_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer 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 (
   type character varying,
   image character varying,
   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
);

nitrogenshipment

Schema
data

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

odttemplates

Templates for ODT reports

Schema
report

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

outbox

Outgoing SMS messages

Schema
sms

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

owners

Owners of animals in a laboratory submission

Schema
lab

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

parameters

Miscellaneous user defined system parameters

Schema
backoffice

CREATE TABLE parameters (
   value character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('parameters_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
);

partisipanpelatihan

Participants at a training course

Schema
data

CREATE TABLE partisipanpelatihan (
   del boolean NOT NULL DEFAULT false,
   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
);

pemilik_hewan

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

Schema
data

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

penolakan

Penolakan Pemotongan di RPH

Schema
data

CREATE TABLE penolakan (
   alasanpenolakanid integer NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('penolakan_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   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
);

permission_types

Schema
backoffice

CREATE TABLE permission_types (
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('permission_types_id_seq'::regclass),
   preference boolean NOT NULL DEFAULT false,
   defaultvalue integer DEFAULT 0,
   userpermission boolean NOT NULL DEFAULT false,
   description character varying[]
);

perubahansampel

Tabel Referensi Untuk Perubahan Sampel Produk Hewan

Schema
reference

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

plr_modules

Schema
backoffice

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

plr_modules2

Schema
backoffice

CREATE TABLE plr_modules2 (
   modsrc text,
   modseq integer
);

population

Village/farm level animal population

Schema
data

CREATE TABLE population (
   del boolean NOT NULL DEFAULT false,
   total integer NOT NULL,
   animaltypeid integer NOT NULL REFERENCES animal_types,
   msgid bigint NOT NULL,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('population_id_seq'::regclass),
   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
);

preventivetreatments

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

Schema
data

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

productionsystems

Production systems

Schema
reference

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

programafkir

Culling programs

Schema
data

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

programtindakan

Disease control programs

Schema
data

CREATE TABLE programtindakan (
   area 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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('programtindakan_id_seq'::regclass),
   startdate date NOT NULL DEFAULT now(),
   enddate date,
   diseaseid integer[] NOT NULL
);

prov

Schema
backoffice

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

purposes

Purposes for vaccination

Schema
reference

CREATE TABLE purposes (
   type purposetypes,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('purposes_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
);

questions

Questions sent by SMS

Schema
data

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

queuestats

Snapshot of SMS outgoing queue

Schema
sms

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

rabiesreports

Schema
data

CREATE TABLE rabiesreports (
   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,
   userid integer NOT NULL REFERENCES users,
   speciesid integer NOT NULL REFERENCES species,
   caseid integer REFERENCES cases,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   humansbitten integer NOT NULL,
   msgid bigint NOT NULL,
   provoked boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   animalsbitten integer NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rabiesreports_id_seq'::regclass),
   locationid integer NOT NULL REFERENCES locations
);

receipt

Schema
data

CREATE TABLE receipt (
   del boolean NOT NULL DEFAULT false,
   infrastructureid integer NOT NULL REFERENCES infrastructure,
   createdby integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   modifiedon timestamp with time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   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()
);

registrationmatrix

Permissions for different user types to register other users

Schema
reference

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

reportcategories

Schema
report

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

reportlog

Schema
report

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

reportparams

Replaceable parameters for reports

Schema
report

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

reportparamtypes

Parameter types for reports

Schema
report

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

reports

Master report definition table

Schema
report

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

reportsubscriptions

Group and individual subscriptions to saved reports for automated periodic reporting

Schema
report

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

reporttags

Schema
backoffice

CREATE TABLE reporttags (
   modifiedby integer NOT NULL DEFAULT 1,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('report.reporttags_id_seq'::regclass),
   del 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(),
   label character varying[] NOT NULL,
   prompt character varying
);

reporttags

Schema
report

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

response

Initial investigation of a disease report, including differential diagnosis

Schema
data

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

rest_client

Schema
backoffice

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

results

Laboratory testing results

Schema
reference

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

riwayatvaksinasi

Vaccination history from priority disease investigation

Schema
data

CREATE TABLE riwayatvaksinasi (
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   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
);

savedreportparams

Parameters for saved reports

Schema
report

CREATE TABLE savedreportparams (
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   paramvalue character varying,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1,
   modifiedby integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false,
   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
);

savedreports

Parameterised versions of reports for automated periodic reporting

Schema
report

CREATE TABLE savedreports (
   frequency report.reportfrequency,
   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,
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   public boolean NOT NULL DEFAULT false,
   delivery report.reportdelivery,
   del boolean NOT NULL DEFAULT false
);

selfcheck

Schema
sms

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

sessions

Schema
backoffice

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

sex

Animal sex

Schema
reference

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

shipments

Schema
data

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

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),
   animalid integer REFERENCES hewan,
   del boolean NOT NULL DEFAULT false,
   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,
   speciesid integer NOT NULL REFERENCES species,
   signsid integer[] NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   msgid bigint NOT NULL,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now()
);

signs

Clinical signs

Schema
reference

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

signs_asia

Schema
reference

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

signspecies

Signs valid for different species

Schema
reference

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

slaughtertotals

Daily abattoir slaughter totals by animal type

Schema
data

CREATE TABLE slaughtertotals (
   reportdate 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 NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   animaltypeid integer NOT NULL REFERENCES animal_types,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('slaughtertotals_id_seq'::regclass),
   infrastructureid integer NOT NULL REFERENCES infrastructure,
   total integer NOT NULL DEFAULT 0,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now()
);

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 (
   app_type character varying,
   del boolean NOT NULL DEFAULT false,
   description character varying NOT NULL,
   field_type character varying NOT NULL,
   name character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sms_field_types_id_seq'::regclass)
);

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 (
   createdby integer NOT NULL REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   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
);

sms_operators

Telephone network operators

Schema
reference

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

sms_prefixes

Mobile phone prefixes

Schema
reference

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

smserrorchecks

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

Schema
data

CREATE TABLE smserrorchecks (
   del boolean NOT NULL DEFAULT false,
   modifiedby integer,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   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,
   modifiedon timestamp without time zone,
   action smscheckaction
);

species

Species and breeds

Schema
reference

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

specimenforms

The form of a laboratory specimen (perservative etc)

Schema
reference

CREATE TABLE specimenforms (
   del boolean NOT NULL DEFAULT false,
   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
);

specimens

Specimens from animals in a laboratory submission

Schema
lab

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

specimentypes

The type of a laboratory specimen (tissue of origin)

Schema
reference

CREATE TABLE specimentypes (
   modifiedon timestamp without time zone,
   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,
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   createdon timestamp without time zone NOT NULL DEFAULT now()
);

sql_features

Schema
information_schema

CREATE TABLE sql_features (
   comments information_schema.character_data,
   is_verified_by information_schema.character_data,
   is_supported information_schema.yes_or_no,
   sub_feature_name information_schema.character_data,
   sub_feature_id information_schema.character_data,
   feature_name information_schema.character_data,
   feature_id 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_binding_style information_schema.character_data,
   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_programming_language information_schema.character_data
);

sql_packages

Schema
information_schema

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

sql_parts

Schema
information_schema

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

sql_sizing

Schema
information_schema

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

sql_sizing_profiles

Schema
information_schema

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

statusasuransi

status asuransi

Schema
reference

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

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 (
   createdby integer NOT NULL REFERENCES users,
   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,
   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),
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   asalid integer NOT NULL,
   harga integer NOT NULL,
   total integer NOT NULL,
   jenisbibitid integer NOT NULL REFERENCES jenisbibit,
   jenishijauanid integer NOT NULL,
   msgid bigint NOT NULL
);

straws

Batch Number

Schema
data

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

straws

Stud ID

Schema
data

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

straws

List of semen straws for artificial insemination

Schema
data

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

straws

Date of production

Schema
data

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

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
);

strawshipment

Schema
data

CREATE TABLE strawshipment (
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('strawshipment_id_seq'::regclass),
   receiptid integer REFERENCES receipt,
   shipmentid integer REFERENCES shipments,
   stocktakeid integer REFERENCES stocktake,
   breedid integer REFERENCES species
);

submission_reasons

Schema
reference

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

submissionreasons

Reason for laboratory submission

Schema
reference

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

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 (
   sectionid integer REFERENCES labsections,
   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,
   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 (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   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,
   modifiedby integer REFERENCES users
);

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),
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   name character varying[] NOT NULL,
   code character varying NOT NULL
);

sumberpenyakit

Suspected source of infection as determined during outbreak investigations

Schema
data

CREATE TABLE sumberpenyakit (
   msgid bigint NOT NULL,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   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
);

surveillance

Both lab-based and field surveillance reports

Schema
data

CREATE TABLE surveillance (
   modifiedby integer REFERENCES users,
   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(),
   labid integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   neg integer,
   pos integer
);

surveillanceprograms

List of surveillance programs

Schema
data

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

survpasar

Tabel untuk surveilans pasar kesmavet

Schema
data

CREATE TABLE survpasar (
   modifiedon timestamp without time zone,
   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,
   del boolean NOT NULL DEFAULT false,
   msgid bigint
);

syndromereports

Disease report for priority cases including a syndrome classification

Schema
data

CREATE TABLE syndromereports (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   userid integer NOT NULL REFERENCES users,
   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,
   modifiedon timestamp without time zone,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL,
   modifiedby integer REFERENCES users
);

syndromes

The disease which the syndrome might reflect

Schema
reference

CREATE TABLE syndromes (
   createdby integer NOT NULL REFERENCES users,
   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,
   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

Defined syndromes related to priority diseases

Schema
reference

CREATE TABLE syndromes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('syndromes_id_seq'::regclass),
   targetspeciesid integer[],
   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,
   code character varying(6) UNIQUE NOT NULL,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

syndromes

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

Schema
reference

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

tablefields

Schema
metadata

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

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),
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   signsid integer[] NOT NULL,
   caseid integer NOT NULL REFERENCES cases
);

targets

Target for laboratory tests (organism etc)

Schema
reference

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

templates

HTML templates for reporting

Schema
report

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

temptesttypes

Schema
backoffice

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

testresults

Results of laboratory testing

Schema
lab

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

testresults

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

Schema
lab

CREATE TABLE testresults (
   jumlahneg integer,
   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),
   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,
   findingquant double precision,
   findingquant double precision,
   findingqual integer REFERENCES findings,
   findingquant double precision,
   resultid integer REFERENCES results,
   resultid integer REFERENCES results,
   resultid integer REFERENCES results,
   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,
   testid integer NOT NULL REFERENCES tests,
   jumlahneg integer,
   jumlahrusak integer,
   jumlahrusak integer,
   jumlahrusak integer,
   findingqual integer REFERENCES findings,
   findingqual integer REFERENCES findings,
   testid integer NOT NULL REFERENCES tests,
   testid integer NOT NULL REFERENCES tests
);

tests

Tests performed on a specimen in a laboratory submission

Schema
lab

CREATE TABLE tests (
   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,
   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
);

testtypes

Master table of laboratory test types

Schema
reference

CREATE TABLE testtypes (
   modifiedby integer REFERENCES users,
   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(),
   name character varying[] NOT NULL,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   rapid boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tests_id_seq'::regclass),
   code character varying UNIQUE
);

testunits

Quantitative units for laboratory test findings

Schema
reference

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

tindakan

Reference table of disease control activity types

Schema
reference

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

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),
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdby integer NOT NULL REFERENCES users,
   jumlah integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   programtindakanid integer NOT NULL REFERENCES programtindakan,
   tindakanid integer NOT NULL REFERENCES tindakan,
   locationid integer NOT NULL REFERENCES locations,
   msgid bigint NOT NULL,
   del boolean NOT NULL DEFAULT false
);

tipeakhir

Disposal types for animals

Schema
reference

CREATE TABLE tipeakhir (
   code character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('tipeakhir_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,
   live boolean DEFAULT false,
   del boolean NOT NULL DEFAULT false
);

tipekejadian

Event types for individual animals

Schema
reference

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

tipepemilik

Schema
backoffice

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

tipepenanganan

Types of procedures for non-case animals

Schema
reference

CREATE TABLE tipepenanganan (
   del boolean NOT NULL DEFAULT false,
   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
);

trainingtypes

Classification of training course types

Schema
reference

CREATE TABLE trainingtypes (
   modifiedon timestamp without time zone,
   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,
   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 (
   modifiedon timestamp without time zone,
   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(),
   del boolean NOT NULL DEFAULT false,
   modifiedby integer REFERENCES users
);

treatmentanimals

Animals treated associated with a case

Schema
data

CREATE TABLE treatmentanimals (
   del boolean NOT NULL DEFAULT false,
   animals integer NOT NULL DEFAULT 1,
   dose numeric(8,3),
   drugid integer NOT NULL REFERENCES drugs,
   treatmentid integer NOT NULL REFERENCES treatments,
   msgid bigint NOT NULL,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('treatmentanimals_id_seq'::regclass),
   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,
   animalid integer[]
);

treatments

Master table of treatments associated with a case

Schema
data

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

ujicepat

Field rapid test results

Schema
data

CREATE TABLE ujicepat (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   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,
   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 (
   code character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('uncertaintytypes_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
);

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 (
   del boolean NOT NULL DEFAULT false,
   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
);

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 (
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('urlinsertjobs_id_seq'::regclass),
   name character varying NOT NULL
);

user_hierarchy

Schema
backoffice

CREATE TABLE user_hierarchy (
   del boolean NOT NULL DEFAULT false,
   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
);

user_permissions

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

Schema
backoffice

CREATE TABLE user_permissions (
   del boolean NOT NULL DEFAULT false,
   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
);

userreportslog

Schema
report

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

users

Master users table

Schema
backoffice

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

users_test

Schema
backoffice

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

vaccinationprograms

Vaccination programs

Schema
data

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

vaccinations

Village/farm level vaccination events associated with a vaccination program

Schema
data

CREATE TABLE vaccinations (
   msgid bigint NOT NULL,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   firstdose integer NOT NULL DEFAULT 0,
   booster integer,
   modifiedon timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('vaccinations_id_seq'::regclass),
   del 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,
   speciesid integer NOT NULL REFERENCES species,
   locationid integer NOT NULL REFERENCES locations,
   programid integer NOT NULL REFERENCES vaccinationprograms,
   userid integer NOT NULL REFERENCES users
);

vachistory

Vaccination history of an animal in a laboratory submission

Schema
lab

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

valdata

Schema
backoffice

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

warna

Animal colour

Schema
reference

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

weekdays

Days of the week

Schema
reference

CREATE TABLE weekdays (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   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,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users
);

yiisession

Session management for web system

Schema
backoffice

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

zoonoses

Data on human involvement in suspected zoonotic disease cases

Schema
data

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