Skip to main content

Source: ocean/docs/adr/0051-ebisu-graphql-integration.md | ✏️ Edit on GitHub

ADR-0051: GraphQL Integration for Ebisu Reference Data

Status​

Accepted

Context​

Ocean needs to display and interact with extensive reference data from the Ebisu database, including:

  • Geographic data (countries, FAO areas, RFMOs)
  • Vessel types and hull materials
  • Gear types (FAO, CBP, MSC standards)
  • Species taxonomies (ASFIS, WoRMS, ITIS)
  • Industry data (MSC fisheries, country profiles)

With Ebisu maintained as a separate repository (ADR-049), we faced a critical decision: how should Ocean components access this data without creating tight coupling between the repositories?

The challenge was that without exposing Ebisu data through our API layer, developers would need to:

  • Import Drizzle schemas directly from Ebisu
  • Handle cross-repository TypeScript compilation
  • Manage database connections from the frontend
  • Deal with version mismatches between repositories

Decision​

We will expose ALL Ebisu reference data through Ocean's GraphQL API. This creates a clean, typed interface between Ocean's UI components and Ebisu's data warehouse.

Implementation Approach​

  1. Comprehensive Schema Extension

    • Created schema-ebisu.ts with complete type definitions for all Ebisu tables
    • Exposed all reference data types through GraphQL
    • Maintained consistent naming with Ebisu's database schema

  2. Dedicated Resolvers

    • Created ebisu-resolvers.ts with query resolvers for all data types
    • Implemented filtering, searching, and pagination
    • Added relationship resolvers for linked data (e.g., country profiles → countries)

  3. Connection Architecture

    • Ebisu database connection configured via EBISU_DATABASE_URL environment variable
    • Connection pooling managed at the Edge Function level
    • Separate database client in GraphQL context

Extension Process for New Ebisu Types​

When new reference data is added to Ebisu, follow these steps:

1. Update GraphQL Schema​

Add the new type to schema-ebisu.ts:

type NewReferenceType {
  id: ID!
  source_id: ID
  field_one: String!
  field_two: String
  created_at: String!
  updated_at: String!
  source: OriginalSource
}

extend type Query {
  new_reference_types(
    search: String
    filter_field: String
    limit: Int
    offset: Int
  ): [NewReferenceType!]!

  new_reference_type(id: ID!): NewReferenceType
}

2. Add Resolver​

Update ebisu-resolvers.ts:

new_reference_types: async (_: unknown, args: any, context: Context) => {
  const client = await getEbisuClient(context)

  let query = 'SELECT * FROM new_reference_table WHERE 1=1'
  const params: any[] = []

  // Add filters based on args
  if (args.search) {
    params.push(`%${args.search}%`)
    query += ` AND name ILIKE $${params.length}`
  }

  // Add pagination
  if (args.limit) {
    params.push(args.limit)
    query += ` LIMIT $${params.length}`
  }

  const result = await client.queryObject(query, params)
  return result.rows
}

3. Generate TypeScript Types​

Run the GraphQL code generation:

pnpm run codegen:graphql

This generates typed interfaces in src/generated/graphql.ts.

4. Create Query Hook​

Add a custom hook for the new data type:

// src/hooks/graphql/use-new-reference-type.ts
import { useGraphQLQuery } from '@/hooks/use-graphql-query'

const NEW_REFERENCE_TYPE_QUERY = `
  query GetNewReferenceTypes($search: String, $limit: Int) {
    new_reference_types(search: $search, limit: $limit) {
      id
      field_one
      field_two
    }
  }
`

export function useNewReferenceTypeQuery(variables?: any) {
  return useGraphQLQuery({
    query: NEW_REFERENCE_TYPE_QUERY,
    variables,
    queryKey: ['new-reference-types', variables],
    staleTime: 1000 * 60 * 60 * 24, // 24 hours for reference data
  })
}

5. Build Components​

Use the generated types and hooks in components:

import { useNewReferenceTypeQuery } from '@/hooks/graphql/use-new-reference-type'
import type { NewReferenceType } from '@/generated/graphql'

export function NewReferenceTypeSelector() {
  const { data, loading, error } = useNewReferenceTypeQuery()

  // Component implementation using typed data
}

Consequences​

Positive​

  • Type Safety: Full TypeScript types generated from GraphQL schema
  • Clean Architecture: Clear separation between data and UI layers
  • Single Source of Truth: GraphQL schema defines the API contract
  • Performance: Built-in caching with TanStack Query
  • Flexibility: Easy to add new fields or types without breaking existing code
  • Developer Experience: IntelliSense and type checking for all Ebisu data

Negative​

  • Schema Maintenance: Must update GraphQL schema when Ebisu schema changes
  • Additional Layer: GraphQL adds complexity compared to direct database access
  • Resolver Code: Need to write resolvers for each data type
  • Testing: Requires mocking GraphQL responses for component tests

Neutral​

  • Code Generation: Must run codegen when schema changes
  • Learning Curve: Developers must understand GraphQL patterns
  • Performance Overhead: GraphQL parsing adds minimal latency

Implementation Details​

Current Exposed Data Types​

Geographic & Regulatory:

  • Countries (ISO codes, names)
  • FAO Major Areas
  • RFMOs (Regional Fisheries Management Organizations)

Vessel Data:

  • Vessel Types (ISSCFV classification)
  • Hull Materials

Gear Types:

  • FAO Gear Types (ISSCFG)
  • CBP Gear Types
  • MSC Gear Types

Species Taxonomies:

  • ASFIS Species (commercial species)
  • WoRMS Taxon (marine species taxonomy)
  • ITIS Species (integrated taxonomic information)

Industry Data:

  • MSC Certified Fisheries
  • Country Profiles (fisheries statistics)

Query Patterns​

All queries support:

  • Pagination (limit, offset)
  • Search functionality
  • Field-specific filtering
  • Relationship resolution

Caching Strategy​

Reference data changes infrequently, so we use aggressive caching:

const REFERENCE_DATA_OPTIONS = {
  staleTime: 1000 * 60 * 60 * 24, // 24 hours
  gcTime: 1000 * 60 * 60 * 24 * 7, // 7 days
  refetchOnWindowFocus: false,
  refetchOnReconnect: false,
}

Example: Vessel Type Lookup​

Here's a complete example of building a vessel type lookup component:

// GraphQL Query
query VesselTypes($search: String) {
  vessel_types(search: $search, limit: 100) {
    id
    vessel_type_cat
    vessel_type_subcat
    vessel_type_isscfv_code
    vessel_type_isscfv_alpha
  }
}

// Component
export function VesselTypeLookup({ onSelect }: { onSelect: (type: VesselType) => void }) {
  const [search, setSearch] = useState('')
  const { data, loading } = useVesselTypesQuery({ search })

  return (
    <>
      <Input
        placeholder="Search vessel types..."
        value={search}
        onChange={(e) => setSearch(e.target.value)}
      />
      {loading ? (
        <LoadingSpinner />
      ) : (
        <div>
          {data?.vessel_types.map((type) => (
            <button
              key={type.id}
              onClick={() => onSelect(type)}
              className="block w-full text-left p-2 hover:bg-accent"
            >
              <div className="font-medium">{type.vessel_type_cat}</div>
              <div className="text-sm text-muted-foreground">
                {type.vessel_type_subcat} • {type.vessel_type_isscfv_code}
              </div>
            </button>
          ))}
        </div>
      )}
    </>
  )
}

Migration Path​

For any existing components that might be importing Ebisu types directly:

  1. Remove direct imports from @ocean/ebisu-database
  2. Add appropriate GraphQL queries
  3. Generate TypeScript types
  4. Update components to use GraphQL hooks
  5. Test with mocked GraphQL responses

Security Considerations​

  • Ebisu connection uses read-only credentials
  • All queries are parameterized to prevent SQL injection
  • GraphQL depth limiting prevents expensive queries
  • Authentication still required for GraphQL endpoint
  • Row-level security not needed (reference data is public)

References​