--- a/schema/gemma.sql	Thu May 16 17:22:33 2019 +0200
+++ b/schema/gemma.sql	Thu May 16 18:41:43 2019 +0200
@@ -273,7 +273,7 @@
 
 
     CREATE TABLE gauges (
-        location isrs PRIMARY KEY CHECK(
+        location isrs CHECK(
             (location).orc SIMILAR TO 'G[[:digit:]]{4}'
             AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
         objname varchar NOT NULL,
@@ -281,56 +281,70 @@
         applicability_from_km int8,
         applicability_to_km int8,
         validity tstzrange,
-        -- pasted text from a more general specification is given
-        -- (a gauge is not a berth!)
-        -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
         zero_point double precision NOT NULL,
         geodref varchar,
-        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar
+        date_info timestamp with time zone NOT NULL,
+        source_organization varchar,
+        lastupdate timestamp with time zone NOT NULL,
+        -- entry removed from external data source (RIS-Index)/historicised:
+        erased boolean NOT NULL DEFAULT false,
+        PRIMARY KEY (location, validity),
+        EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
     )
-    CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
-        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
+    -- Allow only one non-erased entry per location
+    CREATE UNIQUE INDEX gauges_erased_unique_constraint
+        ON gauges (location)
+        WHERE NOT erased
 
     CREATE TABLE gauges_reference_water_levels (
-        gauge_id isrs NOT NULL REFERENCES gauges,
+        location isrs NOT NULL,
+        validity tstzrange NOT NULL,
+        FOREIGN KEY (location, validity) REFERENCES gauges,
         -- Omit foreign key constraint to be able to store not NtS-compliant
         -- names, too:
         depth_reference varchar NOT NULL, -- REFERENCES depth_references,
-        PRIMARY KEY (gauge_id, depth_reference),
+        PRIMARY KEY (location, validity, depth_reference),
         value int NOT NULL
     )
 
     CREATE TABLE gauge_measurements (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-        fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges,
+        location isrs NOT NULL,
+        validity tstzrange NOT NULL,
+        CONSTRAINT gauge_key
+            FOREIGN KEY (location, validity) REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
         country_code char(2) NOT NULL REFERENCES countries,
         sender varchar NOT NULL, -- "from" element from NtS response
         language_code varchar NOT NULL REFERENCES language_codes,
-        date_issue timestamp with time zone NOT NULL,
+        date_issue timestamp with time zone
+            NOT NULL CHECK (date_issue <@ validity),
         reference_code varchar(4) NOT NULL REFERENCES depth_references,
         water_level double precision NOT NULL,
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
         source_organization varchar NOT NULL, -- "originator" from NtS response
         staging_done boolean NOT NULL DEFAULT false,
-        UNIQUE (fk_gauge_id, measure_date, staging_done)
+        UNIQUE (location, validity, measure_date, staging_done)
     )
 
     CREATE TABLE gauge_predictions (
-        fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges,
+        location isrs NOT NULL,
+        validity tstzrange NOT NULL,
+        CONSTRAINT gauge_key
+            FOREIGN KEY (location, validity) REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
         country_code char(2) NOT NULL REFERENCES countries,
         sender varchar NOT NULL, -- "from" element from NtS response
         language_code varchar NOT NULL REFERENCES language_codes,
-        date_issue timestamp with time zone NOT NULL,
+        date_issue timestamp with time zone
+            NOT NULL CHECK (date_issue <@ validity),
         reference_code varchar(4) NOT NULL REFERENCES depth_references,
         water_level double precision NOT NULL,
         conf_interval numrange
             CHECK (conf_interval @> CAST(water_level AS numeric)),
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
         source_organization varchar NOT NULL, -- "originator" from NtS response
-        PRIMARY KEY (fk_gauge_id, measure_date, date_issue)
+        PRIMARY KEY (location, validity, measure_date, date_issue)
     )
 
     CREATE TABLE waterway_axis (
@@ -474,7 +488,9 @@
     CREATE TABLE bottlenecks (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         bottleneck_id varchar UNIQUE NOT NULL,
-        fk_g_fid isrs NOT NULL REFERENCES gauges,
+        gauge_location isrs NOT NULL,
+        gauge_validity tstzrange NOT NULL,
+        FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges,
         -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
         objnam varchar,
         nobjnm varchar,