--- a/schema/gemma.sql	Wed Sep 11 10:32:19 2019 +0200
+++ b/schema/gemma.sql	Thu Sep 12 18:13:47 2019 +0200
@@ -4,7 +4,7 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018,2019 by via donau
+-- Copyright (C) 2018, 2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
@@ -236,49 +236,6 @@
 -- GEMMA data
 --
 
--- Namespace not to be accessed directly by any user
-CREATE SCHEMA internal
-    -- Profile data are only accessible via the view users.list_users.
-    CREATE TABLE user_profiles (
-        username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
-        -- keep username length compatible with role identifier
-        map_extent box2d NOT NULL,
-        email_address varchar NOT NULL
-    )
-    -- Columns referencing user-visible schemas added below.
-;
-
-
--- Namespace to be accessed by sys_admin only
-CREATE SCHEMA sys_admin
-    CREATE TABLE system_config (
-        config_key varchar PRIMARY KEY,
-        config_val varchar
-    )
-
-    CREATE TABLE password_reset_requests (
-        hash varchar(32) PRIMARY KEY,
-        issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        username varchar NOT NULL UNIQUE
-            REFERENCES internal.user_profiles(username)
-                ON DELETE CASCADE ON UPDATE CASCADE
-    )
-
-    -- Tables with geo data to be published with GeoServer.
-    CREATE TABLE external_services (
-        local_name varchar PRIMARY KEY,
-        remote_url varchar NOT NULL,
-        is_wfs     boolean NOT NULL DEFAULT TRUE
-    )
-
-    CREATE TABLE published_services (
-        name regclass PRIMARY KEY,
-        style xml CHECK(style IS DOCUMENT),
-        as_wms boolean NOT NULL DEFAULT TRUE,
-        as_wfs boolean NOT NULL DEFAULT TRUE
-    )
-;
-
 --
 -- Look-up tables with data that are static in a running system
 --
@@ -366,12 +323,76 @@
     'report'
 );
 
+
+-- Namespace not to be accessed directly by any user
+CREATE SCHEMA internal
+    -- Profile data are only accessible via the view users.list_users.
+    CREATE TABLE user_profiles (
+        username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
+        -- keep username length compatible with role identifier
+        country char(2) NOT NULL REFERENCES countries,
+        map_extent box2d NOT NULL,
+        email_address varchar NOT NULL
+    )
+    -- Columns referencing user-visible schemas added below.
+;
+
+
+-- Namespace to be accessed by sys_admin only
+CREATE SCHEMA sys_admin
+    CREATE TABLE system_config (
+        config_key varchar PRIMARY KEY,
+        config_val varchar
+    )
+
+    CREATE TABLE password_reset_requests (
+        hash varchar(32) PRIMARY KEY,
+        issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        username varchar NOT NULL UNIQUE
+            REFERENCES internal.user_profiles(username)
+                ON DELETE CASCADE ON UPDATE CASCADE
+    )
+
+    -- Tables with geo data to be published with GeoServer.
+    CREATE TABLE external_services (
+        local_name varchar PRIMARY KEY,
+        remote_url varchar NOT NULL,
+        is_wfs     boolean NOT NULL DEFAULT TRUE
+    )
+
+    CREATE TABLE published_services (
+        name regclass PRIMARY KEY,
+        style xml CHECK(style IS DOCUMENT),
+        as_wms boolean NOT NULL DEFAULT TRUE,
+        as_wfs boolean NOT NULL DEFAULT TRUE
+    )
+;
+
+
 -- Namespace for user management related data
 CREATE SCHEMA users
-    CREATE TABLE responsibility_areas (
-        country char(2) PRIMARY KEY REFERENCES countries,
-        area geography(MULTIPOLYGON, 4326)
-            CHECK(ST_IsValid(CAST(area AS geometry)))
+    CREATE TABLE stretches (
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+        name varchar NOT NULL,
+        stretch isrsrange NOT NULL,
+        area geography(MULTIPOLYGON, 4326) NOT NULL
+            CHECK(ST_IsValid(CAST(area AS geometry))),
+        objnam varchar NOT NULL,
+        nobjnam varchar,
+        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        source_organization varchar NOT NULL,
+        staging_done boolean NOT NULL DEFAULT false,
+        UNIQUE(name, staging_done)
+    )
+    CREATE TRIGGER stretches_date_info
+        BEFORE UPDATE ON stretches
+        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
+
+    CREATE TABLE stretch_countries (
+        stretch_id int NOT NULL REFERENCES stretches(id)
+            ON DELETE CASCADE,
+        country char(2) NOT NULL REFERENCES countries,
+        PRIMARY KEY(stretch_id, country)
     )
 
     CREATE TABLE templates (
@@ -385,8 +406,7 @@
     CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 ;
-ALTER TABLE internal.user_profiles ADD
-    country char(2) NOT NULL REFERENCES users.responsibility_areas;
+
 
 -- Namespace for waterway data that can change in a running system
 CREATE SCHEMA waterway
@@ -545,30 +565,6 @@
         check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
     )
 
-    CREATE TABLE stretches (
-        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-        name varchar NOT NULL,
-        stretch isrsrange NOT NULL,
-        area geography(MULTIPOLYGON, 4326) NOT NULL
-            CHECK(ST_IsValid(CAST(area AS geometry))),
-        objnam varchar NOT NULL,
-        nobjnam varchar,
-        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL,
-        staging_done boolean NOT NULL DEFAULT false,
-        UNIQUE(name, staging_done)
-    )
-    CREATE TRIGGER stretches_date_info
-        BEFORE UPDATE ON stretches
-        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
-
-    CREATE TABLE stretch_countries (
-        stretches_id int NOT NULL REFERENCES stretches(id)
-            ON DELETE CASCADE,
-        country_code char(2) NOT NULL REFERENCES countries(country_code),
-        UNIQUE(stretches_id, country_code)
-    )
-
     -- Like stretches without the countries
     CREATE TABLE sections (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
@@ -584,7 +580,7 @@
         UNIQUE(name, staging_done)
     )
     CREATE TRIGGER sections_date_info
-        BEFORE UPDATE ON stretches
+        BEFORE UPDATE ON sections
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE waterway_profiles (