test

Tables (31)

alarm_records

  • message_id — uuid — Primary key — NOT NULL
  • device_id — text — NOT NULL
  • time — timestamptz — NOT NULL
  • site_id — text — NOT NULL
  • alarm_type — text — NOT NULL
  • alarm_code — text
  • severity — text
  • dedup_key — text
  • details — jsonb
  • raw — jsonb
  • received_at — timestamptz — NOT NULL

collect_run

  • id — serial4 — Primary key — NOT NULL
  • schedule_id — int4 — Foreign key → collect_schedule.id
  • source_code — text — NOT NULL
  • trigger — text — NOT NULL
  • started_at — timestamptz — NOT NULL
  • finished_at — timestamptz
  • status — text — NOT NULL
  • rows_fetched — int4 — NOT NULL
  • rows_upserted — int4 — NOT NULL
  • rows_rejected — int4 — NOT NULL
  • message — text
  • log — text
  • actor — text
  • rows_inserted — int4 — NOT NULL
  • rows_updated — int4 — NOT NULL

collect_schedule

  • id — serial4 — Primary key — NOT NULL
  • source_code — text — Foreign key → collect_source.code — NOT NULL
  • name — text — NOT NULL
  • cron — text — NOT NULL
  • enabled — bool — NOT NULL
  • params — jsonb — NOT NULL
  • last_run_at — timestamptz
  • last_status — text
  • last_rows — int4
  • next_run_at — timestamptz
  • created_by — text
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

collect_source

  • code — text — Primary key — NOT NULL
  • name — text — NOT NULL
  • category — text
  • provided_by — text
  • provider — text
  • url — text
  • auth_env — text
  • cadence — text
  • temporal — text
  • pathogens — _text
  • target_table — text
  • description — text
  • enabled — bool — NOT NULL
  • updated_at — timestamptz — NOT NULL

dead_letter_queue

  • id — bigserial — Primary key — NOT NULL
  • received_at — timestamptz — NOT NULL
  • topic — text — NOT NULL
  • raw_payload — jsonb — NOT NULL
  • message_id — text
  • device_id — text
  • version — text
  • reject_reason — text — NOT NULL

device_registry

  • device_id — text — Primary key — NOT NULL
  • site_id — text — NOT NULL
  • site_name — text
  • latitude — float8
  • longitude — float8
  • sido — text
  • firmware_version — text
  • registered_at — timestamptz — NOT NULL
  • active — bool — NOT NULL
  • meta — jsonb

environment_measurements

  • message_id — uuid — Primary key — NOT NULL
  • measurement_id — text — Primary key — NOT NULL
  • device_id — text — NOT NULL
  • time — timestamptz — NOT NULL
  • site_id — text — NOT NULL
  • sample_id — text
  • period_start — timestamptz
  • period_end — timestamptz
  • variable_key — text — NOT NULL
  • value — float8
  • unit — text
  • analysis_method — text
  • loq_flag — bool
  • loq_value — float8
  • raw — jsonb
  • received_at — timestamptz — NOT NULL

lifecycle_records

  • message_id — uuid — Primary key — NOT NULL
  • device_id — text — NOT NULL
  • time — timestamptz — NOT NULL
  • site_id — text — NOT NULL
  • event — text — NOT NULL
  • raw — jsonb
  • received_at — timestamptz — NOT NULL

obs_clinical

  • pathogen — text — Primary key — NOT NULL
  • region_code — text — Primary key — NOT NULL
  • iso_year — int4 — Primary key — NOT NULL
  • iso_week — int4 — Primary key — NOT NULL
  • metric — text — Primary key — NOT NULL
  • care_level — text — Primary key — NOT NULL
  • value — float8
  • unit — text
  • surveil_kind — text
  • agg_basis — text
  • disease_group — text
  • temporal — text — NOT NULL
  • agg_method — text
  • source_code — text
  • source_doc — text
  • source_url — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL

obs_conc_us_state

  • state_code — text — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • iso_year — int4 — Primary key — NOT NULL
  • iso_week — int4 — Primary key — NOT NULL
  • conc — float8
  • n_samples — int4
  • n_sites — int4
  • n_days — int4
  • source_code — text
  • ingested_at — timestamptz — NOT NULL

obs_population

  • region_code — text — Primary key — NOT NULL
  • ym — text — Primary key — NOT NULL
  • region_name — text
  • level — text
  • total — int8
  • male — int8
  • female — int8
  • temporal — text — NOT NULL
  • agg_method — text
  • source_code — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL
  • parent_code — text

obs_wastewater_curated

  • site_key — text — Primary key — NOT NULL
  • sample_date — date — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • gene_target — text — Primary key — NOT NULL
  • acq_route — text — Primary key — NOT NULL
  • ruleset_id — int4 — Foreign key → transform_ruleset.id — NOT NULL
  • source_dataset — text — NOT NULL
  • value_raw — float8
  • value_clean — float8
  • value_norm — float8
  • unit_raw — text
  • unit_norm — text
  • loq_flag — bool — NOT NULL
  • loq_imputed — bool — NOT NULL
  • gap_imputed — bool — NOT NULL
  • is_outlier — bool — NOT NULL
  • outlier_reason — text
  • qc — jsonb
  • run_id — int8
  • created_at — timestamptz — NOT NULL

obs_wastewater_kowas

  • site_key — text — Primary key — NOT NULL
  • sample_date — date — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • gene_target — text — Primary key — NOT NULL
  • acq_route — text — Primary key — NOT NULL
  • conc — float8
  • unit — text
  • detect_flag — text
  • lod — float8
  • norm_target — text
  • norm_conc — float8
  • flow_rate — float8
  • site_name — text
  • agency — text
  • region_code — text
  • source_file — text
  • temporal — text — NOT NULL
  • agg_method — text
  • source_code — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL
  • device_id — text
  • qc_status — text

obs_wastewater_nwss

  • site_key — text — Primary key — NOT NULL
  • sample_date — date — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • gene_target — text — Primary key — NOT NULL
  • record_key — text — Primary key — NOT NULL
  • conc — float8
  • unit — text
  • detect_flag — text
  • lod — float8
  • norm_target — text
  • norm_conc — float8
  • flow_rate — float8
  • population_served — int4
  • site_name — text
  • region_code — text
  • temporal — text — NOT NULL
  • agg_method — text
  • source_code — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL

obs_weather

  • station_id — text — Primary key — NOT NULL
  • obs_date — date — Primary key — NOT NULL
  • station_name — text
  • sido_code — text
  • ta_avg — float8
  • ta_min — float8
  • ta_max — float8
  • rn_day — float8
  • rh_avg — float8
  • ws_avg — float8
  • temporal — text — NOT NULL
  • agg_method — text
  • source_code — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL

obs_wval_nwss

  • site_key — text — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • week_end — date — Primary key — NOT NULL
  • iso_year — int4
  • iso_week — int4
  • wval — float8
  • level_group — int4
  • level_label — text
  • state_code — text
  • counties — text
  • population_served — int4
  • source_kind — text
  • temporal — text
  • source_code — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL

pathogen_measurements

  • message_id — uuid — Primary key — NOT NULL
  • measurement_id — text — Primary key — NOT NULL
  • device_id — text — NOT NULL
  • time — timestamptz — NOT NULL
  • site_id — text — NOT NULL
  • sample_id — text
  • collection_start — timestamptz
  • collection_end — timestamptz
  • pathogen — text — NOT NULL
  • value — float8
  • loq_flag — bool
  • loq_value — float8
  • unit — text
  • sample_type — text
  • analysis_method — text
  • gene_target — text
  • qc_overall_status — text
  • qc_note — text
  • raw — jsonb
  • received_at — timestamptz — NOT NULL

raw_document

  • source_code — text — Primary key — NOT NULL
  • doc_no — text — Primary key — NOT NULL
  • title — text
  • published_at — date
  • url — text
  • body — text
  • meta — jsonb
  • fetched_at — timestamptz — NOT NULL
  • content — bytea
  • content_type — text
  • byte_size — int4
  • sha256 — text

southbound_command_log

  • id — bigserial — Primary key — NOT NULL
  • command_id — uuid — NOT NULL
  • device_id — text — NOT NULL
  • command_type — text — NOT NULL
  • params — jsonb — NOT NULL
  • issued_by — text — NOT NULL
  • issued_at — timestamptz — NOT NULL
  • jwt_exp — timestamptz

status_records

  • message_id — uuid — Primary key — NOT NULL
  • device_id — text — NOT NULL
  • time — timestamptz — NOT NULL
  • site_id — text — NOT NULL
  • status — text
  • uptime_seconds — int8
  • firmware_version — text
  • network_type — text
  • sensor_health — jsonb
  • storage_usage_percent — int4
  • pending_messages — int4
  • battery_voltage — float8
  • signal_strength_dbm — int4
  • raw — jsonb
  • received_at — timestamptz — NOT NULL

stp

  • id — serial4 — Primary key — NOT NULL
  • source — text
  • sido — text
  • gugun — text
  • name — text
  • address — text
  • capacity — float8
  • process — text
  • tech — text
  • bod_out — text
  • toc_out — text
  • ss_out — text
  • tn_out — text
  • tp_out — text
  • efficiency — text
  • built — text
  • started — text
  • operator — text
  • contractor — text
  • watershed — text
  • phone — text
  • fac_type — text
  • lat — numeric(11, 8)
  • lon — numeric(11, 8)
  • geo_level — text
  • raw — jsonb

transform_ruleset

  • id — serial4 — Primary key — NOT NULL
  • name — text — NOT NULL
  • description — text
  • source_dataset — text — NOT NULL
  • loq_handling — text — NOT NULL
  • missing_method — text — NOT NULL
  • missing_max_gap — int4 — NOT NULL
  • outlier_method — text — NOT NULL
  • outlier_window — int4 — NOT NULL
  • outlier_n_sigma — float8 — NOT NULL
  • outlier_action — text — NOT NULL
  • domain_min — float8
  • domain_max — float8
  • norm_method — text — NOT NULL
  • fecal_indicator — text — NOT NULL
  • fecal_ref_method — text — NOT NULL
  • population — int4
  • env_coefficients — jsonb
  • enabled — bool — NOT NULL
  • is_default — bool — NOT NULL
  • created_by — text
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

transform_run

  • id — bigserial — Primary key — NOT NULL
  • ruleset_id — int4 — Foreign key → transform_ruleset.id — NOT NULL
  • trigger — text — NOT NULL
  • actor — text
  • started_at — timestamptz — NOT NULL
  • finished_at — timestamptz
  • status — text — NOT NULL
  • series_count — int4 — NOT NULL
  • rows_in — int4 — NOT NULL
  • rows_out — int4 — NOT NULL
  • n_loq — int4 — NOT NULL
  • n_gap_imputed — int4 — NOT NULL
  • n_gap_long — int4 — NOT NULL
  • n_outlier — int4 — NOT NULL
  • n_fallback — int4 — NOT NULL
  • message — text
  • log — text
  • qc — jsonb

wbe_activity

  • region_code — text — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • obs_year — int4 — Primary key — NOT NULL
  • obs_week — int4 — Primary key — NOT NULL
  • value — float8
  • wval — float8
  • zscore — float8
  • level_group — int4
  • level_label — text
  • baseline_log10 — float8
  • sd_log — float8
  • n_baseline — int4
  • scope — text
  • baseline_weeks — int4
  • computed_at — timestamptz — NOT NULL
  • pct_rank — float8

wbe_chart_point

  • report_year — int4 — Primary key — NOT NULL
  • report_week — int4 — Primary key — NOT NULL
  • region_code — text — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • obs_year — int4 — Primary key — NOT NULL
  • obs_week — int4 — Primary key — NOT NULL
  • value — float8
  • unit — text — NOT NULL
  • scope — text
  • region_label — text
  • region_seq — int4
  • site_count — int4
  • agg_method — text
  • value_method — text — NOT NULL
  • value_error — float8
  • precision_rel — float8
  • axis_max — float8
  • axis_step — float8
  • axis_mult — float8
  • axis_ticks — int4
  • bar_top_px — int4
  • baseline_px — int4
  • top_tick_px — int4
  • page_no — int4
  • image_xref — int4
  • temporal — text — NOT NULL
  • source_code — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL
  • axis_kind — text — NOT NULL
  • axis_min — float8
  • chart_style — text

wbe_pdf_doc

  • report_year — int4 — Primary key — NOT NULL
  • report_week — int4 — Primary key — NOT NULL
  • title — text
  • published_at — date
  • file_name — text
  • file_url — text
  • sha256 — text
  • byte_size — int4
  • page_count — int4
  • period_from — date
  • period_to — date
  • site_total — int4
  • pathogens — _text
  • cumulative — jsonb
  • notice — text
  • disclaimer — text
  • charts_found — int4
  • charts_parsed — int4
  • points_loaded — int4
  • status — text
  • note — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL

wbe_pdf_page

  • report_year — int4 — Primary key — NOT NULL
  • report_week — int4 — Primary key — NOT NULL
  • page_no — int4 — Primary key — NOT NULL
  • text — text
  • char_count — int4

wbe_region_agreement

  • region_code — text — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • n_weeks — int4
  • exact_pct — float8
  • within1_pct — float8
  • bias — float8
  • n_pairs — int4
  • inversions — int4
  • inversion_pct — float8
  • grade — text
  • computed_at — timestamptz — NOT NULL

wbe_region_status

  • report_year — int4 — Primary key — NOT NULL
  • report_week — int4 — Primary key — NOT NULL
  • region_code — text — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • region_label — text
  • region_seq — int4
  • level_group — int4
  • level_label — text
  • level_rgb — text
  • trend — text
  • note — text
  • source_code — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL

wbe_report

  • iso_year — int4 — Primary key — NOT NULL
  • iso_week — int4 — Primary key — NOT NULL
  • title — text
  • published_at — date
  • period_from — date
  • period_to — date
  • site_count — int4
  • pathogens — _text
  • doc_url — text
  • file_url — text
  • file_name — text
  • disclaimer — text
  • body — text
  • source_code — text
  • raw — jsonb
  • ingested_at — timestamptz — NOT NULL

wval_compare

  • country — text — Primary key — NOT NULL
  • region_code — text — Primary key — NOT NULL
  • pathogen — text — Primary key — NOT NULL
  • iso_year — int4 — Primary key — NOT NULL
  • iso_week — int4 — Primary key — NOT NULL
  • value — float8
  • wval — float8
  • zscore — float8
  • pct_rank — float8
  • level_group — int4
  • level_label — text
  • n_baseline — int4
  • baseline_weeks — int4
  • computed_at — timestamptz — NOT NULL