Database tables

Revisi per 25 Februari 2014 10.30; Catriona (bicara | kontrib)

(beda) ←Revisi sebelumnya | Revisi terkini (beda) | Revisi selanjutnya→ (beda)

Daftar isi

accesslog

Track web access

Schema
backoffice

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

adminlevels

Spatial administrative unit levels (province, district etc)

Schema
reference

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

afkir

Culling activities

Schema
data

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

ages

Animal age categories

Schema
reference

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

ageunits

Units for age measurement

Schema
reference

CREATE TABLE ageunits (
   days integer,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.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
);

aireportanimals

Schema
data

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

aireports

Schema
data

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

alasanpenolakan

alasan penolakan pemotongan

Schema
reference

CREATE TABLE alasanpenolakan (
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.alasanpenolakan_id_seq'::regclass)
);

animal_types

Animal type classification for slaughter and population

Schema
reference

CREATE TABLE animal_types (
   age integer REFERENCES ages,
   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,
   slaughter boolean NOT NULL DEFAULT false,
   population boolean NOT NULL DEFAULT true,
   productive boolean,
   origin at_origin,
   purpose at_purpose,
   sex at_sex,
   species integer REFERENCES species,
   name character varying[] NOT NULL,
   hiercode character varying UNIQUE,
   code character varying UNIQUE NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.animal_types_id_seq'::regclass)
);

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 (
   animalident character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('lab.animals_id_seq'::regclass),
   labsubmissionid integer NOT NULL REFERENCES labsubmissions,
   ownerid integer REFERENCES owners,
   animalid bigint REFERENCES hewan,
   speciesid integer NOT NULL REFERENCES species,
   sexid integer REFERENCES sex,
   age double precision,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   ageunitsid integer REFERENCES ageunits,
   msgid bigint
);

antemortem

Pemeriksaan antemortem di RPH

Schema
data

CREATE TABLE antemortem (
   identifikasi character varying NOT NULL,
   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 DEFAULT 1 REFERENCES users,
   infrastructureid integer NOT NULL REFERENCES infrastructure,
   idkeputusanam integer NOT NULL REFERENCES keputusanam,
   idsigns integer[] NOT NULL,
   iddiagnosa integer NOT NULL REFERENCES diseases,
   animaltypeid integer NOT NULL REFERENCES animal_types,
   idpemilik integer NOT NULL REFERENCES users,
   skkh text,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.antemortem_id_seq'::regclass)
);

apifields

Schema
metadata

CREATE TABLE apifields (
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('apifields_id_seq'::regclass),
   apiid integer NOT NULL REFERENCES apis,
   name character varying NOT NULL,
   paramtypeid integer REFERENCES apiparamtypes,
   createdby integer NOT NULL REFERENCES users,
   del boolean NOT NULL DEFAULT false
);

apioperations

Schema
reference

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

apiparamtypes

Schema
reference

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

apis

Schema
metadata

CREATE TABLE apis (
   createdby integer NOT NULL REFERENCES users,
   permissionid integer NOT NULL REFERENCES permission_types,
   operationid integer NOT NULL REFERENCES apioperations,
   description character varying NOT NULL,
   name character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('apis_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp with time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp with time zone NOT NULL DEFAULT now()
);

apisql

Schema
metadata

CREATE TABLE apisql (
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp with time zone,
   natorder integer NOT NULL DEFAULT 1,
   apiid integer NOT NULL REFERENCES apis,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('apisql_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   sql character varying NOT NULL,
   serverid integer NOT NULL REFERENCES servers,
   createdby integer NOT NULL REFERENCES users
);

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 (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.app_downloads_id_seq'::regclass),
   userid integer NOT NULL,
   reportid integer NOT NULL,
   outcome character varying,
   createdby integer NOT NULL DEFAULT 1
);

app_projects

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

Schema
metadata

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

app_reports

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

Schema
metadata

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

app_reports

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

Schema
metadata

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

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,
   version integer NOT NULL DEFAULT 1,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   createdby integer NOT NULL DEFAULT 1,
   sql character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.app_static_id_seq'::regclass),
   name character varying[] NOT NULL,
   description character varying[] NOT NULL
);

apphelp

Schema
reference

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

asalbibit

Origin of seed for forage crops

Schema
reference

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

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,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.asuransihewan_id_seq'::regclass),
   nomor numeric NOT NULL,
   msgid bigint NOT NULL,
   masaawal date,
   masaakhir date,
   del boolean NOT NULL DEFAULT false,
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   nilai bigint NOT NULL,
   idstatusasuransi integer REFERENCES statusasuransi
);

auth_assignment

Schema
backoffice

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

auth_item

Schema
backoffice

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

auth_item_child

Schema
backoffice

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

bcs

Reference table for the BCS categories and the corresponding intervals

Schema
reference

CREATE TABLE bcs (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.bcs_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,
   nilai character varying NOT NULL,
   code character varying NOT NULL
);

bmindotempprov

Schema
backoffice

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

brewerpalettes

Colour definitions for charts

Schema
reference

CREATE TABLE brewerpalettes (
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.brewerpalettes_id_seq'::regclass),
   modifiedon timestamp without time zone,
   code character varying NOT NULL
);

businessrules

Business rules definining custom actions to be taken during data processing

Schema
backoffice

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

businessrulesold

Schema
backoffice

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

cadrereports

A dummy table for testing of the spreadsheet parsing

Schema
data

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

cadreresponses

A dummy table for testing of the spreadsheet parsing

Schema
data

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

case_animals

Individual animals in a disease case

Schema
data

CREATE TABLE case_animals (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.case_animals_id_seq'::regclass),
   del boolean 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,
   idhewan integer NOT NULL REFERENCES hewan,
   idcase integer NOT NULL REFERENCES cases
);

caseimages

Images from a disease case

Schema
data

CREATE TABLE caseimages (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.caseimages_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,
   image character varying NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   caseid integer REFERENCES cases,
   msgid bigint NOT NULL
);

caseresults

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

Schema
data

CREATE TABLE caseresults (
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL,
   userid integer NOT NULL REFERENCES users,
   modifiedon timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.caseresults_id_seq'::regclass),
   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,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   jumlah integer
);

caseresulttypes

Outcomes of clinical cases

Schema
reference

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

cases

Master table for all field disease cases

Schema
data

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

containershipment

Schema
data

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

dashboard_report

Schema
backoffice

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

dashboard_report_user

Schema
backoffice

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

dashboard_widget

Schema
backoffice

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

datatables

Schema
metadata

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

daterangetypes

Pre-defined date ranges for reporting

Schema
reference

CREATE TABLE daterangetypes (
   sql text,
   enddate character varying,
   startdate 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,
   description character varying,
   name character varying[] UNIQUE NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.daterangetypes_id_seq'::regclass)
);

deskripsihewan

Description of an animal for individual animal identification

Schema
data

CREATE TABLE deskripsihewan (
   warna integer REFERENCES warna,
   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,
   tanduk character varying,
   cap character varying,
   idhewan bigint REFERENCES hewan,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.deskripsihewan_id_seq'::regclass)
);

diagnoses

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

Schema
data

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

disease_details

Schema
reference

CREATE TABLE disease_details (
   treatment character varying[],
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.disease_details_id_seq'::regclass),
   diseaseid integer REFERENCES diseases,
   name character varying[] NOT NULL,
   other_names character varying[],
   description character varying[],
   diagnosis 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,
   species integer[],
   prevention character varying[]
);

diseaseevents

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

Schema
data

CREATE TABLE diseaseevents (
   startdate date NOT NULL DEFAULT now(),
   name 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,
   area integer[],
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.diseaseevents_id_seq'::regclass),
   diseaseid integer REFERENCES diseases,
   enddate date
);

diseases

Master list of diseases

Schema
reference

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

diseasesigns

Probabiilty matrix of the occurrence of different signs with different diseases

Schema
reference

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

diseasespecies

Species susceptible to different diseases

Schema
reference

CREATE TABLE diseasespecies (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.diseasespecies_id_seq'::regclass),
   speciesid integer NOT NULL REFERENCES species,
   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,
   diseaseid integer NOT NULL REFERENCES diseases
);

distribusistraw

Schema
data

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

drugclasses

Classification of drugs by regulation status

Schema
reference

CREATE TABLE drugclasses (
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.drugclasses_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(),
   name character varying[] NOT NULL
);

drugs

Master table of registered and other drugs

Schema
data

CREATE TABLE drugs (
   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,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.drugs_id_seq'::regclass),
   drugtypeid integer NOT NULL REFERENCES drugtypes,
   code character varying,
   hiercode character varying,
   name character varying NOT NULL,
   regnumber character varying,
   regyear integer,
   di character(1),
   bfpl character(1),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   paravet boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer
);

drugtypes

Classification of drugs by functional group

Schema
reference

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

emailnotifications

Schema
backoffice

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

errors

Log of SMS format errors

Schema
sms

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

estrus

Schema
data

CREATE TABLE estrus (
   del boolean NOT NULL DEFAULT false,
   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(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.estrus_id_seq'::regclass),
   animals integer NOT NULL DEFAULT 1,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users
);

eventcase

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

Schema
data

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

exportreferencenamedranges

Definition of Excel named ranges for the reference table export system

Schema
reference

CREATE TABLE exportreferencenamedranges (
   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,
   tabid integer NOT NULL REFERENCES exportreferencetabs,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.exportreferencenamedranges_id_seq'::regclass)
);

exportreferences

Master table for the reference table export system

Schema
reference

CREATE TABLE exportreferences (
   filename character varying,
   listname character varying[],
   listsql character varying,
   replysql character varying,
   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('reference.exportreferences_id_seq'::regclass),
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

exportreferencetabs

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

Schema
reference

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

favoritereport

Schema
backoffice

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

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,
   typecode character(1) NOT NULL,
   description character varying NOT NULL,
   tablename character varying,
   columnname character varying,
   filtercond character varying,
   synonyms boolean NOT NULL DEFAULT false,
   sqlquery character varying,
   createdby integer NOT NULL REFERENCES users,
   typename character varying UNIQUE NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.fieldtypes_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,
   schemaname character varying,
   domaincond character varying
);

findings

Laboratory findings

Schema
reference

CREATE TABLE findings (
   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('reference.findings_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   section integer NOT NULL DEFAULT 1 REFERENCES labsections
);

form_def

Schema
backoffice

CREATE TABLE form_def (
   row_to_json json
);

fungsi

Animal function

Schema
reference

CREATE TABLE fungsi (
   speciesid integer REFERENCES species,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.fungsi_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,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone
);

gestation

Reproductive parameters of key species for production module reports

Schema
reference

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

group_permissions

Default permissions for a user group

Schema
backoffice

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

groups

User roles determining default permissions in the system

Schema
backoffice

CREATE TABLE groups (
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.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
);

hewan

Master table for individual animal identification

Schema
data

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

hijauan

Data table for forage crop production

Schema
data

CREATE TABLE hijauan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.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,
   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,
   idjeniskebun integer,
   jumlahbibit double precision,
   bulanpanen double precision,
   idanggaran integer,
   tahunfasilitasi integer,
   reportdate timestamp without time zone NOT NULL DEFAULT now()
);

importfields

Field definitions for Excel imports

Schema
metadata

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

importfieldtypes

Field types used for Excel imports

Schema
metadata

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

importjobs

Job definitions for Excel imports. May include multiple tables

Schema
metadata

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

importtables

Table definitions for Excel imports

Schema
metadata

CREATE TABLE importtables (
   linkfield character varying,
   keyfield character varying NOT NULL DEFAULT 'id'::character varying,
   tableorder integer NOT NULL DEFAULT 1,
   tablename character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.importtables_id_seq'::regclass),
   jobid integer NOT NULL REFERENCES importjobs,
   canupdate boolean NOT NULL DEFAULT false,
   keyexpression 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(),
   parenttable integer REFERENCES importtables
);

inbox

Incoming SMS messsages

Schema
sms

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

infrastructure

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

Schema
reference

CREATE TABLE infrastructure (
   locationid integer NOT NULL REFERENCES locations,
   infrastructure_typeid integer NOT NULL REFERENCES infrastructure_types,
   name character varying NOT NULL,
   area integer[],
   shortname character varying,
   code character varying UNIQUE,
   infratype integer UNIQUE,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   modifiedby integer REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.infrastructure_id_seq'::regclass),
   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,
   address character varying
);

infrastructure_types

Classification of infrastructure

Schema
reference

CREATE TABLE infrastructure_types (
   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,
   hiercode character varying UNIQUE NOT NULL,
   code character varying UNIQUE NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.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 (
   userid 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(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   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,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.investigationanimals_id_seq'::regclass)
);

irs

Schema
backoffice

CREATE TABLE irs (
   svdrptid integer,
   userid integer,
   name character varying,
   groupid integer,
   indivsub boolean,
   active boolean
);

jenisbibit

Types of forage crops

Schema
reference

CREATE TABLE jenisbibit (
   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,
   code character varying,
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.jenisbibit_id_seq'::regclass)
);

jenishijauan

Types of forage crops

Schema
reference

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

jeniskebun

Types of garden

Schema
reference

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

jeniskonsentrat

Types of for consentrat

Schema
reference

CREATE TABLE jeniskonsentrat (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.jeniskonsentrat_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
);

jeniskontainer

Types of containers

Schema
reference

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

kejadian

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

Schema
data

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

kejadiankelompok

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

Schema
data

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

keputusanam

keputusan pemeriksaan antemortem

Schema
reference

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

keputusanpm

keputusan pemeriksaan postmortem

Schema
reference

CREATE TABLE keputusanpm (
   code character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.keputusanpm_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
);

keterangan

Comments on a case

Schema
data

CREATE TABLE keterangan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.keterangan_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   keterangan character varying,
   del boolean NOT NULL DEFAULT false,
   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 (
   msgid bigint NOT NULL,
   komentar character varying,
   caseid integer NOT NULL REFERENCES cases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.komentar_id_seq'::regclass),
   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
);

kondisikontainer

condition of containers

Schema
reference

CREATE TABLE kondisikontainer (
   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,
   code character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.kondisikontainer_id_seq'::regclass)
);

konsentrat

Data table for consentrat

Schema
data

CREATE TABLE konsentrat (
   jumlah double precision NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.konsentrat_id_seq'::regclass),
   infraid integer NOT NULL,
   idjeniskonsentrat integer NOT NULL REFERENCES jeniskonsentrat,
   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,
   idanggaran integer,
   reportdate timestamp without time zone NOT NULL DEFAULT now()
);

kursuspelatihan

Training courses

Schema
data

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

labsections

Laboratory sections

Schema
reference

CREATE TABLE labsections (
   modifiedby integer REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.labsections_id_seq'::regclass),
   code character varying UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   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
);

labsubmissions

Laboratory submissions master table

Schema
lab

CREATE TABLE labsubmissions (
   locationid integer REFERENCES locations,
   otherid character varying,
   skkhid integer REFERENCES movement,
   survid integer REFERENCES surveillanceprograms,
   caseid integer REFERENCES cases,
   submissionreason integer NOT NULL REFERENCES submissionreasons,
   epinum character varying 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('lab.submissions_id_seq'::regclass),
   labid integer REFERENCES infrastructure,
   datesampled date,
   datereceived date,
   submitterid integer REFERENCES users,
   submittername character varying,
   submitteraddress character varying,
   submitterphone landline,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint,
   submittertype integer NOT NULL REFERENCES submittertypes
);

labtests

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

Schema
reference

CREATE TABLE labtests (
   ref 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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.labtests_id_seq'::regclass),
   testid integer NOT NULL REFERENCES testtypes,
   sectionid integer NOT NULL REFERENCES labsections,
   labid integer NOT NULL REFERENCES infrastructure,
   qualfinding boolean NOT NULL DEFAULT false,
   quantfinding boolean NOT NULL DEFAULT true,
   quantunitsid integer REFERENCES testunits,
   accredited boolean NOT NULL DEFAULT false
);

languages

List of languages used in the system

Schema
backoffice

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

locationlevels

Schema
reference

CREATE TABLE locationlevels (
   name character varying[] NOT NULL,
   code character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.locationlevels_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
);

locations

Master spatial data table with administrative boundaries

Schema
reference

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

logdelete

Schema
data

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

menu

Website main menu structure definition

Schema
backoffice

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

menu2

Schema
backoffice

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

methods

Methods for laboratory tests

Schema
reference

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

modemlog

Log of modem status

Schema
sms

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

modemstatus

Snapshot of modem status

Schema
sms

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

modulpelatihan

Training modules

Schema
reference

CREATE TABLE modulpelatihan (
   createdby integer NOT NULL REFERENCES users,
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.modulpelatihan_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()
);

months

Calendar months

Schema
reference

CREATE TABLE months (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.months_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
);

movement

Animal movement reporting (health certificate) master table

Schema
data

CREATE TABLE movement (
   ownerphone character varying,
   msgid bigint NOT NULL,
   destination integer NOT NULL REFERENCES locations,
   origin integer NOT NULL REFERENCES locations,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.movement_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,
   ownerid character varying
);

movementanimals

List of species and number of animals for animal movement reporting

Schema
data

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

multilayermaps

Schema
report

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

negativereports

Village-level negative disease reports

Schema
data

CREATE TABLE negativereports (
   msgid bigint NOT NULL,
   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 DEFAULT 1,
   checked boolean,
   userid integer NOT NULL,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer NOT NULL DEFAULT nextval('data.negativereports_id_seq'::regclass)
);

news

News stories

Schema
backoffice

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

nitrogenshipment

Schema
data

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

obat_zataktif

tabel obat dan zat aktifnya

Schema
data

CREATE TABLE obat_zataktif (
   del boolean NOT NULL DEFAULT false,
   idzataktif bigint NOT NULL REFERENCES zataktif,
   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,
   idobat bigint NOT NULL REFERENCES drugs,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.obat_zataktif_id_seq'::regclass)
);

odttemplates

Templates for ODT reports

Schema
report

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

outbox

Outgoing SMS messages

Schema
sms

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

owners

Owners of animals in a laboratory submission

Schema
lab

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

parameters

Miscellaneous user defined system parameters

Schema
backoffice

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

partisipanpelatihan

Participants at a training course

Schema
data

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

pemilik_hewan

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

Schema
data

CREATE TABLE pemilik_hewan (
   idtipepemilik integer NOT NULL DEFAULT 1 REFERENCES tipepemilik,
   tanggalakhir timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.pemilik_hewan_id_seq'::regclass),
   idpemilik bigint NOT NULL REFERENCES users,
   tanggalmulai timestamp without time zone NOT NULL DEFAULT now(),
   msgid bigint,
   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,
   idhewan bigint NOT NULL REFERENCES hewan
);

penolakan

Penolakan Pemotongan di RPH

Schema
data

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

permission_types

Schema
backoffice

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

perubahansampel

Tabel Referensi Untuk Perubahan Sampel Produk Hewan

Schema
reference

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

plr_modules

Schema
backoffice

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

plr_modules2

Schema
backoffice

CREATE TABLE plr_modules2 (
   modseq integer,
   modsrc text
);

population

Village/farm level animal population

Schema
data

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

postmortem

Pemeriksaan postmortem di RPH

Schema
data

CREATE TABLE postmortem (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.postmortem_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL,
   modifiedon timestamp without time zone,
   idorgan integer NOT NULL REFERENCES specimentypes,
   idam integer NOT NULL REFERENCES antemortem,
   reportdate 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,
   infrastructureid integer NOT NULL REFERENCES infrastructure,
   idkeputusanpm integer NOT NULL REFERENCES keputusanpm,
   idsigns integer[] NOT NULL,
   iddiagnosa integer NOT NULL REFERENCES diseases
);

preventivetreatments

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

Schema
data

CREATE TABLE preventivetreatments (
   drugid integer NOT NULL REFERENCES drugs,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.preventivetreatments_id_seq'::regclass),
   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,
   msgid bigint,
   idhewan integer NOT NULL REFERENCES hewan,
   dose numeric(8,3)
);

productionsystems

Production systems

Schema
reference

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

programafkir

Culling programs

Schema
data

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

programtindakan

Disease control programs

Schema
data

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

prov

Schema
backoffice

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

purposes

Purposes for vaccination

Schema
reference

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

questions

Questions sent by SMS

Schema
data

CREATE TABLE questions (
   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,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   question character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.questions_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false
);

queuestats

Snapshot of SMS outgoing queue

Schema
sms

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

rabiesreports

Schema
data

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

receipt

Schema
data

CREATE TABLE receipt (
   datereceived timestamp with time zone NOT NULL DEFAULT now(),
   infrastructureid integer NOT NULL REFERENCES infrastructure,
   msgid bigint NOT NULL,
   shipmentid integer NOT NULL REFERENCES shipments,
   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('data.receipt_id_seq'::regclass)
);

registrationmatrix

Permissions for different user types to register other users

Schema
reference

CREATE TABLE registrationmatrix (
   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,
   canregister integer NOT NULL REFERENCES groups,
   groupid integer NOT NULL REFERENCES groups,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.registrationmatrix_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false
);

reportcategories

Schema
report

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

reportlog

Schema
report

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

reportparams

Replaceable parameters for reports

Schema
report

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

reportparamtypes

Parameter types for reports

Schema
report

CREATE TABLE reportparamtypes (
   typename character varying UNIQUE NOT NULL,
   validationregex 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(),
   description character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('report.rptparamtypes_id_seq'::regclass)
);

reports

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

Schema
report

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

reports

Master report definition table

Schema
report

CREATE TABLE reports (
   naturalorder integer,
   bigreportcached boolean NOT NULL DEFAULT false,
   thumbnails text,
   category character varying,
   tag character varying,
   cache_validity integer NOT NULL DEFAULT 60,
   cacheable boolean NOT NULL DEFAULT true,
   widget_update_interval integer NOT NULL DEFAULT 1880,
   maptype report.maptype,
   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,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 0 REFERENCES users,
   rcode character varying,
   sql character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('report.reports_id_seq'::regclass),
   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
);

reportsubscriptions

Group and individual subscriptions to saved reports for automated periodic reporting

Schema
report

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

reporttags

Schema
report

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

reporttags

Schema
backoffice

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

response

Initial investigation of a disease report, including differential diagnosis

Schema
data

CREATE TABLE response (
   userid integer NOT NULL 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,
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   caseid integer NOT NULL REFERENCES cases,
   msgid bigint NOT NULL,
   userid integer NOT NULL REFERENCES users,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.response_id_seq'::regclass),
   createdon timestamp without time zone NOT NULL DEFAULT now()
);

rest_client

Schema
backoffice

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

results

Laboratory testing results

Schema
reference

CREATE TABLE results (
   del boolean NOT NULL DEFAULT false,
   name character varying[] NOT NULL,
   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 NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.results_id_seq'::regclass)
);

riwayatvaksinasi

Vaccination history from priority disease investigation

Schema
data

CREATE TABLE riwayatvaksinasi (
   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,
   tanggal date,
   diseaseid integer NOT NULL REFERENCES diseases,
   caseid integer NOT NULL REFERENCES cases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.riwayatvaksinasi_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false
);

savedreportparams

Parameters for saved reports

Schema
report

CREATE TABLE savedreportparams (
   svdrptid integer NOT NULL REFERENCES savedreports,
   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(),
   paramvalue character varying,
   rptparamid integer NOT NULL REFERENCES reportparams,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('report.saved_report_params_id_seq'::regclass)
);

savedreports

Parameterised versions of reports for automated periodic reporting

Schema
report

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

selfcheck

Schema
sms

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

servers

Schema
reference

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

sessions

Schema
backoffice

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

sex

Animal sex

Schema
reference

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

shipments

Schema
data

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

signreports

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

Schema
data

CREATE TABLE signreports (
   signsid integer[] NOT NULL,
   animalid integer REFERENCES hewan,
   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,
   cases integer NOT NULL,
   speciesid integer NOT NULL REFERENCES species,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.signreports_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   caseid integer NOT NULL REFERENCES cases
);

signs

Clinical signs

Schema
reference

CREATE TABLE signs (
   code character varying UNIQUE,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.signs_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,
   synname character varying[],
   specific boolean NOT NULL DEFAULT false,
   general boolean NOT NULL DEFAULT false,
   terminal boolean NOT NULL DEFAULT false,
   selectable boolean NOT NULL DEFAULT false,
   pelsa boolean NOT NULL DEFAULT false,
   synonym integer,
   level integer,
   description character varying[],
   name character varying[] NOT NULL,
   hiercode character varying UNIQUE NOT NULL
);

signs_asia

Schema
reference

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

signspecies

Signs valid for different species

Schema
reference

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

slaughtertotals

Daily abattoir slaughter totals by animal type

Schema
data

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

sms

Master table for SMS message definitions

Schema
metadata

CREATE TABLE sms (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   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,
   version integer NOT NULL DEFAULT 1,
   public boolean DEFAULT false,
   readonly boolean NOT NULL DEFAULT false,
   permission character varying,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer NOT NULL DEFAULT 1 REFERENCES users,
   helptext character varying[],
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   msgid_field character varying[] NOT NULL DEFAULT '{msgid}'::character varying[],
   reportdate_field character varying[] NOT NULL DEFAULT '{createdon}'::character varying[],
   groupid integer REFERENCES sms_groups,
   keyfield character varying[],
   userid_field character varying[] NOT NULL DEFAULT '{createdby}'::character varying[],
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.sms_id_seq'::regclass),
   name character varying[] NOT NULL,
   protected boolean NOT NULL DEFAULT false,
   tablename character varying[]
);

sms_field_types

Field types for SMS message definitions

Schema
reference

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

sms_fields

Field definitions for SMS messages

Schema
metadata

CREATE TABLE sms_fields (
   targettable integer,
   title character varying,
   help character varying[],
   list_sql character varying,
   name character varying[] NOT NULL,
   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,
   hidden boolean NOT NULL DEFAULT false,
   groupsequence integer NOT NULL DEFAULT 0,
   data_type integer NOT NULL,
   optional boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.sms_fields_id_seq'::regclass),
   smsid integer NOT NULL REFERENCES sms,
   natorder integer NOT NULL
);

sms_format

Schema
backoffice

CREATE TABLE sms_format (
   ?column? text
);

sms_groups

Schema
metadata

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

sms_operators

Telephone network operators

Schema
reference

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

sms_prefixes

Mobile phone prefixes

Schema
reference

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

smserrorchecks

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

Schema
data

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

species

Species and breeds

Schema
reference

CREATE TABLE species (
   selectable boolean NOT NULL DEFAULT false,
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.species_id_seq'::regclass),
   sikhnascode integer,
   lab boolean NOT NULL DEFAULT false,
   hiercode character varying UNIQUE NOT NULL,
   level integer NOT NULL,
   code character varying UNIQUE,
   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
);

specimenforms

The form of a laboratory specimen (perservative etc)

Schema
reference

CREATE TABLE specimenforms (
   hiercode character varying UNIQUE NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.specimenforms_id_seq'::regclass),
   code 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,
   name character varying[] NOT NULL
);

specimens

Specimens from animals in a laboratory submission

Schema
lab

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

specimentypes

The type of a laboratory specimen (tissue of origin)

Schema
reference

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

sql_features

Schema
information_schema

CREATE TABLE sql_features (
   feature_id information_schema.character_data,
   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
);

sql_implementation_info

Schema
information_schema

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

sql_languages

Schema
information_schema

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

sql_packages

Schema
information_schema

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

sql_parts

Schema
information_schema

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

sql_sizing

Schema
information_schema

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

sql_sizing_profiles

Schema
information_schema

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

statusasuransi

status asuransi

Schema
reference

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

statusrepro

Reproductive statuses

Schema
reference

CREATE TABLE statusrepro (
   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('reference.statusrepro_id_seq'::regclass)
);

stocktake

Schema
data

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

stokbibit

stokbibit hijauan

Schema
data

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

straws

Collection Centre

Schema
data

CREATE TABLE straws (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.straws_id_seq'::regclass),
   spesies integer NOT NULL DEFAULT 22 REFERENCES species,
   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,
   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
);

straws

Batch Number

Schema
data

CREATE TABLE straws (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.straws_id_seq'::regclass),
   idpembuatan character varying NOT NULL,
   idbib integer NOT NULL REFERENCES infrastructure,
   jumlah integer NOT NULL,
   idhewan bigint NOT NULL REFERENCES hewan,
   spesies integer NOT NULL DEFAULT 22 REFERENCES species,
   tanggalproduksi date NOT NULL,
   createdby integer NOT NULL DEFAULT 1 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(),
   idpejantan character varying NOT NULL
);

straws

Stud ID

Schema
data

CREATE TABLE straws (
   modifiedon timestamp without time zone,
   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('data.straws_id_seq'::regclass),
   idpejantan character varying NOT NULL,
   idpembuatan character varying NOT NULL,
   jumlah integer NOT NULL,
   idbib integer NOT NULL REFERENCES infrastructure,
   idhewan bigint NOT NULL REFERENCES hewan,
   spesies integer NOT NULL DEFAULT 22 REFERENCES species,
   del boolean NOT NULL DEFAULT false,
   tanggalproduksi date NOT NULL,
   modifiedby integer REFERENCES users
);

straws

List of semen straws for artificial insemination

Schema
data

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

straws

Date of production

Schema
data

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

strawshipment

Schema
data

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

submission_reasons

Schema
reference

CREATE TABLE submission_reasons (
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.submission_reasons_id_seq'::regclass),
   name character varying[] NOT NULL,
   code character varying UNIQUE NOT NULL
);

submissionreasons

Reason for laboratory submission

Schema
reference

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

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 (
   total integer NOT NULL,
   specimentypeid integer NOT NULL REFERENCES specimentypes,
   caseid integer NOT NULL REFERENCES cases,
   msgid bigint,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.submissions_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   sectionid integer REFERENCES labsections,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   labid integer NOT NULL REFERENCES infrastructure,
   specimenformid integer NOT NULL REFERENCES specimenforms
);

submittertypes

Classification of laboratory submitter types

Schema
reference

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

sumber

Reference list of possible sources of introduction of infection

Schema
reference

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

sumberanggaran

sumber anggaran

Schema
reference

CREATE TABLE sumberanggaran (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.sumberanggaran_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,
   name character varying[] NOT NULL,
   code character varying,
   modifiedon timestamp without time zone
);

sumberpenyakit

Suspected source of infection as determined during outbreak investigations

Schema
data

CREATE TABLE sumberpenyakit (
   sumberid integer NOT NULL REFERENCES sumber,
   caseid integer NOT NULL REFERENCES cases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.sumberpenyakit_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,
   locationid integer REFERENCES locations
);

surveillance

Both lab-based and field surveillance reports

Schema
data

CREATE TABLE surveillance (
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   msgid bigint NOT NULL,
   programid integer NOT NULL REFERENCES surveillanceprograms,
   neg integer,
   pos integer,
   labid 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,
   specimens integer NOT NULL,
   speciesid integer NOT NULL REFERENCES species,
   locationid integer NOT NULL REFERENCES locations,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.surveillance_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users
);

surveillanceprograms

List of surveillance programs

Schema
data

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

survpasar

Tabel untuk surveilans pasar kesmavet

Schema
data

CREATE TABLE survpasar (
   idproduk integer NOT NULL REFERENCES species,
   idinfrastruktur integer NOT NULL REFERENCES infrastructure,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.survpasar_id_seq'::regclass),
   msgid bigint,
   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,
   idtanda integer[] NOT NULL,
   neg integer NOT NULL DEFAULT 0,
   pos integer NOT NULL DEFAULT 0,
   idkit integer NOT NULL REFERENCES testtypes
);

syndromereports

Disease report for priority cases including a syndrome classification

Schema
data

CREATE TABLE syndromereports (
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   cases integer NOT NULL,
   speciesid integer NOT NULL REFERENCES species,
   caseid integer NOT NULL REFERENCES cases,
   syndromeid integer NOT NULL REFERENCES syndromes,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.syndromereports_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,
   msgid bigint NOT NULL
);

syndromes

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

Schema
reference

CREATE TABLE syndromes (
   targetdiseaseid integer,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.syndromes_id_seq'::regclass),
   code character varying(6) UNIQUE NOT NULL,
   name character varying[] NOT NULL,
   description 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,
   targetspeciesid integer[]
);

syndromes

The disease which the syndrome might reflect

Schema
reference

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

syndromes

Defined syndromes related to priority diseases

Schema
reference

CREATE TABLE syndromes (
   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,
   targetdiseaseid integer,
   description character varying[],
   targetspeciesid integer[],
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.syndromes_id_seq'::regclass),
   code character varying(6) UNIQUE NOT NULL
);

tablefields

Schema
metadata

CREATE TABLE tablefields (
   multiple 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(),
   hidden boolean NOT NULL DEFAULT false,
   mandatory boolean NOT NULL DEFAULT false,
   keyfld boolean NOT NULL DEFAULT false,
   typeid integer NOT NULL REFERENCES fieldtypes,
   description character varying,
   displayname character varying[] NOT NULL,
   fldname character varying NOT NULL,
   tableid integer NOT NULL REFERENCES datatables,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.tablefields_id_seq'::regclass)
);

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 (
   signsid integer[] NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.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
);

targets

Target for laboratory tests (organism etc)

Schema
reference

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

templates

HTML templates for reporting

Schema
report

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

temptesttypes

Schema
backoffice

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

testresults

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

Schema
lab

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

testresults

Results of laboratory testing

Schema
lab

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

tests

Tests performed on a specimen in a laboratory submission

Schema
lab

CREATE TABLE tests (
   specimenid integer NOT NULL REFERENCES specimens,
   del boolean NOT NULL DEFAULT false,
   msgid bigint,
   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,
   datetested timestamp without time zone NOT NULL,
   testtypeid integer NOT NULL REFERENCES testtypes,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('lab.tests_id_seq'::regclass)
);

testtypes

Master table of laboratory test types

Schema
reference

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

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

tindakanlain

Specific disease control activities undertaken under a program

Schema
data

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

tipeakhir

Disposal types for animals

Schema
reference

CREATE TABLE tipeakhir (
   modifiedon timestamp without time zone,
   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,
   live boolean DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.tipeakhir_id_seq'::regclass)
);

tipekejadian

Event types for individual animals

Schema
reference

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

tipepemilik

Schema
backoffice

CREATE TABLE tipepemilik (
   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,
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.tipepemilik_id_seq'::regclass)
);

tipepenanganan

Types of procedures for non-case animals

Schema
reference

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

trainingtypes

Classification of training course types

Schema
reference

CREATE TABLE trainingtypes (
   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('reference.trainingtypes_id_seq'::regclass)
);

translation

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

Schema
backoffice

CREATE TABLE translation (
   class integer,
   type boolean NOT NULL DEFAULT false,
   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,
   comment character varying,
   name character varying[] NOT NULL,
   code character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.translation_id_seq'::regclass)
);

translationclasses

Schema
reference

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

treatmentanimals

Animals treated associated with a case

Schema
data

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

treatments

Master table of treatments associated with a case

Schema
data

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

ujicepat

Field rapid test results

Schema
data

CREATE TABLE ujicepat (
   msgid bigint NOT NULL,
   neg integer NOT NULL,
   pos integer NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   testtypeid integer NOT NULL REFERENCES testtypes,
   speciesid integer NOT NULL REFERENCES species,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.ujicepat_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
);

uncertaintytypes

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

Schema
reference

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

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 (
   modifiedby integer,
   modifiedon timestamp without time zone,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   paramtype character varying NOT NULL,
   paramname character varying NOT NULL,
   urlinsertjobid integer NOT NULL REFERENCES urlinsertjobs,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.urlinsertfields_id_seq'::regclass)
);

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 (
   del boolean NOT NULL DEFAULT false,
   modifiedby integer,
   modifiedon timestamp without time zone,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   replysql character varying NOT NULL,
   insertsql character varying NOT NULL,
   name character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.urlinsertjobs_id_seq'::regclass)
);

user_hierarchy

Schema
backoffice

CREATE TABLE user_hierarchy (
   relation relationshiptype,
   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('backoffice.user_hierarchy_id_seq'::regclass),
   superiorid integer REFERENCES users,
   inferiorid integer REFERENCES users
);

user_permissions

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

Schema
backoffice

CREATE TABLE user_permissions (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.user_permissions_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,
   permission integer NOT NULL,
   permission_typeid integer NOT NULL REFERENCES permission_types,
   userid integer NOT NULL REFERENCES users
);

userreportslog

Schema
report

CREATE TABLE userreportslog (
   userid integer 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(),
   createdby integer NOT NULL DEFAULT 0 REFERENCES users,
   svdrptid integer[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('report.userreportslog_id_seq'::regclass)
);

users

Master users table

Schema
backoffice

CREATE TABLE users (
   password character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.users_id_seq'::regclass),
   firstname character varying NOT NULL,
   surname character varying,
   phone phonenumber,
   email character varying,
   userlanguage integer NOT NULL DEFAULT 1,
   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,
   modifiedby integer REFERENCES users,
   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 backoffice.create_pin()
);

users_test

Schema
backoffice

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

vaccination_individu

Schema
data

CREATE TABLE vaccination_individu (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   idhewan integer NOT NULL,
   programid integer NOT NULL REFERENCES vaccinationprograms,
   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('data.vaccination_individu_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users
);

vaccinationprograms

Vaccination programs

Schema
data

CREATE TABLE vaccinationprograms (
   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,
   doseskabupaten integer NOT NULL DEFAULT 0,
   dosesprovinsi integer NOT NULL DEFAULT 0,
   dosespusat integer NOT NULL DEFAULT 0,
   purpose integer NOT NULL DEFAULT 1,
   vaccineid integer[] NOT NULL,
   diseaseid integer[] NOT NULL,
   area integer[] NOT NULL,
   enddate date,
   startdate date NOT NULL DEFAULT now(),
   name character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.vaccinationprograms_id_seq'::regclass),
   diseaseeventid integer REFERENCES diseaseevents,
   modifiedon timestamp without time zone
);

vaccinations

Village/farm level vaccination events associated with a vaccination program

Schema
data

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

vachistory

Vaccination history of an animal in a laboratory submission

Schema
lab

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

valdata

Schema
backoffice

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

warna

Animal colour

Schema
reference

CREATE TABLE warna (
   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('reference.warna_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL REFERENCES users
);

weekdays

Days of the week

Schema
reference

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

yiisession

Session management for web system

Schema
backoffice

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

zataktif

Composition of drugs by regulation status

Schema
reference

CREATE TABLE zataktif (
   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('reference.zataktif_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users
);

zoonoses

Data on human involvement in suspected zoonotic disease cases

Schema
data

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