Support all the Fields supported by GSAK, not only a subset

[AgreeDK]

Current State

We support only a subset of all the fields in our GUI , think they already are in our database as we support GPX import of GSAK exported files.

Proposed Improvement

If we support all the same fields as GSAK in our GUI, more people would use it, here are picture of the field selection window in GSAK.

Expected Benefits

No response

[Fabio-A-Sa]

Nice. Since this is a major feature, I think we need a roadmap with clear targets and deliverables, along with proper test coverage to ensure nothing breaks. For now, a list of missing keys would be sufficient :)

[AgreeDK]

<title></title>
<meta name="generator" content="LibreOffice 24.2.7.2 (Linux)"/>
<style type="text/css">
	@page { size: 21cm 29.7cm; margin: 2cm }
	th p { font-weight: bold; text-align: center; orphans: 0; widows: 0; background: transparent }
	td p { orphans: 0; widows: 0; background: transparent }
	pre { background: transparent }
	pre.western { font-family: "Liberation Mono", monospace; font-size: 10pt }
	pre.cjk { font-family: "Noto Sans Mono CJK SC", monospace; font-size: 10pt }
	pre.ctl { font-family: "Liberation Mono", monospace; font-size: 10pt }
	h3 { margin-top: 0.25cm; margin-bottom: 0.21cm; background: transparent; page-break-after: avoid }
	h3.western { font-family: "Liberation Serif", serif; font-size: 14pt; font-weight: bold }
	h3.cjk { font-family: "Noto Serif CJK SC"; font-size: 14pt; font-weight: bold }
	h3.ctl { font-family: "Noto Sans Devanagari"; font-size: 14pt; font-weight: bold }
	h2 { margin-top: 0.35cm; margin-bottom: 0.21cm; background: transparent; page-break-after: avoid }
	h2.western { font-family: "Liberation Serif", serif; font-size: 18pt; font-weight: bold }
	h2.cjk { font-family: "Noto Serif CJK SC"; font-size: 18pt; font-weight: bold }
	h2.ctl { font-family: "Noto Sans Devanagari"; font-size: 18pt; font-weight: bold }
	p { line-height: 115%; margin-bottom: 0.25cm; background: transparent }
	em { font-style: italic }
	code.western { font-family: "Liberation Mono", monospace }
	code.cjk { font-family: "Noto Sans Mono CJK SC", monospace }
	code.ctl { font-family: "Liberation Mono", monospace }
	strong { font-weight: bold }
</style>

Here is input from Claude AI, with key list and a suggested migration plan:

GSAK fields vs. OpenSAK database

All 58 unique fields from GSAK's "Standard data fields" list

34 Already covered

5 Partially covered

12 Missing — worth adding

7 GSAK-internal / skip

| GSAK field | Status | OpenSAK field / note

-- | -- | -- | --
Identity & classification
1 | Code (GC code) | Covered | Cache.gc_code
2 | Cache Id | Covered | Cache.id (surrogate PK)
3 | Cache type | Covered | Cache.cache_type
4 | Container size | Covered | Cache.container
5 | Waypoint name | Covered | Cache.name + Waypoint.name
6 | Guid | Skip | GSAK internal UUID — no value in OpenSAK
7 | Symbol name | Missing | GPX tag — not currently imported. Low priority.
8 | URL | Covered | Constructed as https://coord.info/{gc_code} — no DB field needed
9 | Smart Name | Skip | GSAK macro-generated name — not applicable
Coordinates & location
10 | Latitude | Covered | Cache.latitude
11 | Longitude | Covered | Cache.longitude
12 | Corrected coordinates indicator | Covered | UserNote.is_corrected, .corrected_lat, .corrected_lon
13 | Country | Covered | Cache.country
14 | State / region | Covered | Cache.state
15 | Municipality | Missing | Not in GPX — requires reverse geocoding or API. Low priority.
16 | Distance | Partial | Calculated from lat/lon + center point in UI — not stored. Add Cache.distance Float.
17 | Bearing (direction) | Partial | Calculated in UI — not stored. Add Cache.bearing Float (degrees 0–360).
18 | Bearing (compass) | Partial | Derived from bearing — no separate field needed.
19 | Altitude | Missing | Not in standard GPX/PQ exports from gc.com. Skip for now.
Ratings & status
20 | Difficulty | Covered | Cache.difficulty
21 | Terrain | Covered | Cache.terrain
22 | Archived / unavailable | Covered | Cache.archived + Cache.available
23 | Premium only | Covered | Cache.premium_only
24 | Favorite points (count) | Partial | Cache.favorite_point is bool only. Add Cache.favorite_points Integer for the count. Not in GPX — API only.
25 | Find count (total) | Missing | Not in standard GPX exports. Would need API.
26 | Watch status | Missing | Requires gc.com API — skip until API access approved.
27 | Resolution | Skip | GSAK internal field — not applicable
28 | Row number | Skip | GSAK internal sequence — not applicable
Owner & dates
29 | Placed by (owner name) | Covered | Cache.placed_by
30 | Owner ID | Covered | Cache.owner_id
31 | Hidden date | Covered | Cache.hidden_date
32 | Last modified date | Covered | Cache.last_updated
33 | Last GPX update | Covered | Cache.imported_at
34 | Last user update | Covered | UserNote.updated_at
Content & logs
35 | Hints | Covered | Cache.encoded_hints
36 | Last found date | Covered | Derived from Cache.logs (newest "Found it")
37 | Last 4 logs | Covered | Cache.logs relationship (sorted desc)
38 | Last log date | Covered | Derived from Log.log_date (first in desc list)
39 | Log count | Covered | Counted from Cache.logs relationship
40 | Travel bugs | Covered | Cache.trackables (Trackable model)
41 | GC.com owner note | Missing | Not in GPX — API only. Skip until API access approved.
42 | Source | Skip | GSAK tracks import source separately — we have Cache.source_file which covers this
Personal / user data
43 | Found status | Covered | Cache.found
44 | Found date | Covered | Cache.found_date
45 | DNF status | Covered | Cache.dnf
46 | DNF date | Partial | Cache.dnf is bool only. Add Cache.dnf_date DateTime — derive from logs on import.
47 | First to find | Missing | Derived from logs (finder of earliest "Found it"). Add Cache.first_to_find Bool.
48 | Notes | Covered | UserNote.note
49 | User flag | Missing | Independent boolean marker. Add Cache.user_flag Bool (default False).
50 | User sort | Missing | User-defined sort integer. Add Cache.user_sort Integer (nullable).
51 | User data 1 | Missing | Free text field. Add Cache.user_data_1 Text (nullable).
52 | User data 2 | Missing | Free text field. Add Cache.user_data_2 Text (nullable).
53 | User data 3 | Missing | Free text field. Add Cache.user_data_3 Text (nullable).
54 | User data 4 | Missing | Free text field. Add Cache.user_data_4 Text (nullable).
55 | Macro sort | Skip | GSAK macro feature — not applicable

Migration plan — adding missing fields

What we recommend adding

The fields worth adding fall into two groups:

Group A — simple new columns on Cache (no data loss risk, all nullable):

• dnf_date (DateTime) — date of the user's last DNF, derived from logs on import

• first_to_find (Boolean) — True if the user was the first finder, derived from logs on import

• user_flag (Boolean, default False) — independent personal marker flag

• user_sort (Integer, nullable) — user-defined sort number

• user_data_1 through user_data_4 (Text, nullable) — four free-text personal fields

• bearing (Float, nullable) — bearing in degrees to cache from the database center point

• distance (Float, nullable) — distance in km to cache from the database center point

• favorite_points (Integer, nullable) — rename/expand the existing favorite_point bool

Group B — skip for now:

• municipality, altitude, find_count, watch_status, gc_owner_note — all require the gc.com API or reverse geocoding. We revisit these once API access is approved.

---

How to migrate safely — Alembic-free approach

Since OpenSAK uses SQLite with a hand-managed schema (no Alembic), the safest migration pattern is an ADD COLUMN migration function that runs at startup via app.py. This is already the pattern you use.

The migration function checks if each new column exists, and only adds it if it doesn't. Existing databases are upgraded transparently on first launch of the new version. No data is ever lost.

The migration function to add to app.py:

def _migrate_v1_7(conn):
    """Add GSAK-compatible user fields and computed distance/bearing columns."""
    cursor = conn.execute("PRAGMA table_info(caches)")
    existing = {row[1] for row in cursor.fetchall()}
    new_columns = [

        ("dnf_date",       "DATETIME"),

        ("first_to_find",  "BOOLEAN DEFAULT 0"),

        ("user_flag",      "BOOLEAN DEFAULT 0"),

        ("user_sort",      "INTEGER"),

        ("user_data_1",    "TEXT"),

        ("user_data_2",    "TEXT"),

        ("user_data_3",    "TEXT"),

        ("user_data_4",    "TEXT"),

        ("bearing",        "FLOAT"),

        ("distance",       "FLOAT"),

        ("favorite_points","INTEGER"),

    ]
    for col_name, col_def in new_columns:

        if col_name not in existing:

            conn.execute(f"ALTER TABLE caches ADD COLUMN {col_name} {col_def}")

            print(f"  [migrate] Added column: caches.{col_name}")
    conn.commit()

This is called from your existing startup migration block in app.py,
after the current migration checks.

---

Changes to models.py

The new columns simply get added to the Cache class:

# Personal / user fields
dnf_date:        Mapped[Optional[datetime]] = mapped_column(DateTime)
first_to_find:   Mapped[bool]               = mapped_column(Boolean, default=False)
user_flag:       Mapped[bool]               = mapped_column(Boolean, default=False)
user_sort:       Mapped[Optional[int]]      = mapped_column(Integer)
user_data_1:     Mapped[Optional[str]]      = mapped_column(Text)
user_data_2:     Mapped[Optional[str]]      = mapped_column(Text)
user_data_3:     Mapped[Optional[str]]      = mapped_column(Text)
user_data_4:     Mapped[Optional[str]]      = mapped_column(Text)
# Computed / cached values (recalculated when center point changes)

bearing:         Mapped[Optional[float]]    = mapped_column(Float)

distance:        Mapped[Optional[float]]    = mapped_column(Float)
# Favorite points count (API only — None until API is available)

favorite_points: Mapped[Optional[int]]      = mapped_column(Integer)

The existing favorite_point bool stays —
it serves a different purpose (did I give this cache a
favorite point). favorite_points is the
community-wide count.

---

Populating dnf_date and first_to_find on import

These two fields are derived from the logs that already get imported. In your importer (importer/__init__.py), after logs are attached to the cache, add:

# Derive dnf_date from logs
dnf_logs = [l for l in cache.logs if l.log_type == "Didn't find it"
            and l.finder == current_user]
if dnf_logs:
    cache.dnf_date = max(l.log_date for l in dnf_logs if l.log_date)
# Derive first_to_find from logs

found_logs = sorted(

    [l for l in cache.logs if l.log_type == "Found it" and l.log_date],

    key=lambda l: l.log_date

)

if found_logs and found_logs[0].finder == current_user:

    cache.first_to_find = True

�

[AgreeDK]

This have been included in release 1.10.0