To allow users to enter up to 8 locations and have the highs and lows display on a Celtic Wheel type layout in order to provide an easy at-a-glance evaluation of where to move during a weather focused nomadic cycle.

High-Level Description

Simple UI with a clock-like display. 8 clickable pie wedges allow the user to choose a location for weather to be fetched.

	SS	CQ
CQ			FE
SE			CQ
	CQ	WS

Definitions

The wheel is based on the movement of the sun and determines the seasons of the year.

SS = Summer Solstice, CQ = Cross Quarter, FE = Fall Equinox, WS = Winter Solstice, SE = Spring Equinox

Summer is placed at the top to represent high elevation. A cross quarter is the midway point between each solar location. The seasons are then places clockwise so that fall and spring are in middle elevations and winter is low elevation.

The solar locations can be calculated roughly as month = 3*X, day = 21. The day is an estimate which typically actually spans between the 21st and 24th.

Cross quarters are calculated to be month = (3*X)-1, day = 1. An example is Samhein which is typically November 1.

This document will refer to the 8 season segments as follows:

Season	Abbr.	Events	Dates
Spring1	S1	Spring Equinox to Spring Cross Quarter	March 21 - May 01
Spring2	S2	Spring Cross Quarter to Summer Solstice	May 01 - June 21
Summer1	V1	Summer Solstice to Summer Cross Quarter	June 21 - August 01
Summer2	V2	Summer Cross Quarter to Summer Equinox	August 01 - September 21
Fall1	F1	Fall Equinox to Fall Cross Quarter	September 21 - November 01
Fall2	F2	Fall Cross Quarter to Winter Solstice	November 01 - December 21
Winter1	W1	Winter Solstice to Winter Cross Quarter	December 21 - February 01
Winter2	W2	Winter Cross Quarter to Spring Equinox	February 01 - March 21

Components

1. Front End

Developer: Hyperling

Language/Framework: Flutter

Planned Versions

0.1: Basic listview app, hardcode limited production API key.
0.2: Change main page to a Celtic Wheel type layout with season signifier.
1.0: Integrate with a backend cache server which provides aggregated data.
1.1: Add settings page and other nice to haves.

Requirements

TBD?

Pages

App shall have a simple layout with all pages linked and visible from Main.
Data entry may be done in a pop-up, modal, or subpage if necessary.

Main Page
- Clock-like interface which has a pointer hand going towards the current date.
- 12:00 is summer cross quarter, August 1.
Location Entry
- Opens when a pie wedge is clicked.
- Allows the user to enter a zip_code OR lat_num + long_num. Not both.
- Allows the user to enter a location_nickname for local use only.
- Saves these fields in simple cache. Data stays local. #: 1. Settings? [TBD: Maybe a future version? KISS for now.]

Main

Initial Versions - Simple Data Entry

8 sections which contain data input fields for:

Zip Code
Lat & Long (used to look up the zip, not sent to cache DB or weather service)
Nickname

Zip is a required field to successfully submit the data. Nickname is optional. Lat and Long can be ignored and will be autopopulated on submit based on the Zip, or if they are both populated and Zip is blank, then the submit function shall calculate Zip based off of them.

Formal Versions - Wheel Layout

Includes and expands upon the previous section.

Circular layout representing the Celtic season wheel with 'LAST_UPDATE_DATE' in the center so the user knows when the highs and lows were last refreshed.

"Hand" which points to the current season. Accurate to the day preferably, so if we're at September 1 it's pointing in between Summer CQ and Fall Equinox.

Initially can just highlight the current half quarter and start making the next one glow as it gets approached. So for June 21, Spring2 and Summer1 would both glow at 50% since that's the day they transition. Then by mid July it would only be Summer1 which glows, and at the end of July Summer1 would be dim and Summer2 would be glowing more. Maybe keep this in play after the hand works too.
Hand and glowing need to be in the opposite direction if the user has more than half of their latitudes as negatives.

Actions

Location Submission {submitLocation(), onLocationSave()}
- IF zip.isEmpty() && lat.isEmpty() && long.isEmpty() THEN ERROR('Zip code must be provided! You may use lat/long to find it if needed.'); FI
- IF zip.isEmpty() && (lat.isEmpty() || long.isEmpty()) THEN ERROR('Coordinate data not filled out completely.'); FI
- IF zip.isEmpty() && !lat.isEmpty() && !long.isEmpty() THEN zip = LOOKUP(lat, long); FI
  - If the user opts to provide a lat and long, find the zip code.
- IF !zip.isEmpty() THEN (lat, long) = LOOKUP(zip); FI
  - This overwrites the user's lat and long for what is actually going to checked by the weather API.

Settings

Not required in initial versions of the app, focus on pulling data first!

'Server'
- Toggle: 'Use Cache Server'
  - TRUE:
    - Radio Group: 'Aggregation Mode'
      - 'Week : Mean / Average'
      - 'Week : Median / Center-Most'
      - 'Week : Mode / Most Common'
      - 'Only : Today'
      - 'Only : Tomorrow'
      - 'Only : Extended (Today + 7)'
  - FALSE:
    - TextEdit: 'API Key' (REQUIRED)
      - 'If not using the cache server, you'll need to provide your own API key for TBD weather service.'
      - TBD: What data is provided, and how easy is it to manipulate?
'Temperature'
- Radio Group: 'Unit'
  - Fahrenheit (^F)
  - Celsius (^C)
- TextEdit: 'Preferred High'
  - Whole Numbers / Integers Only
- Radio Group: '(TBD: Relativity?)'
  - Option: 'Less Than'
  - Option: 'Greater Than'
  - Checkbox: 'Equal To'
- TextEdit: 'Preferred Low'
  - Whole Numbers / Integers Only
- Radio Group: '(TBD: Relativity?)'
  - Option: 'Less Than'
  - Option: 'Greater Than'
  - Checkbox: 'Equal To'

Test Cases

App opens and displays any previously entered locations.
- Screenshot of app
App loads weather data, choosing the DB cache first.
If the DB is down, app accesses weather API directly.
- Take down the DB, or put in an incorrect URL/port.
- Open app and note a few temps and the last_update_date.
- After the update takes place, note the new temps and last_update_date.

App shall start with a Main page only, then have a Settings page added later.
App shall come with a limited use API key. This key shall not be used by any other module of this project. Developers should use their separate API key when using Postman, testing the Weather Fetcher, etc.

2. Database

TBD

Requirements

All temperature data shall be stored in Fahrenheit. Decimals are okay.
All date data shall be displayed as 'YYYY-MM-DDTHH24:MI:SS' GMT (or 'YYYY-MM-DDTHH24:MI:SS+0000') in any reporting or data transfers. Time zones will be handled at the frontend. The 'T' in the center is a dead character. Example:
```
$ echo "Actual Time: `date`" && echo "Report Time: `date -u +'%Y-%m-%dT%H:%M:%S%z'`"
Actual Time: Wed Aug 19 10:49:52 AM MST 2026
Report Time: 2026-08-19T17:49:52+0000
```
Database shall be set to case insensitive, so that SELECT column FROM loc and select COLUMN from LOC are the same command.
Database shall have a daily purge script followed by statistics rebuilding. The records to be removed will look like (TBD: IS_PURGED = true). The purge will cascade through any child tables. Bonus points if they exist, are scheduled, and gets executed from inside the DB rather than needing to run outside files.

Database object script(s) for the primary initialization as well as patches shall be reproducible and rerunnable, meaning they drop then create all database objects, constraints, schedules, etc being added in the revision. Example:

-- Undo Section --
-- Allowed to have failures if the objects don't exist yet.
SET ERRORS OFF / ON ERROR CONTINUE / ETC;
DROP TABLE example_child;
DROP TABLE example;

-- Apply Section --
-- All commands must succeed successfully in order to continue.
SET ERRORS ON / ON ERROR EXIT / ETC;
CREATE TABLE example
    ( example_id INT PRIMARY KEY
    , example_data VARCHAR
);
CREATE TABLE example_child
    ( example_child_id INT PRIMARY KEY
    , example_id INT FOREIGN KEY (example.example_id)
    , more_example_data VARCHAR
);

Scripts shall have a consistent style and formatting. Developer may use the style of their choice.
Scripts shall assume a Docker environment using image debian:trixie-slim.

Tables

Database and schema names shall be short and easy to remember, otherwise they are the developer's choice. Bonus points if the DB does not require them to access the tables, or a setting / parameter can be set to default to them.
```
-- yucky
select * from db_name.schema_name.table_name;
-- preferred
select * from table_name;
```

Table 1: `loc`

Column Name	Type	Constraints	Calculated?
loc_id	INT	PK	-
zip_code	STRING	UNIQUE	-
last_accessed_date	DATE	-	-
needs_purged	-	-	CASE WHEN sysdate < last_accessed_date + 90 THEN true ELSE false ESAC

Table 2: `loc_weather`

Column Name	Type	Constraints?	Calculated?
loc_weather_id	INT	PK,UNIQUE
loc_id	INT	FK
weather_date	DATE
isCurrent	-		`if weather_date >= sysdate then 'Y' else 'N' fi`
isOld	-		`if weather_date < sysdate then 'Y' else 'N' fi`
isExpired	-		`if weather_date < sysdate-7 then 'Y' else 'N' fi`

Extra Constraints
UNIQUE(loc_id, weather_date)

Table 3: `loc_weather_agg`

Column Name	Type	Constraints	Calculated?
loc_weather_agg_id	INT	PK	-
loc_weather_id	INT	FK (loc_weather.loc_weather_id)	-
weekly_mean_high	DECIMAL	-	-
weekly_mean_low	DECIMAL	-	-
weekly_median_high	DECIMAL	-	-
weekly_median_low	DECIMAL	-	-
weekly_mode_high	DECIMAL	-	-
weekly_mode_low	DECIMAL	-	-
tomorrow_high	DECIMAL	-	-
tomorrow_low	DECIMAL	-	-
extended_high	DECIMAL	-	-
extended_low	DECIMAL	-	-

Today's high and low are accessible when joining to the LOC_WEATHER table, which is necessary anyways to get to the WEATHER_DATE and ZIP_CODE.

Extended high and low are for the furthest accessible day up to a week out.

SELECT loc_weather_id, max(weather_date)
  FROM loc_weather
 WHERE weather_date <= sysdate+7
 GROUP BY loc_weather_id;

Roles

Object grants shall be given to roles, and the roles then assigned to users. Example:

CREATE ROLE myrole;
GRANT SELECT, INSERT, UPDATE ON example TO myrole;
CREATE USER me;
GRANT myrole TO me;

Role 0 : `librarian`

Grant SELECT on all loc* tables.

Role 1: `weatherman`

Grant INSERT and UPDATE on all loc_weather* tables.

Role 2: `reporter`

Grant INSERT, UPDATE on loc.

Role 3: `garbageman`

Grant DELETE on all loc* tables.
Grant ability to build statistics on all loc* tables.
Depending on the DB used, may also need grants on keys and constraints in order to rebuild the stats on their indexes or other stat-holding objects.

Role 4: `developer`

Grant ALL on the applicable schema.
CANNOT access or alter SYSTEM type schemas, views, etc.

Users

User 1: `dbuser`

Granted the librarian and developer role.
Used by the main contributors of the project to test in stage environments.
Used by the project owner(s) in production to apply patch scripts.

User 2: `climate`

Granted the librarian and weatherman role.
Used by a local process to add and aggregate weather data.

User 3: `geo`

Granted the librarian and reporter role.
Used by a local process to pull weather data and post new locations.

User 4: `purge`

Granted the librarian and garbageman role.
User / schema who owns the purge routines if they are installed in the DB.
Otherwise, this is the user who logs in and runs external purge scripts.

As Needed: leetbro, hackersis, othercoolalias

Contributors to this project may have their accounts created in test and stage environments as necessary. Otherwise the contributors known as DB admins shall handle all applicable DB support and modifications.

Test Cases

TBD

3. Weather Fetcher

TBD

Description

TBD

Requirements

TBD

Test Cases

Assume the database is local, just as would be during local development. Will not need to access via domain name or a special port, only localhost:defaut_port.
Program shall use its own API key(s) to access the weather data. May use a pool of keys if necessary to alleviate bandwidth or throttling.

Aggregations

For each ZIP_CODE, calculate the following:

Weekly Mean
Weekly Median
Weekly Mode

Tomorrow

-- Can be done the same as Extended, just change the sysdate+7 to sysdate+1.

Extended

Example:

-- Need to get the max(weather_date) per loc_id, identify its loc_weather_id, then use that to join against  the agg table.

-- Temp view of the latest date that each location has.
SELECT lw.loc_id, max(lw.weather_date)
  FROM loc_weather lw
 WHERE lw.weather_date <= sysdate + 7
 GROUP BY lw.loc_id;

-- Joining the temp view to the table to get the individual records.
SELECT lw.loc_weather_id, lw.high, lw.low
  FROM loc_weather lw
     , (SELECT lw.loc_id, max(lw.weather_date) as weather_date
        FROM loc_weather lw
        WHERE lw.weather_date <= sysdate + 7
        GROUP BY lw.loc_id) t
 WHERE lw.loc_id = t.loc_id
   AND lw.weather_date = t.weather_date;

-- Merging the data into the agg table.
MERGE INTO loc_weather_agg o
USING (SELECT lw.loc_weather_id, lw.high, lw.low
         FROM loc_weather lw
            , (SELECT lw.loc_id, max(lw.weather_date) as max_weather_date
                 FROM loc_weather lw
                WHERE lw.weather_date <= sysdate + 7
                GROUP BY lw.loc_id) t
        WHERE lw.loc_id = t.loc_id
          AND lw.weather_date = t.max_weather_date) i
   ON (o.loc_weather_id = i.loc_weather_id)
 WHEN MATCHED THEN UPDATE
  SET o.extended_high = i.high
    , o.extended_low  = i.low;
commit;

4. Response API

Developer: TBD

Language: Developer's choice. Node.js using Express recommended unless a better use case can be made.

Description

Listener API which receives requests from the front end and queries the database on their behalf. This program is responsible for adding new ZIP_CODE's to the database and requesting that the weatherman load the data for these when that is done.

Requirements

Handles requests from front end devices.

Test Cases

Program shall listen JSON #1 in the appendix as its initiating request.
Program shall provide JSON #2 as a response to the request.
Program shall access the database in section #2 as user geo.
Program shall acquire the weather data from the loc_weather_agg table..
Program shall post any new zip codes in the loc table.
program shall update loc.last_accessed_date for the zip codes it retrieved data for.

Flow

Initialize.
Parse request. (Appendix #1)
Merge zip codes to loc.
Notify weatherman if new zip codes exist.
- Wait until a continue or error is replied.
- If continue, proceed to next steps.
- If error, provide error to user (Appendix #8)
Pull weather data from loc_weather and loc_weather_agg.
Form and deliver response. (Appendix #2)

Appendix - JSON Requests and Responses

Between the programs being developed there are 3 themes being used regarding message intent.

data: These messages pass information back and forth.
command: These are used to notify programs that actions need taken.
error: These notify that something went wrong and may provide a message with which to return to the front end.

Theintent is passed at the JSON object's root level "message_type" attribute. The rest of the payload lies within an attribute named the same as the message type.

1. Request: Front End to Response API

Data shall be de-duplicated so that any one zip only appears once.
All payloads shall contain the version number so that the server knows which response to provide, in case the format changes in the future.
Commas are acceptable after every data element, even ending ones.

{
    "version": "1.0",
    "message_type": "data",
    "data": [
        "zip_code": "85901",
        "zip_code": "86004",
        "zip_code": "86001",
    ],
}

2. Response: Response API to Front End

Version shall be included so that the front end knows how to parse the response.
Commas are acceptable after every element, including the ends of lists.
Zip codes and dates shall be treated as strings.
Temperature data shall be treated like numbers.

{
    "version": "1.0",
    "message_type": "data",
    "data": [
        {
            "zip_code": "85901",
            "last_update_date": "1900-01-01T15:00:00+0000",
            "mean_high": 64.237,
            "mean_low": 38.758,
            "median_high": 65,
            "median_low": 43,
            "mode_high": 68,
            "mode_low": 47,
            "today_high": 58,
            "today_low": 33,
            "tomorrow_high": 61,
            "tomorrow_low": 37,
        },
        {
            "zip_code": "86004",
            "last_update_date": "1900-01-01T15:00:00+0000",
            "mean_high": 45.46,
            "mean_low": 22.235,
            "median_high": 45,
            "median_low": 19,
            "mode_high": 42,
            "mode_low": 22,
            "today_high": 46,
            "today_low": 25,
            "tomorrow_high": 52,
            "tomorrow_low": 12,
        },
        {
            "zip_code": "86001",
            "last_update_date": "1900-01-01T15:00:00+0000",
            "mean_high": 45.64,
            "mean_low": 22.532,
            "median_high": 44,
            "median_low": 18,
            "mode_high": 41,
            "mode_low": 21,
            "today_high": 45,
            "today_low": 24,
            "tomorrow_high": 51,
            "tomorrow_low": 11,
        },
    ],
}

3. Request: Applications to Weather Provider (TBD)

(TBD: How to parse a request to the weather service.)

4. Response: Weather Provider (TBD) to Applications

(TBD: How to read a response from the weather service.)

5. Request: New ZIP_CODE Needs Processed

Notification from the reporter for the weatherman to kick off an ad hoc run.

This data never leaves the server and cannot come from outside sources, the endpoint will not available to the public, it is accessed via localhost.
Zip codes do not need provided since the reporter has already inserted and committed them to loc.
reporter runs this in a synchronous fashion, waiting for the response before continuing to reply to the user.

{
    "message_type": "command",
    "command": "run",
}

6. Response: ZIP_CODE Data Now Available

Response from weatherman program to reporter that the new zip codes inserted into the DB have data.

If the reporter sees this, then it continues with building a response for the front end user.

{
    "message_type": "command",
    "command": "continue",
}

7. Response: Issue Loading New ZIP_CODE's

Response from weatherman program to reporter.

If the reporter happens to see this, it provides

{
    "message_type": "error",
    "error": "Message about what went wrong, which may get forwarded to the user.",
}

8. Response: Error to Front End

Sometimes the reporter may need to let the user know things did not go as planned.

{
    "message_type": "error",
    "error": "Failed to pull weather information. REASON.",
}

Purpose

High-Level Description

Definitions

Components

1. Front End

Planned Versions

Requirements

Pages

Main

Actions

Settings

Test Cases

2. Database

Requirements

Tables

Table 1: loc

Table 2: loc_weather

Table 3: loc_weather_agg

Roles

Role 0 : librarian

Role 1: weatherman

Role 2: reporter

Role 3: garbageman

Role 4: developer

Users

User 1: dbuser

User 2: climate

User 3: geo

User 4: purge

As Needed: leetbro, hackersis, othercoolalias

Test Cases

3. Weather Fetcher

Description

Requirements

Test Cases

Aggregations

4. Response API

Description

Requirements

Test Cases

Flow

Appendix - JSON Requests and Responses

1. Request: Front End to Response API

2. Response: Response API to Front End

3. Request: Applications to Weather Provider (TBD)

4. Response: Weather Provider (TBD) to Applications

5. Request: New ZIP_CODE Needs Processed

6. Response: ZIP_CODE Data Now Available

7. Response: Issue Loading New ZIP_CODE's

8. Response: Error to Front End

Table 1: `loc`

Table 2: `loc_weather`

Table 3: `loc_weather_agg`

Role 0 : `librarian`

Role 1: `weatherman`

Role 2: `reporter`

Role 3: `garbageman`

Role 4: `developer`

User 1: `dbuser`

User 2: `climate`

User 3: `geo`

User 4: `purge`