Database tables

Contents

accesslog

Track web access

Schema
backoffice

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

adminlevels

Spatial administrative unit levels (province, district etc)

Schema
reference

CREATE TABLE adminlevels (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.adminlevels_id_seq'::regclass),
   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(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone
);

afkir

Culling activities

Schema
data

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

ages

Animal age categories

Schema
reference

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

ageunits

Units for age measurement

Schema
reference

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

aireportanimals

Schema
data

CREATE TABLE aireportanimals (
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   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,
   totaldead integer NOT NULL,
   speciesid integer NOT NULL REFERENCES species,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.aireportanimals_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users,
   aireportsid integer NOT NULL REFERENCES aireports
);

aireports

Schema
data

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

alasanpenolakan

alasan penolakan pemotongan

Schema
reference

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

animal_types

Animal type classification for slaughter and population

Schema
reference

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

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 (
   msgid bigint,
   ageunitsid integer REFERENCES ageunits,
   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,
   age double precision,
   sexid integer REFERENCES sex,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('lab.animals_id_seq'::regclass),
   labsubmissionid integer NOT NULL REFERENCES labsubmissions,
   animalident character varying NOT NULL,
   ownerid integer REFERENCES owners,
   animalid bigint REFERENCES hewan,
   speciesid integer NOT NULL REFERENCES species
);

antemortem

Pemeriksaan antemortem di RPH

Schema
data

CREATE TABLE antemortem (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.antemortem_id_seq'::regclass),
   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,
   identifikasi character varying NOT NULL,
   idpemilik integer NOT NULL REFERENCES users,
   skkh text,
   reportdate timestamp without time zone NOT NULL DEFAULT now()
);

apifields

Schema
metadata

CREATE TABLE apifields (
   apiid integer NOT NULL REFERENCES apis,
   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,
   paramtypeid integer REFERENCES apiparamtypes,
   name character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('apifields_id_seq'::regclass)
);

apioperations

Schema
reference

CREATE TABLE apioperations (
   del boolean NOT NULL DEFAULT false,
   modifiedby integer REFERENCES users,
   createdon timestamp with 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('apioperations_id_seq'::regclass),
   modifiedon timestamp with time zone
);

apiparamtypes

Schema
reference

CREATE TABLE apiparamtypes (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('apiparamtypes_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,
   regex character varying NOT NULL,
   name character varying NOT NULL
);

apis

Schema
metadata

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

apisql

Schema
metadata

CREATE TABLE apisql (
   apiid integer NOT NULL REFERENCES apis,
   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,
   serverid integer NOT NULL REFERENCES servers,
   sql character varying NOT NULL,
   natorder integer NOT NULL DEFAULT 1,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('apisql_id_seq'::regclass)
);

app_downloads

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

Schema
metadata

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

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

app_reports

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

Schema
metadata

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

app_reports

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

Schema
metadata

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

app_static

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

Schema
metadata

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

apphelp

Schema
reference

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

asalbibit

Origin of seed for forage crops

Schema
reference

CREATE TABLE asalbibit (
   name character varying[] 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,
   id integer NOT NULL,
   code character varying
);

asuransihewan

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

Schema
data

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

audit_trail

Audit trail to log user model activity

Schema
backoffice

CREATE TABLE audit_trail (
   ip_address character varying(25),
   url_referer character varying(150),
   new_value character varying,
   action character varying(200),
   model character varying(100),
   field character varying(50),
   model_id character varying(255),
   user_name character varying(50),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('audit_trail_id_seq'::regclass),
   old_value character varying,
   user_id integer REFERENCES users,
   stamp timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   browser character varying(255)
);

audit_trail_detail

Audit trail detail to log model activity

Schema
backoffice

CREATE TABLE audit_trail_detail (
   model_id character varying(255),
   url_referer character varying(150),
   browser character varying(255),
   stamp timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   user_id integer REFERENCES users,
   audit_trail_id integer REFERENCES audit_trail,
   action character varying(255),
   model character varying(100),
   field character varying(255),
   user_name character varying(50),
   ip_address character varying(25),
   old_value character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('audit_trail_detail_id_seq'::regclass),
   new_value character varying
);

auth_assignment

Schema
backoffice

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

auth_item

Schema
backoffice

CREATE TABLE auth_item (
   type integer NOT NULL,
   name character varying(64) PRIMARY KEY NOT NULL,
   data text,
   bizrule text,
   description 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 (
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.bcs_id_seq'::regclass),
   code character varying NOT NULL,
   nilai 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
);

bmindotempprov

Schema
backoffice

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

brewerpalettes

Colour definitions for charts

Schema
reference

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

businessrules

Business rules definining custom actions to be taken during data processing

Schema
backoffice

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

businessrulesold

Schema
backoffice

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

cadrereports

A dummy table for testing of the spreadsheet parsing

Schema
data

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

cadreresponses

A dummy table for testing of the spreadsheet parsing

Schema
data

CREATE TABLE cadreresponses (
   modifiedby integer,
   modifiedon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL,
   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)
);

case_animals

Individual animals in a disease case

Schema
data

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

caseimages

Images from a disease case

Schema
data

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

caseresults

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

Schema
data

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

caseresulttypes

Outcomes of clinical cases

Schema
reference

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

cases

Master table for all field disease cases

Schema
data

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

coldstorage

Schema
data

CREATE TABLE coldstorage (
   operationalend_date date,
   statuskepememilikan integer NOT NULL,
   nkv character varying,
   idinfra integer NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('coldstorage_id_seq'::regclass),
   createdby integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   idrphu integer,
   coldstorage_ton double precision,
   chiller_ton double precision
);

coldstorage_stock

Schema
data

CREATE TABLE coldstorage_stock (
   idcs integer NOT NULL,
   producttypes integer,
   stockin double precision NOT NULL,
   stockout double precision NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   endstock double precision NOT NULL,
   createdby integer NOT NULL,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   msgid bigint NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('coldstorage_stock_id_seq'::regclass)
);

containershipment

Schema
data

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

dashboard_report

Schema
backoffice

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

dashboard_report_user

Schema
backoffice

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

dashboard_widget

Schema
backoffice

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

datatables

Schema
metadata

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

daterangetypes

Pre-defined date ranges for reporting

Schema
reference

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

deskripsihewan

Description of an animal for individual animal identification

Schema
data

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

diagnoses

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

Schema
data

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

disease_details

Schema
reference

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

diseaseevents

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

Schema
data

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

diseases

Master list of diseases

Schema
reference

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

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 (
   del boolean NOT NULL DEFAULT false,
   diseaseid integer NOT NULL REFERENCES diseases,
   speciesid integer NOT NULL REFERENCES species,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.diseasespecies_id_seq'::regclass)
);

distribusistraw

Schema
data

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

drugclasses

Classification of drugs by regulation status

Schema
reference

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

drugs

Master table of registered and other drugs

Schema
data

CREATE TABLE drugs (
   indication character varying,
   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),
   manufacturer character varying,
   licensee character varying,
   composition character varying,
   class integer REFERENCES drugclasses,
   packaging character varying,
   doseunits units,
   comments character varying,
   synonym integer,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   paravet boolean NOT NULL DEFAULT false
);

drugtypes

Classification of drugs by functional group

Schema
reference

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

ekspor_pencucian_walet

ekspor_pencucian_walet

Schema
data

CREATE TABLE ekspor_pencucian_walet (
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('ekspor_pencucian_walet_id_seq'::regclass),
   infra_id integer NOT NULL,
   negara_tujuan character varying NOT NULL,
   volume_kg double precision,
   rupiah double precision,
   tahun integer NOT NULL,
   triwulan 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
);

emailnotifications

Schema
backoffice

CREATE TABLE emailnotifications (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.emailnotifications_id_seq'::regclass),
   messageid character varying,
   topicarn character varying,
   message jsonb,
   signatureversion character varying,
   notificationtime timestamp with time zone,
   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,
   type character varying
);

errors

Log of SMS format errors

Schema
sms

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

estrus

Schema
data

CREATE TABLE estrus (
   animals integer NOT NULL DEFAULT 1,
   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(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.estrus_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false
);

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

exportreferencenamedranges

Definition of Excel named ranges for the reference table export system

Schema
reference

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

exportreferences

Master table for the reference table export system

Schema
reference

CREATE TABLE exportreferences (
   filename character varying,
   replysql character varying,
   listname character varying[],
   listsql 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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.exportreferences_id_seq'::regclass)
);

exportreferencetabs

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

Schema
reference

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

favoritereport

Schema
backoffice

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

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

findings

Laboratory findings

Schema
reference

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

form_def

Schema
backoffice

CREATE TABLE form_def (
   row_to_json json
);

fungsi

Animal function

Schema
reference

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

gestation

Reproductive parameters of key species for production module reports

Schema
reference

CREATE TABLE gestation (
   createdby integer NOT NULL,
   oestruscycle integer,
   variation integer NOT NULL,
   gestationperiod integer NOT NULL,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp with time zone,
   speciesid integer NOT NULL,
   id integer NOT NULL,
   modifiedby integer,
   createdon timestamp with time zone NOT NULL DEFAULT now()
);

group_permissions

Default permissions for a user group

Schema
backoffice

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

groups

User roles determining default permissions in the system

Schema
backoffice

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

hewan

Master table for individual animal identification

Schema
data

CREATE TABLE hewan (
   idfungsi integer REFERENCES fungsi,
   status_kematian integer REFERENCES tipeakhir,
   tanggal_kematian date,
   status status_pemilik_hewan,
   national_code character varying(30),
   image character varying[],
   msgid bigint NOT NULL,
   tanggallahir date,
   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,
   idsex integer REFERENCES sex,
   idspesies integer REFERENCES species,
   indukbetina bigint REFERENCES hewan,
   indukjantan bigint REFERENCES hewan,
   identifikasi character varying,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.hewan_id_seq'::regclass)
);

hijauan

Data table for forage crop production

Schema
data

CREATE TABLE hijauan (
   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(),
   locationid integer NOT NULL REFERENCES locations,
   idjenishijauan integer NOT NULL REFERENCES jenishijauan,
   luas double precision NOT NULL,
   idasalbibit integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.hijauan_id_seq'::regclass),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone
);

importfields

Field definitions for Excel imports

Schema
metadata

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

importfieldtypes

Field types used for Excel imports

Schema
metadata

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

importjobs

Job definitions for Excel imports. May include multiple tables

Schema
metadata

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

importtables

Table definitions for Excel imports

Schema
metadata

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

inbox

Incoming SMS messsages

Schema
sms

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

infrastructure

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

Schema
reference

CREATE TABLE infrastructure (
   area integer[],
   name character varying NOT NULL,
   infrastructure_typeid integer NOT NULL REFERENCES infrastructure_types,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.infrastructure_id_seq'::regclass),
   locationid integer NOT NULL REFERENCES locations,
   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,
   phone character varying,
   fax character varying,
   contact integer,
   infratype integer UNIQUE,
   postcode character varying,
   address character varying,
   code character varying UNIQUE,
   shortname character varying
);

infrastructure_types

Classification of infrastructure

Schema
reference

CREATE TABLE infrastructure_types (
   hiercode character varying UNIQUE NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.infrastructure_types_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 UNIQUE NOT NULL
);

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 (
   caseid integer NOT NULL REFERENCES cases,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.investigationanimals_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,
   resolved boolean,
   atrisk integer,
   slaughtered integer,
   dead integer,
   sick integer,
   speciesid integer NOT NULL REFERENCES species,
   msgid bigint NOT NULL
);

irs

Schema
backoffice

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

jenisbibit

Types of forage crops

Schema
reference

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

jenishijauan

Types of forage crops

Schema
reference

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

jeniskebun

Types of garden

Schema
reference

CREATE TABLE jeniskebun (
   code character varying,
   name character varying[] NOT NULL,
   id integer 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
);

jeniskonsentrat

Types of for consentrat

Schema
reference

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

jeniskontainer

Types of containers

Schema
reference

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

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,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   datakejadian character varying,
   idtipekejadian integer REFERENCES tipekejadian,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.kejadian_id_seq'::regclass),
   msgid bigint NOT NULL,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone
);

kejadiankelompok

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

Schema
data

CREATE TABLE kejadiankelompok (
   jumlahhewan integer NOT NULL,
   idklasifikasi integer,
   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,
   msgid bigint NOT NULL,
   nilai double precision,
   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
);

keputusanam

keputusan pemeriksaan antemortem

Schema
reference

CREATE TABLE keputusanam (
   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.keputusanam_id_seq'::regclass)
);

keputusanpm

keputusan pemeriksaan postmortem

Schema
reference

CREATE TABLE keputusanpm (
   modifiedby integer REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.keputusanpm_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(),
   del boolean NOT NULL DEFAULT false
);

keterangan

Comments on a case

Schema
data

CREATE TABLE keterangan (
   caseid integer NOT NULL REFERENCES cases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.keterangan_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,
   keterangan character varying
);

komentar

Comments on a case

Schema
data

CREATE TABLE komentar (
   komentar character varying,
   createdby integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.komentar_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   msgid bigint 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 (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.konsentrat_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   idanggaran integer,
   idjeniskonsentrat integer NOT NULL REFERENCES jeniskonsentrat,
   infraid integer NOT NULL,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL,
   jumlah double precision NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   modifiedby integer REFERENCES users
);

kursuspelatihan

Training courses

Schema
data

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

labconfirms

Schema
data

CREATE TABLE labconfirms (
   createdby integer NOT NULL DEFAULT 1,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('labconfirms_id_seq'::regclass),
   caseid integer NOT NULL,
   labid integer NOT NULL,
   epinumber character varying,
   testid integer,
   endtesting_date timestamp without time zone,
   receiveresult_date timestamp without time zone,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

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

labsubmissions

Laboratory submissions master table

Schema
lab

CREATE TABLE labsubmissions (
   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),
   submittername character varying,
   datereceived date,
   datesampled date,
   labid integer REFERENCES infrastructure,
   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,
   submitterphone landline,
   submitteraddress character varying,
   submitterid integer REFERENCES users,
   submittertype integer NOT NULL REFERENCES submittertypes,
   locationid integer REFERENCES locations
);

labtests

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

Schema
reference

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

languages

List of languages used in the system

Schema
backoffice

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

locationlevels

Schema
reference

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

locations

Master spatial data table with administrative boundaries

Schema
reference

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

logdelete

Schema
data

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

menu

Website main menu structure definition

Schema
backoffice

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

menu2

Schema
backoffice

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

methods

Methods for laboratory tests

Schema
reference

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

modemlog

Log of modem status

Schema
sms

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

modemstatus

Snapshot of modem status

Schema
sms

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

modulpelatihan

Training modules

Schema
reference

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

months

Calendar months

Schema
reference

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

movement

Animal movement reporting (health certificate) master table

Schema
data

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

movementanimals

List of species and number of animals for animal movement reporting

Schema
data

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

multilayermaps

Schema
report

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

negativereports

Village-level negative disease reports

Schema
data

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

news

News stories

Schema
backoffice

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

nitrogenshipment

Schema
data

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

notifiables

Schema
data

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

obat_zataktif

tabel obat dan zat aktifnya

Schema
data

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

odttemplates

Templates for ODT reports

Schema
report

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

outbox

Outgoing SMS messages

Schema
sms

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

owners

Owners of animals in a laboratory submission

Schema
lab

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

parameters

Miscellaneous user defined system parameters

Schema
backoffice

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

partisipanpelatihan

Participants at a training course

Schema
data

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

pemilik_hewan

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

Schema
data

CREATE TABLE pemilik_hewan (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.pemilik_hewan_id_seq'::regclass),
   idpemilik bigint NOT NULL REFERENCES users,
   idhewan bigint NOT NULL REFERENCES hewan,
   tanggalmulai timestamp without time zone NOT NULL DEFAULT now(),
   tanggalakhir timestamp without time zone,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   msgid bigint,
   idtipepemilik integer NOT NULL DEFAULT 1 REFERENCES tipepemilik,
   locationid integer REFERENCES locations,
   geom public.geometry(Point,3857),
   lat double precision,
   lon double precision
);

pencucian_walet

pencucian walet

Schema
data

CREATE TABLE pencucian_walet (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('pencucian_walet_id_seq'::regclass),
   infra_id integer NOT NULL,
   nkv character varying,
   nkv_level character varying,
   nib character varying NOT NULL,
   pemilik character varying NOT NULL,
   nik character varying,
   address text,
   manager character varying,
   telp_manager character varying,
   tipe_mitra_id integer NOT NULL,
   jumlah_pekerja 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,
   latitude double precision,
   longitude double precision
);

penolakan

Penolakan Pemotongan di RPH

Schema
data

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

performance

Schema
backoffice

CREATE TABLE performance (
   records integer,
   earliest date,
   id uuid NOT NULL DEFAULT gen_random_uuid(),
   latest date,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   datatable character varying(50)
);

permission_types

Schema
backoffice

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

perubahansampel

Tabel Referensi Untuk Perubahan Sampel Produk Hewan

Schema
reference

CREATE TABLE perubahansampel (
   code character varying,
   createdby integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   id integer NOT NULL,
   name character varying[] NOT NULL
);

plr_modules

Schema
backoffice

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

plr_modules2

Schema
backoffice

CREATE TABLE plr_modules2 (
   modseq integer,
   modsrc text
);

population

Village/farm level animal population

Schema
data

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

postmortem

Pemeriksaan postmortem di RPH

Schema
data

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

preventivetreatments

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

Schema
data

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

productionsystems

Production systems

Schema
reference

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

produksi_pencucian_walet

produksi pencucian walet

Schema
data

CREATE TABLE produksi_pencucian_walet (
   tahun integer NOT NULL,
   infra_id integer NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('produksi_pencucian_walet_id_seq'::regclass),
   produksi_kg double precision,
   vol_dn_kg double precision,
   rp_dn double precision,
   kuota_ln_kg double precision,
   kuota_dn double precision,
   jumlah_gua integer,
   triwulan 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,
   kapasitas_kg double precision,
   del boolean NOT NULL DEFAULT false,
   volume_gua_kg double precision
);

produksi_rumah_walet

produksi rumah walet

Schema
data

CREATE TABLE produksi_rumah_walet (
   siklus_produksi integer,
   msgid bigint NOT NULL,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   triwulan integer NOT NULL,
   tahun integer NOT NULL,
   produksi_kg double precision NOT NULL,
   kapasitas_kg double precision NOT NULL,
   infra_id integer NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('produksi_rumah_walet_id_seq'::regclass),
   rupiah double precision
);

programafkir

Culling programs

Schema
data

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

programtindakan

Disease control programs

Schema
data

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

prov

Schema
backoffice

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

purposes

Purposes for vaccination

Schema
reference

CREATE TABLE purposes (
   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 UNIQUE NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.purposes_id_seq'::regclass),
   type purposetypes
);

questions

Questions sent by SMS

Schema
data

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

queuestats

Snapshot of SMS outgoing queue

Schema
sms

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

rabiesreports

Schema
data

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

receipt

Schema
data

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

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

reportcategories

Schema
report

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

reportlog

Schema
report

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

reportparams

Replaceable parameters for reports

Schema
report

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

reportparamtypes

Parameter types for reports

Schema
report

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

reports

Master report definition table

Schema
report

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

reportsubscriptions

Group and individual subscriptions to saved reports for automated periodic reporting

Schema
report

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

reporttags

Schema
backoffice

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

response

Initial investigation of a disease report, including differential diagnosis

Schema
data

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

rest_client

Schema
backoffice

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

results

Laboratory testing results

Schema
reference

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

riwayatvaksinasi

Vaccination history from priority disease investigation

Schema
data

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

rphu

Schema
data

CREATE TABLE rphu (
   operationalend_date date,
   hourcapacity double precision,
   operationhourperday double precision,
   blastfreezer_tonperhour double precision,
   chiller_ton double precision,
   livekgweight double precision,
   carcasskgweight double precision,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL,
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   operationdayperweek double precision,
   idcoldstorage integer,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rphu_id_seq'::regclass),
   idinfra integer NOT NULL,
   nkv character varying,
   statuskepemilikan integer NOT NULL
);

rumah_suplier_pencucian_walet

rumah_suplier_pencucian_walet

Schema
data

CREATE TABLE rumah_suplier_pencucian_walet (
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   triwulan integer NOT NULL,
   tahun integer NOT NULL,
   volume_kg double precision NOT NULL,
   no_registrasi character varying NOT NULL,
   infra_id integer NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rumah_suplier_pencucian_walet_id_seq'::regclass)
);

rumah_walet

kemitraan

Schema
data

CREATE TABLE rumah_walet (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rumah_walet_id_seq'::regclass),
   telp_manager character varying,
   longitude double precision,
   latitude double precision,
   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,
   tipe_mitra_id integer NOT NULL,
   manager character varying,
   address text,
   nik character varying,
   pemilik character varying NOT NULL,
   nib character varying NOT NULL,
   infra_id integer NOT NULL
);

savedreportparams

Parameters for saved reports

Schema
report

CREATE TABLE savedreportparams (
   svdrptid integer NOT NULL REFERENCES savedreports,
   del boolean NOT NULL DEFAULT false,
   modifiedby integer 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 (
   reportid integer NOT NULL REFERENCES reports,
   name character varying[] DEFAULT '{"",""}'::character varying[],
   public boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   modifiedby integer DEFAULT 1,
   createdby integer NOT NULL DEFAULT 1,
   modifiedon timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('report.saved_reports_id_seq'::regclass),
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   delivery report.reportdelivery,
   frequency report.reportfrequency,
   active boolean NOT NULL DEFAULT true
);

selfcheck

Schema
sms

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

servers

Schema
reference

CREATE TABLE servers (
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('servers_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
);

sessions

Schema
backoffice

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

sex

Animal sex

Schema
reference

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

shipments

Schema
data

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

signreports

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

Schema
data

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

signs

Clinical signs

Schema
reference

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

signs_asia

Schema
reference

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

signspecies

Signs valid for different species

Schema
reference

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

slaughtertotals

Daily abattoir slaughter totals by animal type

Schema
data

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

sms

Master table for SMS message definitions

Schema
metadata

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

sms_field_types

Field types for SMS message definitions

Schema
reference

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

sms_fields

Field definitions for SMS messages

Schema
metadata

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

sms_format

Schema
backoffice

CREATE TABLE sms_format (
   ?column? text
);

sms_groups

Schema
metadata

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

sms_operators

Telephone network operators

Schema
reference

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

sms_prefixes

Mobile phone prefixes

Schema
reference

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

smserrorchecks

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

Schema
data

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

species

Species and breeds

Schema
reference

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

specimenforms

The form of a laboratory specimen (perservative etc)

Schema
reference

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

specimens

Specimens from animals in a laboratory submission

Schema
lab

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

specimentypes

The type of a laboratory specimen (tissue of origin)

Schema
reference

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

sql_features

Schema
information_schema

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

sql_implementation_info

Schema
information_schema

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

sql_languages

Schema
information_schema

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

sql_packages

Schema
information_schema

CREATE TABLE sql_packages (
   feature_name information_schema.character_data,
   is_supported information_schema.yes_or_no,
   is_verified_by information_schema.character_data,
   feature_id information_schema.character_data,
   comments 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,
   is_verified_by information_schema.character_data,
   feature_id information_schema.character_data,
   comments information_schema.character_data
);

sql_sizing

Schema
information_schema

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

sql_sizing_profiles

Schema
information_schema

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

statusasuransi

status asuransi

Schema
reference

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

statuskepemilikan

Schema
reference

CREATE TABLE statuskepemilikan (
   id integer NOT NULL,
   name character varying NOT NULL,
   createdby integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

statusrepro

Reproductive statuses

Schema
reference

CREATE TABLE statusrepro (
   id integer NOT NULL,
   name character varying[] NOT NULL,
   createdby integer NOT NULL,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

stocktake

Schema
data

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

stokbibit

stokbibit hijauan

Schema
data

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

straws

Batch Number

Schema
data

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

straws

Stud ID

Schema
data

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

straws

List of semen straws for artificial insemination

Schema
data

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

straws

Date of production

Schema
data

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

straws

Collection Centre

Schema
data

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

strawshipment

Schema
data

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

submission_reasons

Schema
reference

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

submissionreasons

Reason for laboratory submission

Schema
reference

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

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

submittertypes

Classification of laboratory submitter types

Schema
reference

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

sumber

Reference list of possible sources of introduction of infection

Schema
reference

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

sumberanggaran

sumber anggaran

Schema
reference

CREATE TABLE sumberanggaran (
   id integer 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,
   code character varying,
   name character varying[] NOT NULL
);

sumberpenyakit

Suspected source of infection as determined during outbreak investigations

Schema
data

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

surveillance

Both lab-based and field surveillance reports

Schema
data

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

surveillanceprograms

List of surveillance programs

Schema
data

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

survpasar

Tabel untuk surveilans pasar kesmavet

Schema
data

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

syndromereports

Disease report for priority cases including a syndrome classification

Schema
data

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

syndromes

Defined syndromes related to priority diseases

Schema
reference

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

syndromes

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

Schema
reference

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

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

tablefields

Schema
metadata

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

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

targets

Target for laboratory tests (organism etc)

Schema
reference

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

templates

HTML templates for reporting

Schema
report

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

temptesttypes

Schema
backoffice

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

testresults

Results of laboratory testing

Schema
lab

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

testresults

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

Schema
lab

CREATE TABLE testresults (
   resultid integer REFERENCES results,
   findingquant double precision,
   findingquant double precision,
   findingquant double precision,
   findingqual integer REFERENCES findings,
   findingqual integer REFERENCES findings,
   findingqual integer REFERENCES findings,
   testid integer NOT NULL REFERENCES tests,
   testid integer NOT NULL REFERENCES tests,
   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),
   modifiedby integer REFERENCES users,
   modifiedby integer REFERENCES users,
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   modifiedon timestamp without time zone,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   uncertaintytypeid integer REFERENCES uncertaintytypes,
   uncertaintytypeid integer REFERENCES uncertaintytypes,
   uncertaintytypeid integer REFERENCES uncertaintytypes,
   jumlahrusak integer,
   jumlahrusak integer,
   jumlahrusak integer,
   jumlahneg integer,
   jumlahneg integer,
   jumlahneg integer,
   jumlahpos integer,
   jumlahpos integer,
   jumlahpos integer,
   msgid bigint,
   msgid bigint,
   msgid bigint,
   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(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   resultid integer REFERENCES results,
   resultid integer REFERENCES results
);

tests

Tests performed on a specimen in a laboratory submission

Schema
lab

CREATE TABLE tests (
   modifiedon timestamp without time zone,
   msgid bigint,
   del boolean NOT NULL DEFAULT false,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   datetested timestamp without time zone NOT NULL,
   testtypeid integer NOT NULL REFERENCES testtypes,
   specimenid integer NOT NULL REFERENCES specimens,
   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 (
   name character varying[] NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.tests_id_seq'::regclass),
   code character varying UNIQUE,
   targetid integer NOT NULL REFERENCES targets,
   methodid integer NOT NULL REFERENCES methods,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   rapid boolean NOT NULL DEFAULT false
);

testunits

Quantitative units for laboratory test findings

Schema
reference

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

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 (
   locationid integer NOT NULL REFERENCES locations,
   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,
   jumlah integer NOT NULL,
   tindakanid integer NOT NULL REFERENCES tindakan,
   programtindakanid integer NOT NULL REFERENCES programtindakan,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.tindakanlain_id_seq'::regclass)
);

tipe_kemitraan

kemitraan

Schema
reference

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

tipeakhir

Disposal types for animals

Schema
reference

CREATE TABLE tipeakhir (
   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('tipeakhir_id_seq'::regclass),
   code character varying NOT NULL,
   name character varying[] NOT NULL,
   live boolean DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone
);

tipekejadian

Event types for individual animals

Schema
reference

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

tipepemilik

Schema
backoffice

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

tipepenanganan

Types of procedures for non-case animals

Schema
reference

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

trainingtypes

Classification of training course types

Schema
reference

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

translation

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

Schema
backoffice

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

translationclasses

Schema
reference

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

treatmentanimals

Animals treated associated with a case

Schema
data

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

treatments

Master table of treatments associated with a case

Schema
data

CREATE TABLE treatments (
   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 DEFAULT 1 REFERENCES users,
   caseid integer NOT NULL REFERENCES cases,
   msgid bigint NOT NULL,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.treatments_id_seq'::regclass)
);

ujicepat

Field rapid test results

Schema
data

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

uncertaintytypes

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

Schema
reference

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

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 (
   paramname character varying NOT NULL,
   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,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.urlinsertfields_id_seq'::regclass),
   urlinsertjobid integer NOT NULL REFERENCES urlinsertjobs,
   modifiedby integer
);

urlinsertjobs

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

Schema
metadata

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

user_hierarchy

Schema
backoffice

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

user_infrastructures

Schema
backoffice

CREATE TABLE user_infrastructures (
   infra_id integer NOT NULL REFERENCES infrastructure,
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   modifiedby integer,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   createdby integer NOT NULL,
   user_id integer UNIQUE NOT NULL,
   user_id integer NOT NULL REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('user_infrastructures_id_seq'::regclass),
   infra_id integer UNIQUE NOT NULL
);

user_permissions

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

Schema
backoffice

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

userreportslog

Schema
report

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

users

Master users table

Schema
backoffice

CREATE TABLE users (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.users_id_seq'::regclass),
   national_code character varying(255),
   address text,
   email_confirm_uuid uuid DEFAULT gen_random_uuid(),
   email_confirmed timestamp with time zone,
   nik character varying(16),
   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(),
   password character varying,
   userlanguage integer NOT NULL DEFAULT 1,
   email character varying,
   phone phonenumber,
   surname character varying,
   firstname character varying NOT NULL
);

users_test

Schema
backoffice

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

vaccination_individu

Schema
data

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

vaccinationprograms

Vaccination programs

Schema
data

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

vaccinations

Village/farm level vaccination events associated with a vaccination program

Schema
data

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

vachistory

Vaccination history of an animal in a laboratory submission

Schema
lab

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

valdata

Schema
backoffice

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

warna

Animal colour

Schema
reference

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

weekdays

Days of the week

Schema
reference

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

yii2session

Schema
backoffice

CREATE TABLE yii2session (
   id character varying NOT NULL,
   data text,
   expire integer
);

yiisession

Session management for web system

Schema
backoffice

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

zataktif

Composition of drugs by regulation status

Schema
reference

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

zoonoses

Data on human involvement in suspected zoonotic disease cases

Schema
data

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