SEPURI-SAI-KRISHNA opened a new issue, #13189: URL: https://github.com/apache/seatunnel/issues/13189
### Search before asking - [X] I had searched in the [feature](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22Feature%22) and found no similar feature requirement. I checked `dev`, every open PR, and all issues and PRs since June 2025 for NetSuite and SuiteQL: there is no module, plugin mapping, docs, claim or PR. #10753 lists the row as "Todo". ### Description ## Overview Design proposal for a new **NetSuite source** (Connector V2) for the "NetSuite SuiteAnalytics" row in #10753. It reads NetSuite records with SuiteQL through NetSuite's REST web services, as a `connector-http-netsuite` module on `connector-http-base`, like the Mailchimp, Typeform and Intercom sources. SuiteQL covers every record type (customers, transactions, items, custom records) with one connector, so I would not build one reader per record type. The row is named after SuiteAnalytics; this design reads through SuiteQL over REST, not through SuiteAnalytics Connect (ODBC/JDBC). I have no NetSuite account, and NetSuite has no free developer account. Every API fact below comes from Oracle's documentation, with links. The points the documentation does not settle are listed in section 9, and the design handles them by failing loudly instead of guessing. ## 1. v1 scope - **Bounded only**, one table per job, one split. Streaming is rejected. - **Endpoint:** `POST https://<account>.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=<n>&offset=<n>`, with the header `Prefer: transient` and the body `{"q": "<SuiteQL>"}` ([Executing SuiteQL Queries Through REST](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_157909186990.html)). - **Host:** built from `account_id` ([account-specific URLs](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_1546938065.html): "if your account ID is 123456, your account-specific domain for REST web services is 123456.suitetalk.api.netsuite.com"). An optional `base_url` overrides it, which the tests also use. ## 2. Reading more than 100,000 rows This is the main design point. Oracle states: "Using SuiteQL queries, you can return a maximum of 100,000 results." Offset paging over one query therefore stops at 100,000 rows. A connector that only follows `hasMore` would load a partial table with no error. v1 has two modes: - **Table mode (default):** options `table`, `key_column` (default `id`), and an optional `filter` (a SuiteQL condition). The columns come from the declared `schema`. Each page is a new query: `SELECT <columns> FROM <table> WHERE (<filter>) AND <key_column> > <last key> ORDER BY <key_column>`, with `limit=<page_size>` and `offset=0`. No query ever reaches the cap, and `ORDER BY` makes the order explicit. NetSuite 2026.2 changed the default sort of generic transaction queries, so the connector cannot rely on a default order. The last key is a number the connector parsed, so it is written into the query as a number. Bound `params` arrived only in NetSuite 2026.2, so v1 does not depend on them. The key must be a unique integer, which the default `id` (the internal ID) is; with a repeated key, rows could be skipped at a page boundary. The reader fails if a key is not an integer, or if a page's keys are not strictly increasing. - **Query mode:** option `query`, a full SuiteQL statement, read with `limit`/`offset` paging. If the response reports `totalResults` above 100,000, the job fails before reading and points to table mode. It never stops silently at the cap. `page_size` defaults to 1000, the page size NetSuite's [collection paging](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156414087576.html) uses. ## 3. Authentication - **OAuth 2.0 client credentials (machine to machine) only** ([token endpoint](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_162755359851.html), [JWT structure](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_162790605110.html)). The connector signs a JWT (header `typ`, `alg`, `kid` = certificate ID; claims `iss` = client ID, `scope` = `rest_webservices`, `aud` = the token URL, `iat`, `exp`) and posts it as `client_assertion` with `grant_type=client_credentials`. - Options: `client_id`, `certificate_id`, `private_key` (PKCS#8 PEM), `algorithm` (`PS256` default, or `ES256`). - The access token is valid for 60 minutes and there is no refresh token. The connector requests a new one shortly before expiry, and once on a `401`. - **NetSuite setup, in the docs:** the REST Web Services and OAuth 2.0 features enabled; an integration record with "Client Credentials (Machine to Machine) Grant" and the REST Web Services scope; a certificate mapped to an entity, role and application ([OAuth 2.0 Client Credentials Setup](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_162686838198.html)); and a role with REST Web Services, Log in using OAuth 2.0 Access Tokens and SuiteAnalytics Workbook ([Roles and Permission Considerations for APIs](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/article_0320025211.html)). A `401` or `403` from NetSuite fails the job with a message that points to this setup. - **No TBA (OAuth 1.0).** Oracle's [authentication overview](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/chapter_4369651411.html) says "you should use OAuth 2.0 for REST web services authentication" and "TBA support ends in NetSuite 2028.2." - **No new dependency for signing.** The JDK has both algorithms (`RSASSA-PSS` and `SHA256withECDSA`; ES256 needs the DER signature converted to the raw 64-byte form). I checked this with a standalone probe on JDK 11: 20 PS256 and 60 ES256 tokens signed with JDK classes only all verified with PyJWT, and a token with a changed signature was rejected. ## 4. Values and schema The documented responses return every column value as a JSON string, for example `"creditlimit": "8000"` and `"id": "1725"`. The user declares the schema, and the reader converts each value: - Numbers and decimals are parsed strictly; a value that does not parse fails the job with the table, column and value. - **Booleans:** NetSuite checkbox values are not shown in Oracle's examples. The shared JSON format would read `"T"` as `false` (`Boolean.parseBoolean`), which would be silent data loss, so the reader converts booleans itself. It accepts `T`/`F` and `true`/`false`, and fails on anything else. - **Dates:** Oracle's examples show no date column, so I do not rely on any default format. In table mode the connector selects a `DATE` column as `TO_CHAR(<col>, 'YYYY-MM-DD') AS <col>` and a `TIMESTAMP` column as `TO_CHAR(<col>, 'YYYY-MM-DD HH24:MI:SS') AS <col>`. In query mode the docs ask users to do the same. The reader accepts only those two formats and fails on anything else. - **Column names:** Oracle's [SuiteQL limitations](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156257796125.html) say that the casing of field names in results "may be inconsistent and can change after a NetSuite release". The reader matches columns case-insensitively. A column missing from an item is read as NULL. ## 5. Secret boundary The private key is only in the `private_key` option, the JWT only in the token request, and the access token only in the `Authorization` header. None of them are logged, put in error messages, or stored in checkpoint state. Errors include the HTTP status and NetSuite's error body, never request headers. A unit test checks that no log line or exception message contains the key or token. ## 6. Retries, limits and memory - NetSuite limits concurrent requests per account. [Concurrency governance](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_1500275531.html) gives a base of 5 for development and partner accounts. v1 sends one request at a time. - The reader retries `429`, `5xx` and I/O errors with bounded exponential backoff, using the existing retry options, then fails with a clear error. The status NetSuite returns for a concurrency rejection on this endpoint is not documented (section 9). - Requests time out after the configured timeout. Oracle's limit for one request is 15 minutes. - One page at a time is held in memory. ## 7. Checkpoint and delivery Single split, nothing persisted in v1. After a failure the read starts again from the beginning, so rows can be read twice, and every row carries its key for an upsert or deduplication downstream. No exactly-once claim. Persisting the last key, so a restored job continues where it stopped, and reading key ranges in parallel would be a later slice, if maintainers want them. ## 8. Testing without an account - **Unit tests with `MockWebServer`:** a token endpoint that verifies the JWT signature, claims and form fields against a test key pair; keyset paging across several pages; a terminal empty page; the 100,000 guard in query mode; non-increasing keys; value conversion (including `T`/`F`, bad numbers and missing columns); case-insensitive columns; token renewal on `401`; retries on `429` and `5xx`; no secret in logs or errors. - **E2E:** a SeaTunnel job on every engine against a mock HTTP server serving NetSuite-shaped responses, with an injected `429`. No account or network access needed. - The PR will separate what the mocks prove from what needs a real account, and I would be glad if someone with a NetSuite sandbox could run the example config once. ## 9. Not settled by the documentation 1. Sandbox host names (account IDs like `123456_SB1`). `base_url` covers them in any case. 2. How checkbox and date values look in SuiteQL REST results, and whether a NULL column is omitted or sent as `null`. Section 4 handles each case strictly. 3. Whether `TO_CHAR` with those two formats works in SuiteQL as in Oracle SQL, and which time zone a formatted timestamp is in. v1 maps it to `TIMESTAMP` without a zone, and the docs will say so. 4. The HTTP status for a concurrency rejection on the SuiteQL endpoint. RESTlets return `400` with `SSS_REQUEST_LIMIT_EXCEEDED`. If the endpoint uses `400` too, the reader should retry that error code and nothing else. ## 10. Dependencies, license and deliverables No new runtime dependency. Test scope only: `mockwebserver`, as in the other HTTP sources. Deliverables: the module and its parent pom entry, `plugin-mapping.properties` (`seatunnel.source.NetSuite = connector-http-netsuite`), `plugin_config` and `seatunnel-dist/pom.xml`, EN and ZH docs with an example, unit tests and the E2E. ## Out of scope for v1 TBA, SOAP, RESTlets, SuiteAnalytics Connect (ODBC/JDBC), saved searches, sinks, incremental state between runs, resume from the last key, parallel splits, and streaming. ### Usage Scenario Finance and operations teams keep their ERP data in NetSuite and want customers, transactions, items or custom records in a warehouse or database for reporting, without CSV exports. NetSuite's REST paging stops at 100,000 rows per query, which is often too few for transaction tables, so the source has to page past it. ### Related issues Tracking issue #10753 (NetSuite SuiteAnalytics row). Same pattern as the Typeform (#12822, #12844) and Intercom (#12823, #12831) sources. ### Are you willing to submit a PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
