Consuming and Processing JSON Stream Data in an API Webhook using C#

Recently, I was tasked with developing a custom webhook for our API that would consume a specific client-supplied point-of-sale (POS) JSON stream, parse the data, and write it to a series of custom SQL tables. The provided POS JSON stream includes detailed item information, payment type, customer ID, and client-specific transaction identifiers. This process required efficiently handling large, continuous data streams while ensuring that the data was processed accurately and securely stored in the appropriate database tables.

The first step was to break down the high-level tasks and produce a Level of Effort (LOE) estimate. While I won't go into detail on each step here, the primary stages included Requirements Gathering and Planning, Webhook Setup, Stream Deserialization and Validation, Database Design and Mapping, SQL Database Work, Testing and Debugging, and finally, Deployment and Documentation.

Below is a very brief breakdown of the development process based on this sample client JSON.

{
  "OrderID": "123",
  "OrderType": "Cash",
  "SubTotal": 8.24,
  "StoreID": 0,
  "CashierID": -99,
  "CustomerID": 3481142,
  "Time": "2020-03-21T10:17:46.3093832-04:00",
  "SalesTax": 0.0,
  "Total": 8.24,
  "TransactionTypeID": 0,
  "Comment": "",
  "TransactionNumber": 56355116,
  "Status": 0,
  "TransactionEntries": [
    {
      "ID": 1,
      "TransactionNumber": 56355116,
      "ItemID": 96197,
      "description": "aspirin",
      "Cost": 0.0,
      "FullPrice": 2.99,
      "Price": 2.99,
      "Quantity": 1.0,
      "Taxable": false,
      "SalesTax": 0.0,
      "TaxAmount": 0.0,
      "Comment": "",
      "DateCreated": "2021-05-10T10:17:26.692389-04:00",
      "Notes": "",
      "ItemLookupCode": "002414763521",
      "SellByWeight": false,
      "CategoryName": "Misc.",
      "OptionDescription": null,
      "OptionDescriptionPrice": null
    },
    {
      "ID": 2,
      "TransactionNumber": 56355116,
      "ItemID": 96100,
      "description": "tylenol",
      "Cost": 0.0,
      "FullPrice": 5.25,
      "Price": 5.25,
      "Quantity": 1.0,
      "Taxable": false,
      "SalesTax": 0.0,
      "TaxAmount": 0.0,
      "Comment": "",
      "DateCreated": "2021-05-10T10:17:26.692389-04:00",
      "Notes": "",
      "ItemLookupCode": "123456763521",
      "SellByWeight": false,
      "CategoryName": "Misc.",
      "OptionDescription": null,
      "OptionDescriptionPrice": null
    }
  ]
}

Step 1: Setup a new Webhook and Controller
To set up the webhook and its corresponding controller, I configured a route in the app.MapControllerRoute method as follows:

app.MapControllerRoute
(
  name: "POSTransactionExportCL",
  pattern: "{controller}/{action}",
  defaults: new { controller = "TheClientControllerName", action = "POSTransactionExportCL" }
);

Note: TheClientControllerName is a placeholder for the actual controller name used in the application. I've replaced the actual name in this example for clarity and confidentiality.

Step 2: Define Model Classes for the JSON Stream
To parse the JSON stream provided by the client, I created two model classes: POSTransactionExportCL_Order and POSTransactionExportCL_TransactionEntry. These classes define the structure of the JSON data and include all the necessary properties for orders and their associated transaction entries. By using these models, the JSON stream is deserialized into a strongly typed structure, ensuring accurate data processing, and simplifying further operations in the application.

Order Model

public class POSTransactionExportCL_Order
{
  public int OrderID { get; set; }
  public string? OrderType { get; set; }
  public decimal SubTotal { get; set; }
  public int StoreID { get; set; }
  public int CashierID { get; set; }
  public int CustomerID { get; set; }
  public DateTime Time { get; set; }
  public decimal SalesTax { get; set; }
  public decimal Total { get; set; }
  public int TransactionTypeID { get; set; }
  public string? Comment { get; set; }
  public long TransactionNumber { get; set; }
  public int Status { get; set; }
  public POSTransactionExportCL_TransactionEntry[]? TransactionEntries { get; set; }
}

Transaction Entry Model

public class POSTransactionExportCL_TransactionEntry
{
  public int ID { get; set; }
  public long TransactionNumber { get; set; }
  public int ItemID { get; set; }
  public string? Description { get; set; }
  public decimal Cost { get; set; }
  public decimal FullPrice { get; set; }
  public decimal Price { get; set; }
  public decimal Quantity { get; set; }
  public bool Taxable { get; set; }
  public decimal SalesTax { get; set; }
  public decimal TaxAmount { get; set; }
  public string? Comment { get; set; }
  public DateTime DateCreated { get; set; }
  public string? Notes { get; set; }
  public string? ItemLookupCode { get; set; }
  public bool SellByWeight { get; set; }
  public string? CategoryName { get; set; }
  public string? OptionDescription { get; set; }
  public decimal? OptionDescriptionPrice { get; set; }
}

Step 3: Created a New "POSTransactionExportCL" Webhook to Handle Incoming Transaction Data from the Client JSON Stream
The POSTransactionExportCL webhook in the client controller is designed to securely and efficiently handle incoming transaction data provided in a JSON stream via POST HTTP requests. Acting as a gateway for processing and storing point-of-sale (POS) transaction details, this webhook ensures that data integrity and security remain top priorities throughout its workflow.

This endpoint validates the client's access token (X-Auth-Token), verifies payload integrity, and stores transaction data in the backend database. The processing pipeline includes token validation, payload deserialization, and database interaction via SQL methods. With comprehensive error handling and logging mechanisms, the webhook provides clear feedback to the client in the event of issues, such as invalid tokens, unauthorized access, or payload formatting errors.

The following code snippet provides the complete implementation of the POSTransactionExportCL webhook:

[HttpPost]
[Route("{controller}/{action}")]
public async Task<IActionResult> POSTransactionExportCL()
{
  string? client_token = Request.Headers["X-Auth-Token"]; // client passed in header - this value is generated from GetPatientData
  string? BodyContent = string.Empty;
  string? actionName = Request.Path; // get the IActionResult controller and name (/Base/IActionResult)

  var config = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json"); // read appsettings.json for config settings
  var configuration = config.Build();

  // do the regular token processing
  if (!GeneralFunctions.IsCPTokenValid(client_token!)) // JWT token from the header
  {
    return new ContentResult() { Content = "Unauthorized - X-Auth-Token is invalid", StatusCode = 401 };
  }

  // make sure the client has access to the webhook
  if (!GeneralFunctions.HasEndpointAccess(actionName.Split('/')[2])) // get everything after the second / and check HasEndpointAccess
  {
    GeneralFunctions.LogEndpointCall(actionName.Split('/')[2], $"{client_token} Endpoint access denied: {actionName.Split('/')[2]}");
    return new ContentResult() { Content = $"{client_token} Endpoint access denied", StatusCode = 401 };
  }


  JObject obj; // instantiate empty JObject for reading and processing Body payload
  StreamReader? reader = null; // instantiate empty nullable StreamReader variable
  try
  {
    var httpResponseMessage = HttpContext.Request.Body;
    using(reader = new StreamReader(httpResponseMessage))
      {
        BodyContent = await reader.ReadToEndAsync(); // read the response body
    }

    obj = JObject.Parse(BodyContent); // just to see if we get an error processing the body
    if (!obj.HasValues)
    {
      GeneralFunctions.LogEndpointCall(actionName.Split('/')[2], $"Error: the payload appreasrs to not contain any values");
      return new ContentResult() { Content = $"Error: the payload appreasrs to not contain any values", StatusCode = 500 };
    }

    // check payload parameters
    string payloaderror = String.Empty;
    try
    {
      if ((string?)obj.SelectToken("OrderID") == "" || (string?)obj.SelectToken("OrderID") == String.Empty || (string?)obj.SelectToken("OrderID") == null || string.IsNullOrEmpty((string?)obj.SelectToken("OrderID"))) { payloaderror = "OrderID"; }
      if ((string?)obj.SelectToken("OrderType") == "" || (string?)obj.SelectToken("OrderType") == String.Empty || (string?)obj.SelectToken("OrderType") == null || string.IsNullOrEmpty((string?)obj.SelectToken("OrderType"))) { payloaderror = "OrderType"; }
      if ((string?)obj.SelectToken("CustomerID") == "" || (string?)obj.SelectToken("CustomerID") == String.Empty || (string?)obj.SelectToken("CustomerID") == null || string.IsNullOrEmpty((string?)obj.SelectToken("CustomerID"))) { payloaderror = "CustomerID"; }
      if ((string?)obj.SelectToken("TransactionNumber") == "" || (string?)obj.SelectToken("TransactionNumber") == String.Empty || (string?)obj.SelectToken("TransactionNumber") == null || string.IsNullOrEmpty((string?)obj.SelectToken("TransactionNumber"))) { payloaderror = "TransactionNumber"; }
        if (payloaderror != String.Empty)
        {
        GeneralFunctions.LogEndpointCall(actionName.Split('/')[2], $"{payloaderror} - {payloaderror} missing from payload {JObject.Parse(BodyContent)}");
        return new ContentResult() { Content = $"{payloaderror} - {payloaderror} missing from payload", StatusCode = 422 };
        }
      }
      catch (Exception)
      {
      GeneralFunctions.LogEndpointCall(actionName.Split('/')[2], $"error processing payload: {JObject.Parse(BodyContent)}");
      return new ContentResult() { Content = "error processing payload", StatusCode = 422 };
    }


    POSTransactionExportCL_Order? orderinfo; // declares a variable named orderinfo that will hold an instance of both POSTransactionExportCL_Order/POSTransactionExportCL_TransactionEntry classes
    try
    {
      // deserialize the JSON stream (body) into the orderinfo object
      orderinfo = JsonConvert.DeserializeObject<POSTransactionExportCL_Order>(BodyContent);
    }
    catch (JsonException ex)
    {
      return new ContentResult() { Content = $"Invalid JSON format: {ex.Message}", StatusCode = 422 };
    }

    try
    {
      // call the POSTransactionExportCLSQL SQL processing method
      // write everything to the POSTransactionExportCL table
      // write everything to the POSTransactionExportCLTransactionEntries table
      // using cp_APIPOSTransactionExportCL and cp_APIPOSTransactionEntryExportCL
      string status = await POSTransactionExportCLSQL(orderinfo!);
    }
    catch (Exception ex)
    {
      return new ContentResult() { Content = $"Error: POSTransactionExportCLSQL: {ex.Message}", StatusCode = 422 };
      }
    }
    catch (Exception)
  {
    GeneralFunctions.LogEndpointCall(actionName.Split('/')[2], $"Error reading payload: {JObject.Parse(BodyContent)}");
    return new ContentResult() { Content = "Error reading payload", StatusCode = 500 };
  }

  return new ContentResult() { Content = $"POSTransactionExportCL - success - {client_token}", StatusCode = 200 };
}

At its core, the webhook performs essential operations such as validating access tokens, verify that the client has access to the webhook, verify payload integrity, and write transaction data to specific database tables. With a focus on security, it checks the client's authentication and authorization before processing any request. The incoming payload undergoes validation to ensure the required data is present and formatted correctly. Any anomalies in the process are logged for traceability, and detailed error responses are provided to the client for troubleshooting.

Below is an overview of its key components:

Attributes and Routing

• [HttpPost]: The method handles POST requests.
• [Route("{controller}/{action}")]: Defines a dynamic route based on the controller and action name.

Primary Steps

1. Token Validation

• The client sends an X-Auth-Token in the request headers.

• The token is validated using GeneralFunctions.IsCPTokenValid.

• If the token is invalid, the method returns a 401 Unauthorized response.

1. Access Control

• Extracts the endpoint name from the request path.

• Uses GeneralFunctions.HasEndpointAccess to determine if the token has access to the requested endpoint.

• Logs unauthorized attempts and returns a 401 response if access is denied.

1. Payload Parsing

• Reads the request body and ensures it's valid JSON using JObject.Parse.

• Checks for required fields (OrderID, OrderType, CustomerID, TransactionNumber).

• Logs and returns an error if required fields are missing.

1. Deserialization

• Converts the JSON payload into a POSTransactionExportCL_Order object using JsonConvert.DeserializeObject.

• Handles any deserialization errors gracefully with appropriate logging.

1. Database Interaction

• Processes the order and its transactions using POSTransactionExportCLSQL.

• Writes data into tables (POSTransactionExportCL and POSTransactionExportCLTransactionEntries) via stored procedures.

1. Error Handling

• Comprehensive error handling is implemented at every stage, including token validation, payload parsing, and database interaction.

• Logs specific error details for debugging and returns appropriate HTTP status codes to the client.

1. Response Codes

• 200 OK: Indicates successful processing.

• 401 Unauthorized: Token validation or access control failure.

• 422 Unprocessable Entity: Errors in payload structure or content.

• 500 Internal Server Error: Unexpected errors during payload reading or processing.

Key Highlights

• Dynamic Configuration: Reads configuration from appsettings.json for flexibility.
• Logging: Logs all significant events and errors, aiding in troubleshooting and monitoring.
• Validation-Driven Workflow: Ensures token authenticity, endpoint permissions, and payload integrity before proceeding.
• Modular Structure: Separates concerns like token validation, payload parsing, and database interaction for better maintainability.

Step 4: The SQL Method
The POSTransactionExportCLSQL method is responsible for exporting point-of-sale (POS) order data and its associated transaction entries to the SQL database.

public static async Task<string> POSTransactionExportCLSQL(POSTransactionExportCL_Order orderinfo)
{
  int rowsAffected = 0;
  string retval = string.Empty;
  string pkid = string.Empty;

  if (orderinfo == null)
  {
    return "Invalid order information provided.";
  }


  try
  {
    var config = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json"); // read appsettings.json for config settings
    var configuration = config.Build();
    string? _sql_con_str = configuration["sqlsettings:cphdbintegrated"];

    using (var _sql_con = new System.Data.SqlClient.SqlConnection(_sql_con_str))
    {
      await _sql_con.OpenAsync();

      // insert order data (from orderinfo class)
      using (var _sql_cmd = new System.Data.SqlClient.SqlCommand("cp_APIPOSTransactionExportCL", _sql_con))
      {
        _sql_cmd.CommandType = System.Data.CommandType.StoredProcedure;

        // Add Order parameters
        _sql_cmd.Parameters.AddWithValue("@OrderID", orderinfo.OrderID);
        _sql_cmd.Parameters.AddWithValue("@OrderType", orderinfo.OrderType);
        _sql_cmd.Parameters.AddWithValue("@SubTotal", orderinfo.SubTotal);
        _sql_cmd.Parameters.AddWithValue("@StoreID", orderinfo.StoreID);
        _sql_cmd.Parameters.AddWithValue("@CashierID", orderinfo.CashierID);
        _sql_cmd.Parameters.AddWithValue("@CustomerID", orderinfo.CustomerID);
        _sql_cmd.Parameters.AddWithValue("@Time", orderinfo.Time);
        _sql_cmd.Parameters.AddWithValue("@SalesTax", orderinfo.SalesTax);
        _sql_cmd.Parameters.AddWithValue("@Total", orderinfo.Total);
        _sql_cmd.Parameters.AddWithValue("@TransactionTypeID", orderinfo.TransactionTypeID);
        _sql_cmd.Parameters.AddWithValue("@Comment", orderinfo.Comment);
        _sql_cmd.Parameters.AddWithValue("@TransactionNumber", orderinfo.TransactionNumber);
        _sql_cmd.Parameters.AddWithValue("@Status", orderinfo.Status);

        // Execute the query and read the result
        using (var reader = await _sql_cmd.ExecuteReaderAsync())
        {
          if (await reader.ReadAsync())
          {
            pkid = reader["theGUID"].ToString()!;
          }
        }
      }

      if (IsNull(pkid)) { return "error - parent pkid is null"; }

      using (var _sql_cmd = new System.Data.SqlClient.SqlCommand("cp_APIPOSTransactionEntryExportCL", _sql_con))
      {
        _sql_cmd.CommandType = System.Data.CommandType.StoredProcedure;

        // insert transactionentries data (from POSTransactionExportCL_TransactionEntry)
        foreach (var entry in orderinfo.TransactionEntries!)
        {
          _sql_cmd.Parameters.Clear();

          // Add TransactionEntry parameters
          _sql_cmd.Parameters.AddWithValue("@pkid", pkid);
          _sql_cmd.Parameters.AddWithValue("@ID", entry.ID);
          _sql_cmd.Parameters.AddWithValue("@OrderID", orderinfo.OrderID);
          _sql_cmd.Parameters.AddWithValue("@TransactionNumber", entry.TransactionNumber);
          _sql_cmd.Parameters.AddWithValue("@ItemID", entry.ItemID);

          _sql_cmd.Parameters.AddWithValue("@Description", entry.Description ?? "");

          _sql_cmd.Parameters.AddWithValue("@Cost", entry.Cost);
          _sql_cmd.Parameters.AddWithValue("@FullPrice", entry.FullPrice);
          _sql_cmd.Parameters.AddWithValue("@Price", entry.Price);
          _sql_cmd.Parameters.AddWithValue("@Quantity", entry.Quantity);
          _sql_cmd.Parameters.AddWithValue("@Taxable", entry.Taxable);
          _sql_cmd.Parameters.AddWithValue("@SalesTax", entry.SalesTax);
          _sql_cmd.Parameters.AddWithValue("@TaxAmount", entry.TaxAmount);
          _sql_cmd.Parameters.AddWithValue("@Comment", entry.Comment ?? "");
          _sql_cmd.Parameters.AddWithValue("@DateCreated", entry.DateCreated);
          _sql_cmd.Parameters.AddWithValue("@Notes", entry.Notes ?? "");
          _sql_cmd.Parameters.AddWithValue("@ItemLookupCode", entry.ItemLookupCode ?? "");
          _sql_cmd.Parameters.AddWithValue("@SellByWeight", entry.SellByWeight);
          _sql_cmd.Parameters.AddWithValue("@CategoryName", entry.CategoryName ?? "");
          _sql_cmd.Parameters.AddWithValue("@OptionDescription", entry.OptionDescription ?? "");
          _sql_cmd.Parameters.AddWithValue("@OptionDescriptionPrice", entry.OptionDescriptionPrice ?? 0);

          await _sql_cmd.ExecuteNonQueryAsync();
        }
      }


    }
  }
  catch (Exception ex)
  {
    // log the error to the error logging table
    GeneralFunctions.LogError("POSTransactionExportCLSQL", ex, orderinfo);
    return $"Error: {ex.Message}";
  }

  return rowsAffected.ToString();
}

Here is an overview of its key operations:

Input Validation:

• Validates the orderinfo parameter to ensure it contains valid order data. If null, it returns an error message immediately.

Database Connection Setup:

• Reads connection settings from an appsettings.json file using ConfigurationBuilder.
• Establishes a SQL connection using the provided connection string.

Order Data Export:

• Executes the stored procedure cp_APIPOSTransactionExportCL to insert order-related data into the database. Parameters such as OrderID, OrderType, SubTotal, and others are mapped from the orderinfo object.
• Captures a primary key (theGUID) returned by the stored procedure for use in subsequent operations.

Transaction Entries Export:

• Iterates through the TransactionEntries collection within the orderinfo object.
• For each transaction entry, executes the cp_APIPOSTransactionEntryExportCL stored procedure to store detailed transaction data, including item details, pricing, tax information, and additional metadata.

Error Handling:

• Wraps operations in a try-catch block to handle exceptions.
• Logs any errors encountered using the GeneralFunctions.LogError method and returns a descriptive error message.

Return Value:

• Returns the number of rows affected or an appropriate error message.

Use Cases
This method is well-suited for scenarios requiring:

• Batch processing of POS orders and their line items into a database.
• Structured logging of errors for troubleshooting.
• Integration with a broader POS or order management system.

Considerations:

• The method relies on two stored procedures for database operations, promoting modularity.
• SQL injection risks are mitigated by using parameterized queries.

Step 5: The SQL Table Structure

drop table if exists [POSTransactionExportCLTransactionEntries]
go

drop table if exists [POSTransactionExportCL]
go


-- parent table (Order Entries)
create table [POSTransactionExportCL]
(
  [pkid] uniqueidentifier not null unique,
  [OrderID] int,
  [OrderType] nvarchar(50),
  [SubTotal] decimal(18, 2),
  [StoreID] int,
  [CashierID] int,
  [CustomerID] int,
  [Time] datetime,
  [SalesTax] decimal(18, 2),
  [Total] decimal(18, 2),
  [TransactionTypeID] int,
  [Comment] nvarchar(255),
  [TransactionNumber] bigint,
  [Status] int,
  primary key ([pkid]) -- define primary key (parent)
  )
go



-- child table (Transaction Entries)
create table [POSTransactionExportCLTransactionEntries]
(
  [pkid] uniqueidentifier not null, -- this will be the pkid value from POSTransactionExportCL
  [ID] int,
  [OrderID] int,
  [TransactionNumber] bigint,
  [ItemID] int,
  [Description] nvarchar(255) null,
  [Cost] decimal(18, 2) null,
  [FullPrice] decimal(18, 2) null,
  [Price] decimal(18, 2) null,
  [Quantity] decimal(18, 2) null,
  [Taxable] bit null,
  [SalesTax] decimal(18, 2) null,
  [TaxAmount] decimal(18, 2) null,
  [Comment] nvarchar(255) null,
  [DateCreated] datetime null,
  [Notes] nvarchar(255) null,
  [SellByWeight] bit null,
  [CategoryName] nvarchar(50) null,
  [OptionDescription] nvarchar(255) null,
  [OptionDescriptionPrice] decimal(18, 2) null,
  foreign key ([pkid]) references [POSTransactionExportCL] ([pkid]) -- define foreign key relationship (Child)
)

go

The table structure defines a parent-child relationship between POSTransactionExportCL and POSTransactionExportCLTransactionEntries, designed for managing point-of-sale (POS) transactions and their associated entries.

Parent Table: POSTransactionExportCL
This table serves as the central repository for order-level transaction data. Each row uniquely identifies an order using the pkid column, a uniqueidentifier serving as the primary key. Key fields include OrderID, OrderType, StoreID, CashierID, and financial details such as SubTotal, SalesTax, and Total. It provides a high-level overview of transactions.

Child Table: POSTransactionExportCLTransactionEntries
This table stores detailed line-item entries associated with transactions in the parent table. The pkid column in this table is a foreign key referencing the pkid in the parent table, establishing the relationship. Each row represents an individual item or transaction entry, including details such as ItemID, Description, Price, Quantity, and Taxable status. It supports a comprehensive breakdown of items sold, returned, or discounted.

Key Features:

• The parent table enforces referential integrity through a primary key on pkid.
• The child table maintains a foreign key constraint on pkid, ensuring that all entries correspond to a valid transaction in the parent table.
• This structure supports efficient querying and reporting, enabling quick joins between orders and their detailed line items.

Step 6: The SQL Stored Procedures
cp_APIPOSTransactionExportCL
This stored procedure is responsible for populating the POSTransactionExportCL table with detailed transaction data from a Point of Sale (POS) system. It takes multiple parameters representing key attributes of a transaction, such as order information, store and cashier IDs, customer details, and transaction metadata.

drop procedure if exists [dbo].[cp_APIPOSTransactionExportCL]
go


set ansi_nulls on
go

set quoted_identifier on
go



create procedure [dbo].[cp_APIPOSTransactionExportCL]
  @OrderID int,
  @OrderType nvarchar(50),
  @SubTotal decimal(18, 2),
  @StoreID int,
  @CashierID int,
  @CustomerID int,
  @Time datetime,
  @SalesTax decimal(18, 2),
  @Total decimal(18, 2),
  @TransactionTypeID int,
  @Comment nvarchar(max),
  @TransactionNumber nvarchar(50),
  @Status nvarchar(50)
as
begin
    set nocount on;
    begin try
      declare @theGUID as uniqueidentifier = newid()
      declare @p_namedtransaction varchar(60) = 'cp_APIPOSTransactionExportCL'

      begin transaction @p_namedtransaction
        insert into POSTransactionExportCL
        (
          [pkid],
          [OrderID],
          [OrderType],
          [SubTotal],
          [StoreID],
          [CashierID],
          [CustomerID],
          [Time],
          [SalesTax],
          [Total],
          [TransactionTypeID],
          [Comment],
          [TransactionNumber],
          [Status]
        )
        values
        (
          @theGUID,
          @OrderID,
          @OrderType,
          @SubTotal,
          @StoreID,
          @CashierID,
          @CustomerID,
          @Time,
          @SalesTax,
          @Total,
          @TransactionTypeID,
          @Comment,
          @TransactionNumber,
          @Status
        )
      commit transaction @p_namedtransaction
      select @theGUID as [theGUID] -- return for cp_APIPOSTransactionEntryExportCL
    end try
    begin catch
      if @@trancount > 0
        rollback transaction @p_namedtransaction;
      throw;
    end catch
end
go

Key Features:

1. Primary Purpose:

• Inserts a new record into the POSTransactionExportCL table using the provided parameters.
• Assigns a unique identifier (uniqueidentifier) to each transaction for tracking and reference.

1. Transactional Safety:

• Uses an explicit named transaction (@p_namedtransaction) to ensure atomicity. This prevents partial inserts by rolling back the transaction in case of an error.
• Includes structured error handling with a TRY...CATCH block to handle potential issues during execution.

1. Parameters:

• The procedure accepts a range of input parameters, such as:
• Order Details: @OrderID, @OrderType, @SubTotal, @SalesTax, @total.
• Entity Identifiers: @StoreID, @CashierID, @CustomerID.
• Transaction Metadata: @time, @TransactionTypeID, @Comment, @TransactionNumber, @Status.

1. Output:

• Returns the generated unique identifier (@theGUID) for the newly inserted record. This @theGUID variable is then used by cp_APIPOSTransactionEntryExportCL, to insert records into the POSTransactionExportCLTransactionEntries child table.

1. Resiliency:

• The NOCOUNT setting is enabled to reduce overhead from row count messages.
• Proper use of BEGIN TRANSACTION and COMMIT TRANSACTION ensures data integrity.

1. Benefits

• Data Integrity: The use of transactions ensures that either all or none of the data is written.
• Scalability: Designed to handle a variety of transaction scenarios, from in-store to online orders.
• Traceability: The generated unique identifier (@theGUID) allows easy identification and troubleshooting.

This procedure plays a critical role in maintaining a reliable record of POS transactions, which is essential for reporting, auditing, and analytics.

POSTransactionExportCLTransactionEntries
This stored procedure is designed to populate the POSTransactionExportCLTransactionEntries table with detailed order transaction entries, breaking down the specifics of each item within a transaction and complements the cp_APIPOSTransactionExportCL procedure, which logs the high-level transaction details.

drop procedure if exists [dbo].[cp_APIPOSTransactionEntryExportCL]
go

set ansi_nulls on
go

set quoted_identifier on
go


create procedure [dbo].[cp_APIPOSTransactionEntryExportCL]
  @pkid uniqueidentifier,
  @ID int,
  @OrderID int,
  @TransactionNumber bigint,
  @ItemID int,
  @Description nvarchar(255),
  @Cost decimal(18, 2),
  @FullPrice decimal(18, 2),
  @Price decimal(18, 2),
  @Quantity decimal(18, 2),
  @Taxable bit,
  @SalesTax decimal(18, 2),
  @TaxAmount decimal(18, 2),
  @Comment nvarchar(255),
  @DateCreated datetime,
  @Notes nvarchar(255),
  @ItemLookupCode nvarchar(50),
  @SellByWeight bit,
  @CategoryName nvarchar(50),
  @OptionDescription nvarchar(255),
  @OptionDescriptionPrice decimal(18, 2)
as
begin
  set nocount on;
    begin try
      declare @theGUID as uniqueidentifier = newid()
      declare @p_namedtransaction varchar(60) = 'cp_APIPOSTransactionExportCL'

      begin transaction @p_namedtransaction
        insert into [POSTransactionExportCLTransactionEntries]
        (
          [pkid],
          [ID],
          [OrderID],
          [TransactionNumber],
          [ItemID],
          [Description],
          [Cost],
          [FullPrice],
          [Price],
          [Quantity],
          [Taxable],
          [SalesTax],
          [TaxAmount],
          [Comment],
          [DateCreated],
          [Notes],
          [ItemLookupCode],
          [SellByWeight],
          [CategoryName],
          [OptionDescription],
          [OptionDescriptionPrice]
        )
        values
        (
          @pkid,
          @ID,
          @OrderID,
          @TransactionNumber,
          @ItemID,
          @Description,
          @Cost,
          @FullPrice,
          @Price,
          @Quantity,
          @Taxable,
          @SalesTax,
          @TaxAmount,
          @Comment,
          @DateCreated,
          @Notes,
          @ItemLookupCode,
          @SellByWeight,
          @CategoryName,
          @OptionDescription,
          @OptionDescriptionPrice
        )
      commit transaction @p_namedtransaction
      select @theGUID as [theGUID] -- return this value for cp_APIPOSTransactionEntryExportCL
    end try
    begin catch
      if @@trancount > 0
        rollback transaction @p_namedtransaction;
      throw;
    end catch
end
go

Key Features:

1. Purpose:

• Inserts itemized transaction details into the POSTransactionExportCLTransactionEntries table.
• Handles granular data, including pricing, taxes, discounts, quantities, and metadata for individual items in an order.

1. Parameters:

• Transaction Identifiers: @pkid links the entry to the primary transaction, while @id and @OrderID help identify the item within the context of the transaction.
• Item-Specific Details: @ItemID, @Description, @Cost, @price, @FullPrice, @Taxable, and @Quantity describe the item and its financial attributes.
• Additional Metadata: Fields like @Notes, @ItemLookupCode, @CategoryName, and @OptionDescription add context for reporting or customer service.

1. Transactional Integrity:

• The procedure employs a named transaction to ensure that the insertion is atomic, meaning it is either fully successful or fully rolled back in case of an error.
• A unique identifier (@theGUID) is generated and returned for referencing the specific entry.

1. Error Handling:

• A TRY...CATCH block ensures that any errors encountered during execution result in a rollback of the transaction, maintaining data integrity.

1. Output:

• The unique identifier (@theGUID) of the inserted entry is returned, enabling seamless linking and traceability.

1. Benefits

• Comprehensive Tracking: Captures detailed item-level data for every transaction, crucial for inventory management, tax calculations, and customer service.
• Flexibility: The numerous parameters allow the procedure to handle a wide range of item types and transaction scenarios.
• Auditability: By linking to the primary transaction (@pkid) and generating unique identifiers, it supports clear and traceable records.

This procedure is a critical component of the POS system, ensuring that all transaction details are recorded and accessible for business operations, analysis, and compliance purposes.

A Note About Using System.Data.SqlClient
In this system, I have chosen to use System.Data.SqlClient for database interactions due to its ability to provide high-performance, low-level access to the SQL Server database. This approach is particularly important in scenarios where I need to process large JSON streams efficiently and with minimal latency and minimizes overhead and provides the flexibility required for performance optimization.

While Entity Framework is good for working with databases in higher-level applications, its abstraction layers can introduce unnecessary overhead, which can be a limitation when the goal is to maximize speed, optimization, and resource utilization. By opting for System.Data.SqlClient, I avoid this overhead for more efficient database access and faster processing times - both key factors when dealing with high-performance tasks such as JSON stream processing and data-intensive operations.

In summary, the use of System.Data.SqlClient ensures that the system can handle complex, performance-critical database tasks with greater precision and speed, allowing for the optimization of stored procedure execution and overall system responsiveness.

Conclusion
Developing a custom webhook to process a client-supplied POS JSON stream required a strategic approach to ensure efficiency, accuracy, and security. By breaking down the task into manageable stages - from webhook setup and model definition to SQL database design and stored procedure creation, I was able to deliver a robust solution that seamlessly integrates with the client's data stream.

Using strongly typed C# model classes and carefully crafted SQL structures, the implementation ensures data consistency and simplifies debugging. Additionally, the modular design of the webhook also provides a foundation for extending functionality and supporting additional future integrations.