id: ${hexCode}
created_at: ${datetime}
updated_at: ${datetime}
unique_source_url: ${url}
source_type: ${type}
completion_api_url: ${string}
raw_response_object: ${object} // could be string or object, need to think about querying for error response objects.
children_source_ids: ${array}
parent_source_ids: ${array}

id: ${hexCode}
site_uuid: ${uuid} // for disambiguation in case we have tons of citations and run out of hexCodes (won't happen soon, but it's a good idea to have this field)
created_at: ${datetime}
updated_at: ${datetime}
unique_source_url: ${url} // the unique url of the citation source
source_type: ${type} // the type of source (book, media, website, etc.)
referenced_in_instances: ${array} // an array of markdown files that reference this citation.
completion_api_url: ${string} // the API endpoint that will be used to complete this citation. (e.g. Google Books API, YouTube API, Jina API, BrowserBase API)
parent_source_ids: ${array} // an array of hexCodes of parent sources, if any.
children_source_ids: ${array} // an array of hexCodes of child sources, if any.
classifiers: ${id} or ${default_name} // the hexCode of the classifier, if any.

// db/config.ts
import { defineDb, defineTable, column } from 'astro:db';

// Define the Citation table with our simple field list
const CitedSources = defineTable({
  columns: {
    // Primary key using the hexCode as ID
    id: column.text({ primaryKey: true }),
    
    // Timestamps
    created_at: column.date({ default: () => new Date() }),
    updated_at: column.date({ default: () => new Date() }),
    
    // Basic citation metadata
    unique_source_url: column.text(),
    source_type: column.text(),

    // Designated API endpoint for completion
    completion_api_url: column.text(),
    
    // Store the raw API response as JSON
    raw_response_object: column.json(),
    
    // Relationships (stored as JSON arrays)
    children_source_ids: column.json({ default: () => [] }),
    parent_source_ids: column.json({ default: () => [] }),
  }
});

// Export the database configuration
export default defineDb({
  tables: { CitedSources },
});

// site/src/utils/citations/citationRegistry.ts
import { db } from 'astro:db';
import type { CitedSources } from 'astro:db';

/**
 * Add a new citation to the registry
 * @param citation - CitedSources object without timestamps
 * @returns The newly created citation
 */
export async function addCitation(citation: Omit<CitedSources, 'created_at' | 'updated_at'>) {
  return await db.insert('CitedSources').values({
    ...citation,
    // created_at and updated_at will use the default values
  }).returning();
}

/**
 * Get a citation by its ID
 * @param id - The hexCode ID of the citation
 * @returns The citation or undefined if not found
 */
export async function getCitation(id: string) {
  return await db.select().from('CitedSources').where({ id }).get();
}

/**
 * Update an existing citation
 * @param id - The hexCode ID of the citation
 * @param data - Partial citation data to update
 * @returns The updated citation
 */
export async function updateCitation(id: string, data: Partial<Omit<CitedSources, 'id' | 'created_at'>>) {
  return await db.update('CitedSources').set({
    ...data,
    updated_at: new Date(),
  }).where({ id }).returning();
}

/**
 * Get all citations in the registry
 * @returns Array of all citations
 */
export async function getAllCitations() {
  return await db.select().from('CitedSources');
}

/**
 * Check if a citation exists by URL
 * @param url - The URL to check
 * @returns The citation if found, undefined otherwise
 */
export async function getCitationByUrl(url: string) {
  return await db.select().from('CitedSources').where({ unique_source_url: url }).get();
}

/**
 * Load the citation registry
 * @returns The number of citations loaded
 */
export async function loadCitationRegistry() {
  const citations = await getAllCitations();
  console.log(`Loaded ${citations.length} citations from registry`);
  return citations.length;
}

// scripts/process-citations.ts
import fs from 'fs';
import path from 'path';
import { unified } from 'unified';
import remarkParse from 'remark-parse';
import remarkStringify from 'remark-stringify';
import { visit } from 'unist-util-visit';
import { addCitation, getCitation, updateCitation, getCitationByUrl, loadCitationRegistry } from '../site/src/utils/citations/citationRegistry';

// Import debugging utilities
import { MarkdownDebugger } from '../site/src/utils/markdown/markdownDebugger';
const mdDebugger = new MarkdownDebugger();

// Set up environment variables for debugging
process.env.DEBUG_CITATIONS = process.env.DEBUG_CITATIONS || 'false';
process.env.DEBUG_CITATIONS_VERBOSE = process.env.DEBUG_CITATIONS_VERBOSE || 'false';

/**
 * Process citations in a markdown file
 * @param filePath - Path to the markdown file
 * @returns Object with counts of processed citations
 */
async function processCitations(filePath: string) {
  // Load the citation registry first
  await loadCitationRegistry();
  
  const stats = {
    total: 0,
    new: 0,
    updated: 0,
    unchanged: 0,
  };
  
  // Read the markdown file
  const content = fs.readFileSync(filePath, 'utf-8');
  
  // Parse the markdown
  const processor = unified()
    .use(remarkParse)
    .use(() => (tree) => {
      // Find all footnote references
      visit(tree, 'footnoteReference', (node) => {
        stats.total++;
        
        // Check if this is a hex citation (e.g., [^a1b2c3])
        const hexCode = node.identifier;
        const isHexCitation = /^[0-9a-f]{6}$/.test(hexCode);
        
        if (isHexCitation) {
          // Process hex citation
          processCitation(hexCode, filePath);
        }
      });
      
      return tree;
    });
  
  // Process the markdown
  await processor.process(content);
  
  return stats;
}

/**
 * Process a single citation
 * @param hexCode - The citation hex code
 * @param filePath - The file containing the citation
 */
async function processCitation(hexCode: string, filePath: string) {
  try {
    // Check if citation exists
    const existingCitation = await getCitation(hexCode);
    
    if (existingCitation) {
      // Update the citation with the new file reference
      const referencedIn = existingCitation.referenced_in_instances || [];
      
      // Add the file if it's not already in the list
      if (!referencedIn.includes(filePath)) {
        referencedIn.push(filePath);
        await updateCitation(hexCode, { referenced_in_instances: referencedIn });
        
        if (mdDebugger.isEnabled) {
          console.log(`Updated citation ${hexCode} with new reference: ${filePath}`);
        }
      }
    } else {
      // Create a new citation
      await addCitation({
        id: hexCode,
        unique_source_url: '', // Will be populated later by API
        source_type: 'unknown', // Will be determined later
        completion_api_url: '',
        raw_response_object: {},
        children_source_ids: [],
        parent_source_ids: [],
        referenced_in_instances: [filePath],
      });
      
      if (mdDebugger.isEnabled) {
        console.log(`Added new citation ${hexCode} from file: ${filePath}`);
      }
    }
  } catch (error) {
    console.error(`Error processing citation ${hexCode}:`, error);
  }
}

/**
 * Process all markdown files in a directory
 * @param dirPath - Path to the directory
 * @returns Object with counts of processed citations
 */
async function processDirectory(dirPath: string) {
  const stats = {
    files: 0,
    citations: {
      total: 0,
      new: 0,
      updated: 0,
      unchanged: 0,
    },
  };
  
  // Read all files in the directory
  const files = fs.readdirSync(dirPath);
  
  for (const file of files) {
    const fullPath = path.join(dirPath, file);
    const stat = fs.statSync(fullPath);
    
    if (stat.isDirectory()) {
      // Recursively process subdirectories
      const subStats = await processDirectory(fullPath);
      stats.files += subStats.files;
      stats.citations.total += subStats.citations.total;
      stats.citations.new += subStats.citations.new;
      stats.citations.updated += subStats.citations.updated;
      stats.citations.unchanged += subStats.citations.unchanged;
    } else if (file.endsWith('.md')) {
      // Process markdown files
      stats.files++;
      const fileStats = await processCitations(fullPath);
      stats.citations.total += fileStats.total;
      stats.citations.new += fileStats.new;
      stats.citations.updated += fileStats.updated;
      stats.citations.unchanged += fileStats.unchanged;
    }
  }
  
  return stats;
}

/**
 * Main function to process citations
 * @param target - File or directory to process
 */
async function main(target: string) {
  console.log(`Processing citations in ${target}`);
  
  try {
    const stat = fs.statSync(target);
    
    if (stat.isDirectory()) {
      const stats = await processDirectory(target);
      console.log(`Processed ${stats.files} files with ${stats.citations.total} citations`);
      console.log(`- New: ${stats.citations.new}`);
      console.log(`- Updated: ${stats.citations.updated}`);
      console.log(`- Unchanged: ${stats.citations.unchanged}`);
    } else if (target.endsWith('.md')) {
      const stats = await processCitations(target);
      console.log(`Processed file with ${stats.total} citations`);
      console.log(`- New: ${stats.new}`);
      console.log(`- Updated: ${stats.updated}`);
      console.log(`- Unchanged: ${stats.unchanged}`);
    } else {
      console.error('Target must be a markdown file or directory');
      process.exit(1);
    }
  } catch (error) {
    console.error('Error processing citations:', error);
    process.exit(1);
  }
}

// Check if target is provided
const target = process.argv[2];

if (!target) {
  console.error('Please provide a target file or directory');
  process.exit(1);
}

// Run the script
main(target);

# Process a single file
node scripts/process-citations.js path/to/file.md

# Process a directory
node scripts/process-citations.js path/to/directory