Product management

CREATE ONTOLOGY sercha_product;

-- ROOT. Every artefact in the corpus is exactly one document kind.
-- A release note is a document in its own right, not an attribute of a feature.
CREATE ENTITY sercha_product.ProductDocument (
  doc_title       TEXT NOT NULL
    EXTRACT 'the title as printed at the top of the document, excluding any status prefix such as DRAFT or ARCHIVED',
  doc_type        TEXT NOT NULL CHECK (doc_type IN
    ('prd','tech_spec','rfc','decision_record','roadmap','feedback_report','release_note','postmortem')),
  revision        TEXT
    EXTRACT 'the version label as written, e.g. v2 or Rev C; use the string draft where the document is unversioned and still in draft',
  status          TEXT CHECK (status IN
    ('draft','in_review','approved','shipped','superseded','abandoned')),
  authored_on     DATE
    EXTRACT 'the date the document was written or last materially revised; not the date it was exported',
  decision_due_on DATE
    EXTRACT 'on an RFC or decision record only: the date by which a decision was meant to be made',
  owner_name      TEXT
    EXTRACT 'the single named document owner or driver, as written in the header block; not the whole reviewer list',
  target_release  TEXT
    EXTRACT 'the release or milestone label the document targets, e.g. 2026.Q1 or Release 14; null where the document names none',
  primary_feature_name TEXT
    EXTRACT 'the feature this document is chiefly about, in the document''s own wording; this is the field the about_feature edge resolves on',
  -- Only populated on decision records and RFCs that replace an earlier one.
  supersedes_ref  TEXT
    EXTRACT 'the identifier of the decision or RFC this document replaces, copied verbatim from the header, e.g. ADR-0042',
  decision_ref    TEXT
    EXTRACT 'this document''s own decision identifier where it has one, e.g. ADR-0051 or RFC-118',
  -- Title alone collides across a PRD and its spec; revision distinguishes v1 from v2.
  KEY (doc_title, doc_type, revision)
) ROOT;

-- Resolution target for feature-name drift. Keyed on the name alone, because
-- collapsing every spelling into one node is the whole reason this exists.
CREATE ENTITY sercha_product.Feature (
  feature_name    TEXT KEY
    EXTRACT 'the feature as named, in title case, stripped of surrounding words like support for or improvements to',
  surface_area    TEXT CHECK (surface_area IN
    ('web_app','mobile_app','public_api','admin_console','data_pipeline','notifications','billing','platform')),
  lifecycle_stage TEXT CHECK (lifecycle_stage IN
    ('proposed','committed','in_build','beta','generally_available','deprecated','withdrawn')),
  is_customer_facing BOOLEAN
);

-- UNDER ProductDocument: requirements are structural children of the file they
-- are written in. Composite key because REQ-1 exists in almost every PRD.
CREATE ENTITY sercha_product.Requirement (
  requirement_ref     TEXT
    EXTRACT 'the reference as printed, e.g. REQ-4 or R2.1; null where the document numbers nothing',
  statement           TEXT NOT NULL
    EXTRACT 'the requirement as one sentence in the document''s own words; do not rewrite it into a user story',
  priority            TEXT NOT NULL CHECK (priority IN
    ('must_have','should_have','could_have','wont_have_this_release')),
  requirement_kind    TEXT CHECK (requirement_kind IN
    ('functional','non_functional','constraint','compliance','accessibility','performance')),
  acceptance_criteria TEXT
    EXTRACT 'the stated test for done, where the document gives one; leave null rather than inventing a plausible criterion',
  in_scope_first_release BOOLEAN
    EXTRACT 'true where the document explicitly places the requirement in the first release; false where it is deferred or listed under out of scope',
  feature_name        TEXT
    EXTRACT 'the feature this requirement belongs to, as named in this document; the wording will differ between documents and that is expected',
  KEY (requirement_ref, statement)
) UNDER sercha_product.ProductDocument;

-- Its own entity, not columns: a document states zero to six metrics, and each
-- has a baseline, a target and a window that only mean anything together.
CREATE ENTITY sercha_product.SuccessMetric (
  metric_name      TEXT NOT NULL
    EXTRACT 'the metric as named, e.g. weekly active teams or median time to first digest',
  metric_kind      TEXT CHECK (metric_kind IN
    ('adoption','engagement','retention','revenue','efficiency','quality','guardrail')),
  baseline_value   FLOAT
    EXTRACT 'the stated current value; null where the document sets a target without naming a baseline',
  target_value     FLOAT,
  unit             TEXT CHECK (unit IN
    ('percent','count','currency','seconds','days','ratio','score')),
  measurement_window_days INTEGER
    EXTRACT 'the period over which the target is to be judged, in days; convert a stated quarter to 90',
  KEY (metric_name, metric_kind)
) UNDER sercha_product.ProductDocument;

-- BOUNDED PER DOC: a decision record states one decision, an RFC at most a few.
CREATE ENTITY sercha_product.Decision (
  decision_summary TEXT NOT NULL
    EXTRACT 'the decision itself in one sentence, in the active voice: what was chosen, not what was discussed',
  decision_status  TEXT NOT NULL CHECK (decision_status IN
    ('proposed','accepted','rejected','deferred','superseded')),
  decided_on       DATE,
  rationale        TEXT
    EXTRACT 'the stated reason the option was chosen, not a summary of the whole document',
  reversibility    TEXT CHECK (reversibility IN ('one_way_door','two_way_door','unclear'))
    EXTRACT 'whether the document treats the decision as cheap to reverse; judge on substance where it is not stated in those words',
  KEY (decision_summary, decision_status)
) BOUNDED PER DOC UNDER sercha_product.ProductDocument;

-- Customer input as extracted from feedback reports and PRD appendices.
-- Zero to many per document, each with its own source and its own strength.
CREATE ENTITY sercha_product.CustomerRequest (
  request_summary  TEXT NOT NULL
    EXTRACT 'what the customer asked for, in one sentence, keeping their framing rather than the product team''s',
  account_tier     TEXT CHECK (account_tier IN
    ('enterprise','mid_market','small_business','free','internal','unknown')),
  request_source   TEXT CHECK (request_source IN
    ('support_ticket','sales_call','user_interview','survey','community_forum','advisory_board','churn_review')),
  severity         TEXT CHECK (severity IN ('blocker','major','minor','nice_to_have')),
  is_churn_risk    BOOLEAN
    EXTRACT 'true only where the document states the account raised this in the context of renewal risk or cancellation',
  requested_feature_name TEXT
    EXTRACT 'the feature the request maps to, using the customer''s or the report author''s wording; leave null where the request names no feature',
  KEY (request_summary, request_source)
) UNDER sercha_product.ProductDocument;

-- Named people, for ownership and review questions. Keyed on name alone so
-- every mention across the corpus resolves to one node.
CREATE ENTITY sercha_product.Stakeholder (
  person_name  TEXT KEY
    EXTRACT 'the person''s name as written; keep the form used in the document rather than expanding initials',
  discipline   TEXT CHECK (discipline IN
    ('product','engineering','design','research','data','support','sales','legal','security')),
  involvement  TEXT CHECK (involvement IN ('owner','reviewer','approver','contributor','informed'))
);

-- ---------- Edges ----------

-- External, FUZZY: the canonical case. A single feature is named four different
-- ways across the PRD, the spec, the roadmap row and the release note.
-- WITHIN bounds the judge to Feature nodes, so a feature name is never matched
-- against a person or a metric label.
CREATE EDGE sercha_product.about_feature
  ON sercha_product.ProductDocument ABOUT sercha_product.Feature
  LINK BY primary_feature_name RESOLVE FUZZY
  WITHIN sercha_product.Feature;

-- Same drift, one level down: a requirement names its feature in the PRD's
-- wording, which is not the wording the release note will use.
CREATE EDGE sercha_product.requires_feature
  ON sercha_product.Requirement SPECIFIES sercha_product.Feature
  LINK BY feature_name RESOLVE FUZZY
  WITHIN sercha_product.Feature;

-- Customer wording drifts furthest of all, so FUZZY again.
CREATE EDGE sercha_product.requests_feature
  ON sercha_product.CustomerRequest REQUESTS sercha_product.Feature
  LINK BY requested_feature_name RESOLVE FUZZY
  WITHIN sercha_product.Feature;

-- Self-referencing, EXACT: a decision record transcribes the superseded
-- identifier character for character from its header block.
CREATE EDGE sercha_product.supersedes
  ON sercha_product.ProductDocument SUPERSEDES sercha_product.ProductDocument
  LINK BY supersedes_ref RESOLVE EXACT;

-- Ownership. Names are written inconsistently enough to warrant FUZZY.
CREATE EDGE sercha_product.owned_by
  ON sercha_product.ProductDocument OWNED_BY sercha_product.Stakeholder
  LINK BY owner_name RESOLVE FUZZY
  WITHIN sercha_product.Stakeholder;

-- Internal edges: structural, resolved by position within one document.
CREATE EDGE sercha_product.states_requirement
  ON sercha_product.ProductDocument STATES sercha_product.Requirement;

CREATE EDGE sercha_product.targets_metric
  ON sercha_product.ProductDocument TARGETS sercha_product.SuccessMetric;

CREATE EDGE sercha_product.records_decision
  ON sercha_product.ProductDocument RECORDS sercha_product.Decision;

CREATE EDGE sercha_product.captures_request
  ON sercha_product.ProductDocument CAPTURES sercha_product.CustomerRequest;

CREATE CORPUS product_record
  ON confluence.product_space."Specs.Current",
     confluence.product_space."Decisions",
     gitfiles.docs_repo."product.releases"
  USING ONTOLOGY sercha_product
  PARTITION BY FOLDER LEVEL 1;

BIND CORPUS product_record TO ONTOLOGY sercha_product;

RUN BINDING product_record.sercha_product;

ALTER BINDING product_record.sercha_product
  SET HINT ON extract_internals WHERE root = ProductDocument AS
  'Classify doc_type from the document''s own header and structure, never from
   the folder it sits in: release notes and PRDs are routinely filed together in
   a feature folder. A document with a Decision and Consequences section is a
   decision_record even where it is titled RFC. A document that is a dated list
   of shipped changes is a release_note, whatever it is called.
   For primary_feature_name and for Requirement.feature_name, record the feature
   exactly as this document words it. Do not normalise across documents and do
   not guess at a canonical name: reconciling the wording is the job of the
   about_feature edge, and a helpful rewrite here destroys the evidence it needs.
   Treat every numbered or bulleted shall, must or should statement in a PRD or
   spec as a Requirement, including those under an Out of scope heading; set
   in_scope_first_release = false for those rather than dropping them.
   Only populate supersedes_ref where the document names the identifier it
   replaces, and copy it verbatim including any prefix and leading zeroes.
   Where a document states no success metrics at all, emit no SuccessMetric
   rather than a row of nulls.';

ALTER ENTITY sercha_product.Requirement ADD COLUMN estimated_effort TEXT;

RUN BINDING product_record.sercha_product
  EXTRACT (Requirement.estimated_effort);

SELECT f.feature_name,
       f.surface_area,
       f.lifecycle_stage,
       d.doc_type,
       d.doc_title,
       d.revision,
       d.authored_on
FROM product_record.ProductDocument d
JOIN product_record.Feature f VIA d.about_feature
WHERE f.lifecycle_stage IN ('beta','generally_available')
ORDER BY f.feature_name, d.authored_on ASC;

SELECT f.feature_name,
       COUNT(*)                        AS requests,
       COUNT(DISTINCT cr._doc)         AS across_reports,
       SUM(CASE WHEN cr.account_tier = 'enterprise' THEN 3
                WHEN cr.account_tier = 'mid_market' THEN 2
                ELSE 1 END)            AS weighted_pull,
       SUM(CASE WHEN cr.is_churn_risk = true THEN 1 ELSE 0 END) AS churn_flagged,
       ARRAY_AGG(cr.request_source)    AS sources
FROM product_record.CustomerRequest cr
JOIN product_record.Feature f VIA cr.requests_feature
WHERE cr.severity IN ('blocker','major')
GROUP BY f.feature_name
ORDER BY weighted_pull DESC;

SELECT r._doc,
       r._confidence,
       r._folder,
       r._run_id,
       d.doc_title,
       r.requirement_ref,
       r.priority,
       r.statement
FROM product_record.Requirement r
JOIN product_record.ProductDocument d USING (_doc)
WHERE r._confidence < 0.7
  AND r.priority = 'must_have'
ORDER BY r._confidence ASC
LIMIT 50;

SELECT newer.decision_ref     AS superseding_decision,
       newer.doc_title        AS superseding_doc,
       newer.authored_on      AS superseded_on,
       older.decision_ref     AS retired_decision,
       older.doc_title        AS retired_doc,
       dec.decision_summary,
       dec.reversibility
FROM product_record.ProductDocument newer
JOIN product_record.ProductDocument older VIA newer.supersedes
JOIN product_record.Decision dec ON dec._doc = older._doc
WHERE older.status <> 'superseded'
ORDER BY newer.authored_on DESC;

SELECT d.doc_title,
       d.target_release,
       r.requirement_ref,
       r.requirement_kind,
       r.priority,
       r.acceptance_criteria
FROM product_record.ProductDocument d
JOIN product_record.Requirement r VIA d.states_requirement
WHERE d MATCHES ('data residency regional storage sovereignty' USING default TOP 200)
  AND d.doc_type IN ('tech_spec','prd')
  AND d.status = 'approved'
  AND r.requirement_kind IN ('compliance','non_functional')
ORDER BY d.target_release, r.priority;

WITH shipped_features AS (
  SELECT DISTINCT f._id AS feature_id
  FROM product_record.ProductDocument rn
  JOIN product_record.Feature f VIA rn.about_feature
  WHERE rn.doc_type = 'release_note'
),
committed AS (
  SELECT r._id       AS requirement_id,
         r._doc      AS source_doc,
         r._folder   AS product_area,
         r.requirement_ref,
         r.statement,
         r.priority,
         d.doc_title,
         d.target_release,
         d.authored_on,
         f._id       AS feature_id,
         f.feature_name
  FROM product_record.Requirement r
  JOIN product_record.ProductDocument d USING (_doc)
  JOIN product_record.Feature f VIA r.requires_feature
  WHERE r.priority = 'must_have'
    AND r.in_scope_first_release = true
    AND d.doc_type = 'prd'
    AND d.status IN ('approved','shipped')
)
SELECT c.product_area,
       c.doc_title,
       c.target_release,
       c.requirement_ref,
       c.feature_name,
       c.statement,
       c.authored_on
FROM committed c
WHERE c.feature_id NOT IN (SELECT feature_id FROM shipped_features)
ORDER BY c.authored_on ASC;

SELECT s.person_name,
       s.discipline,
       COUNT(*)                     AS open_rfcs,
       MIN(d.decision_due_on)       AS oldest_due,
       ARRAY_AGG(d.doc_title)       AS documents
FROM product_record.ProductDocument d
JOIN product_record.Stakeholder s VIA d.owned_by
JOIN product_record.Decision dec ON dec._doc = d._doc
WHERE d.doc_type IN ('rfc','decision_record')
  AND dec.decision_status IN ('proposed','deferred')
  AND d.decision_due_on < DATE '2026-07-01'
GROUP BY s.person_name, s.discipline
ORDER BY open_rfcs DESC;

-- "The committed backlog" is a phrase the whole team uses; define it once.
CREATE OR REPLACE VIEW committed_requirements AS
SELECT r._id,
       r._doc,
       r._folder,
       d.doc_title,
       d.revision,
       d.target_release,
       d.authored_on,
       r.requirement_ref,
       r.statement,
       r.priority,
       r.requirement_kind,
       r.acceptance_criteria,
       f.feature_name,
       f.surface_area,
       f.lifecycle_stage
FROM product_record.Requirement r
JOIN product_record.ProductDocument d USING (_doc)
JOIN product_record.Feature f VIA r.requires_feature
WHERE r.priority IN ('must_have','should_have')
  AND r.in_scope_first_release = true
  AND d.doc_type IN ('prd','tech_spec')
  AND d.status IN ('approved','shipped');

-- One row per feature, joining what customers asked for to what was promised.
CREATE OR REPLACE VIEW feature_demand_profile AS
SELECT f.feature_name,
       f.surface_area,
       f.lifecycle_stage,
       f.is_customer_facing,
       COUNT(DISTINCT cr._id)  AS customer_requests,
       COUNT(DISTINCT cr._doc) AS feedback_reports,
       SUM(CASE WHEN cr.is_churn_risk = true THEN 1 ELSE 0 END) AS churn_linked_requests,
       COUNT(DISTINCT d._id)   AS documents_referencing
FROM product_record.Feature f
LEFT JOIN product_record.CustomerRequest cr VIA f.requests_feature
LEFT JOIN product_record.ProductDocument d VIA f.about_feature
GROUP BY f.feature_name, f.surface_area, f.lifecycle_stage, f.is_customer_facing;

-- The decision ledger, with the supersession chain flattened one hop.
CREATE OR REPLACE VIEW decision_ledger AS
SELECT d._doc,
       d._folder,
       d.decision_ref,
       d.doc_title,
       d.authored_on,
       d.status              AS document_status,
       dec.decision_summary,
       dec.decision_status,
       dec.decided_on,
       dec.reversibility,
       d.supersedes_ref      AS replaces_decision,
       s.person_name         AS owner
FROM product_record.ProductDocument d
JOIN product_record.Decision dec VIA d.records_decision
LEFT JOIN product_record.Stakeholder s VIA d.owned_by
WHERE d.doc_type IN ('rfc','decision_record');