Database tables

Revisi per 25 Februari 2014 10.30 oleh Catriona (bicara | kontrib) (Created page with "{{#apGetSQL:select rec from ( select tablename, schema, format ('<h3>%s</h3> <P>%s</p> <dt>Schema</dt><dd>%s</dd> CREATE TABLE %s ( %s );',tablename, description, schema, ta...")
(beda) ← Revisi sebelumnya | Revisi terkini (beda) | Revisi selanjutnya → (beda)

Daftar isi

accesslog

Track web access

Schema
backoffice

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

adminlevels

Spatial administrative unit levels (province, district etc)

Schema
reference

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

afkir

Culling activities

Schema
data

CREATE TABLE afkir (
   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,
   msgid bigint NOT NULL,
   locationid integer NOT NULL REFERENCES locations,
   jumlah integer NOT NULL,
   speciesid integer NOT NULL REFERENCES species,
   programafkirid integer NOT NULL REFERENCES programafkir,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.afkir_id_seq'::regclass)
);

ages

Animal age categories

Schema
reference

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

ageunits

Units for age measurement

Schema
reference

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

aireportanimals

Schema
data

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

aireports

Schema
data

CREATE TABLE aireports (
   modifiedby integer REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   productionsystemid integer NOT NULL REFERENCES productionsystems,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   backyard boolean NOT NULL DEFAULT true,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   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(),
   modifiedon timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.aireports_id_seq'::regclass)
);

alasanpenolakan

alasan penolakan pemotongan

Schema
reference

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

animalid

Schema
backoffice

CREATE TABLE animalid (
   id integer
);

animals

List of animal that registered by Satgas PMK

Schema
data

CREATE TABLE animals (
   visitor_id integer NOT NULL REFERENCES farm_visits,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.animals_id_seq'::regclass),
   total integer NOT NULL,
   farmer_id integer NOT NULL REFERENCES farmers,
   status_id integer NOT NULL REFERENCES status_pmk,
   species_id integer NOT NULL REFERENCES species,
   signs integer[],
   del boolean NOT NULL DEFAULT false,
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   modifiedon timestamp without time zone,
   createdby integer NOT NULL REFERENCES users,
   modifiedby integer REFERENCES users
);

animals

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

Schema
lab

CREATE TABLE animals (
   animalident character varying NOT NULL,
   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,
   speciesid integer NOT NULL REFERENCES species,
   animalid bigint REFERENCES hewan,
   ownerid integer REFERENCES owners,
   labsubmissionid integer NOT NULL REFERENCES labsubmissions,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('lab.animals_id_seq'::regclass)
);

antemortem

Pemeriksaan antemortem di RPH

Schema
data

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

apifields

Schema
metadata

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

apioperations

Schema
reference

CREATE TABLE apioperations (
   createdby integer NOT NULL REFERENCES users,
   name character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('apioperations_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()
);

apiparamtypes

Schema
reference

CREATE TABLE apiparamtypes (
   name character varying NOT NULL,
   regex character varying NOT NULL,
   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
);

apis

Schema
metadata

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

apisql

Schema
metadata

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

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

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

app_reports

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

Schema
metadata

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

app_reports

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

Schema
metadata

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

app_static

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

Schema
metadata

CREATE TABLE app_static (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('metadata.app_static_id_seq'::regclass),
   version integer NOT NULL DEFAULT 1,
   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,
   name character varying[] NOT NULL,
   description character varying[] NOT NULL,
   sql character varying
);

apphelp

Schema
reference

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

asalbibit

Origin of seed for forage crops

Schema
reference

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

asuransihewan

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

Schema
data

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

audit_trail

Audit trail to log user model activity

Schema
backoffice

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

audit_trail_detail

Audit trail detail to log model activity

Schema
backoffice

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

auth_assignment

Schema
backoffice

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

auth_item

Schema
backoffice

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

auth_item_child

Schema
backoffice

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

batch

Schema
data

CREATE TABLE batch (
   populasi_awal integer NOT NULL,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   createdby integer NOT NULL REFERENCES users,
   populasi_akhir integer,
   tanggalakhir date,
   tanggalmulai date NOT NULL,
   idspecies integer NOT NULL REFERENCES species,
   idfarm integer NOT NULL REFERENCES farms,
   idbatch character varying(255) NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('batch_id_seq'::regclass)
);

bcs

Reference table for the BCS categories and the corresponding intervals

Schema
reference

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

bmindotempprov

Schema
backoffice

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

brewerpalettes

Colour definitions for charts

Schema
reference

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

brosur

Schema
backoffice

CREATE TABLE brosur (
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('brosur_id_seq'::regclass),
   title character varying(255) NOT NULL,
   cover_image character varying(255),
   document character varying(255),
   description text,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   del boolean NOT NULL DEFAULT false
);

businessrules

Business rules definining custom actions to be taken during data processing

Schema
backoffice

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

businessrulesold

Schema
backoffice

CREATE TABLE businessrulesold (
   caller 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,
   sql character varying,
   rulefunction character varying,
   ruleorder integer,
   hook hook,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.businessrules_id_seq'::regclass)
);

cadrereports

A dummy table for testing of the spreadsheet parsing

Schema
data

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

cadreresponses

A dummy table for testing of the spreadsheet parsing

Schema
data

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

case_animals

Individual animals in a disease case

Schema
data

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

caseimages

Images from a disease case

Schema
data

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

caseresults

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

Schema
data

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

caseresulttypes

Outcomes of clinical cases

Schema
reference

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

cases

Master table for all field disease cases

Schema
data

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

coldstorage

Schema
data

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

coldstorage_stock

Schema
data

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

compartement_negativereports

Schema
data

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

compartment_disease_cases

Schema
data

CREATE TABLE compartment_disease_cases (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('compartment_disease_cases_id_seq'::regclass),
   persentase integer,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   createdby integer NOT NULL REFERENCES users,
   cases integer NOT NULL,
   farmid integer NOT NULL REFERENCES farms,
   signsid integer[] NOT NULL,
   diagnosisid integer[] NOT NULL,
   diseaseid integer,
   msgid bigint NOT NULL,
   userid integer NOT NULL REFERENCES users,
   reportdate timestamp without time zone NOT NULL DEFAULT now()
);

containershipment

Schema
data

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

dashboard_report

Schema
backoffice

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

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 (
   order integer,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.dashboard_widget_id_seq'::regclass),
   userid integer NOT NULL REFERENCES users,
   reportid integer NOT NULL REFERENCES reports,
   widgettype character varying DEFAULT 'widget'::character varying,
   modifiedon timestamp with time zone,
   modifiedby integer,
   createdon timestamp with time zone NOT NULL DEFAULT now()
);

datatables

Schema
metadata

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

daterangetypes

Pre-defined date ranges for reporting

Schema
reference

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

deleted_vaccination_logs

Logs of deleted vaccination individu

Schema
data

CREATE TABLE deleted_vaccination_logs (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('deleted_vaccination_logs_id_seq'::regclass),
   deletedat timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   deletedby integer NOT NULL REFERENCES users,
   vaccination_id integer NOT NULL REFERENCES vaccination_individu,
   description character varying(255)
);

deskripsihewan

Description of an animal for individual animal identification

Schema
data

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

device_token_users

Schema
backoffice

CREATE TABLE device_token_users (
   createdby integer NOT NULL REFERENCES users,
   endpoint character varying(255) NOT NULL,
   token character varying(255) NOT NULL,
   userid integer NOT NULL REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('device_token_users_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()
);

diagnoses

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

Schema
data

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

disease_parent_species

Schema
reference

CREATE TABLE disease_parent_species (
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('disease_parent_species_id_seq'::regclass),
   disease_id integer NOT NULL REFERENCES diseases,
   parent_species_id integer NOT NULL REFERENCES parent_species,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users
);

disease_status

Schema
reference

CREATE TABLE disease_status (
   location_id integer NOT NULL REFERENCES locations,
   disease_id integer NOT NULL,
   disease_isikhnas_id integer NOT NULL REFERENCES diseases,
   is_special_condition boolean NOT NULL DEFAULT false,
   status character varying(10) NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('disease_status_id_seq'::regclass)
);

diseaseevents

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

Schema
data

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

diseases

Master list of diseases

Schema
reference

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

diseasesigns

Probabiilty matrix of the occurrence of different signs with different diseases

Schema
reference

CREATE TABLE diseasesigns (
   prob real NOT NULL DEFAULT 0.5,
   diseaseid integer NOT NULL REFERENCES diseases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.diseasesigns_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,
   signid integer NOT NULL REFERENCES signs
);

diseasespecies

Species susceptible to different diseases

Schema
reference

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

distribusistraw

Schema
data

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

drugclasses

Classification of drugs by regulation status

Schema
reference

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

drugs

Master table of registered and other drugs

Schema
data

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

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),
   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,
   type character varying,
   recipient character varying,
   unsubscribeurl character varying,
   signingcerturl character varying,
   signature character varying,
   signatureversion character varying,
   notificationtime timestamp with time zone,
   message jsonb,
   topicarn character varying,
   messageid character varying
);

errors

Log of SMS format errors

Schema
sms

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

estrus

Schema
data

CREATE TABLE estrus (
   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,
   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,
   animals integer NOT NULL DEFAULT 1
);

eventcase

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

Schema
data

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

exportreferencenamedranges

Definition of Excel named ranges for the reference table export system

Schema
reference

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

exportreferences

Master table for the reference table export system

Schema
reference

CREATE TABLE exportreferences (
   listsql character varying,
   replysql character varying,
   listname character varying[],
   filename 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 (
   modifiedby integer REFERENCES users,
   createdby integer NOT NULL REFERENCES users,
   sql character varying NOT NULL,
   exportreferenceid integer NOT NULL REFERENCES exportreferences,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   tabname character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.exportreferencetabs_id_seq'::regclass),
   sortorder integer NOT NULL DEFAULT 1,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone
);

farm_details

Schema
data

CREATE TABLE farm_details (
   jenis_usaha_id integer NOT NULL REFERENCES jenis_usaha,
   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('farm_details_id_seq'::regclass),
   farm_id integer NOT NULL REFERENCES farms
);

farm_hewan

Schema
data

CREATE TABLE farm_hewan (
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   createdby integer NOT NULL REFERENCES users,
   tanggalakhir date,
   tanggalmulai date NOT NULL,
   idhewan integer NOT NULL REFERENCES hewan,
   idfarm integer NOT NULL REFERENCES farms,
   del boolean NOT NULL DEFAULT false
);

farm_visits

Visited list identification by Satgas PMK

Schema
data

CREATE TABLE farm_visits (
   modifiedon timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('farm_visits_id_seq'::regclass),
   farmer_id integer NOT NULL REFERENCES farmers,
   visited_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   visited_by integer NOT NULL REFERENCES users,
   del boolean NOT NULL DEFAULT false,
   modifiedby integer REFERENCES users
);

farmers

List of farmers that registered by Satgas PMK

Schema
data

CREATE TABLE farmers (
   centroid public.geometry(Point,4326),
   modifiedby integer REFERENCES users,
   createdby integer NOT NULL REFERENCES users,
   modifiedon timestamp without time zone,
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   del boolean NOT NULL DEFAULT false,
   status_id integer NOT NULL REFERENCES status_pmk,
   address character varying(255),
   location_id integer NOT NULL REFERENCES locations,
   nik character varying(16) NOT NULL,
   phone phonenumber NOT NULL,
   name character varying(255) NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('farmers_id_seq'::regclass)
);

farms

Schema
data

CREATE TABLE farms (
   code character varying(255),
   idspecies integer NOT NULL REFERENCES species,
   name character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('farms_id_seq'::regclass),
   national_code character varying(255) NOT NULL,
   locationid integer REFERENCES locations,
   address text,
   geom public.geometry(Point,4326),
   lat double precision,
   lon double precision,
   farm_types farm_types NOT NULL,
   idpemilik integer NOT NULL REFERENCES users,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false
);

favoritereport

Schema
backoffice

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

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

findings

Laboratory findings

Schema
reference

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

form_def

Schema
backoffice

CREATE TABLE form_def (
   row_to_json json
);

fungsi

Animal function

Schema
reference

CREATE TABLE fungsi (
   speciesid integer REFERENCES species,
   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,
   name character varying[] NOT NULL,
   code 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 (
   gestationperiod integer NOT NULL,
   id integer NOT NULL,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp with time zone,
   modifiedby integer,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL,
   oestruscycle integer,
   variation integer NOT NULL,
   speciesid integer NOT NULL
);

group_can_views

Schema
backoffice

CREATE TABLE group_can_views (
   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,
   view_group_id integer NOT NULL REFERENCES groups,
   group_id integer NOT NULL REFERENCES groups,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('group_can_views_id_seq'::regclass)
);

group_permissions

Default permissions for a user group

Schema
backoffice

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

groups

User roles determining default permissions in the system

Schema
backoffice

CREATE TABLE groups (
   name character varying[] NOT NULL,
   admin_view boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   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 (
   image character varying[],
   status_kematian integer REFERENCES tipeakhir,
   tanggal_kematian date,
   status status_pemilik_hewan,
   national_code character varying(30),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.hewan_id_seq'::regclass),
   identifikasi character varying,
   indukjantan bigint REFERENCES hewan,
   indukbetina bigint REFERENCES hewan,
   idspesies integer REFERENCES species,
   idsex integer REFERENCES sex,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   idfungsi integer REFERENCES fungsi,
   tanggallahir date,
   msgid bigint NOT NULL
);

hijauan

Data table for forage crop production

Schema
data

CREATE TABLE hijauan (
   luas double precision NOT NULL,
   idjenishijauan integer NOT NULL REFERENCES jenishijauan,
   locationid integer NOT NULL REFERENCES locations,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.hijauan_id_seq'::regclass),
   tahunfasilitasi integer,
   idanggaran integer,
   bulanpanen double precision,
   jumlahbibit double precision,
   idjeniskebun 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,
   idasalbibit integer NOT NULL
);

importfields

Field definitions for Excel imports

Schema
metadata

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

importfieldtypes

Field types used for Excel imports

Schema
metadata

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

importjobs

Job definitions for Excel imports. May include multiple tables

Schema
metadata

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

importtables

Table definitions for Excel imports

Schema
metadata

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

inbox

Incoming SMS messsages

Schema
sms

CREATE TABLE inbox (
   modifiedon timestamp without time zone,
   receivedtime timestamp without time zone,
   senttime timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sms.inbox_id_seq'::regclass),
   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,
   message character varying,
   msgid bigint,
   userid integer,
   receiver character varying,
   sender character varying
);

infrastructure

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

Schema
reference

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

infrastructure_types

Classification of infrastructure

Schema
reference

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

investigationanimals

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

Schema
data

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

irs

Schema
backoffice

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

jenis_usaha

Schema
data

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

jenisbibit

Types of forage crops

Schema
reference

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

jenishijauan

Types of forage crops

Schema
reference

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

jeniskebun

Types of garden

Schema
reference

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

jeniskonsentrat

Types of for consentrat

Schema
reference

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

jeniskontainer

Types of containers

Schema
reference

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

kejadian

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

Schema
data

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

kejadiankelompok

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

Schema
data

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

keputusanam

keputusan pemeriksaan antemortem

Schema
reference

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

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

keterangan

Comments on a case

Schema
data

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

komentar

Comments on a case

Schema
data

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

kondisikontainer

condition of containers

Schema
reference

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

konsentrat

Data table for consentrat

Schema
data

CREATE TABLE konsentrat (
   idanggaran integer,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   idjeniskonsentrat integer NOT NULL REFERENCES jeniskonsentrat,
   jumlah double precision NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   infraid integer NOT NULL,
   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.konsentrat_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   msgid bigint NOT NULL
);

kursuspelatihan

Training courses

Schema
data

CREATE TABLE kursuspelatihan (
   trainingtype integer NOT NULL DEFAULT 1 REFERENCES trainingtypes,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.kursuspelatihan_id_seq'::regclass),
   name character varying[] NOT NULL,
   moduleid integer[],
   locationid integer NOT NULL,
   startdate date NOT NULL DEFAULT now(),
   enddate date,
   area integer[] NOT NULL,
   trainers integer[] NOT NULL,
   organiser integer NOT NULL DEFAULT 1 REFERENCES adminlevels,
   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
);

labconfirms

Schema
data

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

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

labtests

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

Schema
reference

CREATE TABLE labtests (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.labtests_id_seq'::regclass),
   ref character varying,
   accredited boolean NOT NULL DEFAULT false,
   quantunitsid integer REFERENCES testunits,
   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,
   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
);

languages

List of languages used in the system

Schema
backoffice

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

locationlevels

Schema
reference

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

locations

Master spatial data table with administrative boundaries

Schema
reference

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

logdelete

Schema
data

CREATE TABLE logdelete (
   key character varying(50),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.logdelete_id_seq'::regclass),
   scema character varying(100),
   table character varying(100),
   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 (
   accesslevel text NOT NULL,
   menuaction character varying,
   parent integer NOT NULL,
   displayorder integer NOT NULL,
   createdon timestamp with time zone NOT NULL DEFAULT now(),
   createdby integer,
   modifiedon timestamp with time zone,
   modifiedby integer,
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.menu_id_seq'::regclass),
   pagetitle character varying[] NOT NULL
);

menu2

Schema
backoffice

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

methods

Methods for laboratory tests

Schema
reference

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

modemlog

Log of modem status

Schema
sms

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

modemstatus

Snapshot of modem status

Schema
sms

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

modulpelatihan

Training modules

Schema
reference

CREATE TABLE modulpelatihan (
   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.modulpelatihan_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone
);

months

Calendar months

Schema
reference

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

movement

Animal movement reporting (health certificate) master table

Schema
data

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

movementanimals

List of species and number of animals for animal movement reporting

Schema
data

CREATE TABLE movementanimals (
   total integer 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,
   speciesid integer NOT NULL REFERENCES species,
   movementid integer NOT NULL REFERENCES movement,
   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.movementanimals_id_seq'::regclass)
);

multilayermaps

Schema
report

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

negativereports

Village-level negative disease reports

Schema
data

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

news

News stories

Schema
backoffice

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

nitrogenshipment

Schema
data

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

notifiables

Schema
data

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

obat_zataktif

tabel obat dan zat aktifnya

Schema
data

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

odttemplates

Templates for ODT reports

Schema
report

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

outbox

Outgoing SMS messages

Schema
sms

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

owners

Owners of animals in a laboratory submission

Schema
lab

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

parameters

Miscellaneous user defined system parameters

Schema
backoffice

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

parent_species

Schema
reference

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

partisipanpelatihan

Participants at a training course

Schema
data

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

password_reset_tokens

Schema
backoffice

CREATE TABLE password_reset_tokens (
   email character varying(255) PRIMARY KEY NOT NULL,
   created_at timestamp without time zone,
   token character varying(255)
);

pemilik_hewan

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

Schema
data

CREATE TABLE pemilik_hewan (
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   tanggalmulai timestamp without time zone NOT NULL DEFAULT now(),
   lon double precision,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   lat double precision,
   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,
   tanggalakhir timestamp without time zone,
   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)
);

pencucian_walet

pencucian walet

Schema
data

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

performance

Schema
backoffice

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

permission_types

Schema
backoffice

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

perubahansampel

Tabel Referensi Untuk Perubahan Sampel Produk Hewan

Schema
reference

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

plr_modules

Schema
backoffice

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

plr_modules2

Schema
backoffice

CREATE TABLE plr_modules2 (
   modsrc text,
   modseq integer
);

population

Village/farm level animal population

Schema
data

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

postmortem

Pemeriksaan postmortem di RPH

Schema
data

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

preventivetreatments

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

Schema
data

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

productionsystems

Production systems

Schema
reference

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

produksi_pencucian_walet

produksi pencucian walet

Schema
data

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

produksi_rumah_walet

produksi rumah walet

Schema
data

CREATE TABLE produksi_rumah_walet (
   rupiah double precision,
   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)
);

programafkir

Culling programs

Schema
data

CREATE TABLE programafkir (
   startdate date NOT NULL DEFAULT now(),
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL 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,
   name character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.programafkir_id_seq'::regclass)
);

programtindakan

Disease control programs

Schema
data

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

prov

Schema
backoffice

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

providers

Schema
backoffice

CREATE TABLE providers (
   modifiedon timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('providers_id_seq'::regclass),
   code character varying(20) NOT NULL,
   name character varying(255) NOT NULL,
   description text,
   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,
   config json
);

purposes

Purposes for vaccination

Schema
reference

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

questions

Questions sent by SMS

Schema
data

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

queuestats

Snapshot of SMS outgoing queue

Schema
sms

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

rabiesreports

Schema
data

CREATE TABLE rabiesreports (
   humansbitten integer NOT NULL,
   del boolean NOT NULL DEFAULT false,
   modifiedon timestamp without time zone,
   modifiedby integer REFERENCES users,
   provoked boolean NOT NULL DEFAULT false,
   locationid integer NOT NULL REFERENCES locations,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   animalsbitten integer NOT NULL,
   speciesid integer NOT NULL REFERENCES species,
   caseid integer 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.rabiesreports_id_seq'::regclass)
);

receipt

Schema
data

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

registrationmatrix

Permissions for different user types to register other users

Schema
reference

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

reportcategories

Schema
report

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

reporting_queue

Schema
data

CREATE TABLE reporting_queue (
   queueid integer NOT NULL REFERENCES reporting_queue_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 REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reporting_queue_id_seq'::regclass),
   daterange character varying[] NOT NULL,
   reportid integer NOT NULL
);

reporting_queue_users

Schema
backoffice

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

reportlog

Schema
report

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

reportparams

Replaceable parameters for reports

Schema
report

CREATE TABLE reportparams (
   lookupfld integer REFERENCES tablefields,
   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(),
   sortorder integer NOT NULL DEFAULT 1,
   dfltval character varying,
   dynamic boolean NOT NULL DEFAULT true,
   mandatory boolean NOT NULL DEFAULT false,
   checkvalue character varying,
   del boolean NOT NULL DEFAULT false,
   lookupqry character varying,
   lookuptyp integer REFERENCES fieldtypes,
   prompt character varying[] DEFAULT '{"",""}'::character varying[],
   paramname character varying NOT NULL,
   paramtype integer NOT NULL REFERENCES reportparamtypes,
   reportid integer NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('report.report_params_id_seq'::regclass)
);

reportparamtypes

Parameter types for reports

Schema
report

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

reports

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

Schema
report

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

reports

Master report definition table

Schema
report

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

reportsubscriptions

Group and individual subscriptions to saved reports for automated periodic reporting

Schema
report

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

reporttags

Schema
backoffice

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

reporttags

Schema
report

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

response

Initial investigation of a disease report, including differential diagnosis

Schema
data

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

rest_client

Schema
backoffice

CREATE TABLE rest_client (
   client_code character varying NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.rest_client_id_seq'::regclass),
   website character varying,
   email character varying NOT NULL,
   api_key character varying,
   ip_address 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,
   client_name character varying NOT NULL
);

rest_logs

Logs of rest api v1

Schema
backoffice

CREATE TABLE rest_logs (
   end_date date,
   start_date date,
   request_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   ip_address character varying(30),
   uri character varying(255),
   rest_action character varying(50) NOT NULL,
   client_id integer NOT NULL REFERENCES rest_client,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rest_logs_id_seq'::regclass),
   metadata json,
   params json,
   exec_time real
);

results

Laboratory testing results

Schema
reference

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

riwayatvaksinasi

Vaccination history from priority disease investigation

Schema
data

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

rphu

Schema
data

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

rumah_suplier_pencucian_walet

rumah_suplier_pencucian_walet

Schema
data

CREATE TABLE rumah_suplier_pencucian_walet (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rumah_suplier_pencucian_walet_id_seq'::regclass),
   infra_id integer NOT NULL,
   no_registrasi character varying NOT NULL,
   volume_kg double precision NOT NULL,
   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,
   del boolean NOT NULL DEFAULT false
);

rumah_walet

kemitraan

Schema
data

CREATE TABLE rumah_walet (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('rumah_walet_id_seq'::regclass),
   infra_id integer NOT NULL,
   nib character varying NOT NULL,
   pemilik character varying NOT NULL,
   nik character varying,
   address text,
   manager character varying,
   tipe_mitra_id integer NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   latitude double precision,
   longitude double precision,
   telp_manager character varying
);

sampling_individu

for individual sampling

Schema
data

CREATE TABLE sampling_individu (
   modifiedby integer REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sampling_individu_id_seq'::regclass),
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   programid integer NOT NULL REFERENCES surveillanceprograms,
   animalid integer 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
);

savedreportparams

Parameters for saved reports

Schema
report

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

savedreports

Parameterised versions of reports for automated periodic reporting

Schema
report

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

selfcheck

Schema
sms

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

sertifikat_kompartemen

Schema
data

CREATE TABLE sertifikat_kompartemen (
   farm_id integer NOT NULL REFERENCES farms,
   disease_id integer[] NOT NULL,
   certificate_number character varying(255),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('sertifikat_kompartemen_id_seq'::regclass),
   start_date date NOT NULL,
   end_date date,
   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
);

servers

Schema
reference

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

sessions

Schema
backoffice

CREATE TABLE sessions (
   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,
   userid integer
);

sex

Animal sex

Schema
reference

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

shipments

Schema
data

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

signreports

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

Schema
data

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

signs

Clinical signs

Schema
reference

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

signs_asia

Schema
reference

CREATE TABLE signs_asia (
   del boolean NOT NULL DEFAULT false,
   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,
   id integer NOT NULL,
   code character varying NOT NULL,
   sign character varying[] NOT NULL,
   description character varying[],
   valid_to date,
   modified_by integer NOT NULL DEFAULT 1,
   modified_on timestamp without time zone NOT NULL DEFAULT now(),
   synonym integer NOT NULL DEFAULT 1
);

signspecies

Signs valid for different species

Schema
reference

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

slaughtertotals

Daily abattoir slaughter totals by animal type

Schema
data

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

sms

Master table for SMS message definitions

Schema
metadata

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

sms_field_types

Field types for SMS message definitions

Schema
reference

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

sms_fields

Field definitions for SMS messages

Schema
metadata

CREATE TABLE sms_fields (
   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(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   targettable integer,
   hidden boolean NOT NULL DEFAULT false,
   title character varying,
   help character varying[],
   list_sql character varying
);

sms_format

Schema
backoffice

CREATE TABLE sms_format (
   ?column? text
);

sms_groups

Schema
metadata

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

sms_operators

Telephone network operators

Schema
reference

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

sms_prefixes

Mobile phone prefixes

Schema
reference

CREATE TABLE sms_prefixes (
   operatorid integer NOT NULL REFERENCES sms_operators,
   prefix integer,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('reference.sms_prefixes_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
);

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,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   action smscheckaction,
   contactdate timestamp without time zone NOT NULL,
   userid integer NOT NULL REFERENCES users,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.smserrorchecks_id_seq'::regclass),
   del boolean NOT NULL DEFAULT false,
   modifiedby integer,
   modifiedon timestamp without time zone
);

species

Species and breeds

Schema
reference

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

specimenforms

The form of a laboratory specimen (perservative etc)

Schema
reference

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

specimens

Specimens from animals in a laboratory submission

Schema
lab

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

specimentypes

The type of a laboratory specimen (tissue of origin)

Schema
reference

CREATE TABLE specimentypes (
   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,
   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 (
   feature_name information_schema.character_data,
   is_supported information_schema.yes_or_no,
   sub_feature_name information_schema.character_data,
   comments information_schema.character_data,
   is_verified_by information_schema.character_data,
   feature_id 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,
   comments information_schema.character_data,
   character_value information_schema.character_data,
   integer_value information_schema.cardinal_number,
   implementation_info_name information_schema.character_data
);

sql_languages

Schema
information_schema

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

sql_packages

Schema
information_schema

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

sql_sizing

Schema
information_schema

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

sql_sizing_profiles

Schema
information_schema

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

status_pmk

List of status PMK

Schema
reference

CREATE TABLE status_pmk (
   name character varying(20) NOT NULL,
   modifiedby integer REFERENCES users,
   createdby integer NOT NULL REFERENCES users,
   modifiedon timestamp without time zone,
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   del boolean NOT NULL DEFAULT false,
   question character varying(255),
   description character varying(255),
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('status_pmk_id_seq'::regclass)
);

statusasuransi

status asuransi

Schema
reference

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

statuskepemilikan

Schema
reference

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

statusrepro

Reproductive statuses

Schema
reference

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

stocktake

Schema
data

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

stokbibit

stokbibit hijauan

Schema
data

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

straws

Stud ID

Schema
data

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

straws

List of semen straws for artificial insemination

Schema
data

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

straws

Date of production

Schema
data

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

straws

Collection Centre

Schema
data

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

straws

Batch Number

Schema
data

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

strawshipment

Schema
data

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

submission_reasons

Schema
reference

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

submissions

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

Schema
data

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

submittertypes

Classification of laboratory submitter types

Schema
reference

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

sumber

Reference list of possible sources of introduction of infection

Schema
reference

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

sumberanggaran

sumber anggaran

Schema
reference

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

surveillance

Both lab-based and field surveillance reports

Schema
data

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

surveillanceprograms

List of surveillance programs

Schema
data

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

survpasar

Tabel untuk surveilans pasar kesmavet

Schema
data

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

syndromereports

Disease report for priority cases including a syndrome classification

Schema
data

CREATE TABLE syndromereports (
   caseid integer NOT NULL REFERENCES cases,
   syndromeid integer NOT NULL REFERENCES syndromes,
   speciesid integer NOT NULL REFERENCES species,
   cases integer NOT NULL,
   reportdate timestamp without time zone NOT NULL DEFAULT now(),
   userid integer NOT NULL REFERENCES users,
   msgid bigint NOT NULL,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   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('data.syndromereports_id_seq'::regclass)
);

syndromes

The disease which the syndrome might reflect

Schema
reference

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

syndromes

Defined syndromes related to priority diseases

Schema
reference

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

syndromes

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

Schema
reference

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

tablefields

Schema
metadata

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

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 (
   del boolean NOT NULL DEFAULT false,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.tandaklinis_id_seq'::regclass),
   caseid integer NOT NULL REFERENCES cases,
   signsid integer[] NOT NULL,
   msgid bigint NOT NULL,
   createdby integer NOT NULL REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone
);

targets

Target for laboratory tests (organism etc)

Schema
reference

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

templates

HTML templates for reporting

Schema
report

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

templates

Schema
backoffice

CREATE TABLE templates (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('templates_id_seq'::regclass),
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   createdby integer NOT NULL REFERENCES users,
   description text,
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone,
   del boolean NOT NULL DEFAULT false,
   provider_id integer NOT NULL REFERENCES providers,
   approved boolean NOT NULL DEFAULT false,
   template_format character varying(255) NOT NULL,
   template_id character varying(255) NOT NULL,
   code character varying(30) NOT NULL
);

temptesttypes

Schema
backoffice

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

testresults

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

Schema
lab

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

testresults

Results of laboratory testing

Schema
lab

CREATE TABLE testresults (
   jumlahpos integer,
   jumlahrusak integer,
   jumlahneg integer,
   msgid bigint,
   uncertaintytypeid integer REFERENCES uncertaintytypes,
   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,
   resultid integer REFERENCES results,
   findingquant double precision,
   findingqual integer REFERENCES findings,
   testid integer NOT NULL REFERENCES tests,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('lab.testresults_id_seq'::regclass)
);

tests

Tests performed on a specimen in a laboratory submission

Schema
lab

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

testtypes

Master table of laboratory test types

Schema
reference

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

testunits

Quantitative units for laboratory test findings

Schema
reference

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

tindakan

Reference table of disease control activity types

Schema
reference

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

tindakanlain

Specific disease control activities undertaken under a program

Schema
data

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

tipe_kemitraan

kemitraan

Schema
reference

CREATE TABLE tipe_kemitraan (
   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('tipe_kemitraan_id_seq'::regclass),
   name character varying NOT NULL,
   createdby integer NOT NULL REFERENCES users
);

tipeakhir

Disposal types for animals

Schema
reference

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

tipekejadian

Event types for individual animals

Schema
reference

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

tipepemilik

Schema
backoffice

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

tipepenanganan

Types of procedures for non-case animals

Schema
reference

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

trainingtypes

Classification of training course types

Schema
reference

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

translation

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

Schema
backoffice

CREATE TABLE translation (
   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,
   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,
   templateid integer REFERENCES templates
);

translationclasses

Schema
reference

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

treatmentanimals

Animals treated associated with a case

Schema
data

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

treatments

Master table of treatments associated with a case

Schema
data

CREATE TABLE treatments (
   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),
   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,
   caseid integer NOT NULL REFERENCES cases,
   msgid bigint NOT NULL
);

ujicepat

Field rapid test results

Schema
data

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

uncertaintytypes

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

Schema
reference

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

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

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

user_hierarchy

Schema
backoffice

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

user_infrastructures

Schema
backoffice

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

user_kompartemen

Schema
backoffice

CREATE TABLE user_kompartemen (
   user_id 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 REFERENCES users,
   farm_id integer NOT NULL REFERENCES farms,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('user_kompartemen_id_seq'::regclass)
);

user_permissions

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

Schema
backoffice

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

userreportslog

Schema
report

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

users

Master users table

Schema
backoffice

CREATE TABLE users (
   restclient_id integer REFERENCES rest_client,
   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),
   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,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('backoffice.users_id_seq'::regclass)
);

users_babinsa

List BABINSA/BABINKAMTIBNAS of INDONESIA

Schema
backoffice

CREATE TABLE users_babinsa (
   name character varying(255) NOT NULL,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('users_babinsa_id_seq'::regclass),
   nrp character varying(20),
   phone character varying(15),
   pangkat character varying(30),
   structure_name character varying(255),
   status character(10),
   address text,
   jabatan character varying(255),
   del boolean NOT NULL DEFAULT false,
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   modifiedon timestamp without time zone,
   createdby integer NOT NULL REFERENCES users,
   modifiedby integer REFERENCES users
);

users_otp

List OTP code by public register user

Schema
backoffice

CREATE TABLE users_otp (
   createdon timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
   verified boolean NOT NULL DEFAULT false,
   del boolean NOT NULL DEFAULT false,
   attempt_tries integer DEFAULT 0,
   expired_at integer DEFAULT 0,
   token text,
   modifiedon timestamp without time zone,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('users_otp_id_seq'::regclass),
   code character varying(4) NOT NULL,
   user_id integer NOT NULL REFERENCES users
);

users_test

Schema
backoffice

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

vaccination_individu

Schema
data

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

vaccination_targets

Schema
data

CREATE TABLE vaccination_targets (
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('vaccination_targets_id_seq'::regclass),
   location_id integer NOT NULL REFERENCES locations,
   disease_id integer NOT NULL REFERENCES diseases,
   total_target integer
);

vaccinationprograms

Vaccination programs

Schema
data

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

vaccinations

Village/farm level vaccination events associated with a vaccination program

Schema
data

CREATE TABLE vaccinations (
   del boolean NOT NULL DEFAULT false,
   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,
   msgid bigint NOT NULL,
   programid integer NOT NULL REFERENCES vaccinationprograms,
   locationid integer NOT NULL REFERENCES locations,
   speciesid integer NOT NULL REFERENCES species,
   firstdose integer NOT NULL DEFAULT 0,
   booster integer,
   createdby integer NOT NULL DEFAULT 1 REFERENCES users,
   createdon timestamp without time zone NOT NULL DEFAULT now(),
   modifiedby integer REFERENCES users,
   modifiedon timestamp without time zone
);

vachistory

Vaccination history of an animal in a laboratory submission

Schema
lab

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

valdata

Schema
backoffice

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

warna

Animal colour

Schema
reference

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

weekdays

Days of the week

Schema
reference

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

yii2session

Schema
backoffice

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

yiisession

Session management for web system

Schema
backoffice

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

zataktif

Composition of drugs by regulation status

Schema
reference

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

zoonoses

Data on human involvement in suspected zoonotic disease cases

Schema
data

CREATE TABLE zoonoses (
   createdby integer NOT NULL REFERENCES users,
   mati integer NOT NULL,
   terpapar integer NOT NULL,
   caseid integer NOT NULL REFERENCES cases,
   id integer PRIMARY KEY NOT NULL DEFAULT nextval('data.zoonoses_id_seq'::regclass),
   sakit integer 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(),
   msgid bigint NOT NULL
);