# CivicSS / CSKE Platform Operating Control and Loading System Snapshot > Public Atlas edition: private-local paths and the repository location have been withheld from this distribution copy. The preserved source remains unchanged and is identified by its original SHA-256 in the private evidence inventory. **Version:** 1.0.0 **Snapshot date:** August 25, 2026 **Evidence cutoff:** August 25, 2026, approximately 9:30 a.m. EDT **Owner:** Sherie Schaefer **System:** Civic Stewardship Studio™ / Civic Stewardship Knowledge Engine™ **Status:** Independent platform description, operating-process record, and evidentiary snapshot ## Document purpose This document explains the current CivicSS/CSKE platform control system in one place. It is intended to help a new reader understand: - what the database and surrounding platform are; - how database architecture, Loader PMO, loaders, independent UAT, GitHub, Master Control, and reporting windows work together; - how a source document becomes normalized, queryable, traceable data; - why loading is much more than inserting rows into PostgreSQL; - what evidence proves each stage; - which roles may make which decisions; - how accepted data becomes usable by reporters without requiring them to reverse-engineer a loader; and - what operating and intellectual-property processes were documented as of the snapshot date. This is not a live database certification, legal opinion, security audit, Town policy, or representation that every active assignment has reached its final gate. Current operational truth remains in the exact database evidence, PMO events, UAT verdicts, Git evidence, Master Control artifacts, and report-adoption records identified below. No password, token, credential value, private contact record, student-level information, or restricted personal record is included in this snapshot. ## Executive description CivicSS/CSKE is an independently developed municipal evidence and decision-support platform. Its purpose is not simply to collect public documents. It converts fragmented source evidence into a governed chain of: > physical source artifact → source-native observation → validated translation → normalized fact, event, or relationship → quality and reconciliation evidence → stable reporting contract → report-specific adoption The platform has two equally important dimensions: 1. **The technical data platform:** PostgreSQL schemas, normalized tables, source/provenance structures, constraints, views, migrations, recovery evidence, Git history, and query contracts. 2. **The operating control system:** assignments, architecture review, one-writer control, release identities, immutable packages, independent UAT, PMO routing, direct delivery receipts, Master Control reconciliation, and reporting adoption. The system is designed to prevent a common failure in civic data work: a spreadsheet, PDF, or web page may be publicly available, yet its information remains too fragmented, inconsistently defined, or poorly connected to support a reliable question. CivicSS/CSKE preserves the original evidence while creating a normalized, queryable, calculation-ready interpretation with visible limitations. The accepted database architecture assessment described CSKE as: > **STRUCTURALLY CAPABLE — MATERIAL REPORTING AND GOVERNANCE REMEDIATION REQUIRED.** That conclusion is important. The platform is substantial and has strong foundations, but it is not represented as uniformly complete or report-ready across all subjects. ## Snapshot boundary and current evidence ### Database assessment boundary The accepted comprehensive database assessment was performed read-only against PostgreSQL 18.4, database `cske_dev`, role `cske_owner`, at snapshot `POST_0356_VERIFIED_PENDING_MASTER_CONTROL` on August 24, 2026. At that point it documented: - 698 base tables; - 292 views in the physical census, including 163 reporting views; - 522 sequences; - 15,330 column records; - 10,523 constraint records, including 1,459 foreign keys; - 2,022 indexes; - primary keys on all 698 base tables; - 36 `NOT VALID` constraints with zero observed violations under bounded independent profiling; and - extensive source, provenance, reconciliation, exception, and assurance structures. Those counts are a sealed point-in-time assessment, not a claim about the exact live object count after every later release. The DB Architect's current visual-catalog work extends beyond the original assessment, but a later visual delivery is not itself a new full database-health census. ### Master Control boundary The newest directly inspected workbook was: `CSKE-Master-Control-Workbook-20260825-47.xlsx` - exact private-local path: withheld from the public Atlas edition; retained in the preserved source snapshot - size: 937,455 bytes; - SHA-256: `041a9fdbea1ef84962a6d7a609fdf89925bd49ea83eaf0a27396ce9b0f42cbdc`; - inspected structure: 75 sheets and 75 tables. Its Control Dashboard states that Master Control baseline `-47` records clean fast-forward main integration of ordered CNA releases `0356 → 0357 → 0358 → 0359` at `origin/main` commit `81ea86b971305a818c8824959dfa4fa36c551298`. It separately states that reporter adoption remains unknown or pending and that the broader CNA assignment remains open for separately governed work. The workbook also identifies a PostgreSQL release baseline accepted through `0153 / 0354 / 0355 / 0356 / 0357 / 0358 / 0359 / 0456 / 0552`. That is a Master Control statement at the workbook's evidence boundary. Later active architecture or loader preparation does not become accepted merely because a file exists. ### Git and GitHub boundary Read-only inspection of the primary repository on August 25, 2026 found: | Item | Observed state | |---|---| | Repository | Private-local repository path withheld from the public Atlas edition | | Remote | Remote repository location withheld from the public Atlas edition | | Local branch | `main` | | Local HEAD | `aa94f7db8638a50c3f67a31628d901cee80c0b64` | | Tracked `origin/main` | `81ea86b971305a818c8824959dfa4fa36c551298` | | Relationship | Local `main` was four commits behind `origin/main` | | Worktree | Not clean: untracked `outputs/` directory | | Repository license file | No `LICENSE` or `COPYING` file was found by repository-wide filename search | No pull, merge, reset, commit, push, cleanup, or other Git change was performed while preparing this document. The observed difference illustrates why feature closeout, remote integration, local synchronization, and worktree cleanliness are recorded as separate states. The repository's README identifies `cske_dev`, the primary `cske` schema, local PostgreSQL at `localhost:5432`, and `cske_owner`, with repository areas for migrations, seeds, reporting views, database tests, approved public import data, documentation, and execution/validation tools. ### Current controlled training paths The current training entry point names Loader PMO as the training authority for Loader, UAT, Master Control, Loader PMO, and DB Architect roles. The governing packages at this snapshot are: | Role | Current package | `CHECKSUMS.sha256` file hash | |---|---|---| | Loader | `CSKE-New-Loader-Window-Training-Package-v1.1.0-20260824` | `82db6592dc8039057ae0ae1e1a2dafa7c427d9499dcfcef1a45d504c199373f7` | | Independent UAT | `CSKE-UAT-Window-Training-Manual-v1.0.0-20260824` | `dc8078f094c2abcd3899a869c9a48bf6a5bfdf752917e2247d57d8b3605dff66` | | Master Control | `CSKE-Master-Control-Window-Initialization-Package-v1.2.0-20260824` | `e25440ba508daabfc120722345e7757164d2dced8fdd1f6cfda08407e276bb08` | | Loader PMO | `CSKE-Loader-PMO-Control-Window-Training-Manual-v1.0.0-20260824` | `80643946a05ac4a610a4b78025cc5e073296f3c5637cacabc701d1151ffc2ad5` | | DB Architect | `CSKE-DB-Architect-Window-Training-Manual-v1.0.1-20260824` | `bae407081222eab4cf88396dfe10df27f181396b36d8e5eb8ffa2a2e9bcd7290` | Package versions and hashes identify the training evidence used for this snapshot. A future package may supersede them through a controlled version-history and training-index update. ## Platform architecture ### The controlled data path ```mermaid flowchart LR S[Official source artifacts] --> N[Source-native evidence] N --> T[Validated translation and staging] T --> C[Conformed identities and dimensions] T --> F[Normalized facts, events, and relationships] C --> F F --> Q[Quality, reconciliation, and provenance] Q --> V[Governed versioned reporting contracts] V --> R[Report or model adoption] A[DB Architect] -. design gate .-> C P[Loader PMO] -. assignment, writer, and gate control .-> T L[Loader] -. builds and explains .-> T U[Independent UAT] -. tests exact versions .-> Q M[Master Control] -. adopts accepted evidence and publishes use contract .-> V G[GitHub] -. preserves reproducible release history .-> T ``` ### Layer 1 — Physical source artifacts The platform begins with the exact thing published or supplied: a CSV, XLSX workbook, PDF, official web page, API response, GIS release, system export, or other artifact. The loader records the publisher, title, authority, retrieval route, dates, coverage, filename, size, hash, format, and known limitations. The artifact is preserved as evidence. A later correction to normalized data does not rewrite the source. ### Layer 2 — Source-native observations Documents are decomposed into traceable observations such as: - workbook sheet, row, column, and cell; - PDF page, line, table, or region; - API record and variable; - GIS feature and source identifier; - report section, label, and source-native value; or - legal document term, party, date, amount, and locator. The native wording and value remain available even after normalization. This makes it possible to explain where a fact came from rather than asking a reporter to trust a transformed number. ### Layer 3 — Validated translation and staging Parsers and mapping rules turn source-native content into controlled candidate records. This layer records parser/mapping version, row disposition, expected populations, coercion rules, confidence, exceptions, and exclusions. Staging is not automatically report-ready. A row can exist in staging while remaining unresolved, excluded, incomplete, or awaiting semantic mapping. ### Layer 4 — Conformed identities and dimensions The platform connects source-specific labels to durable identities for municipalities, organizations, departments, funds, accounts, parcels, buildings, assets, components, projects, funding instruments, periods, units, metrics, statuses, and lifecycle stages. Names are not treated as stable joins merely because they look similar. Candidate matches remain candidates until supported by evidence. Effective-dated crosswalks are used when identities change over time. ### Layer 5 — Normalized facts, events, and relationships The core analytical layer stores each fact or event at a declared grain with typed values and explicit context. A good fact answers: - one fact about what entity or relationship; - for what time period or effective date; - at what budget, lifecycle, or publication stage; - with what numeric value and unit; - according to what source-native value; - with what missingness, verification, or assurance status; and - traceable to which source evidence. Examples of distinctions that the data model must preserve include: - budget versus actual expenditure; - request versus recommendation; - recommendation versus authorization or appropriation; - appropriation versus borrowing; - borrowing versus contractual spending; - original principal versus current outstanding principal; - projected versus estimated versus actual; - report year versus fiscal, school, calendar, measurement, effective, or as-of date; and - blank, unavailable, suppressed, not applicable, unresolved, and literal zero. ### Layer 6 — Quality, reconciliation, and provenance Every important reporting value should support this trace: > reporting row → normalized fact → source-native record/cell/page/feature → physical source artifact → official publication or retrieval Quality controls record expected versus loaded populations, source totals versus calculated totals, missing or excluded records, definition changes, unresolved conflicts, formula differences, assurance, and reconciliation results. Internal formula agreement does not make an unaudited source audited. Source assurance and internal verification are separate facts. ### Layer 7 — Governed reporting contracts Reporters should normally query versioned, documented reporting views rather than improvise joins across unstable base tables. A reporting contract identifies: - exact schema-qualified view or approved bounded query; - one-row grain; - keys and safe joins; - columns and definitions; - units, periods, lifecycle stages, and assurance; - provenance fields; - exclusions and no-double-count controls; - expected populations; - known limitations; and - contract version and breaking-change policy. A `_v1` suffix alone does not make a view governed. The architecture assessment found 163 reporting views but only 10 centralized semantic-contract records at its snapshot. Expanding and maintaining that contract catalog is a material platform priority. ### Layer 8 — Report-specific adoption Database availability does not put data into a report. A reporter must use an accepted contract, run the queries, reconcile the returned data, validate formulas and story labels, create a new controlled report release, and record adoption. This prevents a report from silently changing because a loader added or altered a database object. ## Schema ownership model The accepted architecture principles describe the following intended boundaries: - `cske`: conformed shared core, normalized cross-domain subject models, shared control structures, and governed semantic views; - `cske_atr`: bounded Annual Town Report source, translation, and subject model where its permanent separation and bridge to shared core are explicitly governed; - `cske_gis`: MassGIS release/history source grain and purpose-specific views; - `cske_wilbraham_gis`: protected candidate/legacy boundary while canonical ownership, consumer routing, sequence readiness, and decommission controls are resolved; and - any future schema: requires an explicit ownership, lifecycle, consumer, cross-schema relationship, backup/restore, and decommission rationale. Separate schemas may be justified by subject scope, size, source history, security, ownership, maintenance, or lifecycle. Schema separation alone does not make data confidential or canonical. ## The governing correction rule One of the most important controls added to the loader system is: > **Authoritative source evidence is immutable; demonstrably incorrect normalized canonical data is correctable.** The platform does not preserve known-wrong core data merely because it was loaded earlier. It also does not create a new `corrected`, `fixed`, `repair`, `revised`, version-suffixed, or date-suffixed table solely to avoid fixing the existing canonical model. Instead: 1. preserve the source artifacts, source-native observations, provenance, earlier migration, execution evidence, and failure history; 2. create a new bounded corrective migration; 3. classify affected normalized rows as update, delete, remap, merge, split, supersede, or mark unresolved; 4. repair the existing canonical tables; 5. reconcile every affected stable key and dependent relationship before and after the repair; 6. verify downstream views, queries, and reports; and 7. preserve a complete audit trail. A genuinely new table requires a separately demonstrated entity, grain, lifecycle, provenance, audit, reconciliation, or reporting-contract need. ## Roles and authority ### Sherie Schaefer — owner and material decision authority Sherie owns platform direction and material decisions. Depending on the assignment, she approves bounded scope, business interpretation, consequential identity decisions, release allocation, live mutation, publication, deployment, and Town-facing use. A narrow approval is not expanded into general authority. Sherie should not have to assemble commands, edit SQL, translate terminal output, or reconstruct a loader's technical history. ### CDX Loader PMO | Control — operating-control steward Loader PMO owns ordinary loader-program coordination: - intake and assignment design; - task creation and role training; - release and writer collision control; - architecture/UAT/Master Control routing; - exact-version gate acknowledgements; - evidence reconciliation and disposition; - assignment-state accuracy; - defects and prospective-work preservation; - release completion, assignment completion, and window availability as separate states; and - maintenance of the Loader, UAT, Master Control, Loader PMO, and DB Architect training paths. Loader PMO does not automatically write the loader, act as UAT, adopt its own work through Master Control, mutate PostgreSQL, integrate Git, deploy, publish, or decide material legal, privacy, or business questions. ### CDX DB Architect | Schema Control — design and integrity authority The DB Architect assesses the physical and logical database, maintains relational catalogs and ER diagrams, traces reporting defects to structural causes, defines target principles, and conducts architecture review for material schema changes. Architecture review is required before assignments proposing material new tables, schemas, keys, grains, relationships, cross-schema dependencies, canonical consolidation/decommission, semantic-contract changes, or shared writer ownership. The DB Architect returns design readiness, conditions, correction requirements, evidence blockers, or bounded human decisions. Architecture approval is not permission to mutate the database. ### Loader — source-to-database technical owner The loader owns the bounded technical and documentary chain: - source discovery and preservation; - source census and overlap analysis; - source-field meaning and business grain; - normalized schema fit and payload construction; - acceptance-query design; - database package construction and deep QA; - execution evidence interpretation; - correction package preparation when necessary; - Git-only closeout preparation; - database-object, source, definition, query, decision-use, and gap documentation; and - final structured handoff. The loader does not self-assign a release, silently alter Master Control, claim report adoption, or certify its own work in place of independent UAT. ### CDX UAT — independent acceptance tester UAT tests the exact checksum-sealed candidate independently. It does not edit the candidate or fix the loader's work. - **Pre-execution UAT** determines whether an exact package version is safe and sufficiently complete to execute. - **Post-execution UAT** independently tests the committed live result against the package, source, expected populations, protected boundaries, and reporting contracts. A PASS belongs only to the exact version and checksum named in the verdict. A corrected version requires new UAT. ### Git/GitHub closeout and integration Git preserves reproducible source, migrations, tests, package history, documentation, and accepted execution lineage. Git does not prove that a database load ran, that Master Control adopted it, or that a report uses it. Feature commit/push, remote equality, main integration, local synchronization, and clean worktree are separate facts. Repository mutation is isolated and scoped; main integration requires its own authority and evidence. ### CDX Master Control — evidence registrar and downstream adoption steward Master Control is the governed record of releases, sources, database objects, evidence, Git state, definitions, decisions, dependencies, open actions, and downstream adoption. It reconciles accepted evidence rather than reconstructing technical truth from chat. Master Control updates three coordinated products: 1. the current Master Control workbook; 2. the current Source Explorer/source inventory; and 3. the cumulative Reporting Data and Query Handoff. Master Control does not run the loader, invent missing evidence, silently alter a domain definition, assume Git integration, or assume reporting adoption. ### Reporting and model windows — controlled consumers Reporting windows own the queries they run, returned results, calculations, story treatment, resident-readable explanation, visual and formula QA, report release, adoption evidence, deployment, and public verification. They should use accepted reporting contracts and return wrong, missing, non-normalized, essential-missing, and helpful-missing data findings to the controlled defect and prospective-work process. ## End-to-end control flow ```mermaid flowchart TB I[Need, defect, question, or source opportunity] --> P1[Loader PMO intake and bounded assignment] P1 --> A{Material architecture trigger?} A -- Yes --> D[DB Architect design review] D --> P2[PMO accepts exact design conditions] A -- No --> P2 P2 --> C[Collision census, release reservation, one-writer freeze] C --> L1[Loader source discovery, mapping, normalization, package build] L1 --> U1[Independent pre-execution UAT] U1 -- FAIL --> P3[PMO routes bounded findings] P3 --> L1 U1 -- PASS exact version/hash --> P4[PMO acknowledges and authorizes exact execution] P4 --> E[Atomic database execution] E -- rollback or unknown --> P5[PMO contains state and routes recovery] P5 --> L1 E -- committed --> U2[Independent post-execution UAT] U2 -- FAIL --> P5 U2 -- PASS --> P6[PMO disposition] P6 --> G[Separate Git closeout and authorized integration] P6 --> M[Master Control adoption] M --> H[Source Explorer and cumulative reporting/query handoff] H --> R[Separate report/model adoption and QA] R --> X[Deployment/public verification if separately authorized] ``` No arrow is inferred merely because the prior artifact exists. Each transition requires its own evidence and authority. ## Detailed loading process ### Phase 1 — Need, defect, or opportunity intake A load begins with a business need, not a convenient table name. The initiating material should identify: - the question or decision the data could support; - the source family or evidence believed to exist; - the relevant geography, organization, asset, project, period, or other entity boundary; - whether this is new data, a refresh, a correction, a normalization gap, a reporting defect, or a source-discovery need; - known consumers and dependencies; and - privacy, distribution, or publication concerns. Prospective questions are preserved even when they cannot yet be answered. A blocked question is valuable evidence of intended analytical capability and missing source requirements. ### Phase 2 — PMO assignment, training, and authority Loader PMO determines whether a dedicated task is necessary and creates a bounded assignment with: - a permanent assignment identity; - current role training and subject-specific materials; - exact scope and non-scope; - source and mutation boundary; - authorized and prohibited actions; - expected return event; - current and next owner; - decision and escalation boundaries; and - the Window-to-PMO interface. The new window must return `TRAINING_COMPLETE` through direct task delivery. Training completion is not preparation or execution authority. ### Phase 3 — Architecture trigger and design gate Loader PMO asks whether the assignment proposes a material new table, schema, key, grain, relationship, semantic contract, shared sequence, canonical consolidation, decommission, or shared writer boundary. If so, the DB Architect assesses: - existing objects and possible reuse; - natural grain and stable keys; - source authority and provenance; - entities, periods, units, stages, values, and missingness; - physical and logical relationships; - overlap and no-double-count controls; - expected populations and reconciliations; - transaction, replay, rollback, performance, and recovery implications; - semantic reporting contracts and consumers; and - the exact human decisions still needed. The architect's return becomes a condition of assignment design. It does not authorize execution. ### Phase 4 — Collision census and one-writer control Before a release is reserved, PMO reconciles: - active assignments and tasks; - PMO register and state folders; - package filenames and permanent IDs; - repository branches/worktrees and current Git state; - live migration registry; - Master Control; - source corpus; - schemas, tables, keys, sequences, and stable views; - predecessor/successor relationships; and - reporting consumers. One writer is assigned for every affected object and key domain. Git worktrees isolate repository working copies; they do not isolate a shared live PostgreSQL database or shared Knowledge Base files. A release number is reserved only after the source, schema, identity, and collision boundary are understood. A failed or committed release number is never casually reused. ### Phase 5 — Source discovery and completeness The loader searches for the best available authoritative source, preferring: 1. official structured CSV, XLSX, API, GIS, or system exports; 2. official PDF or document when structured data is unavailable; 3. an official web page when no downloadable artifact exists; and 4. secondary reporting only for discovery or clearly qualified context. For every source, the loader records authority, publication and retrieval dates, entity and period coverage, format, exact bytes, hash, retrieval route, definitions, limitations, and distribution classification. Older evidence is not discarded merely because a later version exists. It becomes a versioned point in a time series when the business meaning supports that treatment. ### Phase 6 — Source census and overlap classification Every expected source and source-native record receives a disposition. The loader identifies whether it: - is new; - refreshes an existing source; - overlaps or duplicates accepted evidence; - supplements a missing period, entity, or field; - corrects a prior translation; - conflicts with another source; - must be excluded; - remains unresolved; or - belongs outside the assignment. This is where the platform prevents duplicate facts and double counting. Two files with different names may contain the same underlying evidence; two similar records may represent different lifecycle stages and must not be collapsed. ### Phase 7 — Acceptance-query-first design Before schema or payload work, the loader writes the questions that will prove success. Acceptance queries cover, as applicable: - exact artifact and source-record populations; - analytical facts by entity and period; - exclusion of out-of-scope entities; - primary, natural, and foreign keys; - source-to-fact lineage; - required-field completeness; - missing/zero/suppression behavior; - published-total reconciliation; - formula identities; - stable reporting-view population and grain; - protected-boundary fingerprints; and - replay adding zero duplicates or unapproved changes. If the loader cannot describe how success will be queried, the design is not ready. ### Phase 8 — Fresh database baseline and schema fit The loader inspects the current database, including: - PostgreSQL/database/schema/user identity; - migration ledger and release availability; - existing tables, columns, constraints, indexes, functions, and views; - source, artifact, entity, period, unit, definition, and reporting structures; - relevant accepted populations; - known empty structures; - prior closeouts and corrections; and - pending, failed, or superseded packages that must not be mistaken for accepted state. Existing structures are reused when their grain, identity, provenance, and constraints fit. A new table name is not evidence of a schema gap. ### Phase 9 — Freeze the normalized data contract For each fact family, the loader defines: - business question; - exact entity identity; - natural grain and stable key; - period type and value; - source stage, version, and as-of date; - measure, category, lifecycle stage, and status; - typed value and unit; - source-native value text; - assurance and verification state; - artifact and record locator; - blank, null, zero, suppressed, and not-applicable rules; - expected population and reconciliation; and - supported use and prohibited inference. The design must permit direct SQL calculations without losing source meaning. ### Phase 10 — Build source-native and normalized payloads The loader converts source evidence into controlled payloads. It preserves native observations while producing typed facts, relationships, definitions, crosswalks, exceptions, and stable-view inputs. Every row has a load, exclude, or review disposition. Numeric values are not stored only as text. Units, signs, periods, stages, missingness, and provenance accompany material values. ### Phase 11 — Construct the executable database package The standard operator package is an immutable ZIP with one obvious top-level `.command`. Its internal structure normally includes: ```text CSKE-[DOMAIN]-[SUBJECT]-[RELEASE]-v[VERSION]-[DATE]/ ├── Run-CSKE-[SHORT-NAME]-[RELEASE].command ├── README-FIRST.md ├── PACKAGE-MANIFEST.json ├── CHECKSUMS.sha256 ├── sql/ │ ├── 00-preflight.sql │ ├── 10-build.sql │ ├── 20-load.sql │ ├── 30-verify.sql │ └── 40-replay-verify.sql ├── data/ ├── sources/ ├── controls/ ├── definitions/ ├── docs/ └── scripts/ ``` The package contains no credential. It refers at runtime to the existing controlled local credential mechanism without displaying, copying, hashing, packaging, or committing the secret. Corrective package versions never silently replace earlier bytes. Every controlled file is listed and hashed. Finder metadata, caches, old outputs, backups, credentials, and unrelated content are excluded. ### Phase 12 — Deep package QA Before delivery, the loader tests the package as a product: - ZIP integrity and one executable root entry point; - manifest and checksum equality; - path handling, including spaces; - shell syntax and fail-closed behavior; - SQL parsing, explicit transactions, and error stopping; - payload encoding, delimiters, quoting, line endings, and field counts; - key, type, relationship, entity, period, unit, source-locator, and total checks; - secret and privacy scans; - first run, full verification, replay, and forced-failure rollback in an accepted-equivalent isolated environment where feasible; - migration-collision and wrong-baseline safe stops; - protected-object fingerprints; and - proof that the final success marker cannot print after a failure. Every planned test is labeled PASS, FAIL, NOT RUN, or NOT APPLICABLE with a reason. Preparation is not represented as execution. ### Phase 13 — Seal and deliver the candidate to PMO The loader creates a durable event identifying the exact package version and checksum-list hash. It then delivers the event directly to Loader PMO. Folder presence, a chat response, idle status, or writing `current owner: PMO` does not transfer ownership. If direct delivery fails, the loader records `DELIVERY_BLOCKED`, retains ownership, and retries. PMO acknowledges the exact event and routes the exact sealed candidate to independent UAT. ### Phase 14 — Independent pre-execution UAT UAT independently verifies: - exact package and seal identity; - complete source and payload inventory; - source authority, coverage, grain, keys, periods, units, missingness, and provenance; - normalized design and expected results; - mutation allowlist and protected boundaries; - source-to-payload conservation; - transaction scope and pre-commit postconditions; - actual-migration rollback under forced failure; - replay/idempotence; - stable relationships and downstream dependencies; - query contracts and performance at the bounded scope; and - absence of credentials, sensitive personal data, or unauthorized publication behavior. UAT issues an exact-version PASS or FAIL. A FAIL does not invite UAT to edit the package. PMO routes bounded findings back to the loader for a new sealed version. ### Phase 15 — Exact execution authorization An exact pre-execution PASS does not itself execute the package. Loader PMO acknowledges the verdict and separately authorizes the named version/hash when the required mutation authority exists. Any later version, failure, rollback, or post-execution FAIL suspends the earlier authorization. ### Phase 16 — Atomic database execution: what the one command actually does The authorized operator runs the single sealed `.command`. In the established operator model, Sherie may run the package and return its generated evidence; a separately governed Codex execution lane may be used only when the exact assignment and PMO authority explicitly provide it. In either case, the operator does not edit the package. The command performs the following orchestration: 1. resolves its own exact package location; 2. creates a unique timestamped results directory; 3. opens a transcript before substantive checks; 4. records package identity, version, hash, safe environment facts, database target, and start time; 5. verifies the package manifest and every checksum; 6. rejects altered, missing, or unexpected controlled files; 7. verifies the PostgreSQL client and controlled credential mechanism without exposing it; 8. confirms database, role, PostgreSQL version, prerequisites, schemas, and expected migration boundary; 9. confirms the release is unused or is present only in the exact accepted replay state; 10. begins an explicit transaction with fail-closed error handling; 11. creates or alters only the authorized objects; 12. loads exact controlled payloads; 13. runs pre-commit postconditions while rollback remains possible; 14. commits only if every required control passes; 15. runs post-load population, key, provenance, quality, reconciliation, view, and protected-boundary checks; 16. runs replay/idempotency verification where applicable; 17. creates a fresh post-load schema/catalog capture and full recovery backup at the accepted boundary; 18. verifies the recovery artifacts and records their identities and hashes; 19. derives the final result from actual transaction and control outcomes; and 20. tells the operator exactly which result file or ZIP to return. A successful transcript uses unambiguous markers such as: ```text TRANSACTION RESULT: COMMIT VERIFICATION RESULT: PASS REPLAY RESULT: PASS OVERALL PASS ``` A failure or safe stop states whether mutation began and whether any durable change remains. The script exits nonzero on failure and must not print a false PASS. ### Phase 17 — Returned execution evidence The results directory normally includes: - the timestamped transcript; - database acceptance controls; - object and population censuses; - quality exceptions and reconciliations; - execution manifest and checksums; - post-load schema/catalog capture; - full recovery backup; and - an exact return instruction. The returned file is evidence, not the conclusion. The loader records its filename, size, hash, package identity, release, timestamps, transaction, verification, replay, exit status, output inventory, and database boundary. ### Phase 18 — Loader evidence review The loader confirms the actual durable state rather than trusting the printed final line. It checks: - expected migration appears exactly once; - actual commit or rollback; - source, fact, relationship, definition, exception, and view populations; - conservation from source-native rows through normalized facts; - no out-of-scope entities or periods; - uniqueness, foreign keys, required fields, units, and codes; - totals, formulas, signs, rounding, and denominators; - provenance and definitions; - stable reporting surface and example queries; - replay; and - backup/catalog integrity. Possible conclusions include: - `DATABASE ACCEPTED — REPLAY PASS`; - `DATABASE ACCEPTED — REPLAY NOT APPLICABLE`, with reason; - `COMMITTED — VERIFICATION INCOMPLETE`; - `FAILED — ROLLED BACK`; - `SAFE STOP — NO DATABASE CHANGE`; or - `NOT CERTIFIED — INCOMPLETE OR CONTRADICTORY EVIDENCE`. Git closeout does not begin unless database evidence is accepted. ### Phase 19 — Independent post-execution UAT Post-execution UAT independently verifies the committed live state against the exact package and execution evidence, including: - migration and checksum identity; - row, value, key, relationship, provenance, and reconciliation equality; - absence of out-of-scope changes; - protected fingerprints; - reporting views and supplied queries; - explicit publishability limitations; and - replay and migration-registry behavior. UAT returns its verdict directly to PMO. PMO then reconciles the loader and UAT evidence and issues the release disposition. ### Phase 20 — Failure, rollback, and correction Failed attempts remain in the lineage. The recovery process: 1. preserves the exact failed package and evidence; 2. identifies the first causal failure; 3. proves the durable database state; 4. determines whether the release remains uncommitted, committed, or unknown; 5. corrects the smallest appropriate boundary; 6. creates a new immutable package version; 7. repeats deep QA and exact-version UAT; and 8. marks the older version superseded and not executable. No one asks Sherie to patch a script manually or rerun an ambiguous older package. If a committed normalized fact is later proved wrong, a new bounded corrective migration repairs the canonical core in place while preserving source and execution history. ### Phase 21 — Separate Git-only closeout The Git closeout is intentionally separate from the database package. It is password-free and bound to the exact accepted database-evidence hash. Its command: 1. verifies its own seal and the accepted database evidence; 2. confirms repository path, branch, baseline, and controlled scope; 3. stops if unrelated or ambiguous work overlaps; 4. confirms required release files and excludes prohibited files; 5. scans for credentials, PII, backups, and unintended bulk output; 6. stages only the exact intended files; 7. records the staged inventory and diff summary; 8. commits with the governed release message; 9. pushes to the authorized branch/remote; 10. proves local commit equals remote; and 11. proves a clean tree or records every explicitly accepted inherited exception. The loader reviews the returned Git evidence before declaring `GIT CLOSED`. Feature Git closeout is not necessarily main integration. `COMPLETED_AWAITING_INTEGRATION` means the bounded feature branch and release evidence are complete, but the authorized integration into the controlling main branch has not yet occurred. Master Control and reporting must not infer main integration from a feature push. ### Phase 22 — Final source, Master Control, and reporting handoff After database acceptance and Git closeout, the loader creates a self-contained handoff that includes: - final release and acceptance control; - source register and provenance; - definitions and data dictionary; - database objects and populations; - quality exceptions and reconciliations; - source-to-object-to-business-question mapping; - reporting query and data-use manual; - remaining gaps and next work; - all-sheet Master Control impact matrix; - release-number and predecessor/successor state; - package, transcript, commit, query, backup, and catalog identities/hashes; and - manifest and checksums. The loader describes actual questions and calculations the data supports. “Useful for reporting” is not adequate documentation. ### Phase 23 — Master Control adoption Loader PMO routes accepted/formally disposed work to Master Control. Master Control: 1. opens the current controlling workbook rather than choosing by filename alone; 2. verifies the PMO disposition and exact package/evidence identities; 3. reviews every workbook sheet for add, update, or no change; 4. allocates live central IDs above the current high-water; 5. preserves prior history and records supersession rather than erasing it; 6. reconciles database, Git, source, definition, query, adoption, dependency, and gap states; 7. updates the Source Explorer/source inventory; 8. updates the cumulative Reporting Data and Query Handoff; 9. verifies that all three products agree; and 10. returns a sealed adoption or rejection event to PMO. The three controlled outputs must agree on release identity, acceptance date, database and Git state, source and population counts, objects and views, definitions, query routes, limitations, ranges, gaps, and adoption status. ### Phase 24 — Reporting handoff and use The cumulative reporting handoff allows another reporting window to understand the new data without reading loader SQL or chat history. It explains: - what information is available; - which authoritative sources supplied it; - entity, geography, period, as-of date, and row grain; - actual, budget, proposed, estimated, projected, or review status; - accepted views and exact query routes; - definitions, units, signs, and formulas; - known limitations and gaps; - safe questions and calculations; - prohibited inferences; and - whether a named report has adopted it. It also provides copyable read-only SQL for release checkpoints, populations, time series, entity drill-down, reconciliation, definitions, provenance, missingness, safe joins, decision-use aggregates, and adoption checks. A new or uncertain reporting window receives the full cumulative baseline. Delta-only delivery is safe only when the window identifies and acknowledges the immediate predecessor. The reporter then: 1. queries the accepted contract fresh; 2. compares results with the handoff examples; 3. validates calculations and labels; 4. records wrong, missing, non-normalized, essential-missing, and helpful-missing data; 5. builds a new governed report release; 6. performs report-specific data, formula, visual, story, accessibility, disclaimer, and link QA; 7. records exact adoption; and 8. separately records Git, deployment, public verification, and official-use status. ### Phase 25 — Final ownership transfer and closeout Every material return has two parts: 1. durable sealed evidence in the controlled Knowledge Base state; and 2. successful direct task-to-task delivery to Loader PMO. PMO must acknowledge the event ID, exact version/hash, database state, next owner/action, and prohibited actions. Until then, the sender retains ownership. PMO records these conclusions separately: - release complete; - overall assignment complete; - Master Control adopted; - handoff published; - reporting adopted; - Git integrated; - deployed/publicly verified; and - task/window available. ## Why the loading process feels like “magic” The visible action may be one command, but the value comes from the invisible preparation and evidence chain. ### 1. It preserves the source before interpreting it The exact source bytes and native wording survive. A later interpretation can be corrected without destroying what the source actually said. ### 2. It turns layout into meaning A PDF page, workbook tab, GIS feature, or API row is decomposed into identifiable observations. Headings, footnotes, units, periods, stages, and source locators become data rather than disappearing during transcription. ### 3. It turns strings into governed facts The system converts source-native text into typed values connected to units, periods, entities, lifecycle stages, definitions, and assurance. This is what makes calculation and safe comparison possible. ### 4. It connects data across time without erasing differences Instead of creating one column or table for each year, periods and versions normally become rows. Definition changes, source editions, and lifecycle stages remain explicit, allowing true time series without pretending every period means exactly the same thing. ### 5. It preserves the path back to evidence A reporter can move from a chart value back to the view, normalized fact, source-native cell/page/feature, and exact official artifact. This is what makes the system explainable and correctable. ### 6. It defines success before writing data Acceptance queries are designed before the load. The loader knows how many records should exist, which identities and periods are allowed, how totals reconcile, and how replay behaves before a live mutation occurs. ### 7. It packages many controls behind one safe operator action The `.command` verifies the package, environment, baseline, transaction, payload, verification, replay, evidence, catalog capture, and recovery backup. The operator does not need to know the internal order or assemble commands. ### 8. It does not let the builder certify itself Independent UAT tests the exact package before execution and the committed state afterward. PMO controls routing and version identity. A later correction cannot inherit an earlier PASS. ### 9. It translates technical work into human use The final handoff explains the data as business content: what it is, how to query it, what questions it answers, what it cannot prove, and what remains missing. That is what turns a load into reusable decision infrastructure. ### Illustrative transformation Consider a capital item appearing in a workbook: | Source form | Controlled platform representation | |---|---| | Workbook file | Hashed physical artifact with publisher, version, dates, and coverage | | Tab, row, and cell | Source-native observation with exact locator and label | | “Roof replacement,” `$1,250,000`, FY2029 | Candidate project/component identity, typed currency value, fiscal period, source stage, and native wording | | Five-year capital-plan appearance | Planning observation, not proof of appropriation, contract, spending, or completion | | Relationship to a building | Evidence-backed component/facility relationship or an unresolved candidate—not a name-only assumption | | Reporting result | Accepted view at a declared grain with provenance, units, stage, and exclusion controls | | Resident story | Layered explanation stating what is planned, what remains unapproved, why timing matters, and what evidence is missing | The system's value lies in preserving every distinction while making the complete chain queryable. ## Evidence states that must never be collapsed | State | What it proves | What it does not prove | |---|---|---| | Source acquired | Exact evidence is available | It has been normalized or accepted | | Source parsed | Source-native observations exist | Semantic mapping is correct | | Package prepared | Executable candidate exists | It ran or is safe to run | | Pre-execution UAT PASS | Exact version/hash passed bounded testing | Database mutation occurred | | Database COMMIT | A durable mutation occurred | All verification passed | | Post-execution UAT PASS | Committed state matches accepted contract | Git or Master Control closed | | Feature Git push | Release history reached the named remote branch | Main contains it | | Main integration | Controlling branch includes the release | Local clones are current or clean | | Master Control adoption | Portfolio/source/query records reflect the accepted release | A report uses the data | | Reporting adoption | Named report release uses and validates the data | It is deployed or official | | Deployment | Artifact is available at a target | Public link or official endorsement exists | | Public verification | Named URL was independently verified | Town adoption or certification exists | ## Current strengths The accepted assessment and current control system establish several material strengths: - strong primary-key coverage and extensive physical relationships; - immutable source and provenance patterns across major domains; - typed normalized structures for many financial, debt, capital, school, property, and source-control subjects; - explicit reconciliation, exception, assurance, and missing-data treatment; - versioned loader, UAT, PMO, Master Control, and architecture training; - acceptance-query-first and replay/rollback expectations; - one-writer collision control across parallel tasks; - separate database, Git, Master Control, reporting, and deployment gates; - controlled correction of wrong normalized facts without corrupting source lineage; - post-load catalog capture and recovery backup requirements; - independent UAT before and after live mutation; - direct-delivery receipt and sender-retains-ownership fail-safe; and - cumulative reporting handoffs that connect technical data to actual questions and safe SQL. ## Current limitations and active remediation themes The platform description must also preserve known limitations: - At the accepted assessment boundary, 38 of 51 `cske.atr_*` tables were empty. Annual Report source discovery and population time series did not mean all recurring subjects were normalized. - The typed Annual Report subject tables lacked parent edition foreign keys at that assessment. - GIS/property-card families existed in both `cske` and `cske_wilbraham_gis`, creating canonical-routing and double-count risk pending governed consolidation/decommission work. - Only 10 of 163 reporting views were registered in the centralized semantic-contract catalog. - Hundreds of tables and views lacked database object comments. - Cross-domain project, asset, component, facility, finance, and lifecycle identity remained incomplete. - CNA data had bounded publishability and identity limitations; later releases must be consumed only through their exact Master Control data-use contracts. - Complete municipal all-fund finance and end-to-end asset/project lifecycle stories remained limited by authoritative source and identity/stage gaps. - Local `main` was behind the remote and the local worktree contained an untracked `outputs/` directory at this snapshot. - A cumulative reporting handoff visible in the historical Loading Plans area was v0.12.0 dated August 23, with later release-specific notices also present. Reporters should obtain the exact current cumulative baseline from Master Control rather than selecting a filename by intuition. These are controlled gaps, not reasons to disregard the platform. They are the reason the architecture, PMO, UAT, correction, and reporting-adoption systems exist. ## How different people can use the platform ### A resident or civic participant Use selected resident-facing reports to understand questions, evidence, changes over time, limitations, and upcoming choices. Official Town, District, state, federal, and other authoritative records continue to control. ### A reporter or analyst Begin with the current cumulative Reporting Data and Query Handoff. Use accepted views and copyable read-only SQL. Preserve units, stages, periods, missingness, assurance, provenance, and prohibited interpretations. Record adoption only for the exact report release that was tested. ### A data loader Begin with the current training index and exact PMO assignment. Do not choose a release or schema independently. Work backward from acceptance queries, preserve source evidence, normalize to a declared grain, and return complete source/query/use knowledge with the load. ### A database architect Use the accepted physical census, relational grain catalog, ER diagrams, findings, target principles, and active remediation records. Return design readiness and conditions without treating recommendations as mutation authority. ### An independent tester or auditor Use exact package versions, checksums, expected results, source/payload conservation, protected fingerprints, rollback/replay tests, live read-only checks, and reporting contracts. Do not edit the candidate or transfer a PASS to another version. ### A future municipal implementation The reusable capability consists of the general evidence, normalization, provenance, quality, control, reporting-contract, and layered-explanation methods and their implementing code/documentation. A new municipality would require its own source inventory, entities, definitions, legal/operating context, acceptance contract, and governed implementation. Wilbraham-specific facts are not a generic product substitute. ## Security, privacy, and distribution controls - Credentials are stored outside loader and Git packages and are never printed, hashed, copied, or committed. - Database backups and credential-bearing execution artifacts do not belong in ordinary Git history. - Student-level PII, private contact records, bank/payment credentials, and unnecessary personal identifiers are excluded. - Public-source status, privacy appropriateness, Git inclusion, and unrestricted redistribution are separate decisions. - Source URLs, packages, logs, SQL, archives, and transcripts receive secret/privacy review. - Reporting SQL is read-only and should use schema-qualified accepted objects. - Missing, suppressed, unresolved, and excluded values are not converted to zero for convenience. - Public availability does not create Town endorsement, audit certification, legal title, survey accuracy, or another unsupported official conclusion. ## Intellectual-property and legal snapshot ### What this record establishes This document records that, by August 25, 2026, CivicSS/CSKE had a developed operating framework including: - role-specific governance and training; - architecture-review and one-writer controls; - source preservation and provenance standards; - normalization and canonical-correction rules; - manifest/checksum/version controls; - transaction, replay, rollback, backup, and recovery practices; - pre- and post-execution independent UAT; - separate database and Git closeout processes; - direct Window-to-PMO delivery and acknowledgement controls; - Master Control, Source Explorer, and reporting-query handoffs; and - explicit separation of database availability, reporting adoption, deployment, and official use. The cited files, versions, paths, hashes, Git commits, workbook, and database assessment provide supporting chronology and evidence. This document is a synthesis and map to those records, not a substitute for them. ### Ownership boundary Copyright may protect qualifying original human-authored writing, code, graphics, documentation, and creative selection or arrangement. It does not protect public facts, ideas, procedures, processes, methods, systems, mathematical principles, or discoveries. See the U.S. Copyright Office's [What Is Copyright?](https://www.copyright.gov/what-is-copyright/) and [Ideas, Methods, or Systems](https://www.copyright.gov/circs/circ31.pdf). Trademark protection concerns names or symbols that identify the source of goods or services; it does not confer ownership of words in every context. See the USPTO's [What Is a Trademark?](https://www.uspto.gov/trademarks/basics/what-trademark). This snapshot: - does not claim ownership of public records, public data, facts, laws, mathematical principles, government source materials, third-party software, or other third-party material; - does not itself grant, assign, or license rights to another party; - does not determine whether any particular work is a work made for hire, jointly authored work, official municipal record, or legally protectable trade secret; - does not infer a repository license from the presence of a GitHub remote; - records that no repository-level `LICENSE` or `COPYING` file was found at the snapshot inspection; and - should be reviewed with the separate CivicSS/CSKE FinCom IP and Monetization Boundary Plan before any Town adoption, employment, appointment, licensing, or commercialization decision. The U.S. Copyright Office explains that employee-scope work and certain specially commissioned works can raise work-made-for-hire questions. See [Work Made for Hire](https://www.copyright.gov/register/se-hire.html). Specific facts and written agreements matter; qualified counsel should review consequential ownership or licensing decisions. ### Project notice > Civic Stewardship Studio™, CivicSS™, Civic Stewardship Knowledge Engine™, and CSKE™ are trademarks of Sherie Schaefer. Copyright © 2026 Sherie Schaefer. All rights reserved. Public records, public data, facts, laws, mathematical principles, and third-party materials retain their respective legal status and ownership. ### Independence boundary CivicSS/CSKE is an independent prototype. This snapshot does not state that it was produced, adopted, endorsed, audited, certified, or officially used by the Town of Wilbraham, the Finance Committee, or another public body. Official records control where they differ from the independent system. ## Authoritative internal evidence map ### Training root `Training / 00 START HERE - WINDOW TRAINING LIBRARY.md` (private-local path retained in the preserved source snapshot) ### Loader training `Training / Loading / CSKE-New-Loader-Window-Training-Package-v1.1.0-20260824` ### Window-to-PMO interface `PMO Control / 01 WINDOW TO PMO RETURN AND ACKNOWLEDGEMENT INTERFACE.md` ### Loader PMO training `Training / Loader PMO Control / CSKE-Loader-PMO-Control-Window-Training-Manual-v1.0.0-20260824` ### UAT training `Training / UAT / CSKE-UAT-Window-Training-Manual-v1.0.0-20260824` ### Master Control training `Training / Master Control / CSKE-Master-Control-Window-Initialization-Package-v1.2.0-20260824` Supporting SME manual: `Training / Master Control / CSKE-Master-Control-Window-Training-Manual-v1.0.0-20260820` ### DB Architect current role home `Database Architecture and Schema Control / 00 START HERE - DB ARCHITECT ROLE HOME.md` ### Accepted database assessment `Database Architecture and Schema Integrity Review / 00-START-HERE-CSKE-DATABASE-ARCHITECTURE-ASSESSMENT-v1.0.0-20260824.md` Executive health assessment: `Database Architecture and Schema Integrity Review / 21-EXECUTIVE-DATABASE-HEALTH-ASSESSMENT-v1.0.0-20260824.md` ### Current Master Control area `CSKE / Loading Plans` (private-local path retained in the preserved source snapshot) ### Repository Private-local and remote repository locations are withheld from the public Atlas edition and retained in the preserved source snapshot. ### IP and FinCom boundary plan `Naming, IP, Patents, Copyright / CSKE-CivicSS-FinCom-IP-Monetization-Boundary-Plan-v1.0.0-20260824.md` ## How to update this snapshot This file is an as-of record. Do not silently rewrite it to describe a later platform state. For a future snapshot: 1. preserve this version unchanged; 2. create a new semantic version and snapshot date; 3. identify the current training package versions and checksum-list hashes; 4. identify the exact accepted database assessment or conduct a separately authorized read-only census; 5. identify the current Master Control workbook by internal evidence, not filename alone; 6. record the exact repository branch, local and remote commits, status, and integration boundary; 7. record active architecture, loader, UAT, Master Control, and reporting gates separately; 8. update limitations and remediation themes without erasing prior conditions; 9. link predecessor and successor snapshots; and 10. seal the delivered package with an external manifest/checksum when it joins a formal evidence package. ## Final summary CivicSS/CSKE is not just a database and the loader is not just an import script. The platform is a governed evidence-conversion and reporting system. Its durable value comes from combining: - immutable source preservation; - explicit definitions, grain, identity, time, units, stages, and missingness; - typed normalized facts and relationships; - source-to-report provenance; - acceptance-query-first design; - immutable checksum-sealed packages; - atomic transaction, replay, rollback, backup, and recovery controls; - independent UAT; - one-writer PMO coordination; - reproducible Git/GitHub history; - Master Control reconciliation; and - human-readable query and decision-use handoffs for reporting. That operating system allows a source to become reusable civic knowledge without concealing uncertainty, losing the path back to evidence, or confusing technical availability with public readiness. --- **Legal and evidentiary boundary:** This document is a practical internal platform description and chronology record. It is not legal, tax, accounting, municipal, security, or database-audit advice. Exact primary evidence and current controlled status records govern.