# CSKE School 0400 — Data Use and Query Manual

Version: `v0.1.0`  
Date: `2026-08-19`  
Migration: `0400_SCHOOL_ORGANIZATION_IDENTITY_V01`  
Database/schema: `cske_dev / cske`  
Execution state: database accepted; Git pending; standalone query prepared/not run

> **DRAFT INDEPENDENT PROTOTYPE — NOT A TOWN OF WILBRAHAM OR HWRSD SYSTEM**
>
> Official records and authoritative sources always control. This release contains public organization-level information only. It contains no student-level data and does not establish the legal or financial terms of a Middle School agreement.

## 1. What loader 0400 adds

Loader 0400 establishes stable, source-faithful identities for the Hampden-Wilbraham Regional School District, its current and historical school codes, its two nonoperating member districts, and related charter/collaborative organizations. It also preserves current grade listings, the exact relationship fragments displayed by DESE, source-dated name/status history, and complete web-retrieval provenance.

One normalized identity observation is one source statement about one DESE-coded organization at one source date or school year. One grade observation is one source-listed grade code or grade-span statement for one organization. One relationship observation is one exact source relationship fragment between two canonical organizations. These rows are observations, not inferred legal conclusions.

The release does **not** load governed enrollment measures, staffing, finance, budgets, Chapter 70, net school spending, member assessments, facilities, capital, MSBA, ownership, debt, or scenario facts. Useful deferred fields remain in `cske.source_record.raw_record_json` for later bounded loaders.

## 2. Source families and official routes

All 38 retrievals succeeded with HTTP 200. They were captured from `2026-08-19T04:05:27Z` through `2026-08-19T15:14:24Z`, totaling 31,193,971 raw bytes. Seven redirects are preserved. Requests, final URLs, sanitized headers, logs, byte counts, SHA-256 hashes, downloader identity, and retrieval-script hash are retained for every response.

| Source publication ID | Authority / custodian | Retrievals | Time/scope | Loader use | Official landing or representative route |
|---|---|---:|---|---|---|
| `SCH0400-PUB-DESE-DIRECTORY-CURRENT` | Massachusetts Department of Elementary and Secondary Education (DESE) | 7 | Current as retrieved 2026-08-19 | Current district/school identities, addresses, organization types, and exact listed grades | `https://profiles.doe.mass.edu/help/` |
| `SCH0400-PUB-E2C-ENROLLMENT-LONGITUDINAL` | Massachusetts Education-to-Career Research and Data Hub / DESE | 4 | HWRSD source rows, school years 1992–2026 | Annual identity/name/type history; enrollment measures preserved but deferred | `https://educationtocareer.data.mass.gov/` |
| `SCH0400-PUB-HWRSD-CURRENT-SCHOOL-NETWORK` | HWRSD and six official school sites | 8 | Current local pages retrieved 2026-08-19 | HWRSD assertions, six-school gallery, and official-site corroboration | `https://www.hwrsd.org/our-schools` |
| `SCH0400-PUB-DESE-HWRSD-ORGANIZATION-PROFILES` | DESE | 10 | Current organization profiles | Organization-specific identity fields and explicit current-status statements | `https://profiles.doe.mass.edu/profiles/general.aspx?orgcode=06800000&orgtypecode=5` |
| `SCH0400-PUB-DESE-HWRSD-RELATIONSHIPS-AND-GRADES` | DESE | 2 | Current relationship and grade pages | Two P–12 member relationships, charter/collaborative fragments, district grade spans, and school counts | `https://profiles.doe.mass.edu/profiles/general.aspx?leftNavId=120&orgcode=06800000&orgtypecode=5&topNavId=1` and `leftNavId=121` |
| `SCH0400-PUB-DESE-RELATIONSHIP-COUNTERPARTIES` | DESE | 7 | Current counterparty profiles/routes | Exact identities for Hampden, Wilbraham, two charter districts, and LPVEC | Retrieval-specific URLs in `WEB-RETRIEVALS-ENRICHED.jsonl` |

Exact URLs and request methods are in `source-manifest/WEB-RETRIEVALS-ENRICHED.jsonl`. Publication membership is in `SOURCE-PUBLICATIONS.json`; source-tree relationships are in `ARTIFACT-RELATIONSHIPS.csv`; raw-file hashes are in `RAW-SHA256SUMS.txt` and `SOURCE-CORPUS-SHA256SUMS.txt`.

## 3. Scope, grain, and value-state rules

### Included

- 15 DESE-coded canonical organizations relevant to HWRSD identity and displayed relationships.
- HWRSD identity/name/type history from complete E2C HWRSD rows for school years 1992–2026.
- Current DESE district and school directory rows.
- HWRSD current school gallery and the six official school home pages.
- Fifteen DESE organization profiles and all 127 captured profile field observations.
- Current DESE grade codes, district grade spans, school counts, and one contextual HWRSD PreK–12 assertion.
- Five exact organization relationship fragments.
- Seven controlled source-quality/non-inference exceptions.
- Full source/retrieval/record lineage.

### Excluded from governed facts

- Enrollment counts, percentages, subgroups, and student characteristics.
- Staff/contact names, email addresses, or phone numbers as governed facts.
- Approximate statements such as “about 2,800 students” or “over 500 employees” as exact values.
- Finance, budgets, aid, assessments, contracts, programs, facilities, capital, MSBA, or scenarios.
- Opening or closure dates not stated by a source.
- A successor relationship between DESE codes `06800020` and `06800025`.
- A resolved direction for ambiguous charter relationships.
- Student-level or otherwise private data.

### Missing, blank, zero, and status

1. Missing is not zero.
2. A blank source field remains blank/null or a controlled presence state; it is not imputed.
3. A current “closed” banner proves current source status only; it does not prove a closure date.
4. An organization absent from a current directory is not automatically closed unless the source explicitly says so.
5. Historical positive enrollment is not relabeled as a current grade offering.
6. Source names and canonical names coexist; source differences are not reconciled away.
7. A malformed source fragment remains source evidence; parsing does not silently repair its meaning.

## 4. Provenance model

The normal lineage path is:

```text
cske.source_system
  → cske.source_artifact
  → cske.source_web_retrieval
  → cske.source_record
  → cske.sch_organization_*_observation
  → cske.core_organization
```

The raw response stays inside the accepted loader at `source_corpus/raw/`. Sidecars at `source_corpus/requests/`, `headers/`, and `logs/` preserve request descriptions, sanitized response headers, redirect evidence, and curl results. Database retrieval rows preserve both requested and final URLs, response bytes/hash, sidecar hashes, retrieval time, script identity/hash, and the no-secret review result.

Controlled manifest identities:

| Manifest | SHA-256 | Purpose |
|---|---|---|
| `WEB-RETRIEVALS-ENRICHED.jsonl` | `9a9bc1a2a4b5bfb98985a68faf5ad6989c59ff6af02ad3106ef96ddaf4c26818` | Exact 38 retrievals, URLs, HTTP metadata, bytes, hashes, sidecars, and scripts |
| `SOURCE-PUBLICATIONS.json` | `5e5dae9042813eaaebd50c3ebf209eaf82b83225ddd5e26b4d7ba2614d271522` | Six publication families and retrieval membership |
| `ARTIFACT-RELATIONSHIPS.csv` | `5a0833466760116f3792c43f8fec344205bd982e074cb68016b6deaca0f2c5a6` | 36 discovery/export/profile/corroboration relationships |
| `RAW-SHA256SUMS.txt` | `20b9eefe0248fe81b45e84462e95e2b8b60ca844a72d1904869af3f1e949457f` | Raw-response hashes |
| `SOURCE-CORPUS-SHA256SUMS.txt` | `cc5a502ae52c90ad5e98a3513896a56e4d08c3950b0760767aefec7d35f3b4a5` | Complete source-corpus sidecar and raw-file control |

## 5. Database objects

### New tables

| Object | Grain / natural key | Main relationships | Use |
|---|---|---|---|
| `cske.source_web_retrieval` | One immutable retrieval event per `source_web_retrieval_code`; one retrieval per loaded source artifact | FK to `cske.source_artifact` | Exact HTTP and retained-response provenance |
| `cske.sch_organization_identity_observation` | One source identity/status observation per source record, organization, and basis | FKs to `source_record` and `core_organization` | Source-dated organization name/type/status/address/site/history |
| `cske.sch_organization_grade_observation` | One source grade code/span observation per source record and organization | FKs to `source_record` and `core_organization` | Current exact grade codes and district grade-span/context rows |
| `cske.sch_organization_relationship_observation` | One exact source relationship fragment between a subject and counterparty | FKs to `source_record` and two `core_organization` rows | Regional membership and unresolved/source-labeled relationships |

All four tables have immutable-row triggers. No pre-existing `reporting_*_v1` view is changed.

### Reused objects

| Object | How it is used |
|---|---|
| `cske.core_organization` | Stores the 15 canonical DESE-coded organizations |
| `cske.finrpt_reporting_entity` | Existing `HWRSD` entity linked to `ORG-DESE-06800000` |
| `cske.source_system` | Adds/uses three official source systems |
| `cske.source_artifact` | Registers 38 retained source bodies |
| `cske.source_record` | Registers 492 exact parsed/retained source records |
| `cske.source_artifact_relationship` | Stores 36 source-tree relationships |
| `cske.source_artifact_quality_exception` | Stores seven controlled conflicts/non-inferences |
| `cske.control_schema_migration` | Registers the migration only after every reconciliation passes |

### Field dictionary — `cske.source_web_retrieval`

| Field(s) | Meaning / unit |
|---|---|
| `source_web_retrieval_id` | Database identity key |
| `source_web_retrieval_code` | Stable `SCH0400-RET-NNN` business code |
| `source_artifact_id` | FK to the retained response artifact |
| `source_web_retrieval_publication_code` | Publication-family code |
| `source_web_retrieval_publication_role` | Role within the publication family |
| `source_web_retrieval_canonical_landing_flag` | Boolean landing-page indicator |
| `source_web_retrieval_publisher` | Source publisher/custodian text |
| `source_web_retrieval_authority_code` | Authority classification |
| `source_web_retrieval_role` | Retrieval-level use/disposition |
| `source_web_retrieval_request_method` | HTTP method, such as GET or POST |
| `source_web_retrieval_requested_url` | Exact requested URL |
| `source_web_retrieval_final_url` | Final URL after redirects |
| `source_web_retrieval_http_status` | HTTP status integer |
| `source_web_retrieval_redirect_count` | Redirect count |
| `source_web_retrieval_retrieved_at` | Timestamp with time zone |
| `source_web_retrieval_response_content_type` | Response media type/encoding text |
| `source_web_retrieval_response_bytes` | Retained response size in bytes |
| `source_web_retrieval_response_sha256` | SHA-256 of retained response |
| `source_web_retrieval_request_metadata_file`, `..._sha256` | Request sidecar and hash |
| `source_web_retrieval_sanitized_header_file`, `..._sha256` | Sanitized header sidecar and hash |
| `source_web_retrieval_log_file`, `..._sha256` | Retrieval log and hash |
| `source_web_retrieval_downloader_identity` | Curl/tool version identity |
| `source_web_retrieval_script_name`, `..._sha256` | Acquisition script identity and hash |
| `source_web_retrieval_secret_review_status_code` | Credential/cookie retention review result |
| `source_web_retrieval_outcome_code` | Success/failure disposition |
| `source_web_retrieval_raw_json` | Exact structured retrieval metadata |
| `source_web_retrieval_created_at` | Database insertion timestamp |

### Field dictionary — identity observations

| Field(s) | Meaning / unit |
|---|---|
| `sch_org_identity_obs_id`, `sch_org_identity_obs_code` | Database key and stable observation code |
| `source_record_id` | FK to the exact parsed source record/locator |
| `business_organization_id` | FK to canonical organization |
| `observation_basis_code` | Source/basis, e.g. current directory, current profile, or annual E2C fields |
| `period_school_year` | School year integer when the source row is annual; otherwise null |
| `observed_as_of_date` | Source observation/retrieval date |
| `source_dese_org_code` | Exact eight-digit DESE code |
| `source_organization_name` | Source-displayed name |
| `source_organization_type` | Source-displayed organization type |
| `source_status_code`, `source_status_text` | Source-preserved status fields |
| `source_address_text`, `source_website_text` | Source-displayed public address/site |
| `source_nces_id` | Source NCES identifier, if present |
| `source_listed_current_flag` | Source-listed-current Boolean, if determinable |
| `source_current_closed_flag` | Explicit current-closed Boolean only |
| `control_identity_resolution_status_code` | Mapping/resolution disposition |
| `source_row_sha256` | Hash of the source row/fragment |
| `sch_org_identity_obs_created_at` | Database insertion timestamp |

### Field dictionary — grade observations

| Field(s) | Meaning / unit |
|---|---|
| `sch_org_grade_obs_id`, `sch_org_grade_obs_code` | Database key and stable observation code |
| `source_record_id`, `business_organization_id` | Exact source record and canonical organization FKs |
| `observation_basis_code`, `observed_as_of_date` | Source basis and date |
| `source_grade_span_label` | Source-displayed span category, if applicable |
| `source_grade_code` | Exact source grade code |
| `normalized_grade_code` | Controlled normalized code for the same source grade |
| `source_grades_offered_text` | Exact source grade-offering text |
| `normalized_grade_codes_text` | Controlled normalized list where applicable |
| `source_number_of_schools` | Source-reported count, an integer count not enrollment |
| `control_scope_disposition` | Governing interpretation/boundary |
| `source_row_sha256` | Hash of the source row/fragment |
| `sch_org_grade_obs_created_at` | Database insertion timestamp |

### Field dictionary — relationship observations

| Field(s) | Meaning / unit |
|---|---|
| `sch_org_relationship_obs_id`, `sch_org_relationship_obs_code` | Database key and stable observation code |
| `source_record_id` | FK to exact source fragment |
| `subject_organization_id`, `counterparty_organization_id` | Canonical organization FKs; they must differ |
| `source_relationship_type_text` | Exact source relationship label |
| `source_relationship_scope_text` | Exact scope, including `P-12` where supplied |
| `business_relationship_code` | Controlled relationship category |
| `control_direction_resolution_status_code` | Direction resolution/non-resolution disposition |
| `source_counterparty_name`, `source_counterparty_org_type_code`, `source_counterparty_href` | Exact counterparty fragment text/link |
| `source_row_sha256`, `source_link_element_sha256` | Source fragment and link-element hashes |
| `sch_org_relationship_obs_created_at` | Database insertion timestamp |

## 6. Accepted populations and controls

| Population/control | Expected | Accepted actual | Variance | Evidence |
|---|---:|---:|---:|---|
| Source systems | 3 | 3 | 0 | Accepted transcript |
| Source artifacts | 38 | 38 | 0 | Accepted transcript |
| Web retrieval events | 38 | 38 | 0 | Accepted transcript |
| Artifact relationships | 36 | 36 | 0 | Accepted transcript |
| Source-quality exceptions | 7 | 7 | 0 | Accepted transcript |
| Canonical organizations | 15 | 15 | 0 | Accepted transcript |
| Source records | 492 | 492 | 0 | Accepted transcript |
| Identity observations | 304 | 304 | 0 | Accepted transcript |
| Grade observations | 43 | 43 | 0 | Accepted transcript |
| Organization relationships | 5 | 5 | 0 | Accepted transcript |
| Annual E2C identity rows | 270 | 270 | 0 | School years 1992–2026 |
| Explicit current-closed observations | 2 | 2 | 0 | Memorial and Thornton Burgess profiles |
| P–12 regional member relationships | 2 | 2 | 0 | Hampden and Wilbraham nonoperating districts |
| Unresolved charter-direction rows | 2 | 2 | 0 | Preserved without inference |
| Duplicate response hashes | 0 | 0 | 0 | Static/source-profile validation |
| Orphan retrievals | 0 | 0 | 0 | Static/source-profile validation |

First-run live insert sequence:

```text
source_system 3
source_artifact 38
source_web_retrieval 38
source_artifact_relationship 36
source_artifact_quality_exception 7
core_organization 15
finrpt_reporting_entity link update 1
source_record 492
sch_organization_identity_observation 304
sch_organization_grade_observation 43
sch_organization_relationship_observation 5
control_schema_migration 1
```

Replay insert sequence: `NOT RUN`. The migration is coded for exact idempotency, but no replay transcript exists.

## 7. Copyable read-only SQL

### 7.1 Migration acceptance and exact populations

```sql
SELECT migration_code,
       migration_name,
       migration_checksum,
       applied_at,
       applied_by
FROM cske.control_schema_migration
WHERE migration_code = '0400_SCHOOL_ORGANIZATION_IDENTITY_V01';

SELECT
  (SELECT count(*) FROM cske.source_artifact
    WHERE source_artifact_code LIKE 'SCH0400-ART-%') AS source_artifacts,
  (SELECT count(*) FROM cske.source_web_retrieval
    WHERE source_web_retrieval_code LIKE 'SCH0400-RET-%') AS web_retrievals,
  (SELECT count(*) FROM cske.source_record
    WHERE source_record_code LIKE 'SCH0400.%') AS source_records,
  (SELECT count(*) FROM cske.sch_organization_identity_observation
    WHERE sch_org_identity_obs_code LIKE 'SCH0400-%') AS identity_observations,
  (SELECT count(*) FROM cske.sch_organization_grade_observation
    WHERE sch_org_grade_obs_code LIKE 'SCH0400-%') AS grade_observations,
  (SELECT count(*) FROM cske.sch_organization_relationship_observation
    WHERE sch_org_relationship_obs_code LIKE 'SCH0400-%') AS relationship_observations;
```

### 7.2 Return all governed rows from every new 0400 table

```sql
SELECT *
FROM cske.source_web_retrieval
WHERE source_web_retrieval_code LIKE 'SCH0400-RET-%'
ORDER BY source_web_retrieval_code;

SELECT *
FROM cske.sch_organization_identity_observation
WHERE sch_org_identity_obs_code LIKE 'SCH0400-%'
ORDER BY business_organization_id,
         period_school_year NULLS LAST,
         observation_basis_code,
         sch_org_identity_obs_code;

SELECT *
FROM cske.sch_organization_grade_observation
WHERE sch_org_grade_obs_code LIKE 'SCH0400-%'
ORDER BY business_organization_id,
         observation_basis_code,
         sch_org_grade_obs_code;

SELECT *
FROM cske.sch_organization_relationship_observation
WHERE sch_org_relationship_obs_code LIKE 'SCH0400-%'
ORDER BY sch_org_relationship_obs_code;
```

### 7.3 Canonical organizations and current HWRSD schools

```sql
SELECT organization_code,
       organization_name,
       organization_type,
       record_status,
       source_evidence_status
FROM cske.core_organization
WHERE organization_id LIKE 'ORG-DESE-%'
ORDER BY organization_code;

SELECT o.organization_code,
       o.organization_name,
       i.source_organization_name,
       i.source_address_text,
       i.source_website_text,
       i.observed_as_of_date
FROM cske.sch_organization_identity_observation i
JOIN cske.core_organization o
  ON o.organization_id = i.business_organization_id
WHERE i.observation_basis_code = 'CURRENT_DESE_DIRECTORY'
  AND i.source_organization_type = 'Public School'
ORDER BY o.organization_code;
```

### 7.4 Complete Wilbraham Middle identity history

```sql
SELECT i.observation_basis_code,
       i.period_school_year,
       i.observed_as_of_date,
       i.source_organization_name,
       i.source_organization_type,
       i.source_status_code,
       i.source_status_text,
       i.source_address_text,
       i.source_website_text,
       a.source_artifact_code,
       a.local_file_name,
       r.source_locator
FROM cske.sch_organization_identity_observation i
JOIN cske.core_organization o
  ON o.organization_id = i.business_organization_id
JOIN cske.source_record r
  ON r.source_record_id = i.source_record_id
JOIN cske.source_artifact a
  ON a.source_artifact_id = r.source_artifact_id
WHERE o.organization_code = '06800310'
ORDER BY i.period_school_year NULLS LAST,
         i.observation_basis_code,
         i.sch_org_identity_obs_code;
```

### 7.5 Historical name timeline for every HWRSD code

```sql
SELECT o.organization_code,
       o.organization_name AS canonical_name,
       i.period_school_year,
       i.source_organization_name,
       i.source_organization_type
FROM cske.sch_organization_identity_observation i
JOIN cske.core_organization o
  ON o.organization_id = i.business_organization_id
WHERE i.observation_basis_code = 'ANNUAL_E2C_IDENTITY_FIELDS'
ORDER BY o.organization_code, i.period_school_year;
```

### 7.6 Regional membership and all displayed relationships

```sql
SELECT subject.organization_name AS regional_district,
       rel.source_relationship_type_text,
       rel.source_relationship_scope_text,
       member.organization_code AS member_dese_code,
       member.organization_name AS member_district,
       rel.control_direction_resolution_status_code
FROM cske.sch_organization_relationship_observation rel
JOIN cske.core_organization subject
  ON subject.organization_id = rel.subject_organization_id
JOIN cske.core_organization member
  ON member.organization_id = rel.counterparty_organization_id
WHERE rel.business_relationship_code = 'REGIONAL_DISTRICT_HAS_MEMBER_DISTRICT'
ORDER BY member.organization_code;

SELECT subject.organization_name AS subject_name,
       rel.source_relationship_type_text,
       rel.source_relationship_scope_text,
       counterparty.organization_name AS counterparty_name,
       rel.business_relationship_code,
       rel.control_direction_resolution_status_code
FROM cske.sch_organization_relationship_observation rel
JOIN cske.core_organization subject
  ON subject.organization_id = rel.subject_organization_id
JOIN cske.core_organization counterparty
  ON counterparty.organization_id = rel.counterparty_organization_id
ORDER BY rel.sch_org_relationship_obs_code;
```

### 7.7 Current listed grades for Wilbraham Middle

```sql
SELECT o.organization_name,
       g.source_grade_code,
       g.normalized_grade_code,
       g.observation_basis_code,
       g.observed_as_of_date
FROM cske.sch_organization_grade_observation g
JOIN cske.core_organization o
  ON o.organization_id = g.business_organization_id
WHERE o.organization_code = '06800310'
ORDER BY g.normalized_grade_code;
```

### 7.8 Trace any identity observation to the retrieval and physical locator

```sql
SELECT i.sch_org_identity_obs_code,
       r.source_record_code,
       r.source_record_type_code,
       r.source_locator,
       r.record_sha256,
       a.source_artifact_code,
       a.local_file_name,
       a.file_sha256,
       a.source_url,
       w.source_web_retrieval_code,
       w.source_web_retrieval_requested_url,
       w.source_web_retrieval_final_url,
       w.source_web_retrieval_retrieved_at,
       w.source_web_retrieval_response_bytes,
       w.source_web_retrieval_response_sha256
FROM cske.sch_organization_identity_observation i
JOIN cske.source_record r
  ON r.source_record_id = i.source_record_id
JOIN cske.source_artifact a
  ON a.source_artifact_id = r.source_artifact_id
JOIN cske.source_web_retrieval w
  ON w.source_artifact_id = a.source_artifact_id
WHERE i.sch_org_identity_obs_code = 'SCH0400-IDENT-DIR-06800310';
```

### 7.9 Missing, unresolved, and controlled exceptions

```sql
SELECT source_artifact_quality_exception_code,
       source_artifact_quality_exception_type_code,
       source_artifact_quality_exception_observed_value,
       source_artifact_quality_exception_controlled_resolution,
       source_artifact_quality_exception_status
FROM cske.source_artifact_quality_exception
WHERE source_artifact_quality_exception_source_module_code = 'SCHOOL_0400'
ORDER BY source_artifact_quality_exception_code;

SELECT rel.sch_org_relationship_obs_code,
       rel.source_relationship_type_text,
       rel.source_counterparty_name,
       rel.control_direction_resolution_status_code
FROM cske.sch_organization_relationship_observation rel
WHERE rel.control_direction_resolution_status_code <> 'RESOLVED'
ORDER BY rel.sch_org_relationship_obs_code;
```

### 7.10 Inspect preserved E2C values that are not yet governed enrollment facts

```sql
SELECT source_record_code,
       raw_record_json ->> 'sy' AS school_year,
       raw_record_json ->> 'org_code' AS dese_org_code,
       raw_record_json ->> 'org_name' AS source_org_name,
       raw_record_json ->> 'total_cnt' AS source_total_enrollment_text,
       raw_record_json ->> 'swd_cnt' AS source_students_with_disabilities_count_text,
       notes
FROM cske.source_record
WHERE source_record_type_code = 'E2C_HWRSD_ROW'
ORDER BY (raw_record_json ->> 'sy')::integer,
         raw_record_json ->> 'org_code';
```

These are preserved source strings, not reporting measures. Do not sum or publish them until a bounded enrollment loader defines units, blank/zero rules, subgroup changes, district/school reconciliation, and organization linkage.

### 7.11 Object and column catalog

```sql
SELECT table_name,
       ordinal_position,
       column_name,
       data_type,
       is_nullable
FROM information_schema.columns
WHERE table_schema = 'cske'
  AND table_name IN (
    'source_web_retrieval',
    'sch_organization_identity_observation',
    'sch_organization_grade_observation',
    'sch_organization_relationship_observation'
  )
ORDER BY table_name, ordinal_position;
```

### 7.12 Reconciliation/control query

```sql
SELECT 'source_artifact' AS population, count(*) AS actual, 38 AS expected
FROM cske.source_artifact
WHERE source_artifact_code LIKE 'SCH0400-ART-%'
UNION ALL
SELECT 'source_web_retrieval', count(*), 38
FROM cske.source_web_retrieval
WHERE source_web_retrieval_code LIKE 'SCH0400-RET-%'
UNION ALL
SELECT 'source_record', count(*), 492
FROM cske.source_record
WHERE source_record_code LIKE 'SCH0400.%'
UNION ALL
SELECT 'identity_observation', count(*), 304
FROM cske.sch_organization_identity_observation
WHERE sch_org_identity_obs_code LIKE 'SCH0400-%'
UNION ALL
SELECT 'grade_observation', count(*), 43
FROM cske.sch_organization_grade_observation
WHERE sch_org_grade_obs_code LIKE 'SCH0400-%'
UNION ALL
SELECT 'relationship_observation', count(*), 5
FROM cske.sch_organization_relationship_observation
WHERE sch_org_relationship_obs_code LIKE 'SCH0400-%'
ORDER BY population;
```

## 8. Prepared all-data query command

Filename: `Run-CSKE-School-0400-All-Data-Query-v0.1.0-20260819.command`  
State: `PREPARED / NOT RUN`

The command uses a read-only PostgreSQL session and exports complete CSVs for the migration row, canonical organizations, source systems, artifacts, web retrievals, artifact relationships, quality exceptions, source records, identity observations, grade observations, relationship observations, HWRSD reporting-entity link, and object/column catalog. It writes a timestamped transcript and manifest. It performs no database or Git write.

## 9. Business use and Middle School analysis placement

### What 0400 helps answer

- Which stable DESE code represents Wilbraham Middle School?
- Which organization represents HWRSD, and which two member-district identities are displayed as P–12 members?
- Which six schools are currently listed by DESE/HWRSD?
- What source-dated names/statuses exist for current and historical school codes?
- Which current grades are listed for Wilbraham Middle?
- Which exact source, response, record, and locator supports an identity or relationship statement?
- Which source conflicts or non-inferences must remain visible?

### What 0400 cannot answer

- Who legally owns or controls Wilbraham Middle School property.
- What the regional agreement says, how it would be amended, or how costs would be allocated.
- Whether a transfer or regionalization vote is legally sufficient.
- Current or projected enrollment, class size, staffing, program, or service effects.
- Building condition, capacity, utilization, project scope, MSBA eligibility, reimbursement, or schedule.
- Operating costs, capital costs, debt service, member assessment, tax effect, reserves, or cash timing.
- Whether any Middle School option is preferable, affordable, or recommended.

### Recommended decision sequence

1. Use 0400 only to resolve stable organization/school identities and source relationships.
2. Add bounded, reconciled enrollment history keyed to these identities.
3. Add source-backed finance, assessment, staffing/program, and service data.
4. Add facility/space/condition/capacity, project/MSBA, ownership/agreement, and governance evidence.
5. Build same-scope status-quo and alternative lifecycle cash-flow/service scenarios.
6. Apply legal-capacity, recurring-budget, cash, capital/debt, household, and service-consequence tests separately.

The strongest next source bridge is a bounded HWRSD enrollment-history loader using the 270 preserved E2C source rows and the 44-field dictionary already captured. It must define measure grain, units, blank/zero rules, subgroup changes, district/school reconciliation, and 0400 organization linkage before receiving number 0401.

## 10. Interpretation and double-counting rules

1. Source identity observation is not a legal finding.
2. Current status is not a source-supported lifecycle date unless the date is explicitly supplied.
3. Current grade listing is not historical enrollment.
4. District-wide facts are not a Wilbraham member share.
5. Organization, building, parcel, program, and agreement are different entities.
6. A current school list and a count of maintained school buildings are different grains.
7. Charter/collaborative relationship rows must not be treated as regional-member rows.
8. Canonical organization rows must not be counted again as separate facts when joined to multiple source observations.
9. Deferred raw JSON fields are source evidence, not governed measures.
10. Missing is not zero, and unresolved is not false.

## 11. Next-release collection and change detection

For a future identity refresh:

1. Use the same official landing/search/profile routes and exact organization codes.
2. Preserve the request method, nonsecret parameters, requested/final URL, redirects, HTTP status, sanitized headers, retrieval time, content type, bytes, SHA-256, downloader, and script hash.
3. Retain changed bytes even when the URL is unchanged.
4. Link new retrievals to the prior artifact as revised/superseding evidence; do not overwrite history.
5. Rebuild offline and compare parser results deterministically.
6. Reconcile the exact current directory/profile/relationship/grade populations.
7. Do not automatically update a canonical status or infer a date from disappearance.

The E2C dataset should be appended by release or school year without overwriting prior rows. Each later loader must preserve source version and field-definition changes.

## 12. Privacy, copyright, and publication boundary

- Data classification: public-source organization-level information plus internal control metadata.
- Student-level data: none.
- Credentials/cookies/tokens: none retained; every retrieval passed the no-secret review.
- Public contact fields: preserved only where required for source fidelity and not promoted as a 0400 governed business fact.
- Raw official pages/data remain subject to their source terms; repository use is for controlled evidence/reproducibility.
- Any public report must cite official sources, disclose data dates and limitations, and apply the current independent-prototype disclaimer.

---

Copyright © 2026 Sherie Schaefer. All rights reserved. Civic Stewardship Studio™, CivicSS™, Civic Stewardship Knowledge Engine™, and CSKE™ are trademarks of Sherie Schaefer.