Graph Schema

LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT * FROM cypher('oilgas', $$
  MATCH (w:Well)-[:LOCATED_IN]->(l:Lease {lease_name: 'Smith Ranch'})
  RETURN w.well_name, w.api_number, w.status
$$) AS (well_name agtype, api_number agtype, status agtype);

SELECT * FROM cypher('oilgas', $$
  MATCH (w:Well {api_number: '42-123-45678'})-[:OFFSET_TO]->(offset:Well)
  RETURN offset.well_name, offset.api_number, offset.operator
$$) AS (well_name agtype, api_number agtype, operator agtype);

SELECT * FROM cypher('oilgas', $$
  MATCH (w:Well {api_number: '42-123-45678'})-[:PRODUCES_TO]->(f:Facility)-[:FEEDS_INTO]->(p:PipelineRoute)
  RETURN w.well_name, f.facility_name, p.route_name
$$) AS (well_name agtype, facility_name agtype, route_name agtype);

SELECT * FROM cypher('oilgas', $$
  MATCH (fe:FlaringEvent)-[:AUTHORIZED_UNDER]->(fa:FlaringAuthorization)
  WHERE fa.expiration_date < '2026-05-01'
  MATCH (fe)-[:FLARES_AT]->(w:Well)
  RETURN w.well_name, fa.auth_number, fa.expiration_date
$$) AS (well_name agtype, auth_number agtype, expiration_date agtype);

SELECT * FROM cypher('oilgas', $$
  MATCH (entity)-[:OPERATED_BY]->(op:Operator {operator_name: 'Permian Basin Energy LLC'})
  RETURN labels(entity) AS type, entity.entity_id, entity
$$) AS (type agtype, entity_id agtype, entity agtype);

SELECT * FROM cypher('oilgas', $$
  MATCH (w:Well {api_number: '42-383-40121'})-[:HAS_DEADLINE]->(d:ComplianceDeadline)
  RETURN d.deadline_date, d.rule_reference
  ORDER BY d.deadline_date
$$) AS (deadline_date agtype, rule_reference agtype);

GET /context/assemble/managed/{entity_id}
GET /context/assemble/{well_api}

async def run_cypher(pool, query: str, graph: str = "oilgas"):
    async with pool.acquire() as conn:
        await conn.execute("LOAD 'age'")
        await conn.execute('SET search_path = ag_catalog, "$user", public')
        result = await conn.fetch(
            f"SELECT * FROM cypher('{graph}', $$ {query} $$) AS (result agtype)"
        )
        return result